Fix MySQL Connection Timeout

What's Actually Happening

Applications cannot connect to MySQL server within the expected timeout period. Connection attempts hang and eventually fail.

The Error You'll See

Connection timeout:

```bash $ mysql -h db-server -u myuser -p

ERROR 2003 (HY000): Can't connect to MySQL server on 'db-server:3306' (110 Connection timed out) ```

Python error:

mysql.connector.errors.DatabaseError: 2003: Can't connect to MySQL server on 'db-server'

Java error:

java.sql.SQLException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago.

Why This Happens

1. 1.MySQL not running - Server process stopped
2. 2.Firewall blocking - Port 3306 blocked
3. 3.Wrong host/port - Incorrect connection parameters
4. 4.Network latency - Slow network connection
5. 5.Max connections - Server reached connection limit
6. 6.Skip networking - MySQL configured without TCP/IP

Step 1: Check MySQL Server Status

```bash # Check MySQL service: systemctl status mysql

# Or mysqld: systemctl status mysqld

# Check process: ps aux | grep mysql

# Check listening port: ss -tlnp | grep 3306 netstat -tlnp | grep 3306

# Start if not running: systemctl start mysql ```

Step 2: Test Local Connection

```bash # Test local connection: mysql -u root -p

# Test with socket: mysql -u root -p --socket=/var/run/mysqld/mysqld.sock

# Test with localhost: mysql -h 127.0.0.1 -u root -p

# Test with full parameters: mysql -h localhost -P 3306 -u root -p

# Check connection works: mysql -u root -p -e "SELECT VERSION();" ```

Step 3: Check Network Connectivity

```bash # Test port connectivity: nc -zv db-server 3306

# Test with telnet: telnet db-server 3306

# Check routing: traceroute db-server

# Check DNS resolution: nslookup db-server

# Ping test: ping -c 3 db-server

# Test with timeout: timeout 5 bash -c 'cat < /dev/null > /dev/tcp/db-server/3306' ```

Step 4: Check MySQL Configuration

```bash # Check bind address: cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep bind-address

# For remote connections, should be: bind-address = 0.0.0.0

# Or specific IP: bind-address = 10.0.0.1

# NOT just localhost: # bind-address = 127.0.0.1

# Check port: cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep port

# Check skip_networking: cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep skip-networking

# If enabled, disable it: # skip-networking # Comment out or remove

# Restart MySQL: systemctl restart mysql ```

Step 5: Check Firewall Rules

```bash # Check iptables: iptables -L -n | grep 3306

# Allow MySQL: iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

# Firewalld: firewall-cmd --add-port=3306/tcp --permanent firewall-cmd --reload

# UFW: ufw allow 3306/tcp

# Check cloud security groups: # AWS: Security group inbound rules # Azure: Network security group # GCP: Firewall rules

# Test from client: nc -zv db-server 3306 ```

Step 6: Check User Host Permissions

```bash # Connect as root: mysql -u root -p

# Check user hosts: SELECT User, Host FROM mysql.user;

# Create user for remote access: CREATE USER 'myuser'@'%' IDENTIFIED BY 'password'; CREATE USER 'myuser'@'10.0.0.1' IDENTIFIED BY 'password';

# Grant permissions: GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%'; FLUSH PRIVILEGES;

# Test remote connection: mysql -h db-server -u myuser -p ```

Step 7: Check Connection Limits

```bash # Check max_connections: mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections'"

# Check current connections: mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected'"

# Check processlist: mysql -u root -p -e "SHOW PROCESSLIST"

# Check max used: mysql -u root -p -e "SHOW STATUS LIKE 'Max_used_connections'"

# Increase max_connections: # In my.cnf: max_connections = 500

# Restart: systemctl restart mysql ```

Step 8: Check Timeout Settings

```bash # Check timeout variables: mysql -u root -p -e "SHOW VARIABLES LIKE '%timeout%'"

# Key timeout variables: # connect_timeout - Connection establishment timeout # wait_timeout - Connection idle timeout # interactive_timeout - Interactive session timeout

# Set in config: # In my.cnf: [mysqld] connect_timeout = 30 wait_timeout = 28800 interactive_timeout = 28800

# Set runtime: mysql -u root -p -e "SET GLOBAL connect_timeout = 30"

# Connection string timeout: mysql -h db-server -u user -p --connect-timeout=30 ```

Step 9: Check MySQL Logs

```bash # Check error log: tail -f /var/log/mysql/error.log

# Or journalctl: journalctl -u mysql -f

# Look for connection errors: grep -i "connection" /var/log/mysql/error.log grep -i "timeout" /var/log/mysql/error.log

# Enable general log for debugging: mysql -u root -p -e "SET GLOBAL general_log = 'ON'"

# Check general log: tail -f /var/log/mysql/general.log

# Disable after debugging: mysql -u root -p -e "SET GLOBAL general_log = 'OFF'" ```

Step 10: Verify and Monitor

```bash # Create verification script: cat << 'EOF' > /usr/local/bin/check-mysql-connection.sh #!/bin/bash

echo "=== MySQL Status ===" systemctl status mysql | head -5

echo "" echo "=== Listening Ports ===" ss -tlnp | grep 3306

echo "" echo "=== Connection Test ===" mysql -u root -p -e "SELECT VERSION();" 2>/dev/null

echo "" echo "=== Connection Count ===" mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected'" 2>/dev/null

echo "" echo "=== Max Connections ===" mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections'" 2>/dev/null

echo "" echo "=== Bind Address ===" cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep bind-address EOF

chmod +x /usr/local/bin/check-mysql-connection.sh

# Run check: /usr/local/bin/check-mysql-connection.sh

# Monitor connections: watch -n 1 'mysql -u root -p -e "SHOW STATUS LIKE \"Threads_connected\""' ```

MySQL Connection Timeout Checklist

Verify the Fix

```bash # After fixing connection timeout

# 1. Check service running systemctl status mysql // Active: active (running)

# 2. Test local connection mysql -u root -p -e "SELECT 1" // Returns 1

# 3. Test remote connection mysql -h db-server -u myuser -p // Connected

# 4. Check listening ss -tlnp | grep 3306 // Port 3306 listening

# 5. Check firewall iptables -L | grep 3306 // ACCEPT rule exists

# 6. Monitor connections mysql -u root -p -e "SHOW PROCESSLIST" // Connections listed ```

Related Issues

• [Fix MySQL Authentication Failed](/articles/fix-mysql-authentication-failed)
• [Fix MySQL Permission Denied](/articles/fix-mysql-permission-denied)
• [Fix MySQL Database Not Found](/articles/fix-mysql-database-not-found)