从 MySQL 5.7 到 MySQL 8.0 的数据库升级攻略

#下载包
[root@srebro.cn ~]# wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz -C /root 

[root@srebro.cn ~]# tar -xf mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz 

[root@srebro.cn ~]# cd /root/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit/bin 


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

The MySQL server at /tmp%2Fmysql.sock, version 5.7.39-log - MySQL Community 
Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 
8.0.34... 

1) Usage of old temporal type 
 No issues found 

2) MySQL 8.0 syntax check for routine-like objects 
 No issues found 

3) Usage of db objects with names conflicting with new reserved keywords 
 No issues found 

4) Usage of utf8mb3 charset 
 Warning: The following objects use the utf8mb3 character set. It is 
 recommended to convert them to use utf8mb4 instead, for improved Unicode 
 support. 
 More information: 
 https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html 

 mysql - schema's default character set: utf8 
 test - schema's default character set: utf8 

5) Table names in the mysql schema conflicting with new tables in 8.0 
 No issues found 

6) Partitioned tables using engines with non native partitioning 
 No issues found 

7) Foreign key constraint names longer than 64 characters 
 No issues found 

8) Usage of obsolete MAXDB sql_mode flag 
 No issues found 

9) Usage of obsolete sql_mode flags 
 Notice: The following DB objects have obsolete options persisted for 
 sql_mode, which will be cleared during upgrade to 8.0. 
 More information: 
 https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals 

 global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER 
 option 

10) ENUM/SET column definitions containing elements longer than 255 characters 
 No issues found 

11) Usage of partitioned tables in shared tablespaces 
 No issues found 

12) Circular directory references in tablespace data file paths 
 No issues found 

13) Usage of removed functions 
 No issues found 

14) Usage of removed GROUP BY ASC/DESC syntax 
 No issues found 

15) Removed system variables for error logging to the system log configuration 
 To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary 
 More information: 
 https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging 

16) Removed system variables 
 To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary 
 More information: 
 https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed 

17) System variables with new default values 
 To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary 
 More information: 
 https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ 

18) Zero Date, Datetime, and Timestamp values 
 No issues found 


19) Schema inconsistencies resulting from file removal or corruption 
 No issues found 

20) Tables recognized by InnoDB that belong to a different engine 
 No issues found 

21) Issues reported by 'check table x for upgrade' command 
 No issues found 

22) New default authentication plugin considerations 
 Warning: The new default authentication plugin 'caching_sha2_password' offers 
 more secure password hashing than previously used 'mysql_native_password' 
 (and consequent improved client connection authentication). However, it also 
 has compatibility implications that may affect existing MySQL installations. 
 If your MySQL installation must serve pre-8.0 clients and you encounter 
 compatibility issues after upgrading, the simplest way to address those 
 issues is to reconfigure the server to revert to the previous default 
 authentication plugin (mysql_native_password). For example, use these lines 
 in the server option file: 
 
 [mysqld] 
 default_authentication_plugin=mysql_native_password 
 
 However, the setting should be viewed as temporary, not as a long term or 
 permanent solution, because it causes new accounts created with the setting 
 in effect to forego the improved authentication security. 
 If you are using replication please take time to understand how the 
 authentication plugin changes may impact you. 
 More information: 
 https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues 
 https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication 

23) Columns which cannot have default values 
 No issues found 

24) Check for invalid table names and schema names used in 5.7 
 No issues found 

25) Check for orphaned routines in 5.7 
 No issues found 

26) Check for deprecated usage of single dollar signs in object names 
 No issues found 

27) Check for indexes that are too large to work on higher versions of MySQL 
Server than 5.7 
 No issues found 

28) Check for deprecated '.
' syntax used in routines. 
 No issues found 

Errors: 0 
Warnings: 3 
Notices: 1 

NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
从输出报告可以看出，升级检查器在28个方面进行了检查，最终得出3个警告信息和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：MySQL 8.0 版本sql_mode不支持NO_AUTO_CREATE_USER，要避免配置的sql_mode中带有NO_AUTO_CREATE_USER。
通过以上的例子，可以发现，MySQL Shell提供的升级检查工具能够帮助我们检测版本兼容性，减轻升级工作负担。
5.2 逻辑备份MySQL数据
which mysqldump 
/home/application/mysql/app/bin/mysqldump 

