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 ????

Author: , 0000-00-00