DB-유

Fixing a Broken MySQL Replica: Skipping Errors Safely

· mysql, replication, troubleshooting

The page comes in, a replica has stopped with Slave_SQL_Running: No, and there's a duplicate key or a missing row in the error. The tempting fix is to skip the event and go back to bed. Sometimes that's right. Sometimes it leaves the replica quietly different from the master, and you find out weeks later during a failover. Here is how I look at the failure first, skip one event on binlog-position or GTID replication, and check consistency afterwards. All of it is for MySQL 5.6 and 5.7 asynchronous replication.

Read SHOW SLAVE STATUS properly

SHOW SLAVE STATUS\G

The fields that matter:

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table shop.orders; Duplicate entry '88123' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000412, end_log_pos 73410221
Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-905
Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-901

Before touching anything, look at the actual event on the master to understand what was being applied:

mysqlbinlog --base64-output=decode-rows -v --start-position=73409000 --stop-position=73410221 /var/lib/mysql/mysql-bin.000412 | less

With row-based logging this shows the exact row values. Compare with the replica:

SELECT * FROM shop.orders WHERE id = 88123;

If the replica row is identical to what the master tried to insert, someone wrote to the replica directly (check read_only) and skipping is safe. If the row differs, you have real divergence and skipping only hides it.

Skipping one event with binlog positions (non-GTID)

When gtid_mode is OFF:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
SHOW SLAVE STATUS\G

sql_slave_skip_counter = 1 skips one event group – a whole transaction for InnoDB tables, so it will not leave half a transaction applied. If the replica stops again immediately with the next duplicate, repeat, but if you need to do this more than two or three times stop and rebuild the replica instead. My own rule is three skips at most.

Skipping one transaction with GTID

With gtid_mode = ON, sql_slave_skip_counter is refused. Instead you inject an empty transaction with the GTID of the failing one so the replica considers it executed. The failing GTID is the first one in Retrieved_Gtid_Set that is not in Executed_Gtid_Set – in the example above, :902.

STOP SLAVE;
SET GTID_NEXT = '3e11fa47-71ca-11e1-9e33-c80aa9429562:902';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

Double check the UUID and the sequence number. Injecting the wrong GTID makes the replica skip a transaction that has not even arrived yet.

Better: fix the data instead of skipping

For a 1032 Can't find record error (the master deleted or updated a row the replica does not have) the cleaner fix is to insert the missing row on the replica with binary logging disabled, then restart the SQL thread:

SET SESSION sql_log_bin = 0;
INSERT INTO shop.orders (id, customer_id, total, created_at) VALUES (88123, 4411, 129.90, '2017-10-22 18:41:07');
SET SESSION sql_log_bin = 1;
START SLAVE SQL_THREAD;

Take the values from the mysqlbinlog output above. This keeps the replica identical rather than merely running.

Please don't use slave_skip_errors

slave_skip_errors = 1062,1032 makes replication never stop on those codes. I dislike it intensely: every server I've seen with it had a replica that had been diverging for months. If you must use it, do it in a short repair window and take it out again.

Is the replica actually fine now?

Confirm that both threads run and lag goes down:

SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master decreasing

Then check consistency with Percona Toolkit. pt-table-checksum runs on the master and computes chunked checksums that are replicated and compared on the replica:

pt-table-checksum --databases=shop --replicate=percona.checksums h=master01,u=checksum,p=secret
pt-table-checksum --replicate=percona.checksums --replicate-check-only h=master01,u=checksum,p=secret

Any table listed with DIFFS greater than zero is out of sync. pt-table-sync --replicate=percona.checksums --print h=master01,... shows the statements needed to fix it, and --execute applies them.

Other stops you may hit

  • ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction – Use the empty-transaction method shown above.
  • Last_SQL_Errno: 1594 Relay log read failure: Could not parse relay log event entry – The relay log on the replica is corrupt, typically after a crash. Note Relay_Master_Log_File and Exec_Master_Log_Pos, then STOP SLAVE; RESET SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; START SLAVE; so the relay logs are re-fetched.
  • Last_SQL_Errno: 1236 Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' – The master already purged the binlog the replica needs. Skipping cannot help; rebuild the replica from a fresh backup.
  • Replica stops again a few seconds after every skip – Data divergence is widespread. Stop, take an xtrabackup or mysqldump --master-data=2 from the master, and reseed the replica.

Skip only what you've looked at.