JIYIK CN >

We are learning how to change the connection timeout in MySQL using Linux (Ubuntu 20.04) and Windows operating systems.

Changing the connection timeout in MySQL

Sometimes you keep losing connection to the MySQL server because the connect_timeout property is set to a few seconds by default.

Here we will see how to change the default value of connect_timeout in MySQL using Linux (Ubuntu 20.04) and Windows operating systems.

We can update configuration files or use SQL queries to make changes in both operating systems. Let's look at each of them.

Changing MySQL connect_timeout using Windows OS

First, we need to open the Windows command line and browse to the MySQL server installation path. By default, MySQL server is installed at C:\Program Files\MySQL\MySQL Server 8.0.

Navigate to the bin folder on the command line using cd bin. Also, log in as the root user by typing mysql -u root -p password.

We can now view the default value of the connect_timeout property using the following query before making the necessary updates.

mysql> SHOW VARIABLES LIKE 'connect_timeout';

Output:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 10    |
+-----------------+-------+
1 row in set (0.00 sec)

As we can see, the connection to the MySQL server is lost after 10 seconds. Therefore, we can change it to 28800 seconds (8 hours) by using the following command on the Windows command line.

Remember that updating interactive_timeoutand wait_timeoutis good because it is useful when running applications that consume a lot of time when fully executed.

mysql> SET GLOBAL connect_timeout=28800;
mysql> SET GLOBAL interactive_timeout=28800;
mysql> SET GLOBAL wait_timeout=28800;

Confirm all updates using the following command.

mysql> SHOW VARIABLES WHERE Variable_Name
    -> IN ('connect_timeout', 'interactive_timeout', 'wait_timeout');

Output:

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| connect_timeout     | 28800 |
| interactive_timeout | 28800 |
| wait_timeout        | 28800 |
+---------------------+-------+
3 rows in set (0.04 sec)

Here, connect_timeout indicates the number of seconds the mysqld server waits for a connect packet before returning a Bad Handshake. interactive_timeout also shows the number of seconds the MySQL server waits for activity on an interactive connection before shutting down.

Like connect_timeout and interactive_timeout, wait_timeout shows the number of seconds the MySQL server waits for connection activity before closing the connection.

If you are writing some application, here is how you can change it through a programming language such as Java or Python.

connection.query('SET GLOBAL connect_timeout=28800')
connection.query('SET GLOBAL interactive_timeout=28800')
connection.query('SET GLOBAL wait_timeout=28800')

Another way is to update the configuration file by opening the my.ini file located at C:\ProgramData\MySQL\MySQL Server 8.0\my.ini (if you have installed MySQL server in the default location). Then, look for the [mysqld] section and write.

[mysqld]
connect_timeout = 28800
interactive_timeout = 28800
wait_timeout = 28800

Restart the MySQL server and enjoy the update.

Changing MySQL connect_timeout using Linux (Ubuntu 20.04) operating system

Open the Ubuntu shell and log in as superuser using sudo su. Then, log in to the MySQL server using the following query.

$ mysql -u root -p password

Once inside, check the default value of connect_timeout, which is 10 seconds, as shown below.

mysql> SHOW VARIABLES LIKE 'connect_timeout';

Output:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 10    |
+-----------------+-------+
1 row in set (0.00 sec)

Now, execute the following query to update the values ​​of connect_timeout, interactive_timout, and wait_timeout. As mentioned earlier, we update these three to run applications that take a lot of time to execute completely.

mysql> SET GLOBAL connect_timeout=28800;
mysql> SET GLOBAL interactive_timeout=28800;
mysql> SET GLOBAL wait_timeout=28800;

Output:

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| connect_timeout     | 28800 |
| interactive_timeout | 28800 |
| wait_timeout        | 28800 |
+---------------------+-------+
3 rows in set (0.13 sec)

Another way is to update the configuration file. Suppose we want 28800 seconds as the value of connect_timeout, interactive_timeout and wait_timeout.

To do this, we need to edit the configuration file located at this path /etc/mysql/mysql.conf.d/mysqld.cnf. We use vim editor; you can use any editor to edit this file.

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Search for [mysqld]the section and add connect_timeout, interactive_timeout, and wait_timeout as shown below.

[mysqld]
connect_timeout = 28800
interactive_timeout = 28800
wait_timeout = 28800

Save and exit the file. systemctl restart mysqlRestart the MySQL server using .

Use the following query to ensure that everything has been changed as expected.

mysql> SHOW VARIABLES WHERE Variable_Name
    -> IN ('connect_timeout', 'interactive_timeout', 'wait_timeout');

Output:

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| connect_timeout     | 28800 |
| interactive_timeout | 28800 |
| wait_timeout        | 28800 |
+---------------------+-------+
3 rows in set (0.90 sec)