# --routines 备份存储过程和函数；--set-gtid-purged=OFF: 禁用GTID（全局事务标识）;xxx1,XXX2 表示库名，备份多个库用空格做为间隔
/home/application/mysql/app/bin/mysqldump -uroot -p --routines --set-gtid-purged=OFF --databases XXX1 XXX2 > /root/all-database-20231026.sql
5.3 优雅的停止数据库
# 进入原5.7 mysql命令行 正确关闭数据库
[root@srebro.cn ~]# mysql -uroot -p'srebro' 
mysql> select version(); 
+------------+ 
| version() | 
+------------+ 
| 5.7.43-log | 
+------------+ 
1 row in set (0.00 sec) 

mysql> show variables like 'innodb_fast_shutdown'; 
+----------------------+-------+ 
| Variable_name | Value | 
+----------------------+-------+ 
| innodb_fast_shutdown | 1 | 
+----------------------+-------+ 
1 row in set (0.00 sec) 

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

Query OK, 0 rows affected (0.00 sec)







mysql> shutdown;

Query OK, 0 rows affected (0.00 sec)



mysql> exit

Bye

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

root     30990 30934  0 16:12 pts/0    00:00:00 grep --color=auto mysql
5.4 备份MySQL 数据目录,安装目录和配置文件
--确认数据库状态为关闭状态
[root@srebro.cn ~]# systemctl status mysqld 

--数据目录备份
[root@srebro.cn ~]# cp -r /home/application/mysql/data /home/application/mysql/data_bak_`date +%F` 

--安装目录备份
[root@srebro.cn ~]# cp -r /home/application/mysql/app/ /home/application/mysql/app_bak_`date +%F` 


--配置文件备份
[root@srebro.cn ~]# cp /etc/my.cnf /etc/my.cnf_`date +%F`
5.5 下载并解压MySQL8.0
https://dev.mysql.com/downloads/
选择 Archives ，查询更多版本。
下载地址：https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz 选择mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz。
#执行以下步骤解压tar包：
# 安装包上传至原安装包目录下 我的是/home/application/mysql 

[root@srebro.cn ~]# cd /home/application/mysql 
[root@srebro.cn mysql]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz 

[root@srebro.cn mysql]# tar -xf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz 

# 文件夹重命名为mysql8 
[root@srebro.cn mysql]# mv mysql-8.0.34-linux-glibc2.12-x86_64 mysql8 

# 更改文件夹所属
[root@srebro.cn mysql]# chown -Rf mysql:mysql /home/application/mysql/mysql8 

# 删除安装包
[root@srebro.cn mysql]# rm -rf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
6.升级
6.1 修改 my.cnf 配置文件
因5.7版本与8.0版本参数有所不同，为了能顺利升级，我们需要更改部分配置参数。主要注意sql_mode、basedir、密码认证插件及字符集设置，其他参数最好还是按照原5.7的来，不需要做调整。下面展示5.7和8.0的配置文件,注意备份原来配置文件。
6.1.1 MySQL5.7_my.cnf 配置文件
[mysql]
socket=/tmp/mysql.sock 
default-character-set=utf8 

[mysqld]
user=mysql 
basedir=/home/application/mysql/app 
datadir=/home/application/mysql/data 
character_set_server=utf8 
collation-server=utf8_general_ci 

#日志时间
log_timestamps=SYSTEM 

port=3306
socket=/tmp/mysql.sock 

max_connections=1000
max_allowed_packet=500M 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 


#慢日志
long_query_time=3
slow_query_log=ON 
slow_query_log_file=/home/application/mysql/slow_query.log 
 
#错误日志
log-error=/home/application/mysql/mysql-error.log 



#binlog配置
server_id=150
log-bin=mysql-bin 
max_binlog_size = 100M 
binlog_format=row 
log_slave_updates 
expire_logs_days=7
#只能用IP地址检查客户端的登录，不用主机名
skip-name-resolve=1
6.1.2 MySQL8.0_my.cnf 配置文件
[mysql]
socket=/tmp/mysql.sock 
default-character-set=utf8 

[mysqld]
user=mysql 
#日志时间
log_timestamps=SYSTEM 
port=3306
socket=/tmp/mysql.sock 
max_connections=1000
max_allowed_packet=500M 
#只能用IP地址检查客户端的登录，不用主机名
skip-name-resolve=1

