MySQL5.6 迁移 MySQL8.0 实战手记（mysqldump 跨版本迁移踩坑全记录）

7次阅读

前言

最近做一套老业务从 MySQL 5.6 迁移到 MySQL 8.0，采用 mysqldump 逻辑导出导入方案。踩了一堆经典坑：--all-databases 系统库冲突、sed 过滤系统库、导入锁表报错、密码策略报错、MyISAM 转 InnoDB、NO_AUTO_CREATE_USER 废弃等，把完整流程和排坑记录整理下来，方便后续复用。

重要前提：MySQL5.6 和 MySQL8.0 数据字典完全不同，严禁直接拷贝 datadir，也不建议直接导入 mysql 系统库。

一、导出方案：不要直接 -A（–all-databases）

mysqldump -A / --all-databases 会导出实例全部库，包含 mysql、sys、performance_schema 系统库。5.6 的系统表结构和 8.0 完全不兼容，直接导入会直接损坏 MySQL8 的系统字典，权限错乱、启动失败。

两种思路

推荐：导出时直接排除系统库（治本）

mysqldump -u root -p \
--single-transaction \
--routines --triggers --events \
--set-gtid-purged=OFF \
--max-allowed-packet=1G \
--default-character-set=utf8mb4 \
--force \
--databases $(mysql -N -e "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','information_schema','performance_schema','sys')") \
> business_dump.sql

如果你已经用 -A 导出全量 dump（仅备份用，不能直接迁移），用 sed 过滤掉 mysql、sys 库区块

过滤后文件体积经常直接缩小一半，属于正常现象，系统库占比很高。

# Linux，同时删除 mysql、sys 区块，兼容sys在末尾场景
sed '/CREATE DATABASE.*`mysql`/,/CREATE DATABASE/d; /CREATE DATABASE.*`sys`/,${/CREATE DATABASE/!d}' all_dump.sql > clean_dump.sql

风险提示：按文本区间删除属于简易方案，如果表注释、字段默认值字符串中包含 CREATE DATABASE 会误删，业务场景一般没问题；过滤完成后校验剩余库：

grep 'CREATE DATABASE IF NOT EXISTS' clean_dump.sql

输出只能看到业务库，不能再有 mysql / sys。

二、导出后预处理：提前修复 5.6 废弃参数

MySQL8.0 已经移除 NO_AUTO_CREATE_USER，导入会直接报错，提前替换：

sed -i 's/NO_AUTO_CREATE_USER,//g' clean_dump.sql

如果 dump 里存在 MyISAM 表，导出自带 LOCK TABLES，导入容易触发锁报错，一并清理锁相关语句：

sed -i '/LOCK TABLES/d; /UNLOCK TABLES/d; /FLUSH TABLES WITH READ LOCK/d' clean_dump.sql

三、导入 MySQL8.0 与报错处理

导入命令：

mysql -u root -p --max-allowed-packet=1G < clean_dump.sql

或者客户端内 source 导入：

SET GLOBAL max_allowed_packet=1073741824;
source /path/clean_dump.sql;

报错 1：ERROR 1192 (HY000) Can’t execute the given command because you have active locked tables or an active transaction

原因：dump 中存在 LOCK TABLES，或者当前会话残留未提交事务、其他会话持有 MDL 锁。解决步骤：

关闭当前 mysql 客户端，新开会话导入（很多时候就是旧会话残留事务）
上面 sed 脚本删掉所有 LOCK / UNLOCK 语句
排查并杀掉长事务、锁会话：

SELECT trx_id,trx_started,trx_query FROM information_schema.innodb_trx;
SHOW PROCESSLIST;
KILL 会话ID;

报错 2：ERROR 1819 (HY000) Your password does not satisfy the current policy requirements

注意：这个报错不是导入业务数据时报错，是单独重建账号、执行 CREATE USER / ALTER USER 的时候触发。MySQL8 默认密码策略严格，老项目弱密码无法创建。

临时调低策略（迁移阶段使用，迁移完成务必恢复）：

-- 查看策略
SHOW VARIABLES LIKE 'validate_password.%';
-- 改为LOW，仅校验长度
SET GLOBAL validate_password.policy=LOW;
SET GLOBAL validate_password.length=4;

