MySQL replication day-to-day operations and failure scenarios (8.0 / 8.4)

Day-to-day operations and failure recovery runbook for an existing source + replica topology (async / semi-sync, GTID or binlog position): health and lag checks, planned switchover, failover, rejoining the old source, rebuilding a replica (clone / XtraBackup / mysqldump), errant GTIDs, 1062/1032 errors, data recovery from a delayed replica, repointing a replica and binlog cleanup. Every scenario follows the pattern diagnosis → safety net → apply → verification → follow-up. This is not an installation guide: to set up replication for the first time open the «MySQL GTID replication» guide, for InnoDB Cluster (Group Replication) the «InnoDB Cluster operations» guide.

Topology: 1 source + 1..5 replicas (async / semi-sync)

Scenarios

• Status and lag check (is replication healthy?) — Read-only: IO/SQL threads, last errors, real lag, semi-sync status and errant GTID check; nothing is changed, no downtime. If you are not sure what to do, start here: the last step points you to the right scenario based on the output.
• Planned switchover (hand the source role over to a replica) — The source and replicas are healthy; you are handing the source role over to a replica in a planned way for maintenance, hardware replacement or an upgrade. Writes stop for a few minutes, no data is lost; switching back is the same scenario in reverse.
• Source lost: fail over to the most advanced replica — The source has crashed or is unreachable and will not be back soon (IO thread Connecting on the replicas, 2003/2013). The most advanced replica becomes the new source and the others connect to it. Writes are unavailable until the new source is opened; with async replication the last few transactions of the source may be lost.
• Rejoin the old source as a replica after failover — A failover has been done and the old source server is back. It is first closed to writes and its binlogs are copied, then checked for errant transactions; if clean it becomes a replica directly, otherwise it is rebuilt from the new source. No downtime on the new source.
• Rebuild a replica from scratch (clone / XtraBackup / mysqldump) — The binlogs the replica needs have been purged on the source (1236), its data has drifted from the source, 1062/1032 keep coming back, or the replica's disk was lost. All data on the replica is replaced with a copy of the source; the source stays online, the target replica is unavailable during this time.
• Errant GTID: the replica has transactions the source does not have — There are transactions written directly to the replica (while super_read_only was off) or left over from an old source; on failover these transactions spread to the other replicas or stop replication with 1236. Fix: empty transaction, apply them on the source, or rebuild. No downtime.
• Replication stopped: 1062 Duplicate entry / 1032 Can't find record — The SQL thread stopped with a data error (Replica_SQL_Running: No, Last_SQL_Errno 1062/1032/1146/1677…). First the row is brought to its state on the source; if that is not possible, a single transaction is skipped as a last resort. Only this replica is affected: it falls behind and applications reading from it see stale data.
• Delayed replica (SOURCE_DELAY): set it up or recover deleted data — You want to keep a replica deliberately N seconds behind (set / remove the delay), or to recover data that was deleted/changed by mistake (DROP, DELETE/UPDATE without WHERE) from the delayed replica before it is applied there. The source stays online.
• Move a replica to a different source (chaining / repointing) — You want to attach a replica under another replica (chain / intermediate replica) or back to the source; no data is copied, only the connection changes. The moved replica stops for a few seconds.
• Binlog cleanup and retention (disk filling up) — Binlogs are filling the disk or you want to set the retention period; files are purged without removing the ones replicas and PITR still need. No downtime.

Before you start

