从MySQL5.7平滑升级到MySQL8.0的最佳实践分享

一、前言

升级需求：将5.7.35升级到8.0.27, 升级方式 in-place升级【关闭现有版本MySQL，将二进制或包替换成新版本并在现有数据目录上启动MySQL并执行升级任务的方式，称为in-place升级】

原版本 5.7.35 CentOS Linux release 7.9.2009

新版本 8.0.27 CentOS Linux release 7.9.2009

二、Mysql 生命周期

以下Mysql 生命周期-内容来自于互联网

关于数据库版本升级，一直都是热议话题，对于升级的缘由各家也有所不同，有业务驱动的，有DBA自发驱动的，有规划导向也有方向指引的……抛开各种原因，当升级这个决定落下来的时候，对于DBA手头的几百几千套数据库来说，就好比是一场动物大迁徙，满满的画面感。

从Oracle发布的版本生命周期规划可以看到，Mysql5.7已经走到了生命周期的终点，意味着后续将不再为Mysql5.7提供官方更新、错误修复或安全补丁。

阿里云和AWS都在官方公布了版本支持计划,Mysql5.7版本已经开始了倒计时。

三、MySQL8.0的新特性

默认字符集由latin1变为utf8mb4。

MyISAM系统表全部换成InnoDB表。

JSON特性增强。

支持不可见索引，支持直方图。

sql_mode参数默认值变化。

默认密码策略变更。

新增角色管理。

支持窗口函数，支持Hash join。

四、升级建议

支持从MySQL5.7升级到MySQL8.0，注意仅支持GA版本之间的升级。

不支持跨大版本的升级，如从5.6升级到8.0是不支持的。

建议升级大版本前先升级到当前版本的最近小版本，如5.7先升级到5.7.35后再升级到8.0。

做好充足的备份！数据无价！！!

五、升级前准备

5.1 Mysql-shell 检查工具兼容性

在执行升级操作前需要做一些检查工作，确认准备工作是否就绪，避免升级过程中出现异常。可以使用MySQL Shell使用util.checkForServerUpgrade进行检查，返回内容包括不符合迁移要求的问题，error的问题需要迁移前修改。

Mysql-shell 下载地址：https://dev.mysql.com/downloads/shell/

这里我们选择8027版本

然后上传文件至5.7.35的机器/root目录下

[root@localhost ~]# tar -xf mysql-shell-8.0.27-linux-glibc2.12-x86-64bit.tar.gz

[root@localhost ~]# cd mysql-shell-8.0.27-linux-glibc2.12-x86-64bit/bin/

[root@localhost bin]# ./mysqlsh -uroot -p -S /tmp/mysql.sock -e "util.checkForServerUpgrade()" > util.checkForServerUpgrade.log

从输出报告可以看出，升级检查器在23个方面进行了检查，最终得出4个警告信息和1个提示。

消除警告：

Usage of utf8mb3 charset 在MySQL 8.0版本之前，默认字符集为latin1 ，utf8字符集指向的是utf8mb3 。从MySQL8.0开始，数据库的默认编码将改为utf8mb4 ；为了避免新旧对象字符集不一致的情况，可以在配置文件将字符集和校验规则设置为旧版本的字符集和比较规则。

New default authentication plugin considerations，密码认证插件变更。为了避免连接问题，可以仍采用5.7的mysql_native_password认证插件。

消除提示：

Usage of obsolete sql_mode flags：Mysq8.0 版本sql_mode不支持NO_AUTO_CREATE_USER，要避免配置的sql_mode中带有NO_AUTO_CREATE_USER。

通过以上的例子，可以发现，MySQL Shell提供的升级检查工具能够帮助我们检测版本兼容性，减轻升级工作负担。

5.2 逻辑备份Mysql数据

[root@localhost opt]#which mysqldump

[root@localhost opt]#/usr/local/mysql/bin/mysqldump

# --routines 备份存储过程和函数；--set-gtid-purged=OFF: 禁用GTID（全局事务标识）;xxx1,XXX2 表示库名，备份多个库 用空格做为间隔

