Fix MySQL GTID Replicating Duplicate Key Error

Recently, the MySQL cluster is always out of sync. I checked show slave status\G and found out that some errors that I had never encountered before were reported. Resetting the master-slave and re-importing did not work, so I checked the information and found a solution. .

0, error message

1
2
3

Error Duplicate entry 'xxx' for key 'xxx'
# or
Error Duplicate name 'xxx'

Error code: 1053, 1146 or 1060, 1062

1, MySQL traditional master-slave replication

solution

1
2
3

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N ; #skip N transactions
start slave;

When using, generally replace N with 1 and execute.

Or in my.cnf, set to ignore errors:

1
2
3

[mysqld]
slave-skip-errors=1053,1146 #Skip the specified type of error
#slave-skip-errors=all #Skip all errors (not recommended)

2, MySQL GTID replication

In GTID replication, using the above method will report an error:

1
2

mysql> set global sql_slave_skip_counter = 1;
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

Therefore, it is necessary to use the method of inserting empty transactions.

View host status:

1

show master status \G

return:

1
2
3
4
5
6
7

*************************** 1. row ********************* *****
File: mysql-115-bin.000002
Position: 1273
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 94b06c13-1f09-11e5-97ea-000c29c8caec:1-16
1 row in set (0.00 sec)

The transaction id of the master library is 1-16, and then check the synchronization status of the slave library:

1

show slave status \G

return:

1
2
3
4
5

...
Last_SQL_Error: Worker 2 failed executing transaction '' at master log mysql-115-bin.000002, end_log_pos 614; Error 'Duplicate entry '620' for key 'PRIMARY'...
Retrieved_Gtid_Set: 94b06c13-1f09-11e5-97ea-000c29c8caec:1-16
Executed_Gtid_Set: 94b06c13-1f09-11e5-97ea-000c29c8caec:1-10

Interpretation:

• Retrieved_Gtid_Set item: Records the location where the relay log obtained the binlog log from the Master
• Executed_Gtid_Set item: record the binlog log position executed by the local machine, and this item on the slave machine includes the binlog log positions of the master machine and the slave machine.

Executed_Gtid_Set is 1-10, indicating that it is stuck in the 11th transaction.

Insert an empty transaction, skipping the error:

1
2
3
4
5

STOP SLAVE;
SET GTID_NEXT="94b06c13-1f09-11e5-97ea-000c29c8caec:11";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;

It can solve the problem.

Or modify the configuration my.cnf:

Comment first then view it after your comment is approved. Join QQ Group to display all hidden texts.

3, Pt-Slave-Restart tool

Execute in SSH:

1
2
3
4
5
6
7

#Ignore all 1062 errors and start the SLAVE process again
pt-slave-resetart -S./mysql.sock --error-numbers=1062

# or

#Check that the error message contains Duplicate, ignore it, and start the SLAVE process again
pt-slave-resetart -S./mysql.sock --error-text="Duplicate"

4. Summary

MySQL master-slave replication errors can generally be solved by skipping transactions or inserting empty transactions. The laziest way is to modify the configuration and ignore the specified errors.

No matter which replication method is used, you can also re-export the master database data to reconfigure the master-slave.

5. Reference

1. [MySQL FAQ] series - how to ignore certain binlog events in different replication modes¹
2. Insert empty transaction to fix mysql 5.6 GTID replication error²