How to Set Connection Timeout in MySQL 8

Overview

Setting the connection timeout parameter in MySQL 8 is crucial to managing your database application’s performance and handling non-responsive network connections. Connection timeout defines the amount of time a server will wait for a connection to be established before it is terminated. This guide focuses on tuning your MySQL server for better connection timeout handling.

Understanding Connection Timeouts in MySQL

In MySQL, connection timeouts can be configured in two ways: Interactive and Non-Interactive (also known as the wait_timeout and interactive_timeout). The interactive_timeout is for interactive sessions, whereas the wait_timeout is used for non-interactive connections, like those made through a script or an API.

Checking the Current Timeout Settings

SHOW VARIABLES LIKE '%timeout%';

The output will show the current settings, including the wait_timeout and interactive_timeout values.

Setting Timeouts in my.cnf or my.ini File

To set up the wait_timeout and interactive_timeout, you can define them in the MySQL configuration file (my.cnf or my.ini). Here’s how to set the wait_timeout to 60 seconds and the interactive_timeout to 100 seconds:

[mysqld]
wait_timeout = 60
interactive_timeout = 100

Restart the MySQL service after making changes to the configuration:

sudo systemctl restart mysql

Adjusting Timeouts at Runtime

You can also change the timeouts on the fly, without restarting the MySQL server, by executing the following SQL commands:

SET GLOBAL wait_timeout = 120;
SET GLOBAL interactive_timeout = 180;

Working with Connection Timeouts in Programming Languages

Setting Connection Timeout in PHP

$db = new PDO('mysql:host=localhost;dbname=testdb', 'username', 'password', [
    PDO::ATTR_TIMEOUT => 10,
]);

This PDO attribute sets the connection timeout for a PHP script to 10 seconds.

Setting Connection Timeout in Python using MySQL Connector

import mysql.connector

config = {
  'host': 'localhost',
  'database': 'testdb',
  'user': 'root',
  'password': '',
  'connection_timeout': 10
}
cnx = mysql.connector.connect(**config)

Here, the connection_timeout option in the MySQL Connector/Python configuration dictionary sets the timeout to 10 seconds.

Managing Connection Timeouts in Environment Configs

In cloud or containerized environments, such as Docker or Kubernetes, you might configure the MySQL settings using environment variables:

MYSQLD_WAIT_TIMEOUT=60
MYSQLD_INTERACTIVE_TIMEOUT=100

Dealing with Connection Timeouts in an Application

Setting a reasonable connection timeout is part of the equation. The other part is handling these timeouts gracefully in your application:

try {
    // Attempt to connect to database
} catch (Exception $e) {
    // Handle connection timeout exception
}

Automating Connection Timeout Adjustments

You can automate the adjustment of connection timeouts based on the server load or specific time intervals using cron jobs or by incorporating the logic in application startup scripts.

Automating the adjustment of MySQL connection timeouts can be particularly useful when dealing with varying server load conditions or during different times of the day. You can achieve this through cron jobs on a Unix-like system, or scheduled tasks on Windows, combined with a script that adjusts the MySQL settings. Below is an example using a cron job and a shell script on a Unix-like system.

Step 1: Create a Shell Script to Adjust MySQL Timeout

First, create a shell script that connects to your MySQL server and adjusts the connection timeout settings. We’ll use the wait_timeout system variable in this example, but you can modify it for other timeout-related settings.

1. Create the Script File:

• Open a terminal.
• Run nano adjust_mysql_timeout.sh (or use your preferred text editor).

2. Add the Following Script:

#!/bin/bash

# MySQL credentials
MYSQL_USER="your_username"
MYSQL_PASS="your_password"
MYSQL_DB="your_database"

# Logic to determine the new timeout value
# This is just an example. Modify it based on your specific criteria.
HOUR=$(date +"%H")
if [ "$HOUR" -ge 9 ] && [ "$HOUR" -le 17 ]; then
    # During business hours, set a shorter timeout
    NEW_TIMEOUT=100
else
    # After business hours, set a longer timeout
    NEW_TIMEOUT=300
fi

# Execute the SQL command to change the timeout
mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -D"$MYSQL_DB" -e "SET GLOBAL wait_timeout=$NEW_TIMEOUT;"

echo "MySQL wait_timeout set to $NEW_TIMEOUT"

Don’t forget to:

• Replace your_username, your_password, and your_database with your MySQL credentials.
• Save and close the file.

3. Make the Script Executable

Run chmod +x adjust_mysql_timeout.sh

Step 2: Set Up a Cron Job to Run the Script Automatically

Cron jobs can be used to run scripts at specific times or intervals.

1. Open the Cron Job File:
   • Run crontab -e.
2. Add a Cron Job:
   • Add the following line to schedule the script to run at a specific interval, for example, every hour: 0 * * * * /path/to/adjust_mysql_timeout.sh
   • Replace /path/to/ with the actual path to your script.
3. Save and Exit.

Explanation

• Shell Script: The script checks the current hour and sets the wait_timeout value based on whether it’s within business hours or outside business hours. This is an example, and you can adapt the logic to fit your server load or other conditions.
• Cron Job: The cron job ensures that this script runs every hour. You can adjust the timing according to your needs.

Conclusion

By carefully setting and managing your MySQL 8 server’s connection timeouts, you can ensure that your applications remain responsive and are not bogged down by stalled connections. Always test changes to your database server configurations in a development or staging environment before applying them in production.