MySQL Error: Must at least set–server-id to enable either a master or a slave

"Scene" MySQL version: Percona-server 5.6.23, related main parameters:

server_id = 3306sync_binlog=1gtid_mod = onenforce-gtid-consistency = 1log-slave-updates = 1relay-log-purge = 1relay_log_ recovery = 1master_info_repository = "Table" relay_log_info_repository = "Table"

To deploy a new slave instance for testing, the diagram is convenient to copy the entire directory of MySQL system library from master to slave, after initialization, perform change master configuration master-slave copy, but prompt error message:

mysql> Change Master to master_host= ' 10.x.x.x ', master_port=3306,master_user= ' repl ', master_password= ' xx ', master_ Auto_position=1; ERROR 1794 (HY000): Slave is isn't configured or failed to initialize properly. You must in least set–server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

The most intuitive response to this error message is that the server_id is incorrectly set, such as repeating the ID of master. After checking, verify that this is not the reason, try to check the other parameters, also prompt the same error. It is also strange that the above error message is not logged in the error log.

"Analysis" should note that the above mentioned initialization of the slave instance, the entire directory of the MySQL system library is copied from master directly, but does not also copy ibdata1, ib_logfile* and other InnoDB related files. In fact, the problem is here, because the MySQL system library, the following tables are using the InnoDB engine, so after initialization, can not read and write:

Innodb_index_statsinnodb_table_statsslave_master_infoslave_relay_log_infoslave_worker_info

It is verified that the following error message will be prompted when accessing the MySQL library through the client:

2015-05-04 17:21:25 14407 [warning] innodb: cannot open table mysql/ Innodb_index_stats from the internal data dictionary of innodb though  the .frm file for the table exists. see http://dev.mysql.com/doc /refman/5.6/en/innodb-troubleshooting.html for how you can resolve the  problem.2015-05-04 17:21:25 14407 [warning] innodb: cannot open table  mysql/innodb_table_stats from the internal data dictionary of innodb  though the .frm file for the table exists. see http:// Dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve  the problem.2015-05-04 17:21:25 14407 [warning] innodb: cannot open  table mysql/slave_mastEr_info from the internal data dictionary of innodb though the  .frm file for the table exists. see http://dev.mysql.com/doc/refman/ 5.6/en/innodb-troubleshooting.html for how you can resolve the  problem.2015-05-04 17:21:25 14407 [warning] innodb: cannot open table  Mysql/slave_relay_log_info from the internal data dictionary of innodb  though the .frm file for the table exists. see http:// Dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve  the problem.2015-05-04 17:21:25 14407 [warning] innodb: cannot open  table mysql/slave_worker_info from the internal data dictionary of  innodb though the .frm file for the table exists. see http://dev.mysql.com/doc/refman/5.6/en/ Innodb-troubleshooting.html for how you can resolve the problem.

The cause of the problem in this case is that it was not initialized in accordance with the standard process, or that all the related files on master were not copied to slave, resulting in several InnoDB tables under the MySQL library that could not be used.

There are two ways to solve this problem:

1. Delete the IBD files corresponding to the above tables in the MySQL system library directory and perform mysql_install_db reinitialization;

2, from master to the entire database all files are copied to slave, and confirm that the InnoDB shared tablespace file and redo log size configuration and master is consistent;

This article from "11726068" blog, declined reprint!

MySQL Error: Must at least set–server-id to enable either a master or a slave