FAQ系列 | 无法配置主从复制案例

【场景】MySQL版本:Percona-Server 5.6.23,相关主要参数:

server_id = 3306
sync_binlog=1
gtid_mod = on
enforce-gtid-consistency = 1
log-slave-updates = 1
relay-log-purge = 1
relay_log_recovery = 1
master_info_repository = "TABLE"
relay_log_info_repository = "TABLE"

打算部署一个新的slave实例用于测试,图方便从master上直接把mysql这个系统库整个目录拷贝到slave上,进行初始化后,执行 CHANGE MASTER 配置主从复制,但却提示错误信息:

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 not configured or failed to initialize properly. You 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 设置错误了,比如和 master 的ID重复了。经过检查,确认不是这个原因,尝试检查了其他参数,也提示同样的错误。另外奇怪的是,上面的错误信息,未被记录到错误日志中。

【分析】应该注意到一点,上面提到初始化slave实例时,是从master上直接拷贝mysql系统库整个目录的,但并没有同时也拷贝 ibdata1、ib_logfile* 等InnoDB相关文件。其实问题就在这里,因为mysql系统库中,下面这几个表是采用InnoDB引擎的,所以初始化后,无法正常读写:

innodb_index_stats
innodb_table_stats
slave_master_info
slave_relay_log_info
slave_worker_info

经验证,通过客户端访问mysql库时,就会提示下面的错误信息了:

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.

【解决】本次案例中造成上述问题的原因是没有按照标准流程进行初始化,或者没有把master上的全部相关文件都拷贝到slave上去,导致mysql库下的几个InnoDB表无法使用。

解决的方法有两个:

1、删解除mysql系统库目录下上述几个表对应的ibd文件,执行 mysql_install_db 重新初始化;

2、从master将整个数据库全部文件都拷贝到slave上,并确认InnoDB的共享表空间文件及REDO LOG大小配置和master上是一致的;

由于InnoDB相关初始化不当导致异常的另一个例子是:[MySQL FAQ]系列 — MySQL无法启动例一

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

发表评论

电子邮件地址不会被公开。

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

Time limit is exhausted. Please reload CAPTCHA.