How to deal with “Too Many Connections” error in MySQL

Easy Solution Web : MySQL Tutorials

Too Many Connections issue is very common in MySQL which occurs mainly due too many users have come to your website and demanding to connect to your database

Basic details

Default number of connections are available to connect with the MySQL is 151, previously it was 100.
Max number of connection is allowed to connect with max_connection+1, One is reserved for the super user.
Firstly, we can change the no. of allowed connection to connect to MySQL
To change the no of connection allowed to connect the MySQL can be changed if you have super admin privilege.
It is mandatory to have super admin access to MySQL server to change a global variable in MySQL.

Ways to resolve too many connections issue in MySQL

Syntax to increase the no connection allowed to connect the MySQL is:-

Mysql> SET GLOBAL max_connections = 1000;
Mysql> SET @@global.max_connections = 1000;

Secondly, you can free your connection pool using below syntax:-

Mysql> FLUSH HOSTS;

It will remove all the connection which is currently connected to the mysqld.

Thirdly, you can analyze all the connection by observing the process-list.
To get the current processlist you can use below mentioned syntax:-

Mysql> SHOW FULL PROCESSLIST\G; 

Sample Output:

*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave
I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 3112
User: replikator
Host: artemis:2204
db: NULL
Command: Binlog Dump
Time: 2144
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 3113
User: replikator
Host: iconnect2:45781
db: NULL
Command: Binlog Dump
Time: 2086
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 5. row ***************************
Id: 3123
User: stefan
Host: localhost
db: apollon
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
5 rows in set (0.00 sec) 

 

Now to kill any specific process you can use

MySQL> Kill connection 3113;