MySQL 5.7到8.0升级指南与性能优化实践

1. 升级背景与必要性分析

MySQL 5.7作为长期支持版本（LTS）已于2023年10月结束官方支持周期，这意味着后续将不再获得安全补丁和错误修复。根据Oracle官方发布的生命周期规划，5.7版本已进入"Extended Support"阶段，仅对付费客户提供有限支持。与此同时，MySQL 8.0自2018年发布以来，经过多个版本的迭代已趋于成熟稳定，其性能提升可达2倍以上（官方基准测试显示OLTP读写负载提升达180%）。

从技术架构来看，8.0版本进行了多项重大改进：

• 默认存储引擎全面转向InnoDB，淘汰了MyISAM系统表
• 事务性数据字典取代了原有的文件元数据管理
• 支持原子DDL操作，使表结构变更更安全可靠
• 新增窗口函数、通用表表达式(CTE)等SQL高级特性
• 改进的JSON支持包括JSON聚合函数和JSON合并操作

在实际业务场景中，升级需求通常来自三个方面：

1. 安全合规要求：金融、政务等行业对漏洞修复有强制时效要求
2. 性能瓶颈突破：高并发场景下8.0的读写分离优化器有明显优势
3. 新特性需求：如需要使用GIS空间索引、哈希连接等特性

重要提示：生产环境升级前必须确认应用系统的兼容性，特别是使用MyISAM引擎或特定字符集的老系统

2. 升级前准备与兼容性检查

2.1 版本支持矩阵验证

MySQL官方明确规定了版本升级路径：

• 支持从5.7的最新GA版本升级到8.0的最新GA版本
• 不支持跨大版本升级（如5.6直接到8.0）
• 建议先升级到5.7的最终版本（5.7.39+）再升级到8.0

版本检查命令：

# 查看当前版本 mysql> SELECT @@version; +------------+ | @@version | +------------+ | 5.7.35-log | +------------+

2.2 使用MySQL Shell进行预检查

MySQL 8.0提供了专门的升级检查工具，需下载MySQL Shell 8.0+版本：

wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.27-linux-glibc2.12-x86-64bit.tar.gz tar -xzf mysql-shell-8.0.27-linux-glibc2.12-x86-64bit.tar.gz cd mysql-shell-8.0.27-linux-glibc2.12-x86-64bit/bin/ ./mysqlsh -uroot -p -S /tmp/mysql.sock -e "util.checkForServerUpgrade()" > upgrade_check.log

典型检查项包括：

1. 废弃特性的使用情况（如password()函数）
2. 保留关键字冲突（如8.0新增的RANK、SYSTEM等60+关键字）
3. 字符集兼容性（utf8mb3 vs utf8mb4）
4. 表名大小写敏感设置（lower_case_table_names）
5. 分区表兼容性检查

2.3 关键参数调整准备

需要特别注意的配置变更：

# 字符集调整（5.7默认是latin1） character_set_server=utf8mb4 collation-server=utf8mb4_0900_ai_ci # 认证插件兼容性 default_authentication_plugin=mysql_native_password # SQL模式差异 sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

3. 备份策略与停机方案

3.1 多维度备份实施

必须执行的备份类型：

1. 逻辑备份（全量+增量）：

mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers --events --all-databases > full_backup_$(date +%F).sql

2. 物理备份：

# 使用Percona XtraBackup进行热备 xtrabackup --backup --target-dir=/backups/$(date +%F) --user=root --password=xxx

3. 配置文件归档：

cp /etc/my.cnf /etc/my.cnf.bak_$(date +%s) cp -r /var/lib/mysql /var/lib/mysql.bak_$(date +%s)

3.2 停机窗口规划

推荐采用以下停机流程：

1. 业务低峰期执行graceful shutdown：

SET GLOBAL innodb_fast_shutdown=0; SHUTDOWN;

2. 验证服务状态：

ps -ef | grep mysqld netstat -tulnp | grep 3306

3. 记录binlog位置（GTID模式可跳过）：

-- 5.7版本执行 SHOW MASTER STATUS;

4. 二进制替换升级实操

4.1 软件包部署

推荐使用官方二进制包进行in-place升级：