修改 global 变量后，已经连接的会话不生效，需要重连。

内网测试环境可临时卸载密码校验组件（公网生产不建议长期使用）：

UNINSTALL COMPONENT 'file://component_validate_password';
-- 恢复：INSTALL COMPONENT 'file://component_validate_password';

创建用户模板（兼容老程序，指定 mysql_native_password）：

CREATE USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
GRANT SELECT,INSERT,UPDATE,DELETE ON 业务库.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

四、权限单独迁移（重点，极易漏掉）

因为我们删掉了 mysql 系统库，账号权限不会跟着业务 dump 一起导入，需要从 5.6 单独导出授权语句：

mysql -u root -p -N -e "SELECT CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user WHERE user NOT IN ('','root','mysql.sys')" > grants_list.sql

在 5.6 执行每一条 SHOW GRANTS，拿到授权 SQL，在 MySQL8 重建用户和权限，同时设置 native 密码插件。

五、导入完成：MyISAM 批量转为 InnoDB

5.6 老库经常混杂 MyISAM 表，MySQL8 推荐全部使用 InnoDB。

⚠️ ALTER TABLE xxx ENGINE=InnoDB 在 MySQL8 是 COPY 算法，会加 MDL 写锁，必须业务低峰执行，磁盘预留至少一倍表空间；无主键表转换会自动生成隐藏主键，尽量提前补主键。

先查询所有 MyISAM 表

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE ENGINE='MyISAM' 
AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');

批量生成转换 SQL

SELECT CONCAT('ALTER TABLE `',TABLE_SCHEMA,'`.`',TABLE_NAME,'` ENGINE=InnoDB;') AS sql_text
FROM information_schema.TABLES 
WHERE ENGINE='MyISAM' 
AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');

前置风险检查

-- 检查无主键MyISAM表
SELECT TABLE_SCHEMA,TABLE_NAME 
FROM information_schema.TABLES t 
LEFT JOIN information_schema.KEY_COLUMN_USAGE k 
  ON t.TABLE_SCHEMA=k.TABLE_SCHEMA AND t.TABLE_NAME=k.TABLE_NAME AND k.CONSTRAINT_NAME='PRIMARY'
WHERE t.ENGINE='MyISAM' AND k.COLUMN_NAME IS NULL;

-- 检查超长索引（utf8mb4 767字节限制）
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_MAXIMUM_LENGTH 
FROM information_schema.COLUMNS 
WHERE DATA_TYPE='varchar' AND CHARACTER_MAXIMUM_LENGTH>191;

超长索引报错处理：

ALTER TABLE 表名 ROW_FORMAT=DYNAMIC;

超大表不停机方案：使用 pt-online-schema-change /gh-ost 在线 DDL，避免锁表阻塞业务。

MySQL8 InnoDB 基础参考配置

[mysqld]
default-storage-engine=InnoDB
innodb_buffer_pool_size=50%~75%物理内存
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=1
innodb_autoinc_lock_mode=2

六、迁移后校验清单

核对所有库、表数量
核对核心表行数，抽样对比
确认所有表引擎为 InnoDB
检查主键、索引是否正常
业务账号连通性测试，确认认证插件为 mysql_native_password（适配老客户端）
恢复密码策略为生产安全级别

七、禁止操作清单

❌ 不要直接拷贝 5.6 的 datadir 到 MySQL8 启动
❌ 不要 mysqldump -A 之后直接导入 8
❌ 不要全局 sed 替换 ENGINE=MyISAM（容易误匹配注释、字符串），优先导入后 ALTER
❌ 公网环境长期关闭密码校验策略

总结

MySQL5.6 → 8.0 mysqldump 迁移核心思路：分离业务数据与系统库，业务 dump 单独迁移，账号权限单独重建；提前清理废弃 sql_mode、锁语句；导入完成统一收敛到 InnoDB 引擎。大部分报错都来自 5.6 和 8.0 之间废弃语法、权限认证、引擎行为差异，提前处理可以大幅减少上线故障。

正文完

可以使用微信扫码关注公众号（ID：xzluomor）