Mysql – Multi source replication not working in MySQL

master-mastermaster-slaveMySQLmysql-replication

I am trying to setup multi source replication. In my slave I have defined following two properties in its configuration file.

master-info-repository=table  
relay-log-info-repository=table

These entries are needed because I am defining channels in command "CHANGE MASTER TO" as below: 

change master to master_host="127.0.0.1", master_port=20000, master_user="replication",master_password="password1" for channel="master1";

Above command is showing error:

ERROR 1794 (HY000): Slave is not configured or failed to initialize
properly. Yo u must at least set –server-id to enable either a master
or a slave. Additional error messages can be found in the MySQL error
log.

SERVER ID is clearly defined in config file.

If I don't define those two properties (shown on top) in config file and start slave without "for channel" the it works fine. Only when I try to define multi source it is showing this error.

Error log file contain following entries:

2017-01-01T12:41:54.446764Z 0 [ERROR] Error in checking
mysql.slave_master_info repository info type of TABLE.
2017-01-01T12:41:54.446764Z 0 [ERROR] Error creating master info:
Error checking repositories. 2017-01-01T12:41:54.446764Z 0 [ERROR]
Failed to create or recover replication info repository.
2017-01-01T12:41:54.446764Z 0 [ERROR] Failed to create or recover
replication info repositories.

How do I fix this issue?

EDIT
One thing I came to know that this is related to 5 tables innodb_index_stats, innodb_table_stats, slave_master_info, slave_relay_log_info, slave_worker_info. Even if I delete those tables and then create again then it still shows same error. By the way I am using multiple instance on a single machine.

Best Answer

You are missing information in your change master command. For multi-source replication, just like ordinary replication, you need to include either initial log coordinates (when the master is using binary logging) or you need to specify auto positioning (when the master is using GTIDs). Here is the mysql documentation on the additional parameters you need to specify.

For auto positioning:

change master to 
  master_host="127.0.0.1", 
  master_port=20000, 
  master_user="replication",
  master_password="password1",
  master_auto_position=1 
for channel="master1";

For log-based replication:

change master to 
  master_host="127.0.0.1", 
  master_port=20000, 
  master_user="replication",
  master_password="password1",
  master_log_file='some_log_file.bin',
  master_log_pos=1
for channel="master1";
Related Topic