本文共 3874 字,大约阅读时间需要 12 分钟。
直接上步骤:
1.首先在mysql5.6.34 那台主机上使用下面的,命令进行备份
innobackupex --host=127.0.0.1 --user=root --password=xxx --slave-info --kill-long-queries-timeout=100 /opt/2020-11-112.将备份传送到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)
转载地址:http://ezhji.baihongyu.com/