Terminating Inactive MySQL Connections

It is common to observe numerous MySQL connections remaining idle over extended periods. This can prompt questions regarding how to terminate or close such connections from the server, without resorting to restarting the service.

Manual Termination

One approach is to manually terminate idle connections using the KILL command. To identify the process ID for the idle connection, execute the following query:

mysql> show full processlist;

Once the process ID is known, it can be terminated with:

mysql> kill [process_id];

However, this method has limitations:

• PHP applications may report errors upon connection termination.
• It is advisable to avoid terminating connections unnecessarily if there is no connection shortage.

Automatic Connection Management

Alternatively, configuring MySQL's timeout settings can automatically handle the termination of idle connections. By reducing the wait_timeout and interactive_timeout values, MySQL will automatically close connections that remain inactive for a specified duration. To adjust these settings, run the following commands:

mysql> show variables like "%timeout%";
mysql> set global wait_timeout=[new timeout value];
mysql> set global interactive_timeout=[new timeout value];

It is important to note that while this approach addresses the symptom of idle connections, it does not resolve the underlying cause. It is recommended to investigate why connections remain open and consider whether connection pooling is being used by the web server.