Broken replication because of duplicate values MySQL 5.6 onward :
In non GTID enabled replication , we use to skip duplicate errors in case of Last_Errno: 1062 . But once we enable GTID we will have to deal it differently. The procedure is to follow the following simple steps
Let use assume two server A as master B as slave.
-
Check slave status as shown below
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: A Master_User: svc-mysql-repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000167 Read_Master_Log_Pos: 72611417 Relay_Log_File: relay-log.000364 Relay_Log_Pos: 776 Relay_Master_Log_File: mysql-bin.000161 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: admindb Replicate_Do_Table: Replicate_Ignore_Table: mysql.ibbackup_binlog_marker,mysql.backup_history,mysql.backup_progress,mysql.backup_sbt_history,mysql.inventory Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '21555' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO auditlogs (text, category, datetime, username) VALUES ('Starting application Web version 5.4', 'ACTION', '2017-07-26 09:49:41', '')' Skip_Counter: 0 Exec_Master_Log_Pos: 611 Relay_Log_Space: 5064799212 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_Error: Error 'Duplicate entry '21555' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO auditlogs (text, category, datetime, username) VALUES ('Starting application Web version 5.4', 'ACTION', '2017-07-26 09:49:41', '')' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4b7a0a70-146b-11e7-80f7-941882781532 Master_Info_File: /opsrt12p/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 170727 08:59:35 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4b7a0a70-146b-11e7-80f7-941882781532:1-87044138 Executed_Gtid_Set: 4b7a0a70-146b-11e7-80f7-941882781532:1-82186600 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
In above example we can see two Gtid values which are
Executed_Gtid_Set : represents the last executed statement , The set of global transaction IDs written in the binary log. This is the same as the value for the global gtid_executed
system variable on this server.
In above example we have following last executed Gtid value.
Executed_Gtid_Set: 4b7a0a70-146b-11e7-80f7-941882781532:1-82186600
and we have following master binlog file and position
Master_Log_File: mysql-bin.000167 Read_Master_Log_Pos: 72611417
- If we try to skip the counter we will get following .
mysql> stop slave; Query OK, 0 rows affected (0.00 sec) 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
- Now we should check the entry int the mentioned binary log at specific position on server A (master server) and we need to check how many more steps needs to be executed from the perticular Gtid. using following commands.
- create an temporary txt file using mysqlbinlog utility as shown
bash-4.2$ /apps/mysql/opsrt11p/install/bin/mysqlbinlog --base64-output=decode-rows --verbose mysql-bin.000167> /tmp/temp-binlog.txt
- Now view the temp-binlog.txt using less or any other utility and find out set of statement executed under 4b7a0a70-146b-11e7-80f7-941882781532:1-82186600 Gtid at step number 82186600 there may be few more steps to be executed therefore we need to skip step number 82186600 and move to next step of the statement under particular Gtid.
- Skip to next step of Gtid using following statement.
mysql> set GTID_NEXT="4b7a0a70-146b-11e7-80f7-941882781532:82186601; Query OK, 0 rows affected (0.00 sec) mysql> start slave;
- Now do show slave status again and you will see statement has been skipped and there are no duplicate errors any more.
Thanks for visiting , please dont forget to leave you feedback
Regards
Raja Naveed