本文档详细介绍如何将MySQL从5.7版本升级到8.4版本,包括升级前的准备工作、升级执行步骤、升级后验证以及常见问题处理。风哥教程参考MySQL官方文档MySQL Server Administration、Security等内容。
Part01-基础概念与理论知识
1.1 MySQL 5.7到8.4的版本变化
MySQL从5.7版本升级到8.4版本,包含了许多重要的特性和改进:
- JSON功能增强,支持更多JSON操作和索引
- InnoDB存储引擎性能提升
- 安全特性增强,包括密码验证插件改进
- SQL语法和函数增强
- 系统变量和状态变量的变化
- 数据字典的改进
1.2 升级前的兼容性检查
在升级前,需要进行以下兼容性检查:
- 检查不兼容的SQL语法
- 检查不兼容的函数和特性
- 检查系统变量的变化
- 检查存储引擎的兼容性
- 检查字符集和排序规则的变化
1.3 升级路径规划
MySQL 5.7到8.4的升级路径如下:
- MySQL 5.7.9+ → MySQL 8.0.x → MySQL 8.4.x
- 不支持直接从5.7升级到8.4,必须先升级到8.0版本
Part02-生产环境规划与建议
2.1 硬件要求
MySQL 8.4对硬件的要求比5.7更高,建议:
- CPU:至少4核,推荐8核以上
- 内存:至少8GB,推荐16GB以上
- 存储:SSD存储,至少200GB空间
- 网络:千兆网络以上
2.2 操作系统要求
支持的操作系统:
- Oracle Linux 7.4+ / 8.x / 9.x
- RHEL 7.4+ / 8.x / 9.x
- CentOS 7.4+ / 8.x / 9.x
- Ubuntu 18.04+
- Debian 9+
- Windows Server 2016+
2.3 网络要求
网络要求:
- 确保网络连接稳定
- 开放必要的端口(默认3306)
- 确保升级过程中网络不中断
Part03-生产环境项目实施方案
3.1 升级前准备
升级前的准备工作:
mysqldump –all-databases –single-transaction –master-data=2 –routines –triggers > all_databases.sql
— MySQL dump 10.13 Distrib 5.7.40, for Linux (x86_64)
—
— Host: localhost Database:
— ——————————————————
— Server version 5.7.40-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
— Position to start replication or point-in-time recovery from
— CHANGE MASTER TO MASTER_LOG_FILE=’binlog.000001′, MASTER_LOG_POS=154;
—
— Current Database: `fgedudb`
—
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `fgedudb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `fgedudb`;
—
— Table structure for table `fgedu_users`
—
DROP TABLE IF EXISTS `fgedu_users`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fgedu_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
—
— Dumping data for table `fgedu_users`
—
LOCK TABLES `fgedu_users` WRITE;
/*!40000 ALTER TABLE `fgedu_users` DISABLE KEYS */;
/*!40000 ALTER TABLE `fgedu_users` ENABLE KEYS */;
UNLOCK TABLES;
—
— Dumping routines for database ‘fgedudb’
—
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
— Dump completed on 2026-04-01 10:00:00
mysql -V
mysqlcheck –all-databases –check-upgrade
fgedudb.fgedu_users OK
3.2 升级执行步骤
升级执行步骤:
systemctl stop mysqld
cp /etc/my.cnf /etc/my.cnf.bak
yum remove mysql-community-server mysql-community-client mysql-community-common mysql-community-libs
Resolving Dependencies
–> Running transaction check
—> Package mysql-community-client.x86_64 0:5.7.40-1.el7 will be erased
—> Package mysql-community-common.x86_64 0:5.7.40-1.el7 will be erased
—> Package mysql-community-libs.x86_64 0:5.7.40-1.el7 will be erased
—> Package mysql-community-server.x86_64 0:5.7.40-1.el7 will be erased
–> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository
================================================================================
Removing:
mysql-community-client x86_64 5.7.40-1.el7 @mysql57-community
mysql-community-common x86_64 5.7.40-1.el7 @mysql57-community
mysql-community-libs x86_64 5.7.40-1.el7 @mysql57-community
mysql-community-server x86_64 5.7.40-1.el7 @mysql57-community
Transaction Summary
================================================================================
Remove 4 Packages
Installed size: 1.2 G
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Erasing : mysql-community-server-5.7.40-1.el7.x86_64 1/4
Erasing : mysql-community-client-5.7.40-1.el7.x86_64 2/4
Erasing : mysql-community-libs-5.7.40-1.el7.x86_64 3/4
Erasing : mysql-community-common-5.7.40-1.el7.x86_64 4/4
Verifying : mysql-community-common-5.7.40-1.el7.x86_64 1/4
Verifying : mysql-community-libs-5.7.40-1.el7.x86_64 2/4
Verifying : mysql-community-client-5.7.40-1.el7.x86_64 3/4
Verifying : mysql-community-server-5.7.40-1.el7.x86_64 4/4
Removed:
mysql-community-client.x86_64 0:5.7.40-1.el7 mysql-community-common.x86_64 0:5.7.40-1.el7
mysql-community-libs.x86_64 0:5.7.40-1.el7 mysql-community-server.x86_64 0:5.7.40-1.el7
Complete!
yum install mysql-community-server-8.0.36
Resolving Dependencies
–> Running transaction check
—> Package mysql-community-server.x86_64 0:8.0.36-1.el7 will be installed
–> Processing Dependency: mysql-community-client(x86-64) = 8.0.36-1.el7 for package: mysql-community-server-8.0.36-1.el7.x86_64
–> Processing Dependency: mysql-community-common(x86-64) = 8.0.36-1.el7 for package: mysql-community-server-8.0.36-1.el7.x86_64
–> Processing Dependency: mysql-community-client-plugins(x86-64) = 8.0.36-1.el7 for package: mysql-community-server-8.0.36-1.el7.x86_64
–> Processing Dependency: mysql-community-libs(x86-64) = 8.0.36-1.el7 for package: mysql-community-server-8.0.36-1.el7.x86_64
–> Running transaction check
—> Package mysql-community-client.x86_64 0:8.0.36-1.el7 will be installed
—> Package mysql-community-client-plugins.x86_64 0:8.0.36-1.el7 will be installed
—> Package mysql-community-common.x86_64 0:8.0.36-1.el7 will be installed
—> Package mysql-community-libs.x86_64 0:8.0.36-1.el7 will be installed
–> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository
================================================================================
Installing:
mysql-community-server x86_64 8.0.36-1.el7 mysql80-community
Installing for dependencies:
mysql-community-client x86_64 8.0.36-1.el7 mysql80-community
mysql-community-client-plugins x86_64 8.0.36-1.el7 mysql80-community
mysql-community-common x86_64 8.0.36-1.el7 mysql80-community
mysql-community-libs x86_64 8.0.36-1.el7 mysql80-community
Transaction Summary
================================================================================
Install 1 Package (+4 Dependent packages)
Total download size: 484 M
Installed size: 2.0 G
Is this ok [y/N]: y
Downloading packages:
(1/5): mysql-community-common-8.0.36-1.el7.x86_64.rpm | 648 kB 00:01
(2/5): mysql-community-client-plugins-8.0.36-1.el7.x86_64.rpm | 2.5 MB 00:01
(3/5): mysql-community-libs-8.0.36-1.el7.x86_64.rpm | 2.2 MB 00:01
(4/5): mysql-community-client-8.0.36-1.el7.x86_64.rpm | 41 MB 00:05
(5/5): mysql-community-server-8.0.36-1.el7.x86_64.rpm | 438 MB 00:55
——————————————————————————–
Total 7.8 MB/s | 484 MB 01:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql-community-common-8.0.36-1.el7.x86_64 1/5
Installing : mysql-community-client-plugins-8.0.36-1.el7.x86_64 2/5
Installing : mysql-community-libs-8.0.36-1.el7.x86_64 3/5
Installing : mysql-community-client-8.0.36-1.el7.x86_64 4/5
Installing : mysql-community-server-8.0.36-1.el7.x86_64 5/5
Verifying : mysql-community-common-8.0.36-1.el7.x86_64 1/5
Verifying : mysql-community-client-plugins-8.0.36-1.el7.x86_64 2/5
Verifying : mysql-community-libs-8.0.36-1.el7.x86_64 2/5
Verifying : mysql-community-client-8.0.36-1.el7.x86_64 3/5
Verifying : mysql-community-server-8.0.36-1.el7.x86_64 4/5
Installed:
mysql-community-server.x86_64 0:8.0.36-1.el7
Dependency Installed:
mysql-community-client.x86_64 0:8.0.36-1.el7
mysql-community-client-plugins.x86_64 0:8.0.36-1.el7
mysql-community-common.x86_64 0:8.0.36-1.el7
mysql-community-libs.x86_64 0:8.0.36-1.el7
Complete!
systemctl start mysqld
mysql -V
mysql_upgrade -u root -p
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.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
Checking databases.
fgedudb.fgedu_users OK
Upgrade process completed successfully.
Checking if update is needed.
systemctl stop mysqld
yum remove mysql-community-server mysql-community-client mysql-community-common mysql-community-libs mysql-community-client-plugins
Resolving Dependencies
–> Running transaction check
—> Package mysql-community-client.x86_64 0:8.0.36-1.el7 will be erased
—> Package mysql-community-client-plugins.x86_64 0:8.0.36-1.el7 will be erased
—> Package mysql-community-common.x86_64 0:8.0.36-1.el7 will be erased
—> Package mysql-community-libs.x86_64 0:8.0.36-1.el7 will be erased
—> Package mysql-community-server.x86_64 0:8.0.36-1.el7 will be erased
–> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository
================================================================================
Removing:
mysql-community-client x86_64 8.0.36-1.el7 @mysql80-community
mysql-community-client-plugins x86_64 8.0.36-1.el7 @mysql80-community
mysql-community-common x86_64 8.0.36-1.el7 @mysql80-community
mysql-community-libs x86_64 8.0.36-1.el7 @mysql80-community
mysql-community-server x86_64 8.0.36-1.el7 @mysql80-community
Transaction Summary
================================================================================
Remove 5 Packages
Installed size: 2.0 G
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Erasing : mysql-community-server-8.0.36-1.el7.x86_64 1/5
Erasing : mysql-community-client-8.0.36-1.el7.x86_64 2/5
Erasing : mysql-community-client-plugins-8.0.36-1.el7.x86_64 3/5
Erasing : mysql-community-libs-8.0.36-1.el7.x86_64 4/5
Erasing : mysql-community-common-8.0.36-1.el7.x86_64 5/5
Verifying : mysql-community-common-8.0.36-1.el7.x86_64 1/5
Verifying : mysql-community-libs-8.0.36-1.el7.x86_64 2/5
Verifying : mysql-community-client-plugins-8.0.36-1.el7.x86_64 3/5
Verifying : mysql-community-client-8.0.36-1.el7.x86_64 4/5
Verifying : mysql-community-server-8.0.36-1.el7.x86_64 5/5
Removed:
mysql-community-client.x86_64 0:8.0.36-1.el7
mysql-community-client-plugins.x86_64 0:8.0.36-1.el7
mysql-community-common.x86_64 0:8.0.36-1.el7
mysql-community-libs.x86_64 0:8.0.36-1.el7
mysql-community-server.x86_64 0:8.0.36-1.el7
Complete!
yum install mysql-community-server-8.4.0
Resolving Dependencies
–> Running transaction check
—> Package mysql-community-server.x86_64 0:8.4.0-1.el7 will be installed
–> Processing Dependency: mysql-community-client(x86-64) = 8.4.0-1.el7 for package: mysql-community-server-8.4.0-1.el7.x86_64
–> Processing Dependency: mysql-community-common(x86-64) = 8.4.0-1.el7 for package: mysql-community-server-8.4.0-1.el7.x86_64
–> Processing Dependency: mysql-community-client-plugins(x86-64) = 8.4.0-1.el7 for package: mysql-community-server-8.4.0-1.el7.x86_64
–> Processing Dependency: mysql-community-libs(x86-64) = 8.4.0-1.el7 for package: mysql-community-server-8.4.0-1.el7.x86_64
–> Running transaction check
—> Package mysql-community-client.x86_64 0:8.4.0-1.el7 will be installed
—> Package mysql-community-client-plugins.x86_64 0:8.4.0-1.el7 will be installed
—> Package mysql-community-common.x86_64 0:8.4.0-1.el7 will be installed
—> Package mysql-community-libs.x86_64 0:8.4.0-1.el7 will be installed
–> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository
================================================================================
Installing:
mysql-community-server x86_64 8.4.0-1.el7 mysql84-community
Installing for dependencies:
mysql-community-client x86_64 8.4.0-1.el7 mysql84-community
mysql-community-client-plugins x86_64 8.4.0-1.el7 mysql84-community
mysql-community-common x86_64 8.4.0-1.el7 mysql84-community
mysql-community-libs x86_64 8.4.0-1.el7 mysql84-community
Transaction Summary
================================================================================
Install 1 Package (+4 Dependent packages)
Total download size: 502 M
Installed size: 2.1 G
Is this ok [y/N]: y
Downloading packages:
(1/5): mysql-community-common-8.4.0-1.el7.x86_64.rpm | 650 kB 00:01
(2/5): mysql-community-client-plugins-8.4.0-1.el7.x86_64.rpm | 2.6 MB 00:01
(3/5): mysql-community-libs-8.4.0-1.el7.x86_64.rpm | 2.3 MB 00:01
(4/5): mysql-community-client-8.4.0-1.el7.x86_64.rpm | 42 MB 00:05
(5/5): mysql-community-server-8.4.0-1.el7.x86_64.rpm | 455 MB 00:58
——————————————————————————–
Total 8.2 MB/s | 502 MB 01:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql-community-common-8.4.0-1.el7.x86_64 1/5
Installing : mysql-community-client-plugins-8.4.0-1.el7.x86_64 2/5
Installing : mysql-community-libs-8.4.0-1.el7.x86_64 3/5
Installing : mysql-community-client-8.4.0-1.el7.x86_64 4/5
Installing : mysql-community-server-8.4.0-1.el7.x86_64 5/5
Verifying : mysql-community-common-8.4.0-1.el7.x86_64 1/5
Verifying : mysql-community-client-plugins-8.4.0-1.el7.x86_64 2/5
Verifying : mysql-community-libs-8.4.0-1.el7.x86_64 2/5
Verifying : mysql-community-client-8.4.0-1.el7.x86_64 3/5
Verifying : mysql-community-server-8.4.0-1.el7.x86_64 4/5
Installed:
mysql-community-server.x86_64 0:8.4.0-1.el7
Dependency Installed:
mysql-community-client.x86_64 0:8.4.0-1.el7
mysql-community-client-plugins.x86_64 0:8.4.0-1.el7
mysql-community-common.x86_64 0:8.4.0-1.el7
mysql-community-libs.x86_64 0:8.4.0-1.el7
Complete!
systemctl start mysqld
3.3 升级后验证
升级后验证:
mysql -V
mysql_upgrade -u root -p
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.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
Checking databases.
fgedudb.fgedu_users OK
Upgrade process completed successfully.
Checking if update is needed.
mysql -u root -p -e “SELECT version();”
+———–+
| version() |
+———–+
| 8.4.0 |
+———–+
mysql -u root -p -e “USE fgedudb; SHOW TABLES;”
+——————+
| Tables_in_fgedudb |
+——————+
| fgedu_users |
+——————+
Part04-生产案例与实战讲解
4.1 升级案例分析
以下是一个生产环境中MySQL 5.7升级到8.4的案例:
- 环境信息:
- 操作系统:Oracle Linux 7.9
- MySQL版本:5.7.40
- 数据库大小:50GB
- 服务器配置:8核16GB内存,500GB SSD
- 升级过程:
- 升级前备份:使用mysqldump备份所有数据库
- 升级步骤:5.7 → 8.0.36 → 8.4.0
- 升级时间:约2小时(包括备份和验证)
- 遇到的问题:
- 密码验证插件兼容性问题
- SQL语法兼容性问题
- 解决方案:
- 更新密码验证插件配置
- 修改不兼容的SQL语法
4.2 常见问题处理
升级过程中常见的问题及解决方案:
4.2.1 密码验证插件问题
mysql -u root -p
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword123!’;
4.2.2 SQL语法兼容性问题
SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’ ESCAPE ‘\’;
SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’ ESCAPE ‘\\’;
Part05-风哥经验总结与分享
1. 升级前准备:一定要做好充分的备份,包括数据库备份和配置文件备份,以防止升级失败时可以回滚。
2. 升级路径:严格按照官方推荐的升级路径进行,从5.7升级到8.0,再从8.0升级到8.4,不要尝试直接从5.7升级到8.4。
3. 兼容性检查:升级前使用mysqlcheck工具检查数据库的兼容性,确保没有不兼容的表结构或数据。
4. 测试环境:在生产环境升级前,一定要在测试环境进行完整的升级测试,包括应用程序的兼容性测试。
5. 升级时间:选择业务低峰期进行升级,预留足够的时间用于升级和验证。
6. 监控:升级过程中要密切监控系统资源使用情况,特别是CPU、内存和磁盘I/O。
7. 回滚计划:制定详细的回滚计划,包括如何在升级失败时恢复到原始版本。
8. 文档记录:详细记录升级过程中的每一步操作和遇到的问题,以便后续参考。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
