How to Change the Connection Timeout in MySQL

1. Change the Connection Timeout in MySQL
2. Change MySQL connect_timeout Using Windows OS
3. Change MySQL connect_timeout Using Linux (Ubuntu 20.04) OS

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

Change the Connection Timeout in MySQL

Sometimes, you keep losing the connection to 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 the connect_timeout in MySQL using Linux (Ubuntu 20.04) and Windows operating systems.

We can either update the configuration file or use SQL queries to make changes in both operating systems. Let’s see each of them.

Change MySQL connect_timeout Using Windows OS

Firstly, we need to open the Windows Command Line and navigate the MySQL Server installation path. By default, the MySQL Server is installed at C:\Program Files\MySQL\MySQL Server 8.0.

Navigate to the bin folder using the cd bin on Command-Line. Further, type mysql -u root -p password to log in as a root user.

Now, we can see the default value of the connect_timeout property by 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 MySQL Server will be lost in 10 seconds. So, we can change it to 28800 seconds (8 hours) by using the following commands on Windows Command-Line.

Remember, updating the interactive_timeout and wait_timeout is good because it would be useful while running the applications that consume a lot of time in full execution.

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

Use the following command to confirm all the updates.

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, the connect_timeout represents the number of seconds the mysqld server waits for a connect packet before returning Bad Handshake. The interactive_timeout also shows the number of seconds the MySQL Server waits for an activity on the interactive connection before closing.

Like connect_timeout and interactive_timeout, the wait_timeout also shows the number of seconds that the MySQL Server waits for an activity on the connection before closing it.

If you are writing some application, then you can do in the following manner to change it via programming language, for instance, 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 at the C:\ProgramData\MySQL\MySQL Server 8.0\my.ini path (if you have installed the MySQL server at 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 updates.

Change MySQL connect_timeout Using Linux (Ubuntu 20.04) OS

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

$ mysql -u root -p password

Once we are in, check the default value of the connect_timeout, which is 10 seconds, as we can see 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 queries to update values of the connect_timeout, interactive_timout, and wait_timeout. As mentioned earlier, we update these three to run applications that take a lot of time in full execution.

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)

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

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

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

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

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

Save and exit the file. Restart the MySQL server using systemctl restart mysql.

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)