从MySQL 7升级到MySQL 8的避坑指南与实操指南_Mysql

前言

mysql 8 作为里程碑式的版本，带来了诸多重磅特性（如窗口函数、cte、更强的 json 支持、默认 utf8mb4 编码、性能优化等），但从 mysql 7（注：mysql 官方无 “7” 正式版，通常指 5.7，下文统一以 5.7 代指）升级并非 “一键无脑更”，涉及语法、配置、权限、数据类型等多维度兼容问题。本文结合实战经验，梳理升级核心注意事项、实操步骤与常见坑点，帮你平稳完成升级。

一、升级前必看：核心兼容性差异

1. 默认字符集与排序规则变更

mysql 5.7：默认字符集为latin1，排序规则latin1_swedish_ci；
mysql 8.0：默认字符集改为utf8mb4，排序规则utf8mb4_0900_ai_ci（基于 unicode 9.0，区分语言特性）。
风险点：若原库未显式指定字符集，升级后可能出现乱码、排序异常；若业务依赖latin1，需提前规划字符集迁移。
建议：升级前全库检查字符集，执行select table_schema, table_name, column_name, character_set_name from information_schema.columns where character_set_name != ‘utf8mb4’;，提前将核心表字段改为utf8mb4。

2. 认证插件与密码策略变更

mysql 5.7：默认认证插件为mysql_native_password；
mysql 8.0：默认改为caching_sha2_password，且强化了密码策略（默认要求密码长度≥8、包含大小写 / 数字 / 特殊字符）。
风险点：
旧客户端（如 php5.x、python2.x）不支持caching_sha2_password，会导致连接失败；
原弱密码用户升级后可能无法登录。
建议：
若需兼容旧客户端，可临时改回旧认证插件：alter user ‘user’@‘%’ identified with mysql_native_password by ‘new_password’;；
升级前梳理所有数据库用户，提前更新弱密码，避免升级后登录失败。

3. 系统表与数据字典重构

mysql 8 彻底移除了 myisam 存储的系统表，改用 innodb 的事务型数据字典，mysql库结构大幅调整：
原mysql.user/mysql.db等权限表变为视图，直接修改表的操作（如insert into mysql.user）失效；
不再支持mysql_upgrade（5.7 升级 8.0 需用mysqld --upgrade=force）；
移除information_schema中部分冗余表（如innodb_sys_*）。
建议：检查业务中是否有直接操作系统表的 sql，改为官方推荐的create user/grant等语法。

4. sql 模式与语法兼容性

默认 sql 模式增强：mysql 8 默认启用only_full_group_by、strict_trans_tables等，5.7 中未开启的严格模式可能导致原有 “不规范 sql” 执行失败（如 group by 中未包含非聚合字段）；
关键字新增：mysql 8 新增role、window、cte等关键字，若表名 / 字段名与这些关键字重名（如create table role (id int);），需加反引号`包裹；
函数行为变更：now()、sysdate()等函数精度提升，json_extract语法更严格，旧 json 操作 sql 可能报错。

5. 存储引擎与功能移除

移除query_cache（查询缓存）：mysql 8 彻底删除查询缓存功能，若 my.cnf 中配置了query_cache_type、query_cache_size等参数，启动会报错；
移除no_auto_create_user sql 模式：5.7 中grant语句若未指定密码会自动创建用户，8.0 中此行为被禁止，需先create user再授权；
不支持mysql40密码格式：仅保留mysql56和caching_sha2_password，极旧的用户密码需重置。

二、升级实操：分步执行（推荐离线升级）

步骤 1：升级前准备

全量备份：使用mysqldump或物理备份工具（如 xtrabackup）备份整个数据库，命令示例：

# 全量备份（包含存储过程、触发器、事件）
mysqldump -uroot -p --all-databases --routines --triggers --events > mysql57_full_backup.sql

环境检查

确认服务器系统满足 mysql 8 要求（如 glibc≥2.17、libaio≥0.3.109）；
检查 my.cnf 配置文件，删除query_cache_*、innodb_large_prefix等 8.0 废弃的参数；

兼容性检测：使用 mysql 官方工具mysqlcheck或第三方工具（如 percona toolkit）扫描 sql 兼容性：

# 检查单库语法兼容性
pt-upgrade --source h=127.0.0.1,p=3306,u=root,p=xxx,d=test --dest h=127.0.0.1,p=3307,u=root,p=xxx

步骤 2：停止旧库，安装 mysql 8

1.停止 mysql 5.7 服务：

systemctl stop mysqld

2.卸载旧版本（保留数据目录）：

yum remove mysql-community-server-5.7*

3.安装 mysql 8（以 centos 为例）：

# 配置yum源
wget https://dev.mysql.com/get/mysql80-community-release-el8-3.noarch.rpm
rpm -ivh mysql80-community-release-el8-3.noarch.rpm
# 安装服务
yum install mysql-community-server -y

步骤 3：初始化与升级数据

1.启动 mysql 8 并强制升级数据字典：

# 启动并执行升级
mysqld --upgrade=force --user=mysql &

2.检查升级日志（/var/log/mysqld.log），确认无报错：

grep "upgrade" /var/log/mysqld.log

3.重置 root 密码（mysql 8 初始密码在日志中，且默认要求强密码）：

# 查看初始密码
grep "temporary password" /var/log/mysqld.log
# 登录后重置
alter user 'root'@'localhost' identified by 'newpass@123';

步骤 4：验证与适配

检查数据库连接：验证业务程序、客户端工具能否正常连接；
执行核心 sql：运行业务关键 sql（如查询、插入、更新），检查是否有语法报错；
性能监控：观察 cpu、内存、io 使用率，对比升级前性能，若有下降需优化配置（如调整innodb_buffer_pool_size）。

三、常见坑点与解决方案

坑点现象	原因	解决方案
客户端连接报错 “caching_sha2_password auth failed”	客户端不支持新认证插件	1. 改用户认证插件为 mysql_native_password；2. 升级客户端（如 php 升级到 7.4+）
group by 查询报错 “expression #1 of select list is not in group by”	8.0 默认启用 only_full_group_by	1. 优化 sql，group by 包含所有非聚合字段；2. 临时关闭该模式（不推荐）
启动报错 “unknown variable ‘query_cache_size’”	8.0 移除查询缓存参数	删除 my.cnf 中所有 query_cache_* 相关配置
授权报错 “error 1064 (42000): you have an error in your sql syntax”	8.0 不支持 grant 自动创建用户	先执行 create user，再执行 grant