MariaDB cnf files

Here is the original /etc/my.cnf file for a default mariadb installation: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the…

How do you calculate mysql max_connections variable

The basic formulas are: Available RAM = Global Buffers + (Thread Buffers x max_connections) max_connections = (Available RAM - Global Buffers) / Thread Buffers To get the list of buffers and their values: SHOW VARIABLES LIKE '%buffer%'; Here's a list of the buffers and whether they're Global or Thread: Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size Thread Buffers: sort_buffer_size,…

List sizes of MySQL databases

Do you wonder which databases are actually taking up how much space but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don't represent the actual database sizes? Run from a mysql root console: mysql> SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP…

How do I disable MySQL strict mode on the server?

Please follow the under mentioned instructions to turn off the MySQL strict mode. Make the following changes in the "my.ini/my.cnf": Look for the following line: sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Change it to: sql_mode="" Restart the MySQL service. or Look for the following line , this line will set MySQL strict mode sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" To disable MySQL strict mode, you can…

How large should be mysql innodb_buffer_pool_size?

First - run mysqltuner: ]# ./mysqltuner.pl >> MySQLTuner 1.6.4 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.6.29-log [OK] Operating…

Change and Update WordPress URLS in Database When Site is Moved to new Host

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl'; UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl'); UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl'); UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl'); mysql> UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl'; Query OK, 0 rows affected (0.00…

Explain mysql statement

# mysql -u root -p MariaDB [(none)]> wp_database -e "EXPLAIN SELECT * FROM posts" EXPLAIN SELECT * FROM 2qGr3sjm_posts; +------+-------------+----------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 2519…

Left Join on mysql

SQL LEFT JOIN Syntax SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name; or: SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name; PS! In some databases LEFT JOIN is called LEFT OUTER JOIN. Example: You have a surveys table and a services table and the id's are both client_id select surveys.date, surveys.client_id, surveys.score from surveys left join services…