Fixing Master-Slave Replication Desynchronization in MySQL

Checking Replication Status

When high database traffic causes Master-Slave replication desynchronization, the first step is to check the replication status on the Slave.

Step 1: Check Slave Status

Run the following command on the Slave:

SHOW SLAVE STATUS\G

Key fields to check:

Field	Description
Slave_IO_Running	Yes if the I/O thread is running
Slave_SQL_Running	Yes if the SQL thread is running
Seconds_Behind_Master	0 means fully synchronized, a high value indicates lag
Last_IO_Error / Last_SQL_Error	Provides details if an error occurred

✅ If Seconds_Behind_Master is high, the Slave is lagging.
✅ If Slave_IO_Running or Slave_SQL_Running is No, replication is completely stopped.

---

Fixing Replication Issues

Method 1: Restart Replication

If replication is just stalled, restarting may resolve the issue:

STOP SLAVE;
START SLAVE;
SHOW SLAVE STATUS\G;

✅ If Seconds_Behind_Master = 0, the issue is resolved.

---

Method 2: Fixing Binlog Position on the Slave

If replication is broken, the Slave must be realigned with the Master’s binlog position.

1. Check the Master’s latest binlog position:

SHOW MASTER STATUS;

Example output:

+------------------+----------+
| File            | Position |
+------------------+----------+
| mysql-bin.000123 | 456789   |
+------------------+----------+

2. Check the Slave’s current position:

SHOW SLAVE STATUS\G;

• Relay_Master_Log_File → The last binlog file read by the Slave.
• Exec_Master_Log_Pos → The last binlog position executed on the Slave.

3. Manually adjust the Slave’s binlog position:

STOP SLAVE;
CHANGE MASTER TO 
    MASTER_LOG_FILE='mysql-bin.000123', 
    MASTER_LOG_POS=456789;
START SLAVE;
SHOW SLAVE STATUS\G;

If Seconds_Behind_Master = 0, replication is now synchronized.

---

Method 3: Rebuilding the Slave from Scratch

If the binlog position is lost or data inconsistency occurs, rebuilding the Slave is the safest approach.

1. Backup the Master’s database:

mysqldump -u root -p --single-transaction --all-databases > master_backup.sql

2. Restore the backup on the Slave:

mysql -u root -p < master_backup.sql

3. Reconnect the Slave to the Master:

CHANGE MASTER TO 
    MASTER_HOST='master-ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000123',
    MASTER_LOG_POS=456789;
START SLAVE;

The Slave is now fully resynchronized with the Master.

---

Choosing the Right Solution

Issue	Solution
Replication is stalled	STOP SLAVE; START SLAVE;
Slave is behind	Wait for Seconds_Behind_Master to reach 0
Partial binlog desync	Use CHANGE MASTER TO with correct binlog position
Data inconsistency	Rebuild the Slave with mysqldump

---

Summary

1. Run SHOW SLAVE STATUS\G to check replication state.
2. Try restarting replication with STOP SLAVE; START SLAVE;.
3. If necessary, adjust the Slave’s binlog position using CHANGE MASTER TO.
4. If replication is severely broken, rebuild the Slave from a fresh backup.