MySQL Slave IO Thread Error: Connection and Network Issues

# MySQL Slave IO Thread Error: Connection and Network Issues

Your MySQL slave shows IO thread stopped:

SHOW SLAVE STATUS\G

Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: error connecting to master 'repl@master:3306' - retry-time: 60 retries: 10
Last_IO_Error_Number: 2003

The IO thread connects to the master and reads binary logs. Connection issues stop replication entirely.

Understanding IO Thread Errors

1. 1.The IO thread performs:
2. 2.Connects to master using CHANGE MASTER credentials
3. 3.Requests binary log events from master
4. 4.Writes received events to relay log on slave

Common failure causes: - Network connectivity issues - Authentication failures - Master binary log position mismatch - Master server down or unreachable - Timeout or connection limit exceeded

Step 1: Identify the IO Error

Check slave status:

SHOW SLAVE STATUS\G

Focus on:

Slave_IO_Running: No
Last_IO_Error: [Full error message]
Last_IO_Error_Number: [MySQL error code]
Master_Host: [Master hostname]
Master_Port: [Master port]
Master_User: [Replication user]

Common IO error codes:

Step 2: Test Network Connectivity

Verify basic connectivity:

```bash # Test TCP connection ping master-hostname telnet master-hostname 3306

# Check if MySQL port is open nc -zv master-hostname 3306

# Check DNS resolution nslookup master-hostname dig master-hostname

# Check firewall rules iptables -L -n | grep 3306 ```

If network is blocked:

```bash # Add firewall rule if needed iptables -A INPUT -p tcp --dport 3306 -s slave-ip -j ACCEPT

# Or check MySQL bind address on master grep bind-address /etc/mysql/my.cnf ```

Step 3: Test MySQL Connection

Try connecting directly:

```bash mysql -h master-hostname -P 3306 -u repl_user -p

# If hostname fails, try IP mysql -h 192.168.1.100 -P 3306 -u repl_user -p

# Check if master is running mysqladmin -h master-hostname ping ```

If connection fails:

```bash # Check master is running ssh master-hostname 'systemctl status mysqld'

# Check master is listening ssh master-hostname 'netstat -tlnp | grep 3306' ```

Step 4: Fix Authentication Errors (1045)

Access denied error:

```sql -- Error: Access denied for user 'repl_user'@'slave-ip'

-- On master, check user privileges SELECT User, Host FROM mysql.user WHERE User = 'repl_user'; SHOW GRANTS FOR 'repl_user'@'slave-ip';

-- If user missing, create it CREATE USER 'repl_user'@'slave-ip' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave-ip'; FLUSH PRIVILEGES;

-- If host pattern wrong, update UPDATE mysql.user SET Host = '192.168.1.%' WHERE User = 'repl_user'; FLUSH PRIVILEGES;

-- Or create for any host (less secure) GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password'; ```

Verify password is correct:

# Test with same credentials used in CHANGE MASTER
mysql -h master -u repl_user -p'exact_password_from_change_master'

Step 5: Fix Binary Log Position Error (1236)

Master can't find requested position:

```sql -- Error: Could not find first log file name in binary log index file

-- On master, check available logs SHOW BINARY LOGS;

-- Check current position SHOW MASTER STATUS; ```

If requested log doesn't exist:

```sql -- On slave, get a valid position from master STOP SLAVE;

-- On master SHOW MASTER STATUS; -- Use: mysql-bin.000XXX, Position XXX

-- Update slave position CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000XXX', MASTER_LOG_POS = XXX;

START SLAVE; ```

For GTID replication:

```sql -- Check master's GTID -- On master SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

-- If slave is behind master's purged GTIDs, need to rebuild -- Or skip to current GTID if data consistency is acceptable STOP SLAVE; SET GLOBAL GTID_PURGED = 'uuid:1-xxx'; -- Set missing range START SLAVE; ```

Step 6: Fix Connection Lost Error (2013)

Connection drops during replication:

```bash # Check for network instability ping master-hostname -c 100

# Check for timeout settings ```

SHOW VARIABLES LIKE '%timeout';

Increase timeout values:

```ini [mysqld] # On master wait_timeout = 86400 interactive_timeout = 86400

# Connection timeout connect_timeout = 60

# On slave slave_net_timeout = 60 -- Seconds before retry after connection lost ```

Step 7: Fix Network Read Errors (1158, 1159)

Network issues during read:

```bash # Check network stability mtr master-hostname

# Check packet loss ping master-hostname -i 0.1 -c 1000 | grep "packet loss"

# Check bandwidth saturation iftop -i eth0 ```

Adjust replication network settings:

```ini [mysqld] # On slave slave_net_timeout = 120

# Read timeout net_read_timeout = 120 net_write_timeout = 120 ```

Step 8: Fix Master Connection Limit

If master max_connections exceeded:

```sql -- On master, check connection count SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';

-- Increase if needed SET GLOBAL max_connections = 500; ```