[root@localhost opt]#/usr/local/mysql/bin/mysqldump -uroot -p --routines --set-gtid-purged=OFF -A > /root/all-database-20231115.sql

5.3 优雅的停止数据库

select version();

show variables like 'innodb_fast_shutdown';

# 确保数据都刷到硬盘上，更改成0InnoDB关闭模式。如果值为0，InnoDB会在关闭前进行缓慢关闭、完全清除和更改缓冲区合并。如果值为1（默认值），InnoDB会在关闭时跳过这些操作，这个过程称为快速关闭。如果值为2，InnoDB刷新其日志并冷关机，就好像MySQL崩溃了；没有提交的事务丢失，但崩溃恢复操作使下一次启动需要更长的时间。在仍然缓冲大量数据的极端情况下，缓慢关闭可能需要几分钟甚至几小时。

set global innodb_fast_shutdown=0;

shut down;

exit

[root@localhost opt]# ps -ef|grep mysql

5.4 备份Mysql 数据目录,安装目录 和配置文件

--确认数据库状态为关闭状态

[root@localhost opt]# service mysql status

--数据目录备份

[root@localhost opt]# cp -r /data/mysql /data/mysql_bak_`date +%F`

--安装目录备份

[root@localhost opt]# cp -r /usr/local/mysql/ /usr/local/mysql_bak_`date +%F`

--配置文件备份

[root@localhost local]# cp /etc/my.cnf /etc/my.cnf_`date +%F`

5.5 下载并解压MySQL8

安装包上传至原安装包目录下 我的是/usr/local/mysql

[root@localhost local]# tar -xf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

# 文件夹重命名为mysql8

[root@localhost local]# mv mysql-8.0.27-linux-glibc2.12-x86_64 mysql8

# 更改文件夹所属

[root@localhost local]# chown -Rf mysql:mysql /usr/local/mysql8

# 删除安装包

[root@localhost local]# rm -rf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

六、升级

6.1 修改my.cnf 配置文件

因5.7版本与8.0版本参数有所不同，为了能顺利升级，我们需要更改部分配置参数。主要注意sql_mode、basedir、密码认证插件及字符集设置，其他参数最好还是按照原5.7的来，不需要做调整。下面展示5.7和8.0的配置文件,注意备份原来配置文件。

6.1.1 Mysql5.7_my.cnf 配置文件

相比5.7，8多了以下配置

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

basedir=/home/application/mysql/mysql8

datadir=/home/application/mysql/data

character_set_server=utf8

collation-server=utf8_general_ci

# 默认使用"mysql_native_password"插件认证

default_authentication_plugin=mysql_native_password

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

6.2 执行升级程序

在mysql5.7升级的时候，MySQL启动后还需执行mysql_upgrade后重启MySQL。MySQL8.0.16开始，MySQL 不推荐使用mysql_upgrade；直接使用 mysqld_safe 直接启动。关于--upgrade=的一些参数。

--upgrade=AUTO MySQL升级所有过时的内容

--upgrade=NONE MySQL跳过升级步骤，可能会导致报错

--upgrade=MINIMAL MySQL在必要时升级数据字典表，information_schema和information_schema。这可能会导致部分功能不能正常使用，例如MGR

--upgrade=FORCE MySQL会升级所有的内容，这会检查所有schema的所有对象，导致MySQL需要更长的时间启动。此模式下MySQL会重新创建系统表if they are missing。

[root@localhost local]# /usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE

新开一个窗口，可观察下错误日志看是否报错

tailf /data/mysql/mysql-error.log 然后登录数据库测试

重新登录
[root@localhost bin]# mysql -uroot -proot

mysql> select version();

+-----------+

| version() |

+-----------+

| 8.0.27    |

+-----------+

1 row in set (0.00 sec)

6.3 修改Mysql环境变量

由于basedir 从/usr/local/mysql 变成了 /usr/local/mysql8，需要修改下环境变量信息：

