Running «sql slave skip counter» for mysql Multisource Replication
How to skip one transaction in one channel of a Mysql multisource replication setup?
Recently Mysql announced a new feature called “Multisource Replication” , which is available in mysql versions 5.7.2+ . In a nutshell, Multi-Source Replication means that a Mysql slave can have more than one master.
How “Multi-Source Replication” works?
Now we have the concept of communication channels. Each communication channel is a connection from slave to master to get the binary log events. So we can run commands like “CHANGE MASTER TO” , “SHOW SLAVE STATUS”, “START SLAVE” with the argument “channel-name”. Each channel represents one master server. Most of the featured are described here in the official documentation.
How to skip one transaction on a particular channel ?
Consider the situation where one of the replication channels are broken due to a wrong command ran on master and we need to skip that transaction. Let us see how to skip transaction on a channel
First we will stop replication on all channels using
1 2 3 4 | # First stop all slaves stop slave; |
Next we will set the “skip counter”
1 2 3 4 | # set skip counter set global sql_slave_skip_counter=1; |
Now we will start replication on broken channel only
1 2 3 4 | # start slave that will skip one entry start slave for channel “channel-name”; |
With above statement, mysql will start replication on channel “channel-name” and skip the very next transaction.
Now we can start all slaves using
1 2 3 4 | # start all other slaves start slave; |
This is how we recover the replication by skipping the transactions in a particular channel
Please let me know if you face any issue. Always Happy to help ????