最稳的MySQL平滑升级教程（另附经验总结）

小周的数据库进阶之路 2024-10-18

热衷于分享各种干货知识，大家有想看或者想学的可以评论区留言，秉承着“开源知识来源于互联网，回归于互联网”的理念，分享一些日常工作中能用到或者比较重要的内容，希望大家能够喜欢，不足之处请大家多提宝贵地意见，我们一起提升，守住自己的饭碗。

一、前言

最近有好多网友问有没有mysql升级的文章，今天它来了！

数据库版本	操作系统版本
5.7.35	CentOS Linux release 7.9.2009
8.0.37	CentOS Linux release 7.9.2009

二、升级方法

选择in-place升级，顾名思义就是关闭现有版本MySQL，将二进制包替换成新版本并在现有数据目录上启动MySQL并执行升级任务的方式。

MySQL的升级建议不要跨版本，如果是5.6及以下版本，我们需要先升级到5.7版本，再从MySQL5.7升级到MySQL8.0，注意仅支持GA版本之间的升级。建议升级大版本前先升级到当前版本的最近小版本，如5.7先升级到5.7.44后再升级到8.0。

三、升级前准备

升级前一定做好充足的备份，不要嫌麻烦，因为数据一旦丢失，将引起巨大的问题。

使用MySQL Shell工具检查兼容性

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

下载地址：

https://dev.mysql.com/downloads/shell/

这里我们选择8037版本

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

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

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

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

复制

[root@localhost bin]# cat util.checkForServerUpgrade.log

复制

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

消除警告：

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数据

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

复制

停止数据库

mysql>select version();

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

mysql>shutdown;

mysql>exit

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

复制

备份相关的数据目录

[root@localhost ~]# cp -r data/mysql data/mysql_bak_`date +%F`
[root@localhost ~]# cp -r usr/local/mysql/ usr/local/mysql_bak_`date +%F`
[root@localhost ~]#  cp etc/my.cnf etc/my.cnf_`date +%F`

复制

下载MySQL8并上传解压

[root@localhost local]# tar -xvf mysql-8.0.37-linux-glibc2.12-x86_64.tar.xz
[root@localhost local]# mv mysql-8.0.37-linux-glibc2.12-x86_64 mysql8
[root@localhost local]# chown -Rf mysql:mysql usr/local/mysql8

复制

四、升级

修改my.cnf 配置文件

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

[root@localhost local]# vi etc/my.cnf
新增以下内容：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
basedir=/usr/local/mysql8
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB


复制

执行升级程序（核心步骤）

在mysql5.7升级的时候，MySQL启动后还需执行mysql_upgrade后重启MySQL。MySQL8.0.16开始，MySQL 不推荐使用mysql_upgrade；直接使用 mysqld_safe 直接启动。

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

复制

参数说明：

–upgrade=AUTO MySQL升级所有过时的内容
–upgrade=NONE MySQL跳过升级步骤，可能会导致报错
–upgrade=MINIMAL MySQL在必要时升级数据字典表，information_schema和information_schema。这可能会导致部分功能不能正常使用，例如MGR–upgrade=FORCE
MySQL会升级所有的内容，这会检查所有schema的所有对象，导致MySQL需要更长的时间启动。此模式下MySQL会重新创建系统表。

新开一个窗口，可观察下错误日志看是否报错，如有下图标红内容即为升级成功，关掉上一个窗口。

[root@localhost data]# tail -100f data/mysql/mysql.err

复制

重新登录验证

[root@localhost ~]# mysql -uroot -p123456
mysql> select version();
mysql> show databases;

复制

修改mysql环境变量

[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
[root@localhost ~]# mysql -V

复制

验证数据

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

mysql> show databases;
mysql> use test;
mysql> show tables;
mysql> select count(*) from users;

复制

MySQL8默认的字符是utf8mb4d，我们对配置文件进行修改。

[root@localhost ~]# vi etc/my.cnf
修改为MySQL8的默认编码，注释5.7的。
character-set-server=utf8mb4
collation-server = utf8mb4_unicode_ci

复制

新增数据，进行测试

mysql> create database zhh;
mysql> use zhh;
mysql> CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

mysql>DELIMITER 
CREATE PROCEDURE InsertMockData()
BEGIN
    DECLARE i INT DEFAULT 0;
    
    WHILE i < 1000 DO -- 假设需要插入1000条数据
        INSERT INTO Users (username, email)
        VALUES (CONCAT('user', i), CONCAT('user', i, '@example.com'));
        SET i = i + 1;
    END WHILE;
END 
DELIMITER ;
mysql> CALL InsertMockData();
mysql> select * from Users;

复制

五、问题及经验总结

问题一

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

方法一：从数据库成面，直接在my.cnf 中添加skip_ssl 参数，从源头上关闭SSL 认证的方式
方法二：从代码层面，在JDBC 连接中，使用 &useSSL=false 参数，表示不使用SSL 认证

问题二

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

JDBC连接报错，报错内容 ERROR 1129 (HY000): Host ‘192.168.59.141’ 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认证的问题。

问题三

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参数进行数据恢复作为最后手段，做好完善的备份恢复机制，避免对数据库做高危操作。

MySQL部分历史文章合计：

MySQL8物理一键备份和恢复脚本分享

分享一个比较实用的MySQL8一键巡检脚本

全网首例！MySQL8 MGR多主一键搭建脚本分享

MYSQL高可用集群之双主+keepalived搭建

MySQL数据库一键安装脚本，适合任何版本

实战！MySQL主从复制一键搭建脚本分享

文中的概念来源于互联网，如有侵权，请联系我删除。

欢迎关注公众号：小周的数据库进阶之路，一起交流数据库、中间件和云计算等技术。如果觉得读完本文有收获，可以转发给其他朋友，大家一起学习进步！感兴趣的朋友可以加我微信，拉您进群与业界的大佬们一起交流学习。