Troubleshooting·MySQL
MySQL Replication Failure and Failover
A local write on a replica collides with the source's next insert and replication stops with Last_SQL_Errno: 1062. The IO thread stays green throughout, which is why monitoring only one of the two threads misses it entirely.
Replication Guide 30 of 45 Advanced
- OSUbuntu 26.04 LTS
- MySQL8.4.10-0ubuntu0.26.04.1
- ReplicationGTID, ROW format, auto-position
- TimeAbout 18 min
Seconds_Behind_Source: NULL is the single most reliable signal that replication is not running. A lag alert that treats NULL as zero will never fire on this failure.
| Server Name | IP Address | OS | Roles | CPU | RAM | HDD |
|---|---|---|---|---|---|---|
| db-a01 | 192.168.0.81 | Ubuntu 26.04 LTS | Primary / Source / Replica Set Member 1 | 2 Core | 4 GB | 50 GB |
| db-b01 | 192.168.0.82 | Ubuntu 26.04 LTS | Standby / Replica / Replica Set Member 2 | 2 Core | 4 GB | 50 GB |
| db-c01 | 192.168.0.83 | Ubuntu 26.04 LTS | Replica Set Member 3 / Cascading and Delayed Replica | 2 Core | 4 GB | 50 GB |
This guide includes
Use this when replication has stopped and you need to know why. This matters because the usual cause is a local write on the replica colliding with the source - and proving where the offending transaction came from turns a mystery into a diagnosis.
- stopping and starting the replica deliberately, before breaking anything
- breaking it the way it really breaks - a local write with no protection in place
- proving where the offending transaction came from, in two commands
- promoting a replica
Before you start
- A running replica, and a willingness to break it.
-
Stop and start the replica deliberately
-
Break it the way it really breaks
-
Prove where the offending transaction came from
-
Promote a replica