通过rpm方式将mysql5.6.48升级到mysql5.7.29

项目背景

项目前期使用的是阿里云的rds，数据库版本为5.6.48，后面自建数据库时也选择了相应的系统版本（为了方便迁移数据），但5.6.48版本有些旧了，5.7中增加了一些新特性，为了后续顺利升级，这里提前做一下升级测试。

系统环境

升级前
centos7.5+mysql5.6.48
升级后
centos7.5+mysql5.7.29
说明：mysqlq5.6.46是通过rpm包方式来安装的

数据库目录及配置文件

数据目录：/data/mysql_13306
配置文件：/etc/my13306.cnf

升级方式in-place

步骤1、关闭数据库
mysql> set global innodb_fast_shutdownn=0;
[[email protected] sbin]# /etc/mysql_stop.sh

步骤2、卸载mysql5.6.48

rpm -e -nodeps MySQL-client-5.6.48-1.el7.x86_64
rpm -e -nodeps MySQL-devel-5.6.48-1.el7.x86_64
rpm -e -nodeps MySQL-embedded-5.6.48-1.el7.x86_64
rpm -e -nodeps MySQL-server-5.6.48-1.el7.x86_64
rpm -e -nodeps MySQL-shared-5.6.48-1.el7.x86_64
rpm -e -nodeps MySQL-shared-compat-5.6.48-1.el7.x86_64
rpm -e -nodeps MySQL-test-5.6.48-1.el7.x86_64
rpm -e -nodeps pdksh-5.2.14-30.x86_64

步骤3、安装5.7.29
rpm -ivh /opt/mysql-community-common-5.7.29-1.el7.x86_64.rpm
rpm -ivh /opt/mysql-community-libs-5.7.29-1.el7.x86_64.rpm
rpm -ivh /opt/mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm
rpm -ivh /opt/mysql-community-client-5.7.29-1.el7.x86_64.rpm
rpm -ivh /opt/mysql-community-devel-5.7.29-1.el7.x86_64.rpm
rpm -ivh /opt/mysql-community-embedded-5.7.29-1.el7.x86_64.rpm
rpm -ivh /opt/mysql-community-embedded-compat-5.7.29-1.el7.x86_64.rpm
rpm -ivh /opt/mysql-community-embedded-devel-5.7.29-1.el7.x86_64.rpm
rpm -ivh /opt/mysql-community-server-5.7.29-1.el7.x86_64.rpm
rpm -ivh /opt/mysql-community-test-5.7.29-1.el7.x86_64.rpm
步骤4、启动数据库
[[email protected] mysql57]#/etc/mysql_start.sh
[[email protected] mysql57]# tail -f /data/mysql_13306/mysql_error.log
后台日志如下

2020-06-14T14:28:04.419134Z 0 [ERROR] Native table ‘performance_schema’.‘global_status’ has the wrong structure
2020-06-14T14:28:04.419203Z 0 [ERROR] Native table ‘performance_schema’.‘session_status’ has the wrong structure
2020-06-14T14:28:04.419269Z 0 [ERROR] Native table ‘performance_schema’.‘variables_by_thread’ has the wrong structure
2020-06-14T14:28:04.419344Z 0 [ERROR] Native table ‘performance_schema’.‘global_variables’ has the wrong structure
2020-06-14T14:28:04.419409Z 0 [ERROR] Native table ‘performance_schema’.‘session_variables’ has the wrong structure
2020-06-14T14:28:04.420025Z 0 [ERROR] Incorrect definition of table mysql.db: expected column ‘User’ at position 2 to have type char(32), found type char(16).
2020-06-14T14:28:04.420278Z 0 [ERROR] mysql.user has no Event_priv column at position 28
2020-06-14T14:28:04.438575Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2020-06-14T14:28:04.439244Z 0 [Note] mysqld: ready for connections.
Version: ‘5.7.29-log’ socket: ‘/data/mysql_13306/mysql.sock’ port: 13306 MySQL Community Server (GPL)

[[email protected] mysql57]# lsof -i:13306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 13610 mysql 18u IPv6 113494 0t0 TCP *:13306 (LISTEN)
步骤5、执行升级操作
[[email protected] mysql57]#
[[email protected] mysql57]# mysql_upgrade --defaults-file=/etc/my13306.cnf -uroot -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading the sys schema.
Checking databases.
qjqx.aa OK
qjqx.bb OK
qjqx.cc OK
qjqx.dd OK
qjqx.ff OK
qjqx.testflashback2 OK
sys.sys_config OK
yjgk.aa OK
Upgrade process completed successfully.
Checking if update is needed.

升级成功，关闭数据库后，再次启动数据，观看后台日志是否有报错信息

后台日志有一些告警信息，主要是一些参数过期，调整即可

总结

1、升级前，需要对原数据库进行备份
2、通过rpm方式升级还是需要谨慎操作，这里是先删除原来的安装软件，再安装新数据库软件，第一次样操作还是有些忐忑，网上这种升级方式很少，官方文档也介绍不多，不是知道是这种方式太简单了，还是说生产环境很少通过rpm安装。

参考资料

https://www.docs4dev.com/docs/zh/mysql/5.7/reference/upgrade-binary-package.html#%E5%9C%A8-unixlinux-%E4%B8%8A%E5%8D%87%E7%BA%A7-mysql-%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%88%96-package-based-%E5%AE%89%E8%A3%85