HostOnNet Blog

MariaDB Too many connections

On CentOS 7 server using MariaDB 10.1.21 site stopped working with following error.

[root@freexblogs ~]# mysql -u freexblogs -pSHwz5DST@jBaxr
ERROR 1040 (08004): Too many connections
[root@freexblogs ~]# 

This is because by default MySQL only allow 151 connections

[root@freexblogs ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22277
Server version: 10.1.21-MariaDB MariaDB Server

Copyright (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

vi /etc/my.cnf.d/server.cnf 

Add max_connections = 500 under [mysqld] section.

[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

service mysql restart

Verify max_connections limit increased.

[root@freexblogs ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 489
Server version: 10.1.21-MariaDB MariaDB Server

Copyright (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 ~]# 

Posted in MySQL