In this post I will share the smartest way to skip slave error like duplicate entry, missing rows etc. in MySQL GTID based replication.
In normal replication it is quite easy to skip an error but in GTID based it becomes quite tricky to do the same.
The traditional method of inserting a empty transaction is still hard to follow, so let me share the quickest way to do the same.
MySQL skip slave error in GTID based Replication:
stop slave; SET global GTID_MODE=ON_PERMISSIVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G SET global GTID_MODE=ON;
The above method may create inconsistencies between primary and secondary by skipping the transactions so please make sure you check the data consistency.
According to the MySQL documentation, there are four different GTID modes available as noted below
- OFF: Both new and replicated transactions must be anonymous.
- OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions.
- ON_PERMISSIVE: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions.
- ON: Both new and replicated transactions must be GTID transactions.
This way you can skip any error on your slave in the quickest way.