MySQL跨版本主从部署及问题

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

环境

主库：MySQL5.6.27 IP:192.168.100.1
从库：MySQL5.7.22 IP:192.168.100.2

计划部署过程

1. 主库创建复制账号

create user 'repl'@'192.168.100.1' identified by 'password';
grant replication slave on *.* to 'repl'@'192.168.100.1';

2. 主库进行全备

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

3. 全备恢复到从库上

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

4. 从库设置复制相关信息

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