With generic asynchronous MySQL Replication we often use skip_slave_counter to tackle / skip anonymous transactions which creates errors or halt MySQL Replication. But if we try to skip slave counter whilst GTID_MOD is ON we get following error.
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON.
In this case we should do following steps
- Stop Replication.
- .Set GTID_MODE to ON_PERMISSIVE
- Skip slave counter.
- Start slave.
- Set GTID_MODE to ON once it catches the master.
GTID_MODE is dynamic variable which can be set on the run. Here we need to look at GTID_MODE following options.
- 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.
All these steps are shown below
mysql> stop slave; mysql> set global gtid_mode=ON_PERMISSIVE; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G; ### Once caught up set it back mysql> set global gtid_mode=ON;
Thanks for visiting , please dont forget to leave you feedback