How to set MySQL connection timeout?-Mysql Tutorial-php.cn

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.

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, namelywait_timeoutandinteractive_timeout.

wait_timeoutSpecifies 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_timeoutSpecifies 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.

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

To open themy.cnffile, you can use the commandvi /etc/my.cnfor directly in the Windows system Edit the file.
In the file, look for the two parameterswait_timeoutandinteractive_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.

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';
```
Copy after login
Copy after login
Use the following command to modify the value of the connection timeout parameter:
```
SET GLOBAL wait_timeout = 600; SET GLOBAL interactive_timeout = 600;
```
Copy after login
Among them,600means 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';
```
Copy after login
Copy after login
Close the MySQL command line client.

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
```
Copy after login
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!