#binlog配置
server_id=150
log-bin=mysql-bin 
max_binlog_size = 100M 
binlog_format=row 
log_slave_updates 
expire_logs_days=7
#慢日志
long_query_time=3
slow_query_log=ON 
slow_query_log_file=/home/application/mysql/slow_query.log 
 
#错误日志
log-error=/home/application/mysql/mysql-error.log 



#for8.0 
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 执行升级程序
在MySQL 5.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@srebro.cn ~]# /home/application/mysql/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE

2023-10-30T07:33:23.595626Z mysqld_safe Logging to '/home/application/mysql/mysql-error.log'.

2023-10-30T07:33:23.620303Z mysqld_safe Starting mysqld daemon with databases from /home/application/mysql/data
 
如出现一直卡住，不用担心！
新开一个窗口，可观察下错误日志看是否报错/home/application/mysql/mysql-error.log 然后登录数据库测试。
[root@srebro.cn ~]# mysql -uroot -p'srebro' 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 12 
Server version: 8.0.34 MySQL Community Server - GPL 

Copyright (c) 2000, 2022, Oracle and/or its affiliates. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 


mysql> select version(); 
+-----------+ 
| version() | 
+-----------+ 
| 8.0.34 | 
+-----------+ 
1 row in set (0.00 sec) 

mysql> 
6.3 修改MySQL环境变量
由于basedir 从/home/application/mysql/app 变成了 /home/application/mysql/mysql8，需要修改下环境变量信息：
[root@srebro.cn ~]# vim /etc/profile 
export PATH=$PATH:/home/application/mysql/mysql8/bin 
....... 


#使环境变量生效
[root@srebro.cn ~]# source /etc/profile 
#验证下mysql环境变量
#退出当前终端
[root@srebro.cn ~]# exit 

[root@srebro.cn ~]# which mysql 
/home/application/mysql/mysql8/bin/mysql 
[root@srebro.cn ~]# mysql -V 
mysql Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL)
6.4 停止mysqld_safe进程，使用systemd管理MySQL 8.0
[root@cmdb ~]# kill -9 `ps -ef | grep mysql | awk '{print $2}'` 


#确认没有mysql进程
[root@cmdb ~]# ps -ef | grep mysql 


#使用systemd管理mysql8 
#修改原先的ExecStart中，basedir的路径，改为mysql8 的路径


[root@cmdb ~]# vim /etc/systemd/system/mysqld.service 


[Unit] 
Descriptinotallow=MySQL Server 
Documentatinotallow=man:mysqld 
Documentatinotallow=http://dev.mysql.com/doc/refman/en/using-systemd.html 
After=network.target 
After=syslog.target 


[Install] 
WantedBy=multi-user.target 
[Service] 
User=mysql 
Group=mysql 
ExecStart=/home/application/mysql/mysql8/bin/mysqld --defaults-file=/etc/my.cnf
6.5 配置MySQL 8.0开机自启&启动MySQL 8.0
#reload下systemd 
[root@cmdb ~]# systemctl daemon-reload 

#加入开机自启动
[root@cmdb ~]# systemctl enable mysqld 
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /etc/systemd/system/mysqld.service. 
#启动mysql8数据库
[root@cmdb ~]# systemctl start mysqld 

[root@cmdb ~]# ps -ef | grep mysql 
mysql 9497 1 36 14:59 ? 00:00:01 /home/application/mysql/mysql8/bin/mysqld --defaults-file=/etc/my.cnf 
root 9544 8560 0 14:59 pts/0 00:00:00 grep --color=auto mysql 


#登录数据库验证
[root@cmdb ~]# mysql -uroot -p'srebro' 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 8 
Server version: 8.0.34 MySQL Community Server - GPL 

Copyright (c) 2000, 2023, Oracle and/or its affiliates. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql> select version(); 
+-----------+ 
| version() | 
+-----------+ 
| 8.0.34 | 
+-----------+ 
1 row in set (0.00 sec) 

mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| mysql | 
| performance_schema | 
| srebro | 
| sys | 
| test | 
+--------------------+ 
5 rows in set (0.00 sec) 

mysql> 
7.遇到的问题
7.1 问题一
在升级MySQL8.0后，关于JDBC中SSL连接的一些报错信息，如下图：
经排查发现，MySQL 8.0数据库默认开启了SSL认证，且之前MySQL 5.7.39 也是默认开启了SSL认证。代码和JDBC驱动版本都没有变化，那很有可能就是MySQL 8.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.1.34' 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认证的问题。
参考资料
https://www.modb.pro/db/1715541568826990592
https://www.modb.pro/db/530848
https://www.modb.pro/db/1716302208709517312
作者介绍
蔡柱梁，51CTO社区编辑，从事Java后端开发8年，做过传统项目广电BOSS系统，后投身互联网电商，负责过订单，TMS，中间件等。

