How to Diagnose and Resolve MySQL Replication Errors in High‑Availability Environments

Collect Replication Information

When replication errors occur, first gather replication status and error details using commands such as SHOW SLAVE STATUS and by inspecting the MySQL error log.

Master_Log_File: mysql-bin.000063
Read_Master_Log_Pos: 282657539
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 313885
Slave_IO_Running: Yes
Slave_SQL_Running: No
Master_UUID: 0c1b77a7-c113-11e6-9bd6d4ae52a34783
Retrieved_Gtid_Set: 0c1b77a7-c113-11e6-9bd6d4ae52a34783:1-5
Executed_Gtid_Set: 0c1b77a7-c113-11e6-9bd6d4ae52a34783:1-5,3a169e6c-f1d0-11e6-bb30-d4ae52a34783:1

Check Error Log

The error log records the exact error messages, start/stop timestamps, and GTID information.

Analyze Binary Logs

Inspect the master binlog, relay log, and slave binlog to locate the failing transaction. Use SHOW BINLOG EVENTS or mysqlbinlog to decode events.

Common Replication Errors

Database already exists : error 1007 when creating a database that already exists on the slave.

Duplicate primary key : error 1062 when inserting a row with an existing primary key.

Missing table : error 1146 when the slave tries to execute a statement on a table that does not exist.

Binlog loss : error 1236 when the master binlog file is missing or truncated.

GTID and Version Differences

GTID ensures that a transaction is not re‑executed on the slave. MySQL 5.6 stores GTID in global system variables, while MySQL 5.7 uses the mysql.gtid_executed table. Binlog formats (STATEMENT vs ROW) also affect the recorded events.

Configuration Tips

Key variables include gtid_mode and binlog_format. Enabling GTID simplifies troubleshooting, and using ROW format provides detailed event data.

Summary

By systematically collecting replication status, error logs, and binary log contents, and by understanding version‑specific differences, most MySQL replication issues can be diagnosed and resolved.