印刻数据库

外观

MySQL 跨版本迁移 ​

跨版本迁移概述 ​

跨版本迁移是指将MySQL数据库从一个主版本迁移到另一个主版本，如从5.6迁移到5.7，或从5.7迁移到8.0。与同版本迁移相比，跨版本迁移需要特别注意版本间的功能差异、兼容性问题和配置变更。

迁移风险：

  • 版本间语法差异导致SQL无法执行
  • 配置参数变更导致性能下降或服务不稳定
  • 数据格式变化导致数据丢失或损坏
  • 应用程序兼容性问题

迁移原则：

  • 先进行充分的测试迁移
  • 制定详细的迁移计划和回滚方案
  • 注意版本间的兼容性问题
  • 迁移后进行全面的验证

迁移前准备 ​

版本差异评估 ​

核心版本差异：

特性MySQL 5.6MySQL 5.7MySQL 8.0
默认存储引擎InnoDBInnoDBInnoDB
字符集latin1utf8mb4utf8mb4
认证插件mysql_native_passwordmysql_native_passwordcaching_sha2_password
查询优化器基础版增强版高级版
JSON支持无基础支持完整支持
窗口函数无无支持
通用表表达式无无支持
原子DDL无部分支持完整支持
密码策略简单增强更严格

配置参数差异：

  • 已废弃的参数：如innodb_large_prefix（5.7.7+废弃）
  • 默认值变更：如innodb_buffer_pool_size默认值从128M调整为物理内存的1/4
  • 新增参数：如innodb_directories（8.0新增）

兼容性检查 ​

  1. 使用mysqlcheck检查表结构

    bash
    mysqlcheck -h source_host -u root -p --all-databases

  2. 使用mysql_upgrade --dry-run检查兼容性

    bash
    # 注意：需要在测试环境执行
mysql_upgrade -h target_host -u root -p --dry-run

  3. 检查存储过程、触发器、函数

    sql
    -- 检查是否使用了已废弃的语法
SELECT routine_schema, routine_name, routine_definition 
FROM information_schema.routines 
WHERE routine_definition LIKE '%deprecated_function%';

  4. 检查字符集和排序规则

    sql
    -- 检查数据库字符集
SELECT schema_name, default_character_set_name, default_collation_name 
FROM information_schema.schemata 
WHERE schema_name NOT IN ('information_schema','mysql','performance_schema','sys');

-- 检查表字符集
SELECT table_schema, table_name, table_collation 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys');

环境准备 ​

  1. 测试环境准备

    • 搭建与生产环境相似的测试环境
    • 模拟跨版本迁移，验证迁移过程和结果
    • 测试应用程序在目标版本下的兼容性

  2. 目标环境准备

    • 安装目标版本的MySQL
    • 配置基础参数，确保服务正常运行
    • 准备足够的磁盘空间
    • 配置网络连接

  3. 备份准备

    • 对源数据库进行全量备份
    • 确保备份文件可用且完整
    • 测试备份恢复流程

迁移工具选择 ​

工具适用场景优点缺点版本支持
mysqldump中小型数据库、需要严格兼容性检查生成标准SQL，兼容性好，支持版本间迁移迁移时间长，锁表所有版本
XtraBackup大型数据库、需要最小化停机时间热备份、无锁、迁移速度快需要注意版本兼容性，配置复杂5.6+（需匹配MySQL版本）
MySQL Enterprise Backup企业级数据库官方支持、功能齐全商业软件、需要付费5.6+
逻辑导出导入（myloader/mydumper）大型数据库、需要并行迁移并行导出导入，迁移速度快需要安装额外工具5.6+

迁移方法详解 ​

1. 逻辑迁移（mysqldump） ​

适用场景：中小型数据库、需要严格兼容性检查

操作步骤：

  1. 源数据库备份

    bash
    # 全库备份，使用--compatible参数确保兼容性
mysqldump -h source_host -u root -p --single-transaction --routines --triggers --events --all-databases --compatible=mysql57 > all_databases.sql

  2. 传输备份文件到目标服务器

    bash
    scp all_databases.sql target_host:/tmp/

  3. 目标数据库恢复

    bash
    # 恢复前确保目标数据库为空
mysql -h target_host -u root -p < /tmp/all_databases.sql

  4. 运行mysql_upgrade

    bash
    mysql_upgrade -h target_host -u root -p

