MySQL Too many connections MySQL Too many connections MySQL Too many connections
Can not connect to MySQL server. Too many connections “- MySQL error is annoying. This is basically losing control of MySQL. This tutorial details a variety of ways to handle this error.
sudo mysql -uroot -p
ERROR 1040 (00000): Too many connections
Copy the codeThis tutorial will explain the causes of these errors.
- How do I check the MySQL connection status?
- How do I check whether the current MySQL connection pool is full?
- Limit the timeout method, shorten the sleep time, so that the system faster recovery connection.
- How to change the maximum number of connections in the configuration file to ensure smooth connections.
- Firewire rescue method, do not need to restart, do not need to log in MySQL, can change the maximum number of connections.
- Lay out the layout in advance and reserve connection channels for the root.
1. Reasons for errors
Mysql_connect (mysql_close); mysql_close (mysql_close); Too many connections error occurs when sleep connections reach max_connections.
Max_connections + 1 max_connections + 1 max_connections + 1 max_connections + 1 max_connections + 1 max_connections + 1 But in practice, for various reasons, this one could be occupied. At this point, we cannot handle this error by logging in to MySQL to adjust the parameters.
Check the current MySQL connection
We can use SHOW PROCESSLIST; View the top 100 connections.
SHOW PROCESSLIST;
Copy the codeYou can also use SHOW full PROCESSLIST; View all connections.
SHOW full PROCESSLIST;
Copy the code
In the connection with ID 15, we can see that it has 11388s
How to Use MySQL Slow Query Logs for Performance Optimization
MySQL connection pool is full.
Use mysqladmin -u kalacloud -p status to check the current number of connections
Replace kalacloud with your MySQL account name. In the result returned, Threads is the current number of connections. If the current number of connections is close to or equal to the maximum number of connections, then the MySQL connection number is full or close to full.
MySQL triggers create, use, check, and delete MySQL triggers
Set the timeout time reasonably
The reason for the occurrence of a large number of sleep-filled connections is not only the business volume, but also the timeout time can be adjusted. The timeout time can be appropriately shortened according to the actual situation, so that MySQL can automatically clear the timeout connections in a short time to ensure the normal purpose of connection.
Mysqld connection timeout parameters have the following two parameters:
- interactive_timeout
- wait_timeout
By default, both are 28800 seconds (8 hours) and we can change these two parameters in the MySQL configuration file.
If you are using a persistent connection like mysql_pconnect, you can reduce the timeout to a more appropriate value, such as 600 (10 minutes) or even 60 (1 minute). There is no specific timeout, but it depends on the requirements of your application scenario.
1. Change the timeout duration in the configuration file (MySQL restart takes effect).
First open the mysqld.cnf configuration file.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Copy the codeThen find these two lines in the configuration file and modify the corresponding parameters:
[mysqld]
interactive_timeout=60
wait_timeout=60
Copy the codeMySQL configuration file in Detail
2. Temporarily change the connection timeout period (no need to restart) :
SET GLOBAL interactive_timeout = 60;
SET GLOBAL wait_timeout = 60;
Copy the codeNote:
- This is a temporary way to change the configuration. After MySQL restarts, the configuration file will be restored.
- Connections that are already open will not be closed. Only new connections are closed after 60 seconds.
How to connect to MySQL database remotely
View and modify the maximum number of connections
In MySQL, the default connection number is 151. You can change the connection number parameter permanently by modifying the MySQL configuration file, or temporarily by using SQL commands.
1. Check the current number of MySQL connections
mysql> show variables like '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC) mysql >Copy the code2. Temporarily adjust the number of MySQL connections
set GLOBAL max_connections = 300;
Copy the code
3. Modify the MySQL configuration file to adjust the maximum number of connections
First open the MySQL configuration file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Copy the codeFind max_connections under [mysqld], if not, add it directly.
[mysqld]
...
max_connections = 300
...
Copy the codeAfter the modification, restart MySQL to make the configuration file take effect:
sudo systemctl restart mysql
Copy the codeAfter the restart, enter MySQL, we can see that the maximum number of connections configuration has taken effect.
mysql> show variables like '%max_connections%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_connections | 300 | | mysqlx_max_connections | 100 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 rows in the set (0.02 SEC) mysql >Copy the codeMySQL configuration file my.cnf/my.ini
How do I change the maximum number of connections when I cannot log in to MySQL
If you cannot log in and restart MySQL, you can use the following methods to increase the number of connections.
1. Modify pid to increase the number of connections
We can use the GDB tool to change the maximum number of connections without entering the database.
gdb -p $(cat data/kalacloud.pid)
-ex "set max_connections=5000" -batch
Copy the codedata/kalacloud.pid: Change this to your serverpidThe file path and file name of.
This method is only applicable to special and emergency situations, and should be used with caution in production environment.
Seven. Advance layout, nip in the bud
In the MySQL configuration file, there are two parameters about the number of connections
max_connections: Controls the maximum number of connections.max_user_connections: Controls the maximum number of connections for a single user. When this parameter is 100, any user (including root user) can create a maximum of 100 connections.
Develop a connection strategy:
max_connections = 2000
max_user_connections= 300
Copy the codeWhen MySQL has 6 users (root not included), the maximum number of connections per user is 300, so the maximum number of connections per 6 users is 1800. There will always be 200 connections left for root to use.
6. Summary
MySQL connection number too many errors, we need to practice in daily work, here many key parameters need to be our work in a broader understanding, in order to better set these parameters.
Finally, we recommend the following Cara cloud. Cara Cloud is a set of low code development tools, which can access common databases and apis including MySQL with one key. Without knowing any front-end, you can quickly build internal tools by simply dragging and dropping. Months of development work can be reduced to a few days with a free trial of Cara Cloud.
Cara Cloud provides one-click access to common databases and apis on the market
MySQL > MySQL > MySQL
- MySQL > select * from timestamps
- How to implement BLOB data type access in MySQL? What are the application scenarios of BLOB?
- How to skip multiple table exports or specify multiple table export backups in MySQL/MariaDB
- How to save MySQL/MariaDB query results to a file
- MySQL Trigger Use tutorial – Create, delete, View