Fix MySQL Replication Not Syncing

What's Actually Happening

MySQL replication stops synchronizing between master and slave servers. Data is not replicating from master to slave.

The Error You'll See

```sql mysql> SHOW SLAVE STATUS\G

Last_IO_Error: error connecting to master 'repl@master:3306' Last_SQL_Error: Error 'Duplicate entry' on query ```

Replication stopped:

Slave_IO_Running: No
Slave_SQL_Running: No

Lag error:

Seconds_Behind_Master: NULL

Binary log error:

Got fatal error 1236 from master when reading data from binary log

Why This Happens

1. 1.Network issues - Cannot connect to master
2. 2.Binary log corrupted - Log files damaged
3. 3.GTID issues - GTID consistency problems
4. 4.Duplicate key error - Conflicting data on slave
5. 5.Binlog position mismatch - Wrong position configured
6. 6.Authentication failure - Replication user issues
7. 7.Server ID conflict - Duplicate server IDs

Step 1: Check Replication Status

```sql -- Check slave status: SHOW SLAVE STATUS\G

-- Key fields to check: -- Slave_IO_Running: Yes -- Slave_SQL_Running: Yes -- Seconds_Behind_Master: 0 (or low number) -- Last_IO_Error: (should be empty) -- Last_SQL_Error: (should be empty)

-- Check master status: SHOW MASTER STATUS;

-- Check binary logs: SHOW BINARY LOGS;

-- Check processlist: SHOW PROCESSLIST;

-- Check variables: SHOW VARIABLES LIKE 'server_id'; SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'gtid%'; ```

```bash # Check MySQL process: ps aux | grep mysql

# Check service: systemctl status mysql

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

# Check network: ping master-server nc -zv master-server 3306 ```

Step 2: Fix IO Thread Issues

```sql -- If Slave_IO_Running is No:

-- Check IO error: SHOW SLAVE STATUS\G -- Look at Last_IO_Error

-- Common IO errors:

-- 1. Cannot connect to master: -- Check master is running -- Check network connectivity -- Check replication user

-- 2. Authentication failed: -- Check replication user: SHOW GRANTS FOR 'repl'@'%';

-- Recreate user if needed: CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES;

-- 3. Binary log not found: -- Check master binary logs: SHOW BINARY LOGS;

-- If log purged, need to re-sync from backup

-- 4. Network timeout: -- Increase connection timeout: STOP SLAVE; CHANGE MASTER TO MASTER_CONNECT_RETRY = 60; START SLAVE;

-- 5. Server ID conflict: -- Check server_id is unique: SHOW VARIABLES LIKE 'server_id'; -- Must be different on master and slave

-- Fix in my.cnf: server-id = 2 ```

Step 3: Fix SQL Thread Issues

```sql -- If Slave_SQL_Running is No:

-- Check SQL error: SHOW SLAVE STATUS\G -- Look at Last_SQL_Error

-- Common SQL errors:

-- 1. Duplicate entry: -- Error: Duplicate entry '123' for key 'PRIMARY'

-- Skip one transaction: STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;

-- Or handle manually: -- Option 1: Skip multiple: SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 10;

-- Option 2: Fix data manually: -- Resolve conflict, then: STOP SLAVE; START SLAVE;

-- 2. Table doesn't exist: -- Sync table structure from master: SHOW CREATE TABLE tablename;

-- 3. Constraint violation: -- Fix data or disable constraints temporarily

-- 4. Query execution error: -- Check query causing error -- Fix underlying issue

-- 5. GTID issues: -- If using GTID: SHOW VARIABLES LIKE 'gtid_mode';

-- Reset GTID: SET GLOBAL GTID_PURGED = 'uuid:1-100'; ```

Step 4: Check Binary Logs

```sql -- On master, check binary logs: SHOW BINARY LOGS;

-- Check current position: SHOW MASTER STATUS;

-- On slave, check relay logs: SHOW RELAYLOG EVENTS;

-- Check relay log position: SHOW SLAVE STATUS\G -- Relay_Log_File -- Relay_Log_Pos

-- Purge old binary logs (master): PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00'; PURGE BINARY LOGS TO 'mysql-bin.000100';

-- Flush logs: FLUSH LOGS;

-- Check log format: SHOW VARIABLES LIKE 'binlog_format'; -- ROW, STATEMENT, or MIXED

-- Should match on master and slave ```

