Recently I ran a command in mysql that resulted in this error:

mysql: [ERROR] unknown variable 'sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

So I had to add a line to the mysql config file. In Ubuntu 16:

# nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following line under mysqld


[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Restart mysql:

# systemctl restart mysql

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
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Location of other .cnf files you can use:

/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

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, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack

MariaDB [(none)]> SHOW VARIABLES LIKE '%buffer%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| aria_pagecache_buffer_size            | 134217728 |
| aria_sort_buffer_size                 | 134217728 |
| bulk_insert_buffer_size               | 8388608   |
| innodb_blocking_buffer_pool_restore   | OFF       |
| innodb_buffer_pool_instances          | 1         |
| innodb_buffer_pool_populate           | OFF       |
| innodb_buffer_pool_restore_at_startup | 0         |
| innodb_buffer_pool_shm_checksum       | ON        |
| innodb_buffer_pool_shm_key            | 0         |
| innodb_buffer_pool_size               | 134217728 |
| innodb_change_buffering               | all       |
| innodb_log_buffer_size                | 8388608   |
| join_buffer_size                      | 131072    |
| join_buffer_space_limit               | 2097152   |
| key_buffer_size                       | 16777216  |
| mrr_buffer_size                       | 262144    |
| myisam_sort_buffer_size               | 8388608   |
| net_buffer_length                     | 8192      |
| preload_buffer_size                   | 32768     |
| read_buffer_size                      | 262144    |
| read_rnd_buffer_size                  | 524288    |
| sort_buffer_size                      | 524288    |
| sql_buffer_result                     | OFF       |
+---------------------------------------+-----------+

Lets find out the RAM:

# free -b
              total        used        free      shared  buff/cache   available
Mem:     3975184384   978608128  1691045888     9445376  1305530368  2661937152

Lets get our data together.

RAM = 3975184384
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_length, query_cache_size
or, from above…
Global Buffers: 16777216 + 134217728 + 8388608 + 0 + 8192 + 0 = 159391744

Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack
or, from above…
Thread Buffers: 524288 + 8388608 + 262144 + 131072 + 524288 + 0 = 9830400

With this information, the following is the calculation:
max_connections = (Available RAM – Global Buffers) / Thread Buffers
max_connections = (3975184384 – 159391744) / 9830400

So the formula shows 378 Max Connections on this machine

Test info with mysqltuner

Log into your server with a root or sudo user via SSH.
Download MySQLTuner by executing the following command:


wget -O mysqltuner.pl https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl --no-check-certificate

Give the script 775 permissions:


chmod 775 mysqltuner.pl

Run the script with the following command:


perl mysqltuner.pl

Resources:
Handy Calculator Download: https://journeyontux.wordpress.com/2011/12/22/calculate-number-of-connections-for-mysql-server/
Another Calculator: http://www.mysqlcalculator.com/

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:
[bash]

mysql> SELECT table_schema AS “Database name”, SUM(data_length + index_length) / 1024 / 1024 AS “Size (MB)” FROM information_schema.TABLES GROUP BY table_schema;
+——————–+————-+
| Database name | Size (MB) |
+——————–+————-+
| information_schema | 0.00878906 |
| mysql | 0.62860394 |
| performance_schema | 0.00000000 |
| roundcube | 0.46875000 |
+——————–+————-+
8 rows in set (1.21 sec)
[bash]

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 change the above line as follow:


sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER,,NO_ENGINE_SUBSTITUTION"

Restart the MySQL service.

You may be able to run an SQL query within your database management tool such as phpMyAdmin which can normally be found from your web hosting control panel:


SET @@global.sql_mode= '';

Test

Run the following:

#mysql -uroot -p -e 'select @@GLOBAL.sql_mode;'
+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+

First – run mysqltuner:

]# ./mysqltuner.pl
 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
 >>  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 on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 445K (Tables: 9)
[--] Data in InnoDB tables: 204M (Tables: 13)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'sysadmin@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17d 19h 48m 54s (1M q [0.865 qps], 87K conn, TX: 39B, RX: 258M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 432.0M global + 6.5M per thread (151 max threads)
[OK] Maximum reached memory usage: 913.0M (49.61% of installed RAM)
[OK] Maximum possible memory usage: 1.4G (76.81% of installed RAM)
[OK] Slow queries: 0% (1/1M)
[OK] Highest usage of available connections: 49% (74/151)
[OK] Aborted connections: 2.80%  (2442/87276)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 271K sorts)
[!!] Temporary tables created on disk: 71% (80K on disk / 114K total)
[OK] Thread cache hit rate: 94% (4K created / 87K connections)
[!!] Table cache hit rate: 2% (115 open / 5K opened)
[OK] Open file limit used: 9% (99/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] Binlog cache memory access: 99.95% ( 52384 Memory / 52412 Total)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.3% (49M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/291.0K
[OK] Read Key buffer hit rate: 100.0% (9M cached / 510 reads)
[!!] Write Key buffer hit rate: 1.4% (1K cached / 1K writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/204.8M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
&#91;OK&#93; InnoDB Used buffer: 87.50% (7167 used/ 8191 total)
&#91;OK&#93; InnoDB Read buffer efficiency: 99.97% (88883891 hits/ 88912014 total)
&#91;OK&#93; InnoDB Write log efficiency: 96.95% (869671 hits/ 897014 total)
&#91;OK&#93; InnoDB log waits: 0.00% (0 waits / 27343 writes)

-------- ThreadPool Metrics --------------------------------------------------
&#91;--&#93; ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
&#91;--&#93; AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
&#91;--&#93; TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
&#91;--&#93; Galera is disabled.

-------- Replication Metrics -------------------------------------------------
&#91;--&#93; No replication slave(s) for this server.
&#91;--&#93; This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache ( 256)
Variables to adjust:
    query_cache_type (=1)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 256)
    innodb_buffer_pool_size (>= 204M) if possible.
    innodb_buffer_pool_instances (=1)

Here is what you should do. First run this query



mysql> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    -> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    -> FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     1 |
+-------+
1 row in set (0.08 sec)

This will give you the RIBPS, Recommended InnoDB Buffer Pool Size (in GB)based on all InnoDB Data and Indexes with an additional 60%.

After the restart, run mysql for a week or two. Then, run this query:


SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

This will give you how many actual pages of InnoDB data reside in the InnoDB Buffer Pool.

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 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, ‘http://www.oldurl’,’http://www.newurl’);
Query OK, 0 rows affected (0.02 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.oldurl’, ‘http://www.newurl’);
Query OK, 0 rows affected (0.05 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://www.oldurl’,’http://www.newurl’);g
Query OK, 0 rows affected (0.01 sec)
Rows matched: 686 Changed: 0 Warnings: 0

# 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 |       |
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+

Or from command line

#  mysql -u root -p wp_database -e "EXPLAIN SELECT * FROM posts"