mysql5.6.34 升级到mysql5.7.32

直接上步骤：

1.首先在mysql5.6.34 那台主机上使用下面的，命令进行备份
innobackupex --host=127.0.0.1 --user=root --password=xxx  --slave-info --kill-long-queries-timeout=100  /opt/2020-11-11

2.将备份传送到mysql5.7.32那台机器上

3.在mysql5.7.32 进入到备份文件夹里面，执行下面的命令进行恢复
innobackupex  --apply-log .

4.启动mysql5.7.32 数据库
/webapp/mysql-5.7.32_150/bin/mysqld_safe --defaults-file=/webapp/mysql-5.7.32_150/my.cnf --user=mysql &

5.开启主从同步mysql5,6 ->mysql5.7
change master to master_host='xxxx',master_user='xxx',master_password='xxxx',master_log_file='mysql-bin.001619',master_log_pos=541055364,Master_Port=3306,master_connect_retry=30;  

注意这里的master_log_file='mysql-bin.001619',master_log_pos=541055364 在xtrabackup_info 里记录

6.等待数据同步成功

但是mysql5.6 到mysql 5.7 需要更新数据字典，不然会报如下的错误
MySQL [(none)]> show status like 'read';
ERROR 1146 (42S02): Table 'performance_schema.session_status' doesn't exist
MySQL [(none)]>

查看报错信息如下:
2020-11-11T06:52:58.692884Z 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:52:58.692893Z 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:52:58.692947Z 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:52:58.692952Z 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:52:58.692985Z 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:52:58.692990Z 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:53:00.783115Z 7 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:53:00.783130Z 7 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:53:04.023675Z 7 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:53:04.023691Z 7 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:53:04.406669Z 7 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2020-11-11T06:53:04.406695Z 7 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

执行下面的命令进行字典更新

 

[root@cpe-twcny.res.rr.com bin]#./mysql_upgrade -uroot -p'xxxxx' -h'127.0.0.1';

.......

octopus_op.OP_APP_MERCHANT_REL                     OK
octopus_op.OP_APP_RULE_REL                         OK
octopus_op.OP_CAPACITY_INFO                        OK
octopus_op.OP_RULE_INFO                            OK
percona.checksums                                  OK
percona.dsns                                       OK
sys.sys_config                                     OK
uip.LM_UIP_RESULTCODE_MESSAGE_REL                  OK
uip.LM_UIP_TRADE_01                                OK
uip.LM_UIP_TRADE_02                                OK
uip.LM_UIP_TRADE_03                                OK
uip.LM_UIP_TRADE_04                                OK
uip.LM_UIP_TRADE_05                                OK
uip.LM_UIP_TRADE_06                                OK
uip.LM_UIP_TRADE_07                                OK
uip.LM_UIP_TRADE_08                                OK
uip.LM_UIP_TRADE_09                                OK
uip.LM_UIP_TRADE_10                                OK
uip.LM_UIP_TRADE_11                                OK
uip.LM_UIP_TRADE_12                                OK
uip.UIP_PLATCODE_DICTIONARY                        OK
uip.UIP_UPCODE_PLATCODE_REL                        OK
Upgrade process completed successfully.
Checking if update is needed.

然后重启数据库即可
MySQL [(none)]> show variables like 'read%';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| read_buffer_size     | 8388608 |
| read_only            | OFF     |
| read_rnd_buffer_size | 8388608 |
+----------------------+---------+
3 rows in set (0.00 sec)

MySQL [(none)]> select version();
+------------+
| version()  |
+------------+
| 5.7.32-log |
+------------+
1 row in set (0.00 sec)