```bash # Check binary log files: ls -la /var/lib/mysql/mysql-bin.*

# Check relay log files: ls -la /var/lib/mysql/relay-log.*

# Check disk space: df -h /var/lib/mysql

# Check log size: du -sh /var/lib/mysql/mysql-bin.*

# Corrupted log: mysqlbinlog mysql-bin.000100 > /dev/null # If error, log is corrupted ```

Step 5: Fix GTID Replication

```sql -- Check GTID status: SHOW VARIABLES LIKE 'gtid_mode'; SHOW VARIABLES LIKE 'enforce_gtid_consistency';

-- Enable GTID: gtid_mode = ON enforce_gtid_consistency = ON

-- Check GTID executed: SHOW MASTER STATUS; -- Executed_Gtid_Set

-- On slave: SHOW SLAVE STATUS\G -- Retrieved_Gtid_Set -- Executed_Gtid_Set

-- GTID gap: -- If missing GTIDs: SHOW GLOBAL VARIABLES LIKE 'GTID_PURGED';

-- Set GTID purged: SET GLOBAL GTID_PURGED = 'uuid:1-100';

-- Reset slave GTID: STOP SLAVE; RESET MASTER; SET GLOBAL GTID_PURGED = 'uuid:1-100'; START SLAVE;

-- Common GTID issues: -- 1. GTID consistency error -- 2. Missing GTIDs -- 3. Duplicate GTIDs ```

Step 6: Reset Replication

```sql -- Complete replication reset:

-- On slave: STOP SLAVE;

-- Reset slave completely: RESET SLAVE ALL;

-- Get master position: -- On master: SHOW MASTER STATUS; -- Note: File and Position

-- On slave: CHANGE MASTER TO MASTER_HOST = 'master-ip', MASTER_USER = 'repl', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000100', MASTER_LOG_POS = 12345;

START SLAVE;

-- For GTID: CHANGE MASTER TO MASTER_HOST = 'master-ip', MASTER_USER = 'repl', MASTER_PASSWORD = 'password', MASTER_AUTO_POSITION = 1;

START SLAVE;

-- Check status: SHOW SLAVE STATUS\G ```

Step 7: Sync from Backup

```bash # If replication too far behind or corrupted:

# 1. Stop slave: mysql -e "STOP SLAVE;"

# 2. Backup master: mysqldump --all-databases --master-data=2 --single-transaction > master_backup.sql

# 3. Get master position from backup: head -50 master_backup.sql | grep "CHANGE MASTER"

# 4. Restore on slave: mysql < master_backup.sql

# 5. Configure replication: mysql -e " CHANGE MASTER TO MASTER_HOST = 'master-ip', MASTER_USER = 'repl', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000100', MASTER_LOG_POS = 12345; START SLAVE; "

# Using GTID: mysqldump --all-databases --set-gtid-purged=ON --single-transaction > master_backup.sql ```

Step 8: Check Network and Permissions

```bash # Test connectivity: ping master-server

# Test MySQL port: nc -zv master-server 3306

# Check firewall: iptables -L -n | grep 3306

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

# Using ufw: ufw allow 3306/tcp

# Test MySQL connection: mysql -h master-server -u repl -p

# Check bind address: grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf

# Should be: bind-address = 0.0.0.0 # Or specific IP

# Check replication user: mysql -e "SHOW GRANTS FOR 'repl'@'%';"

# Recreate if needed: mysql -e " CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES; " ```

Step 9: Monitor Replication Lag