• MySQL …… (8.0.26 and later)… on the same minor version on all servers…, gtid_mode = ON and enforce_gtid_consistency = ON…, binlog enabled…; the operating system root can connect with mysql without a password (auth_socket or ~/.mylogin.cnf). The topology was set up with the replication installation guide or equivalently.
• Roles verified to match the inputs: … (…)… is the lost old source… is the current source (read_only = 0, no replica configuration)…, the servers in the list are replicas.… Promoted to source during the failover: … (…).…
• The password of the … account is known. The account exists for every server's IP ('…'@'<ip>', REQUIRE SSL); the steps use it to read values such as the GTID set on the other servers and for the replication connection on a role change. The password contains no ', " or \.
• The servers can reach each other on port ….
• Both semi-sync plugins are installed and ACTIVE on all servers: rpl_semi_sync_source (semisync_source.so) and rpl_semi_sync_replica (semisync_replica.so); on a role change both sides are enabled or disabled on every server. If the old-named rpl_semi_sync_master / rpl_semi_sync_slave (before 8.0.26, deprecated in 8.4) are installed, the rpl_semi_sync_source_enabled / rpl_semi_sync_replica_enabled variables used in the steps do not exist: first remove the old plugin with UNINSTALL PLUGIN and install the new one (both cannot be loaded on the same server; INSTALL PLUGIN is rejected on a read-only server, so super_read_only is turned off temporarily).
• On the replica that becomes the new source / intermediate server, log_bin = ON and log_replica_updates = ON (the default in 8.x); otherwise its binlog does not contain the transactions coming from the source and the other replicas cannot connect to it.
• Root SSH access (key-based) exists between the servers involved for file transfer…: … → ……. If not, move the files with your organization's approved method; the steps show the file paths.
• Percona XtraBackup of the same major version as MySQL is installed on the donor (…) and the target (…); the backup account bkpuser (auth_socket) exists on the donor — the first two phases of the XtraBackup guide.
• Change approval and a communication channel are in place (application team, maintenance window). For a role change it is known who changes the application's write endpoint (VIP, DNS, ProxySQL / HAProxy / Router) and how.

Phases of the guide

• Status: source, replicas and lag (read-only) — Show the source's role, binlog position and connected replicas · Show the replicas' thread status, errors and lag · Measure the real lag and the apply queue from performance_schema · Errant GTID check (does the replica have transactions the source does not have?) · Interpret the result and choose the right scenario
• 1. Diagnosis: is it ready for switchover? — Check the source and the connected replicas · Verify that the candidate replica is suitable · Check that all replicas are healthy
• 2. Safety net — Record the current state on all servers
• 3. Apply: hand over the role — Stop the application's writes · Close the source to writes and get the last position · Wait for the replicas to catch up with the source · Promote the candidate to source · Make the old source a replica of the new source · Connect the other replicas to the new source (…) …
• 4. Verification — Make a test write on the new source · Check replication and the test row on all servers
• 5. Follow-up — Drop the test database and update operations
• 1. Diagnosis: is the source really lost, and which replica is the most advanced? — Try to reach the source one last time (from the candidate replica) · Isolate the old source (fencing) · Wait on every replica until the transactions in the relay log are applied · Determine the most advanced replica · Determine the most advanced replica (position-based)
• 2. Safety net — Record the current state on the replicas
• 3. Apply: failover — Promote the candidate to source · Connect the other replicas to the new source (…) · Open writes on the new source · Redirect the application to the new source
• 4. Verification — Make a test write on the new source · Check replication and the test row on the replicas
• 5. Follow-up — Plan for lost transactions, backups and the old source
• 1. Diagnosis: is the old source clean? — Show the old source's status · Find errant transactions (compare with the new source) · Rejoin decision with position-based replication
• 2. Safety net — Close the old source to writes immediately and record its state · Extract the errant transactions to a separate file
• 3. Apply: connect as a replica — Connect the old source to the new source
• 4. Verification — Verify that the old source is running as a replica
• 5. Follow-up — Plan the topology and the future of the old source
• 1. Diagnosis: what is the errant set? — Calculate the errant GTID set
• 2. Safety net: save the contents of the errant transactions — Decode the errant transactions from the binlog and save them
• 3. Apply: inject empty transactions on the source — Run the errant GTIDs as empty transactions on the source
• 3. Apply: apply the errant transactions on the source — Extract the errant transactions to a file and copy it to the source · Apply the transactions on the source
• 4. Verification — Verify that no errant set remains
• 5. Follow-up: prevent recurrence — Find the root cause and protect the replicas
• 1. Diagnosis: which transaction, which row? — Show the error and the failed transaction · Decode the failed transaction's rows from the relay log · Compare the row on the source and the replica
• 2. Safety net — Record the replica's status and the table's current state
• 3. Apply: fix the row on the replica — Prepare the fix SQL · Apply the fix without writing to the binlog and start replication
• 3. Apply: skip the failed transaction (last resort) — Skip only the failed transaction
• 4. Verification — Compare the row and the table checksum on both sides
• 5. Follow-up — Find the root cause
• 1. Diagnosis — Show the replica's current delay setting
• 2. Safety net — Record the replica's status