来源：51CTO内容投诉
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。 
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
软考中级精品资料免费领 
历年真题答案解析
备考技巧名师总结
高频考点精准押题
获取网盘下载链接 
资料下载
历年真题
2024上半年软考中级软件测评师考试基础知识真题
193.9 KB下载数265
查看
2024上半年软考中级软件设计师考试基础知识真题
191.63 KB下载数245
查看
2023下半年-系统集成项目管理工程师-真题考点汇总（完整版）
143.91 KB下载数1148
查看
2023年下半年系统集成项目管理工程师第一、二、三批次真题考点整理(考友回忆版)
183.71 KB下载数642
查看
2023年上半年软考中级《系统集成项目管理工程师》-基础知识-考试真题及答案
644.84 KB下载数2756
查看
2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）
 难度      813人已做 
 查看 
【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析
 难度      354人已做 
 查看 
【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析
 难度      318人已做 
 查看 
2024年上半年软考高项第一、二批次真题考点汇总（完整版）
 难度      435人已做 
 查看 
2024年上半年系统架构设计师考试综合知识真题
 难度      224人已做 
 查看 
相关文章
发现更多好内容
2024下半年广东软考成绩复查时间及流程
软考可以抵扣个税吗？软考证书个税抵扣几次
速看！软考的5类合格证书，最快12月下旬可查？！
2024下半年上海软考成绩复查时间及流程
软考高级过了还可以考中级吗？软考中级多少分算过
软考成绩通过后，证书领取时间是什么时候
如何在 Java 中实现对正方形的缩放操作？(如何在Java中对正方形进行缩放操作)
如何正确使用 Java 的 join 方法？(java join方法怎么使用)
Java 中 DecimalFormat 在哪些场景下使用较为合适？(Java DecimalFormat在哪里使用合适)
如何确保Redis客户端的安全性：实用技巧与最佳实践
猜你喜欢
AI推送时光机
从 MySQL 5.7 到 MySQL 8.0 的数据库升级攻略
后端开发MySQL数据库2024-11-29
MySQL数据库入门到高薪培训教程（从MySQL 5.7 到 MySQL 8.0）
后端开发2024-04-02
不可不知的 MySQL 升级利器及 5.7 升级到 8.0 的注意事项
后端开发MySQL数据库2024-12-01
MySQL从5.6版本到5.7版本的升级过程
后端开发2024-04-02
mysql升级到5.7时,wordpress导数据报错1067的问题
后端开发2024-04-02
5.7停服倒计时！关于MySQL升级到8.0版本的一些经验
后端开发MySQLSQL2024-11-30
MySQL 5.7及8.0版本数据库的root密码遗忘的解决
后端开发2024-04-02
MySQL 5.7及8.0版本数据库的root密码遗忘怎么办
后端开发2024-04-02
MySQL 5.7停服就上8.0？这些数据库替代也杠杠的
后端开发MySQLDBA2024-11-30
超详细的MySQL数据库升级
后端开发MySQL数据库2024-11-30
MySQL数据库升级的一些坑
后端开发数据库MySQL2024-12-11
MySQL数据库升级的坑有哪些
后端开发2024-04-02
MySQL分库分表全攻略：从小白到大神的进阶指南！
后端开发MySQL分库2024-11-30
【从删库到跑路】MySQL系列——数据库的介绍&&MySQL的启动
后端开发2023-09-06
MySQL数据库升级的一些"陷阱"
后端开发2022-05-25
从MySQL到Redis的简单数据库迁移方法
后端开发2022-06-04
从数据仓库到数据中台再到数据飞轮：数据技术的打怪升级之路
后端开发数据技术数据飞轮2024-11-29
深入浅析MySQL从删库到跑路_高级（一）——数据完整性
后端开发2024-04-02
MySQL：级联从库延迟数据库的延迟计算问题
后端开发MySQL数据库2024-11-30
深入MySQL优化：提升数据库性能的关键策略
后端开发MySQL优化2024-11-30
 位置：首页-资讯-后端开发
 咦！没有更多了？去看看其它编程学习网 内容吧 
首页课程
资料下载问答资讯