```sql -- Check lag: SHOW SLAVE STATUS\G -- Seconds_Behind_Master

-- Check heartbeat: SHOW STATUS LIKE 'Slave_heartbeat_period';

-- Configure heartbeat: STOP SLAVE; CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 30; START SLAVE;

-- Monitor in real-time: SELECT TIME_TO_SEC(TIMEDIFF(NOW(), ts)) AS lag_seconds FROM heartbeat;

-- Check replication filters: SHOW VARIABLES LIKE 'replicate%';

-- Check skip filters: SHOW VARIABLES LIKE 'binlog_ignore_db'; SHOW VARIABLES LIKE 'replicate_ignore_db';

-- Performance schema: SELECT * FROM performance_schema.replication_connection_status; SELECT * FROM performance_schema.replication_applier_status; ```

Step 10: MySQL Replication Verification Script

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

echo "=== MySQL Service ===" systemctl status mysql 2>/dev/null | head -5 || echo "Service not running"

echo "" echo "=== Slave Status ===" mysql -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_IO_Error|Last_SQL_Error" || echo "Not configured as slave"

echo "" echo "=== Master Status ===" mysql -e "SHOW MASTER STATUS\G" 2>/dev/null || echo "Cannot get master status"

echo "" echo "=== Server ID ===" mysql -e "SHOW VARIABLES LIKE 'server_id'" 2>/dev/null || echo "Cannot get server_id"

echo "" echo "=== Binary Log Configuration ===" mysql -e "SHOW VARIABLES LIKE 'log_bin'" 2>/dev/null || echo "Binary log not configured" mysql -e "SHOW VARIABLES LIKE 'binlog_format'" 2>/dev/null || true

echo "" echo "=== GTID Configuration ===" mysql -e "SHOW VARIABLES LIKE 'gtid_mode'" 2>/dev/null || echo "GTID not configured"

echo "" echo "=== Binary Logs ===" mysql -e "SHOW BINARY LOGS" 2>/dev/null | head -10 || echo "Cannot list binary logs"

echo "" echo "=== Network Connectivity ===" MASTER_HOST=$(mysql -N -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Master_Host" | awk '{print $2}') if [ -n "$MASTER_HOST" ]; then echo "Master: $MASTER_HOST" ping -c 2 -W 2 $MASTER_HOST 2>&1 | tail -2 nc -zv $MASTER_HOST 3306 2>&1 || echo "Cannot connect to master" fi

echo "" echo "=== Replication User ===" mysql -e "SHOW GRANTS FOR 'repl'@'%'" 2>/dev/null || echo "Replication user not found"

echo "" echo "=== Disk Space ===" df -h /var/lib/mysql 2>/dev/null || echo "Cannot check disk"

echo "" echo "=== Binary Log Size ===" du -sh /var/lib/mysql/mysql-bin.* 2>/dev/null | tail -5 || echo "No binary logs"

echo "" echo "=== Recent Errors ===" grep -i "error|fail" /var/log/mysql/error.log 2>/dev/null | tail -10 || echo "No errors in log"

echo "" echo "=== Recommendations ===" echo "1. Ensure both IO and SQL threads running" echo "2. Check Seconds_Behind_Master for lag" echo "3. Verify network connectivity to master" echo "4. Check replication user permissions" echo "5. Ensure server_id is unique" echo "6. Verify binary log format matches" echo "7. Monitor replication lag regularly" EOF

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

# Usage: /usr/local/bin/check-mysql-replication.sh ```

MySQL Replication Sync Checklist

Verify the Fix

```sql -- After fixing MySQL replication

-- 1. Check slave status SHOW SLAVE STATUS\G -- Slave_IO_Running: Yes -- Slave_SQL_Running: Yes -- Seconds_Behind_Master: 0

-- 2. Test data replication -- On master: INSERT INTO test VALUES (1, 'test'); -- On slave: SELECT * FROM test;

-- 3. Check consistency -- Use pt-table-checksum

-- 4. Monitor lag -- Seconds_Behind_Master should be stable

-- 5. Check logs -- No replication errors

-- 6. Verify GTID SHOW SLAVE STATUS\G -- Retrieved_Gtid_Set and Executed_Gtid_Set ```

Related Issues

• [Fix MySQL Connection Refused](/articles/fix-mysql-connection-refused)
• [Fix MySQL Replication Lag High](/articles/fix-mysql-replication-lag-high)
• [Fix PostgreSQL Replication Lag](/articles/fix-postgresql-replication-lag)