mysql主从版本,MySQL跨版本主从部署及问题

最近搞了个跨版本的MySQL主从同步，之前没搞过这个，遇到了一些问题，记录一下。

环境

主库：MySQL5.6.27 IP:192.168.100.1

从库：MySQL5.7.22 IP:192.168.100.2

计划部署过程

主库创建复制账号

create user 'repl'@'192.168.100.1' identified by 'password';

grant replication slave on *.* to 'repl'@'192.168.100.1';

主库进行全备

mysqldump -uuser -ppassword --all-databases --master-data --single-transaction > all_databases.sql

全备恢复到从库上

mysql -uroot -ppassword -h192.168.100.2 < all_databases.sql

从库设置复制相关信息

CHANGE MASTER TO

master_host='192.168.100.1',

master_port=3306,

master_user='repl'

master_password='password';

遇到问题及处理方法

数据恢复时间过长

# 加快备份恢复速度

# 禁用binlog，数据恢复过程不写入到日志中，可以加快恢复速度。

mysql -uroot -ppassword -h192.168.100.2

mysql > set sql_log_bin=0;

mysql > source all_databases.sql

恢复备份后创建账号报错

原因：

备份时备份了mysql.user数据库，MySQL5.7对权限表进行了升级，表结构有变更

处理方法：

使用mysql_upgrade升级数据库

mysql_upgrade --force

CHANGE MASTER报错

错误信息：

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.

原因：

mysql库中记录日志相关的表结构有变更

处理方法：

删除相关表并重新加载

# 删除表

use mysql

drop table innodb_index_stats;

drop table innodb_table_stats;

drop table slave_master_info;

drop table slave_relay_log_info;

drop table slave_worker_info;

# 重新加载表

mysql> source /usr/local/mysql3306/share/mysql_system_tables.sql

后续优化

遇到的问题都是由于mysql库中的表结构更改导致的。

以后再进行类似操作时，备份时可以只对用户数据库进行备份，这样不更改系统库的表结构，就不会遇到类似错误了。

权限表可以通过导出语句的方式进行备份，例如：

# 适用于MySQL5.6及以前版本

mysql -e "select distinct concat ('show grants for ','''',user,'''@''',host,'''',';') as query from mysql.user where user!='root'" |grep -v query >/tmp/showgrants.sql && mysql < /tmp/showgrants.sql |egrep -v 'Grants for|query'

或者使用pt-show-grants工具导出权限表

pt-show-grants -uroot -ppassword