Can I terminate dormant MySQL connections without restarting the service?

Terminating Dormant MySQL Connections

Innumerable connections remain idle for extended periods, up to 5 minutes, creating a significant concern. Is it possible to terminate or close these connections without resorting to MySQL service restarts? Despite maintaining a legacy PHP system that precludes closing connections during query execution, should timeout values in the my.cnf file be reduced from the default 8 hours?

Manual Cleanup

Process IDs can be used to manually terminate connections using the KILL command:

mysql> show full processlist;
+---------+------------+-------------------+------+---------+-------+-------+-----------------------+
| Id      | User       | Host              | db   | Command | Time  | State | Info                  |
+---------+------------+-------------------+------+---------+-------+-------+-----------------------+
| 1193777 | TestUser12 | 192.168.1.11:3775 | www  | Sleep   | 25946 |       | NULL                  |
+---------+------------+-------------------+------+---------+-------+-------+-----------------------+

mysql> kill 1193777;

However, this approach has potential drawbacks:

• PHP applications may encounter errors.
• If connection resources are not scarce, it's best to leave them alone.

Automated Cleaning

An alternative solution involves configuring MySQL server with shorter timeouts for wait_timeout and interactive_timeout:

mysql> show variables like "%timeout%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 28800 |
| net_read_timeout         | 30    |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)

These timeouts can be set using the following commands:

set global wait_timeout=3;
set global interactive_timeout=3;

It's important to remember that these changes only address the symptoms and not the underlying cause of persistent connections. PHP scripts should be properly configured to close connections upon completion, and connection pooling by the webserver should be disabled.

The above is the detailed content of Can I terminate dormant MySQL connections without restarting the service?. For more information, please follow other related articles on the PHP Chinese website!