Diagnosing and Fixing MySQL Binlog Replication Failures

-- Check current binlog format
SHOW VARIABLES LIKE 'binlog_format';

-- MySQL 8.0+ default and recommended:
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_row_image = 'FULL';

-- On the replica:
SHOW REPLICA STATUS\G

-- Key fields to check:
-- Replica_IO_Running: Yes/No
-- Replica_SQL_Running: Yes/No
-- Last_SQL_Error: error message
-- Seconds_Behind_Source: lag in seconds

# Use mysqlbinlog to inspect the failing event
mysqlbinlog --base64-output=DECODE-ROWS -v \
  --start-position=POSITION \
  /var/lib/mysql/binlog.000042 | head -100

-- On the replica: identify the conflicting row
SELECT * FROM orders WHERE id = 12345;

-- Option 1: Delete the conflicting row and let replication re-insert it
DELETE FROM orders WHERE id = 12345;
START REPLICA;

-- Option 2: Skip ONE event (non-GTID mode only)
STOP REPLICA;
SET GLOBAL SLAVE_SKIP_COUNTER = 1;
START REPLICA;

-- Inject a no-op transaction to skip the failing GTID
STOP REPLICA;
SET GTID_NEXT = 'abc123:100';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;

# Step 1: Find diverged tables with pt-table-checksum
pt-table-checksum \
  --user=checker --password=secret \
  --host=primary.db.internal \
  --replicate=percona.checksums \
  --databases=myapp

# Step 2: Sync diverged rows
pt-table-sync \
  --execute \
  --sync-to-master \
  --user=syncer --password=secret \
  h=replica.db.internal,D=myapp,t=orders

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log_recovery = ON
relay_log_info_repository = TABLE
master_info_repository = TABLE
binlog_format = ROW
binlog_row_image = FULL
read_only = ON
super_read_only = ON