multi source replication mysql,Disabling Multi-Source Replication in MySQL 5.7

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 ”

Multi-Channel Replication

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 and we are able to rollback the Multi-source replication successfully.

注意：本文来自Planet MySQL。本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证，请您自行验证核实并承担相关的风险与后果！

CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场，本站不承担此类作品侵权行为的直接责任及连带责任。您有版权、意见、投诉等问题，请通过[eMail]联系我们处理，如需商业授权请联系原作者/原网站。