[root@localhost bin]# vi /etc/profile

export PATH=$PATH:/usr/local/mysql8/bin:/usr/local/mysql8/lib

[root@localhost bin]# source /etc/profile

[root@localhost ~]# which mysql

/usr/local/mysql8/bin/mysql

[root@localhost ~]# mysql -V

mysql  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)

6.4验证恢复过来的数据是否正常

七、问题及经验总结

7.1 问题一

在升级Mysql8.0后，关于JDBC中SSL连接的一些报错信息，如下图：

经排查发现，Mysql8.0 数据库默认开启了SSL认证，且之前Mysql5.7.39 也是默认开启了SSL认证，代码和JDBC驱动版本都没有变化，那很有可能就是Mysql8.0 中对于SSL的一个变化，咨询了DBA 朋友，专业的解释是，在5.7.31的时候SSL在源码中貌似没有真正的起作用，后面版本完善了这块的内容。倘若，不使用SSL去连接，就 需要按照如下的方法去处理：

方法一：从数据库成面，直接在my.cnf 中 添加skip_ssl 参数，从源头上关闭SSL 认证的方式

方法二：从代码层面，在JDBC 连接中，使用 &useSSL=false 参数，表示不使用SSL 认证

7.2 问题二

Mysql 报错unblock with ‘mysqladmin flush-hosts’，报错如下：

JDBC连接报错，报错内容 ERROR 1129 (HY000): Host ‘192.168.59.202’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

原因：同一个ip在短时间内产生太多，中断的数据库连接而导致的阻塞；而中断的因为有些业务使用SSL去连接数据库，导致登录失败，登录被锁；

临时解决方法，使用mysqladmin flush-hosts 命令清理一下hosts文件，mysqladmin -u xxx -p flush-hosts,根本上去解决，就需要排查什么异常的连接导致阻塞，登录被锁，比如上面提到的SSL认证的问题。

7.1 问题三

MySQL--使用innodb_force_recovery修复数据库异常

当MySQL服务异常重启失败后，可以通过配置参数innodb_force_recovery来对MySQL服务进行修复启动。

参数innodb_force_recovery选项：

1 (SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页。尽管检测到了损坏的 page 仍强制服务运行。一般设置为该值即可，然后 dump 出库表进行重建。

2 (SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行，如主线程需要执行 full purge 操作，会导致 crash。阻止 master thread 和任何 purge thread 运行。若 crash 发生在 purge 环节则使用该值。

3 (SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作。

4 (SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。不要进行统计操作。该值可能永久损坏数据文件。若使用了该值，则将来要删除和重建辅助索引。

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志，InnoDB 存储引擎会将未提交的事务视为已提交。此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。

6 (SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。使数据库页处于废止状态，继而可能引起 B 树或者其他数据库结构更多的损坏。

参数innodb_force_recovery设置：

在配置文件中的mysqld模块添加参数innodb_force_recovery。

[mysqld]

innodb_force_recovery    =    N

相同参数innodb_force_recovery在不同MySQL版本允许的操作可能不同，所有版本中innodb_force_recovery>0时都允许对表进行SELECT操作。

使用参数innodb_force_recovery建议：

1、如果MySQL服务故障重启后，因为事务回滚导致异常，可以将参数innodb_force_recovery设置为3跳过回滚阶段

2、如果因为MySQL数据页损坏导致异常，可以使用SELECT+WHERE查找出未损坏数据并将其通过mysqldump导出。

3、将innodb_force_recovery参数设置大于0启动服务后，应通过修改端口或域名(VIP)指向来屏蔽应用访问。

4、将innodb_force_recovery参数设置大于0启动服务后，可以通过mysqlcheck命令来对表进行检查/分析/优化/修复。

5、使用force_recovery重启服务前，建议对数据库所有文件进行备份，避免修复过程中对数据进行二次损害。

在日常运维中，应将使用innodb_force_recovery参数进行数据恢复作为最后手段，做好完善的备份恢复机制，避免对数据库做高危操作。