# 下载8.0版本 wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz # 解压到新目录 tar -xJf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz -C /usr/local/ mv /usr/local/mysql-8.0.27-linux-glibc2.12-x86_64 /usr/local/mysql8 # 权限设置 chown -R mysql:mysql /usr/local/mysql8

4.2 数据目录升级

关键升级命令：

# 使用新版本启动升级 /usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE & # 监控错误日志 tail -f /var/lib/mysql/error.log

升级过程会执行：

1. 数据字典升级（mysql系统表结构变更）
2. InnoDB表空间文件升级（.ibd文件格式变更）
3. 系统表数据迁移（如权限表结构调整）

4.3 环境变量调整

更新PATH变量：

echo 'export PATH=/usr/local/mysql8/bin:$PATH' >> /etc/profile source /etc/profile # 验证版本 mysql -V mysql> SELECT @@version;

5. 升级后验证与回退方案

5.1 基础功能测试

必须验证的核心功能点：

1. 用户权限验证：

SHOW GRANTS FOR 'app_user'@'%';

2. 数据完整性检查：

CHECK TABLE important_table QUICK;

3. 存储过程执行：

CALL critical_procedure();

4. 复制状态确认（如有）：

SHOW SLAVE STATUS\G

5.2 性能基准测试

建议对比测试项：

-- 并发查询测试 sysbench oltp_read_write --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=xxx run -- 事务处理测试 BEGIN; INSERT INTO test_table VALUES(...); UPDATE counter_table SET count=count+1; COMMIT;

5.3 回退应急预案

当出现严重问题时，回退步骤：

1. 立即停止8.0服务
2. 恢复5.7二进制文件
3. 还原备份的data目录
4. 启动5.7服务并验证

关键命令：

# 停止8.0服务 mysqladmin -uroot -p shutdown # 恢复5.7环境 mv /usr/local/mysql8 /usr/local/mysql8.failed cp -r /backup/mysql57 /usr/local/mysql cp /backup/my.cnf /etc/my.cnf # 启动旧版本 /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

6. 典型问题解决方案

6.1 SSL连接异常处理

8.0版本强化了SSL验证，可能导致客户端连接失败。解决方案：

方法1：服务端禁用SSL

[mysqld] skip_ssl

方法2：客户端连接参数调整

jdbc:mysql://host:3306/db?useSSL=false&allowPublicKeyRetrieval=true

6.2 认证插件兼容性问题

错误现象：

Authentication plugin 'caching_sha2_password' cannot be loaded

解决方案：

-- 修改用户认证方式 ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

6.3 内存参数优化调整

8.0版本内存管理有重大变更，建议调整：

[mysqld] innodb_buffer_pool_size = 12G # 总内存的50-70% innodb_log_file_size = 4G # 原默认48M table_open_cache = 4000 # 原默认2000

7. 升级后的优化建议

7.1 利用新特性重构

值得关注的8.0特性应用：

1. 窗口函数优化分页查询：

SELECT id, name, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank FROM employees;

2. 哈希连接提升多表关联性能：

SELECT /*+ HASH_JOIN(t1,t2) */ * FROM t1 JOIN t2 ON t1.id=t2.id;

3. 不可见索引测试索引效果：

ALTER TABLE orders ALTER INDEX idx_amount INVISIBLE;

7.2 监控指标调整

新增需要监控的关键指标：

1. 数据字典性能（performance_schema.metadata_locks）
2. 资源组使用情况（resource_group）
3. 克隆操作状态（clone_status）
4. 直方图统计信息（column_statistics）

推荐监控工具：

# 使用MySQL Enterprise Monitor或Percona PMM pmm-admin add mysql --username=pmm --password=xxx --service-name=mysql8-node1

7.3 参数调优指南

需要重新评估的重要参数：

[mysqld] # 新版本默认值变化 innodb_flush_neighbors=0 # SSD建议禁用 innodb_io_capacity=2000 # 原默认200 innodb_io_capacity_max=4000 # 原默认2000 # 新增参数 information_schema_stats_expiry=86400 # 统计信息缓存时间

升级完成后，建议运行mysql_upgrade（仅8.0.16之前版本需要）并重启服务一次以确保所有组件完成升级。对于大型集群环境，可采用蓝绿部署方式逐步迁移，先升级从库验证无误后再切换主库。