How to set MySQL connection timeout?

MySQL is a commonly used relational database management system that is widely used for data storage and management in various applications. When developing with MySQL, we often encounter the problem of connection timeout. Connection timeout refers to the situation that during the process of establishing a database connection, the connection cannot be established due to some reasons or the establishment time is too long, which ultimately leads to the connection timeout.

Connection timeout may affect the performance and stability of the system, therefore, we need to set the MySQL connection timeout appropriately to avoid this problem. Below, we will introduce in detail how to set the MySQL connection timeout.

1. Understand MySQL’s connection timeout parameters
   Before setting the connection timeout, we must first understand the MySQL-related connection timeout parameters. MySQL has two important connection timeout parameters, namely wait_timeout and interactive_timeout.

• wait_timeout Specifies the length of time a non-interactive (i.e. non-terminal) connection remains open without active operations. The default value is 28800 seconds (8 hours).
• interactive_timeout Specifies the length of time an interactive (i.e. terminal) connection remains open without active operation. The default value is 28800 seconds.

Note: MySQL's connection timeout is only valid for connections in an idle state, that is, when no request is sent or any response is received on the connection, the connection will be closed after the timeout is exceeded.

2. View and change the connection timeout parameters
   You can view and change the connection timeout parameters through the MySQL configuration file my.cnf. The specific steps are as follows:

• To open the my.cnf file, you can use the command vi /etc/my.cnf or directly in the Windows system Edit the file.
• In the file, look for the two parameters wait_timeout and interactive_timeout. You can use the search function to quickly find them.
• Modify the values ​​of these two parameters to the required connection timeout. You can set a shorter time to improve the system's response speed, or set a longer time to reduce the frequency of re-establishing connections based on actual needs.
• Save and close the file.

3. Dynamic modification of connection timeout parameters
   In addition to modifying the connection timeout parameters in the configuration file, we can also dynamically modify these parameters through MySQL commands. The specific steps are as follows:

• Open the MySQL command line client.

• Execute the following command to view the current connection timeout parameters:

  SHOW VARIABLES LIKE 'wait_timeout';
  SHOW VARIABLES LIKE 'interactive_timeout';

• Use the following command to modify the value of the connection timeout parameter:

  SET GLOBAL wait_timeout = 600;
  SET GLOBAL interactive_timeout = 600;

  Among them, 600 means setting the timeout to 600 seconds, which you can modify according to actual needs.

• Execute the following command to confirm that the parameter value has been modified:

  SHOW VARIABLES LIKE 'wait_timeout';
  SHOW VARIABLES LIKE 'interactive_timeout';

• Close the MySQL command line client.

4. Restart the MySQL service
   After modifying the connection timeout parameters, we need to restart the MySQL service to make the new parameter values ​​take effect. The specific steps are as follows:

• Use the following command to restart the MySQL service:

  sudo service mysql restart

• Make sure that the MySQL service has been started successfully.

Through the above steps, we can easily set the MySQL connection timeout to optimize the performance and stability of the system. In practical applications, we can adjust the connection timeout setting according to the system load and business needs.

The above is the detailed content of How to set MySQL connection timeout?. For more information, please follow other related articles on the PHP Chinese website!