On CentOS 7 server using MariaDB 10.1.21 site stopped working with following error.
1 2 3 | [root@freexblogs ~]# mysql -u freexblogs -pSHwz5DST@jBaxrERROR 1040 (08004): Too many connections[root@freexblogs ~]# |
This is because by default MySQL only allow 151 connections
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [root@freexblogs ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 22277Server version: 10.1.21-MariaDB MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show variables like '%connection%';+---------------------------+-----------------+| Variable_name | Value |+---------------------------+-----------------+| character_set_connection | utf8 || collation_connection | utf8_general_ci || default_master_connection | || extra_max_connections | 1 || max_connections | 151 || max_user_connections | 0 |+---------------------------+-----------------+6 rows in set (0.00 sec)MariaDB [(none)]> Bye[root@freexblogs ~]# |
To fix, edit file
1 | vi /etc/my.cnf.d/server.cnf |
Add max_connections = 500 under [mysqld] section.
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | [root@freexblogs ~]# cat /etc/my.cnf.d/server.cnf ## These groups are read by MariaDB server.# Use it for options that only the server (but not clients) should see## See the examples of server my.cnf files in /usr/share/mysql/## this is read by the standalone daemon and embedded servers[server]# this is only for the mysqld standalone daemon[mysqld]max_connections = 500## * Galera-related settings#[galera]# Mandatory settings#wsrep_on=ON#wsrep_provider=#wsrep_cluster_address=#binlog_format=row#default_storage_engine=InnoDB#innodb_autoinc_lock_mode=2## Allow server to accept connections on all interfaces.##bind-address=0.0.0.0## Optional setting#wsrep_slave_threads=1#innodb_flush_log_at_trx_commit=0# this is only for embedded server[embedded]# This group is only read by MariaDB servers, not by MySQL.# If you use the same .cnf file for MySQL and MariaDB,# you can put MariaDB-only options here[mariadb]# This group is only read by MariaDB-10.1 servers.# If you use the same .cnf file for MariaDB of different versions,# use this group for options that older servers don't understand[mariadb-10.1]You have mail in /var/spool/mail/root[root@freexblogs ~]# |
Now restart MySQL
1 | service mysql restart |
Verify max_connections limit increased.
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 | [root@freexblogs ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 489Server version: 10.1.21-MariaDB MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show variables like 'max_connections%';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 500 |+-----------------+-------+1 row in set (0.00 sec)MariaDB [(none)]> Bye[root@freexblogs ~]# |
