Multi-channel replication is one of the  great feature shipped with MySQL 5.7, With allowed the capability of slave to have many masters, having a channel for each master by which they replicate. Each channel id has a unique “channel_name”

In the above DB Architecture “channel_1, channel_2 and channel_3” represent the channel_name used for replication from different MySQL servers ( Source ).

In this blog we are not going see about configuration of multi_source replication, rather we are going to see about rolling back multi-source replication in MySQL.

Recently we were working on a client, where we had deployed multi-channel replication replication from two master onto a single slave, sync was happening very fine

Then came the situation to break the replication from two channel and make it as normal replication strategy in default mode ie., with single master and single slave. We had faced a few issues while disabling that.

Version used : 5.7.19-17-57-log Percona server

Stopping the Multi channel replication.

 
mysql > stop slave for channel ‘channel_1’;
mysql > stop slave for channel ‘channel_2’;
	

Channels has been reset as below.

 
mysql > reset slave for channel ‘channel_1’;
mysql > reset slave for channel ‘channel_2’;
	

Reseting all available channels ( if any present in case )

 
mysql > reset slave;
	

Since reset of all the channels has been completed, We have proceed with the ‘CHANGE MASTER’ command as below for making it as normal slave without specifying any channel name.

 
mysql> change master to master_host='172.0.0.10',master_user='repltest',master_password='repltest',master_log_file='mysql-bin.000135',master_log_pos=1011568029;
	

On execution i had encountered the below error.

 
“ERROR 3079 (HY000): Multiple channels exist on the slave. Please provide channel name as an argument.”
	

Tried to dig in deeper. MySQL server is under the impression “once the replication channel is enabled ( multi source ), it always demands a channel name  for any ‘CHANGE MASTER’ related commands irrespective of resetting all channels. Adding a unique ‘channel name’ at the end along with ‘CHANGE MASTER’ command allows the smooth executions but that is not the goal.

We tried the below flush information in memory through replication replication related flush commands and flush status to overcome this behaviour, but that does not helped to.

 
mysql> flush status;
mysql> flush logs;
mysql> flush relay logs;
	

The possible place where MySQL might get the channel information is the replication meta data ( master info). In MySQL 5.7 these informations are stored as tables by default inside mysql DB for crash safe and consistency. When validated we are still able to note the channel information inside the table slave_master_info.  The table is truncated to empty its content.

 
mysql> truncate table mysql.slave_master_info;
Query OK, 0 rows affected (0.01 sec)
	

Once again we tried the “CHANGE MASTER” , the same error persisted,since replication metadata info is cached on to to the memory it prevented it. To clear cache we did a restart of mysql service.  ‘CHANGE MASTER ‘ worked as expected. This can be considered as hard method, there is one more easy method, Thanks to Pavel Katiushyn who has pointed it out

 
mysql> reset slave all;
	

This removes all the ‘channel_name’ and make it to default mode.