[mysqld]
max_connections = 500

Step 9: Fix Hostname Resolution Issues

If hostname can't be resolved:

```sql -- Error: Unknown MySQL server host 'master-hostname'

-- Use IP address instead STOP SLAVE; CHANGE MASTER TO MASTER_HOST = '192.168.1.100'; START SLAVE; ```

Or fix DNS:

```bash # Add to /etc/hosts echo "192.168.1.100 master-hostname" >> /etc/hosts

# Or configure skip-name-resolve ```

[mysqld]
skip_name_resolve = 1

Step 10: Fix SSL/TLS Connection Issues

If SSL is required:

```sql -- Error: SSL connection required

-- Check SSL requirement on master SHOW VARIABLES LIKE 'require_secure_transport';

-- Configure SSL on slave connection STOP SLAVE; CHANGE MASTER TO MASTER_SSL = 1, MASTER_SSL_CA = '/path/to/ca.pem', MASTER_SSL_CERT = '/path/to/client-cert.pem', MASTER_SSL_KEY = '/path/to/client-key.pem'; START SLAVE; ```

Step 11: Handle Master Restart

If master restarted and logs rotated:

```sql -- On master, check new position SHOW MASTER STATUS;

-- On slave, update position STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 4; START SLAVE; ```

For GTID, this is automatic with MASTER_AUTO_POSITION:

CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

Step 12: Fix Time Zone Differences

Time zone mismatch can cause issues:

```sql -- Check time zones on both servers SHOW VARIABLES LIKE 'system_time_zone'; SELECT NOW();

-- On master, set explicit time zone SET GLOBAL time_zone = '+00:00';

-- On slave, match master SET GLOBAL time_zone = '+00:00'; ```

[mysqld]
default_time_zone = '+00:00'

Step 13: Monitor IO Thread Health

```bash #!/bin/bash # check_slave_io.sh

IO_RUNNING=$(mysql -N -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running:" | awk '{print $2}') IO_ERROR=$(mysql -N -e "SHOW SLAVE STATUS\G" | grep "Last_IO_Error:" | cut -d: -f2-) RETRIES=$(mysql -N -e "SHOW SLAVE STATUS\G" | grep "Master_Retry_Count:" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ]; then echo "Slave IO thread stopped: $IO_ERROR" | mail -s "Replication Alert" admin@example.com fi ```

Set retry configuration:

```sql -- Check retry settings SHOW VARIABLES LIKE 'master_retry_count'; SHOW VARIABLES LIKE 'master_connect_retry';

-- Increase retries CHANGE MASTER TO MASTER_CONNECT_RETRY = 60, -- Seconds between retries MASTER_RETRY_COUNT = 86400; -- Number of retries before giving up ```

Verification After Fix

SHOW SLAVE STATUS\G

Success indicators: - Slave_IO_Running: Yes - Empty Last_IO_Error - Master_Log_File advancing - Read_Master_Log_Pos increasing

Test replication flow:

```sql -- On master SHOW MASTER STATUS; INSERT INTO test_table VALUES (1, 'test');

-- Wait 1-2 seconds

-- On slave, check if relay log received SHOW SLAVE STATUS; -- Read_Master_Log_Pos should have increased ```

Best Practice Configuration

On master:

```ini [mysqld] # Binary logging log_bin = mysql-bin binlog_format = ROW sync_binlog = 1

# Connections max_connections = 500 wait_timeout = 86400

# Network bind-address = 0.0.0.0 # Or specific IP

# GTID gtid_mode = ON enforce_gtid_consistency = ON ```

On slave:

```ini [mysqld] # Replication slave_net_timeout = 60 master_connect_retry = 60 master_retry_count = 1000

# Crash-safe relay_log_recovery = 1 relay_log_info_repository = TABLE master_info_repository = TABLE

# GTID gtid_mode = ON enforce_gtid_consistency = ON ```

Quick Reference Commands

```sql -- Check IO status SHOW SLAVE STATUS\G

-- Restart IO thread only STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

-- Update connection CHANGE MASTER TO MASTER_HOST = 'new-host', MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = 'password';

-- Update position CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.xxx', MASTER_LOG_POS = xxx;

-- GTID auto-position CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

-- Enable SSL CHANGE MASTER TO MASTER_SSL = 1;

-- Reset replication STOP SLAVE; RESET SLAVE ALL; ```

Troubleshooting Checklist

• [ ] Identify error code from SHOW SLAVE STATUS
• [ ] Test network connectivity with ping/telnet
• [ ] Verify master MySQL is running
• [ ] Test direct MySQL connection
• [ ] Check replication user privileges
• [ ] Verify binary log position is valid
• [ ] Check timeout settings
• [ ] Verify DNS resolution or use IP
• [ ] Check SSL requirements
• [ ] Adjust retry settings
• [ ] Restart IO thread after fix
• [ ] Verify replication continues
• [ ] Set up monitoring