背景:zabbix 数据库迁移,搭建主从,主是5.6.25,从是5.7.15,流式备份应用 redo.log 之后,change master 和reset slave 时报出如下错误
mysql> CHANGE MASTER TO-> MASTER_HOST='192.168.40.129',-> MASTER_USER='repl',-> MASTER_PASSWORD='repl_123',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='mysql-bin.000005', -> MASTER_LOG_POS=749,-> MASTER_AUTO_POSITION=0;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.
原因:从 5.6.25 版本使用innobackupex 备份,在 5.7.15 版本中应用恢复,在 5.6.25 版本中,主从信息记录到了文件中,5.7.15 版本中的主从信息记录的是在表中,所以数据备份后需要对下面几张表进行重建。
--5.6.25mysql> show variables like '%repository%';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| master_info_repository | FILE || relay_log_info_repository | FILE |+---------------------------+-------+2 rows in set (0.00 sec)
--5.7.15mysql> show variables like '%repository%';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| master_info_repository | TABLE || relay_log_info_repository | TABLE |+---------------------------+-------+2 rows in set (0.00 sec)
解决步骤:
1、drop 备份的 ibd表
use mysqldrop table slave_master_info;drop table slave_relay_log_info;drop table slave_worker_info;drop table innodb_index_stats;drop table innodb_table_stats;
2、重建
mysql> source /usr/local/mysql/share/mysql_system_tables.sqlQuery OK, 0 rows affected, 1 warning (0.00 sec)
3、重启数据库
[root@zero02 data]# /etc/init.d/mysqld restartShutting down MySQL.. [ OK ]Starting MySQL.. [ OK ]
至此,问题解决,登陆数据库,重新 change master to 即可!