版本差异注意事项：

  • 从5.6迁移到5.7：使用--compatible=mysql57参数
  • 从5.7迁移到8.0：使用--compatible=mysql80参数
  • 8.0版本的mysqldump默认使用caching_sha2_password认证插件，注意客户端兼容性

2. 物理迁移（XtraBackup） ​

适用场景：大型数据库、需要最小化停机时间

操作步骤：

  1. 源数据库备份

    bash
    # 使用与源数据库版本匹配的XtraBackup
xtrabackup --backup --target-dir=/backup/full --user=root --password=password

# 准备备份文件
xtrabackup --prepare --target-dir=/backup/full

  2. 传输备份文件到目标服务器

    bash
    rsync -avP /backup/full/ target_host:/backup/full/

  3. 目标数据库恢复

    bash
    # 停止目标MySQL服务
systemctl stop mysqld

# 清空目标数据目录
rm -rf /var/lib/mysql/*

# 恢复备份
xtrabackup --copy-back --target-dir=/backup/full

# 设置正确的权限
chown -R mysql:mysql /var/lib/mysql

  4. 启动目标数据库并运行升级

    bash
    # 启动MySQL服务
systemctl start mysqld

# 运行mysql_upgrade
mysql_upgrade -h target_host -u root -p

版本差异注意事项：

  • 从5.6迁移到5.7：使用XtraBackup 2.4.x，恢复后需要运行mysql_upgrade
  • 从5.7迁移到8.0：使用XtraBackup 8.0.x，恢复后需要运行mysql_upgrade
  • 注意备份文件与目标MySQL版本的兼容性

3. 增量迁移 ​

适用场景：数据量大、无法承受长时间停机

操作步骤：

  1. 执行初始全量备份（参考逻辑或物理迁移方法）

  2. 在源数据库上执行增量备份

    bash
    # XtraBackup增量备份示例
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=root --password=password

  3. 准备增量备份

    bash
    xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc1

  4. 在业务低峰期，停止源数据库写入

    bash
    # 可以通过修改应用配置或使用读写分离来实现

  5. 执行最后一次增量备份

    bash
    xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 --user=root --password=password

  6. 准备最终备份

    bash
    xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc2

  7. 恢复最终备份到目标数据库（参考物理迁移恢复步骤）

  8. 运行mysql_upgrade并验证

版本差异处理 ​

5.6 → 5.7 迁移注意事项 ​

1. 数据目录结构变化

  • 5.7新增了sys系统数据库
  • InnoDB表空间管理增强，默认启用innodb_file_per_table

2. 配置参数调整

  • 移除innodb_large_prefix参数（5.7.7+）
  • 调整innodb_buffer_pool_size默认值
  • 新增innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup参数

3. 密码策略增强

  • 新增validate_password插件
  • 密码复杂度要求提高
  • 密码过期策略

4. SQL_mode默认值变更

  • 5.7默认SQL_mode包含STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
  • 可能导致现有SQL语句执行失败

5. 时间类型处理

  • DATETIME和TIMESTAMP精度支持到微秒
  • TIMESTAMP默认行为变更

5.7 → 8.0 迁移注意事项 ​

1. 认证插件变更

  • 默认认证插件从mysql_native_password改为caching_sha2_password
  • 客户端需要支持新的认证插件

2. 数据字典重构

  • 8.0使用事务性数据字典存储元数据
  • 不再使用.frm文件
  • 元数据操作支持原子性

3. 原子DDL支持

  • 大部分DDL操作变为原子操作
  • 支持事务回滚

4. 窗口函数和CTE支持

  • 新增窗口函数支持
  • 新增通用表表达式（CTE）支持

5. JSON功能增强

  • 新增JSON路径语法
  • 增强JSON函数
  • 支持JSON表函数

6. 已移除的功能

  • 移除mysql_install_db脚本（使用mysqld --initialize替代）
  • 移除查询缓存
  • 移除innodb_large_prefix等多个废弃参数

7. SQL_mode默认值变更

  • 移除NO_AUTO_CREATE_USER
  • 新增ONLY_FULL_GROUP_BY为默认值

迁移验证 ​

基本验证 ​

  1. 数据库版本验证

    sql
    SELECT version();

  2. 数据库完整性验证

    bash
    # 对比源和目标数据库的库表数量
mysql -h source_host -u root -p -e "show databases;" > source_dbs.txt
mysql -h target_host -u root -p -e "show databases;" > target_dbs.txt
diff source_dbs.txt target_dbs.txt

  3. 数据量验证

    sql
    -- 统计各库数据量
SELECT table_schema, SUM(data_length + index_length) / 1024 / 1024 AS total_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
GROUP BY table_schema;

功能验证 ​

  1. 业务SQL验证

    • 执行核心业务SQL查询，对比源和目标数据库结果
    • 测试插入、更新、删除操作

  2. 存储过程、触发器验证

    sql
    -- 执行存储过程测试
CALL procedure_name();

  3. 权限验证

    sql
    -- 验证用户权限
SHOW GRANTS FOR 'user'@'host';

性能验证 ​

  1. 监控关键性能指标

    • QPS、TPS变化
    • 响应时间变化
    • 连接数变化
    • 缓存命中率

  2. 慢查询分析

    • 对比迁移前后慢查询数量
    • 分析新增慢查询的原因

迁移后优化 ​

  1. 更新统计信息

    sql
    -- MySQL 5.7
ANALYZE TABLE dbname.tablename;

-- MySQL 8.0
ANALYZE TABLE dbname.tablename UPDATE HISTOGRAM ON col1, col2;

  2. 重建索引

    sql
    ALTER TABLE dbname.tablename ENGINE=InnoDB;

  3. 调整配置参数

    • 根据目标版本的最佳实践调整参数
    • 重点关注新增参数和默认值变更的参数
    • 如innodb_directories（8.0）、caching_sha2_password相关参数

  4. 优化表结构

    • 利用新版本特性优化表结构
    • 如使用JSON类型替代文本存储JSON数据
    • 使用窗口函数优化复杂查询

回滚方案 ​

回滚条件 ​

当出现以下情况时，需要执行回滚操作：

  • 迁移过程中出现不可修复的错误
  • 目标数据库验证失败
  • 应用程序在目标版本下出现严重兼容性问题
  • 性能下降超出预期

回滚步骤 ​

  1. 停止应用访问目标数据库
  2. 恢复应用连接到源数据库
  3. 启动源数据库服务（如果已停止）
  4. 清理目标数据库环境

最佳实践 ​

  1. 迁移前

    • 制定详细的迁移计划和回滚方案
    • 在测试环境进行充分的测试迁移
    • 评估版本差异对应用的影响
    • 通知相关业务团队，获得支持

  2. 迁移中

    • 严格按照迁移计划执行
    • 记录每一步操作和结果
    • 实时监控迁移过程
    • 保持与业务团队的沟通

  3. 迁移后

    • 进行全面的验证
    • 监控数据库性能
    • 优化配置和表结构
    • 保留源数据库一段时间

  4. 版本特定建议

    • 5.6 → 5.7：重点关注SQL_mode和密码策略变更
    • 5.7 → 8.0：重点关注认证插件和数据字典变更
    • 所有版本：务必运行mysql_upgrade

常见问题处理 ​

  1. mysql_upgrade执行失败

    • 检查错误日志，定位具体问题
    • 修复表结构或数据问题
    • 尝试使用--force参数强制升级

  2. 应用程序连接失败

    • 检查认证插件兼容性
    • 验证用户密码和权限
    • 检查网络连接和防火墙配置

  3. SQL语句执行失败

    • 检查SQL_mode设置
    • 检查是否使用了已废弃的语法
    • 调整SQL语句以适应新版本

  4. 性能下降

    • 分析慢查询日志
    • 调整配置参数
    • 优化表结构和索引

  5. 数据丢失或损坏

    • 立即执行回滚操作
    • 检查备份文件完整性
    • 分析迁移过程中的错误日志

总结 ​

跨版本迁移是一项复杂的任务，需要DBA充分了解版本间的差异和兼容性问题。在实际生产环境中，DBA应该：

  1. 充分评估迁移风险和影响
  2. 制定详细的迁移计划和回滚方案
  3. 进行充分的测试迁移
  4. 严格按照计划执行迁移操作
  5. 进行全面的验证和优化
  6. 保持与业务团队的密切沟通

通过遵循上述最佳实践和注意事项，可以最大限度地降低跨版本迁移的风险，确保迁移过程顺利进行，目标数据库稳定运行。