MySQL Connection Management: Best Practices for Diagnosing and Terminating Database Connections

Problem Diagnosis and Connection Monitoring

When encountering the "Too many connections" error in MySQL, the first step is to diagnose the current connection status. Use the SHOW PROCESSLIST command to view all active database connections:

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+-------+---------------+
| Id  | User | Host            | db   | Command | Time | State | Info         |
+-----+------+-----------------+------+---------+------+-------+---------------+
| 143 | root | localhost:61179 | cds  | Query   |    0 | init  | SHOW PROCESSLIST |
| 192 | root | localhost:53793 | cds  | Sleep   |    4 |       | NULL         |
+-----+------+-----------------+------+---------+------+-------+---------------+
2 rows in set (0.00 sec)

This command returns connection ID, user, host, database, command type, execution time, and status information, helping identify abnormal or idle connections.

Connection Termination Operations

After identifying connections that need termination, use the KILL command followed by the connection ID to terminate specific connections:

mysql> KILL 192;
Query OK, 0 rows affected (0.00 sec)

Upon successful execution, the terminated connection disconnects immediately, and any ongoing operations are interrupted. The affected user receives a connection loss error:

mysql> SELECT * FROM exept;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Batch Connection Management

For scenarios requiring termination of multiple connections, generate batch KILL statements by querying the INFORMATION_SCHEMA.PROCESSLIST system table:

SELECT 
CONCAT('KILL ', id, ';') 
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE `User` = 'some_user' 
AND `Host` = '192.168.1.1'
AND `db` = 'my_db';

This query generates KILL commands for all connections matching specific user, host, and database criteria. Copy and execute the results in the MySQL client for batch processing.

Preventive Configuration Strategies

To prevent recurring connection overflow issues, configure the following parameters:

Connection Timeout Settings: Adjust wait_timeout and interactive_timeout parameters to enable MySQL to automatically close idle connections. Add to the my.cnf configuration file:

[mysqld]
wait_timeout = 300
interactive_timeout = 300

This automatically closes connections after 300 seconds of inactivity.

User Connection Limits: Set maximum connection limits for specific users:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';
GRANT USAGE ON *.* TO 'app_user'@'localhost' WITH MAX_USER_CONNECTIONS 10;

This ensures that even if an application abnormally creates excessive connections, it won't affect other users' database access.

Emergency Access Solutions

When connection limits are reached and normal login is impossible, MySQL reserves one additional connection for users with SUPER privileges. Ensure at least one database user has this privilege:

GRANT SUPER ON *.* TO 'admin_user'@'localhost' IDENTIFIED BY 'secure_password';

In emergencies, using this privileged account bypasses connection limits, enabling necessary troubleshooting and repairs.

Root Cause Resolution

After temporarily resolving connection issues, identify and fix the root cause of connection overflow. Common solutions include optimizing application connection pool configurations, improving database query performance, and checking for connection leaks in applications. Regular monitoring of database connection status using system monitoring tools and establishing alert mechanisms can effectively prevent similar issues.