知识库

MySQL运维：数据库迁移

12/31/2025 应用模板

目录

参考

# MySQL运维：数据库迁移

# 一、MySQL数据库迁移核心要点

以下是 MySQL数据库迁移指南核心要点总结，涵盖迁移场景、策略、工具对比及风险控制：

# 1、三大核心迁移场景

场景 核心目标 推荐工具 关键注意事项
日常备份恢复 数据安全与快速恢复 - 中小库：mysqldump - 大型库：Percona XtraBackup - 通过校验和 & 沙箱演练验证备份可用性 - 混合策略（全量+增量）平衡存储与恢复效率
同版本迁移 最小化业务中断 - 物理备份：Percona XtraBackup - 逻辑备份：mydumper - MySQL 8.0.17+：原生 CLONE 插件 - 冷迁移（停机）适合非关键业务 - 热迁移（物理复制/克隆）保障业务连续性
低版本→高版本升级 规避兼容性风险 & 平稳过渡 逻辑备份工具：mysqldump/mydumper - 必做兼容性检查：存储引擎、字符集（utf8→utf8mb4）、认证插件 - 升级后通过 pt-table-checksum 校验数据一致性

# 2、迁移策略选择依据

决策因素 说明 策略影响
业务中断容忍度 高要求场景需热迁移（在线） - 热迁移工具：XtraBackup/CLONE 插件 - 增量迁移结合 binlog 同步
数据一致性要求 严格一致性需校验机制 - 校验工具：pt-table-checksum - 物理备份需版本一致，逻辑备份需语法兼容
操作复杂度 简单场景选逻辑备份，高效场景选物理备份 - 逻辑备份（简单但慢） - 物理备份（高效但复杂）

# 3、主流工具对比

工具 类型 优势 局限 适用场景
mysqldump 逻辑备份 跨版本兼容性强、操作简单 速度慢、存储占用大 中小库备份、跨版本升级
mydumper 逻辑备份 多线程支持、压缩备份 需手动处理触发器 大型库迁移、高并发场景
Percona XtraBackup 物理备份 热备份、速度快、支持增量备份 需环境一致（版本/OS）、操作复杂 同版本大库迁移、最小停机需求
MySQL CLONE 插件 物理克隆 原生支持、无需额外工具、分钟级克隆 仅限 MySQL 8.0.17+、版本严格匹配 同版本快速迁移

# 4、通用风险控制措施

  1. 数据一致性
    • 必做校验：pt-table-checksum 验证源库与目标库数据一致性，异常时用 pt-table-sync 修复。
  2. 业务中断
    • 执行时机：业务低峰期操作。
    • 技术手段：热迁移 + binlog 增量同步（停机时间秒级）。
  3. 工具链兼容性
    • 验证步骤：测试环境预演，确保工具版本（如 XtraBackup）与 MySQL 版本匹配。
  4. 资源与网络
    • 监控指标：磁盘空间、网络带宽（iftop）、服务器负载（iostat）。
    • 优化手段：备份压缩、断点续传（rsync）。

# 5、关键操作流程

# 1. 日常备份恢复

  • 全量备份：

    # mysqldump（事务一致）
mysqldump --single-transaction -u root -p dbname > backup.sql

# XtraBackup（物理热备）
xtrabackup --backup --target-dir=/backup/
    1
    2
    3
    4
    5

  • 增量备份：基于 binlog 的增量同步 + FLUSH LOGS 切割日志。

# 2. 同版本迁移

  • XtraBackup 物理迁移：

    xtrabackup --prepare --target-dir=/backup/    # 预处理
xtrabackup --copy-back --target-dir=/backup/  # 恢复
    1
    2

  • CLONE 插件（MySQL 8.0.17+）：

    SET GLOBAL clone_valid_donor_list='源库IP';
CLONE INSTANCE FROM 'user@源库IP:3306' IDENTIFIED BY '密码';
    1
    2

# 3. 低版本升级

  • 前置检查：

    ALTER TABLE myisam_table ENGINE=InnoDB;  -- 引擎转换
SELECT User, plugin FROM mysql.user;     -- 认证插件检查
    1
    2

  • 逻辑迁移：mysqldump 导出 → 高版本库导入 → pt-table-checksum 校验。

# 二、迁移方案：物理备份Percona XtraBackup

# 1、概念与术语

# 核心定义

Percona XtraBackup：Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB等，支持增量备份，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

MyISAM与InnoDB核心区别

特性 InnoDB MyISAM
事务支持 ✅ 支持ACID事务 ❌ 不支持
锁机制 行级锁（默认）+ 表级锁 表级锁
外键约束 ✅ 支持 ❌ 不支持
崩溃恢复 ✅ 支持安全恢复 ❌ 数据易损坏
适用场景 高并发写入、需事务的场景 读密集型、静态数据
全文索引 ✅ MySQL 5.6+ 支持 ✅ 支持
注：MySQL 5.5+ 默认引擎为InnoDB。

# 解决的问题

传统备份痛点 XtraBackup解决方案
冷备份需停服务（业务中断） 在线热备份（读写正常）
mysqldump恢复慢（逻辑备份） 物理备份，TB级数据分钟级恢复
不支持增量备份 增量备份仅备份变化数据

# 竞品对比

技术对比：物理复制 vs 逻辑备份

特性 XtraBackup (物理复制) mysqldump (逻辑备份)
备份速度 ⚡️ 快（直接拷贝文件） 🐢 慢（逐行生成SQL）
恢复速度 ⚡️ 分钟级（TB级数据） 🐢 小时级（需SQL解析）
锁机制影响 全局读锁（短暂阻塞） 表级锁（长时间阻塞）
一致性保障 Redo Log + 两阶段prepare 单次快照（无崩溃恢复机制）

💡 核心价值：XtraBackup 通过 物理复制+日志追踪+两阶段恢复 三位一体，实现生产环境零数据丢失备份。

工具对比

工具 备份类型 是否阻塞 速度 增量备份 适用场景
XtraBackup 物理 非阻塞 ⚡️极快 ✅支持 大数据库生产环境
mysqldump 逻辑 表锁 慢 ❌不支持 小数据迁移
MySQL Enterprise Backup 物理 部分阻塞 快 ✅支持 商业版用户

# 主要工具

Percona XtraBackup 包含两个主要的工具：xtrabackup 和 innobackupex ，以下是二者的区别与联系，以及版本兼容性的详细说明：

  • 核心区别

    特性 xtrabackup innobackupex
    本质 C/C++ 编译的二进制程序 Perl 脚本封装 xtrabackup
    引擎支持 仅 InnoDB/XtraDB ✅，不支持 MyISAM ❌ 支持 InnoDB 和 MyISAM 等所有引擎 ✅
    与 MySQL 交互 无直接交互（仅操作文件系统） 需连接 MySQL（执行 FTWRL、获取 binlog 位点）
    MyISAM 备份方式 不支持 加全局读锁（FLUSH TABLES WITH READ LOCK）🔒
    增量备份支持 支持 InnoDB 增量（基于 LSN）✅ MyISAM 不支持增量，每次全量拷贝 ❌
    备份流程复杂度 简单直接 需协调多个进程（通过文件信号控制）🔄

  • 核心联系

    1. 调用关系：innobackupex 在备份 InnoDB 表时自动调用 xtrabackup，自身负责 非 InnoDB 表（如 MyISAM）的备份。

    2. 备份文件结构： 两者生成的备份目录均包含一致性元数据文件（如 xtrabackup_checkpoints 记录 LSN、xtrabackup_binlog_info 记录 binlog 位点）。

    3. 恢复流程： 均需三步， ​备份 → Prepare（应用 redo/undo 日志） → 复制到数据目录。

  • 版本兼容性（与 MySQL 对应关系）

    1. 工具演进与弃用时间线

      XtraBackup 版本 关键变化 兼容 MySQL 版本
      ≤ 2.2 xtrabackup 仅备份 InnoDB；innobackupex 独立处理全引擎 5.1, 5.5, 5.6 ✅
      2.3–2.4 innobackupex 改为 xtrabackup 的符号链接（功能保留但已弃用） 5.6, 5.7 ✅（不支持 8.0 ❌）
      ≥ 8.0 彻底移除 innobackupex，xtrabackup 集成全引擎备份能力 仅 8.0+ ✅（不兼容 5.x ❌）

    2. 兼容性关键细节

      • MySQL 8.0+ 专属限制：

        • XtraBackup 8.0 采用新 redo log 格式与数据字典（SDI），无法备份低版本 MySQL（如 5.7）。
        • 需授予 BACKUP_ADMIN 权限及 performance_schema.log_status 查询权限。

      • MyRocks 引擎支持： XtraBackup 8.0+ 新增对 MyRocks 引擎的备份支持。

    3. 版本匹配原则

      • 大版本必须对齐： XtraBackup 大版本号需 ​≥ MySQL 主版本号​（例如 MySQL 8.1 需 XtraBackup 8.1）。

      • 混用风险： 混合使用版本（如 XtraBackup 2.4 备份 MySQL 8.0）会导致备份失败或数据损坏。

    4. 生产环境建议

      • 工具选择：

        • MySQL ≤ 5.7：使用 innobackupex（或 XtraBackup 2.4 + --lock-ddl 避免 MyISAM 锁冲突）。
        • MySQL ≥ 8.0：仅用 xtrabackup（例如 xtrabackup --backup --target-dir=/backup/）。

      • 备份策略：

        • 避免增量备份：MyISAM 不支持增量，且合并流程复杂；推荐 全量备份 + binlog 组合。
        • 权限配置： MySQL 8.0+ 需授权 BACKUP_ADMIN, SELECT ON performance_schema.*。

# 2、常用命令

# 查看存储引擎

-- 查看当前默认引擎
SHOW VARIABLES LIKE '%storage_engine%';  

-- 查看所有支持的引擎
SHOW ENGINES;  -- 结果中的`Support`列显示是否启用

-- 列出指定数据库所有表及引擎
SELECT TABLE_NAME, ENGINE 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '数据库名';
1
2
3
4
5
6
7
8
9
10

# 全量备份与恢复

# 全量备份
xtrabackup --user=backup_admin --password=SecurePass123! \
  --backup --target-dir=/backups/full

# 准备备份（应用redo log）
xtrabackup --prepare --target-dir=/backups/full

# 恢复数据（需先停止MySQL）
xtrabackup --copy-back --target-dir=/backups/full
chown -R mysql:mysql /var/lib/mysql  # 修复权限
1
2
3
4
5
6
7
8
9
10

# 增量备份与恢复

# 首次增量（基于全量）
xtrabackup --backup --target-dir=/backups/inc1 \
  --incremental-basedir=/backups/full

# 后续增量（基于前次增量）
xtrabackup --backup --target-dir=/backups/inc2 \
  --incremental-basedir=/backups/inc1

# 增量恢复（合并所有增量）
xtrabackup --prepare --apply-log-only --target-dir=/backups/full
xtrabackup --prepare --apply-log-only --target-dir=/backups/full \
  --incremental-dir=/backups/inc1
xtrabackup --prepare --target-dir=/backups/full  # 最终合并
1
2
3
4
5
6
7
8
9
10
11
12
13

# 高级功能

# 流式压缩备份
xtrabackup --backup --stream=xbstream | gzip > backup.xb.gz

# 加密备份
xtrabackup --backup --encrypt=AES256 \
  --encrypt-key="your-key" --target-dir=./encrypted_backup
1
2
3
4
5
6

# 3、使用指南

# 1.安装与配置（CentOS）

# 版本对应
MySQL 版本 XtraBackup版本
5.6 / 5.7 2.4.x
8.0 8.0.x
8.1+ 8.1.x
# 下载安装

官网：Percona XtraBackup (opens new window)

  • 离线安装包下载安装

image-20250805171712742

  • 通过YUM源安装

    官网教程2.4.x (opens new window)

    # 添加Percona仓库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
    1
    2
    # 查看
yum list | grep percona
    1
    2

    image-20250806101153464

    # 启用工具仓库
percona-release enable-only tools release
    1
    2
    # 查看
yum list | grep percona
    1
    2

    image-20250806101335966

    # 安装（以2.4.x为例）
yum install percona-xtrabackup-24
    1
    2
    # 查看安装情况
rpm -qa | grep xtrabackup
# 验证安装
xtrabackup --version
innobackupex --version
    1
    2
    3
    4
    5
    # 删除
yum remove percona-xtrabackup
# 或，卸载
rpm -e percona-xtrabackup
    1
    2
    3
    4

# 2. 迁移实践：MySQL5（5.7.44→5.7.44）

以下为MySQL 5.7.44使用Percona XtraBackup迁移的详细操作步骤，按工具分类说明全量/增量备份与恢复：

# 1）innobackupex工具

MySQL 5.7推荐，封装xtrabackup并支持MyISAM表

# 全量备份与恢复

  • 备份步骤：

    1. 创建备份用户及目录

      # 创建备份专用用户（需 REPLICATION CLIENT, RELOAD, PROCESS 权限）
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'YourPassword';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
      1
      2
      3
      4
      # 创建备份文件保存的目录
mkdir -pv /data/backup
      1
      2

    2. 备份命令

      innobackupex \
  --defaults-file=/etc/my.cnf \  # 指定MySQL配置文件
  --user=root \                  # 备份用户
  --password='123456' \          # 备份用户密码
  --no-timestamp \               # 备份到指定目录，而非时间戳子目录
  /data/backup                   # 备份目录
      1
      2
      3
      4
      5
      6

  • 恢复步骤：

    1. 准备备份（应用事务日志）：

      innobackupex --defaults-file=/etc/my.cnf --apply-log --use-memory=2G /data/backup
      1

      注意：--use-memory加速回滚，建议分配服务器空闲内存的50-70%

    2. 停止MySQL并清空数据目录：

      # 停止MySQL
systemctl stop mysqld
# 备份并清空数据目录
cp -r /var/lib/mysql /var/lib/mysql.bak
rm -rf /var/lib/mysql/*
      1
      2
      3
      4
      5

    3. 复制数据回原目录：

      innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup
      1

      或使用--move-back移动（速度更快但会删除备份）

    4. 修正权限并启动：

      # 修正权限
chown -R mysql:mysql /var/lib/mysql
# 启动MySQL
systemctl start mysqld
      1
      2
      3
      4
# 增量备份与恢复

  • 备份步骤：

    1. 创建备份用户及目录

      # 创建备份专用用户（需 REPLICATION CLIENT, RELOAD, PROCESS 权限）
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'YourPassword';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
      1
      2
      3
      4
      # 创建备份文件保存的目录
mkdir -pv /backup/full
      1
      2

    2. 备份命令

      # 全量备份（基线）
innobackupex --defaults-file=/etc/my.cnf --user=root --password='123456' /backup/full

# 第一次增量（基于全量）
innobackupex --defaults-file=/etc/my.cnf --incremental /backup/inc1 --incremental-basedir=/backup/full

# 第二次增量（基于上一次增量）
innobackupex --defaults-file=/etc/my.cnf --incremental /backup/inc2 --incremental-basedir=/backup/inc1
      1
      2
      3
      4
      5
      6
      7
      8

  • 恢复步骤：

    1. 准备全量备份（仅重做不回滚）：

      innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /backup/full
      1

    2. 合并增量到全量：

      # 合并第一次增量（仍保留redo）
innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /backup/full \
  --incremental-dir=/backup/inc1

# 合并最后一次增量（执行回滚）
innobackupex --defaults-file=/etc/my.cnf --apply-log /backup/full \
  --incremental-dir=/backup/inc2
      1
      2
      3
      4
      5
      6
      7

    3. 恢复合并后的数据：

      innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
      1
      2
      3
# 2）xtrabackup工具

直接操作InnoDB，需手动处理非InnoDB表

# 全量备份与恢复

  • 备份步骤：

    1. 创建创建备份用户及目录

      # 创建备份专用用户（需 REPLICATION CLIENT, RELOAD, PROCESS 权限）
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'YourPassword';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
      1
      2
      3
      4
      # 创建备份文件保存的目录
mkdir -pv /backup/full
      1
      2

    2. 备份命令

      xtrabackup \
  --defaults-file=/etc/my.cnf \    # 指定MySQL配置文件
  --backup \                       # 备份模式
  --target-dir=/backup/full        # 存储目录
  --user=root \                    # 备份用户
  --password='123456' \            # 备份用户密码
      1
      2
      3
      4
      5
      6

      Docker下命令

      docker run --rm --name xtrabackup-temp \
  --user $(id -u mysql):$(id -g mysql) \
  --volumes-from MySQL容器名称 \
  -v /宿主机/备份目录:/backup/full \
  percona/percona-xtrabackup:2.4 \
  xtrabackup \
  --defaults-file=/etc/my.cnf \
  --backup \
  --target-dir=/backup/full \
  --user=root \
  --password='123456'
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      • --user 强制 XtraBackup 进程以 MySQL 容器的用户身份运行，可权限冲突。

  • 恢复步骤：

    1. 准备备份：

      xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup/full
      1

      Docker下命令

      docker run --rm --name xtrabackup-temp \
  --user $(id -u):$(id -g) \
  -v /宿主机/备份目录:/backup/full \
  percona/percona-xtrabackup:2.4 \
  xtrabackup \
  --defaults-file=/etc/my.cnf \
  --prepare \
  --target-dir=/backup/full \
      1
      2
      3
      4
      5
      6
      7
      8
      • --user 强制 XtraBackup 进程以宿主机备份目录属主的用户身份运行，可权限冲突。

    2. 停止MySQL并清空数据目录：

      # 停止MySQL
systemctl stop mysqld
# 备份并清空数据目录
cp -r /var/lib/mysql /var/lib/mysql.bak
rm -rf /var/lib/mysql/*
      1
      2
      3
      4
      5

    3. 替换数据目录：

      xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/backup/full
      1

      Docker下命令

      docker run --rm --name xtrabackup-temp \
  --user $(id -u):$(id -g) \
  -v /宿主机/mysql数据目录:/var/lib/mysql \
  ...
  -v /宿主机/备份目录:/backup/full \
  percona/percona-xtrabackup:2.4 \
  xtrabackup \
  --defaults-file=/etc/my.cnf \
  --copy-back \
  --target-dir=/backup/full \
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      • --user 强制 XtraBackup 进程以宿主机备份目录属主的用户身份运行，可权限冲突。
      • ...省略号部分为宿主机本地MySQL映射目录。

    4. 修正权限并启动：

      # 修正权限
chown -R mysql:mysql /var/lib/mysql
# 启动MySQL
systemctl start mysqld
      1
      2
      3
      4
# 增量备份与恢复

  • 备份步骤：

    1. 创建创建备份用户及目录

      # 创建备份专用用户（需 REPLICATION CLIENT, RELOAD, PROCESS 权限）
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'YourPassword';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
      1
      2
      3
      4
      # 创建备份文件保存的目录
mkdir -pv /backup/full
      1
      2

    2. 备份命令

      # 全量备份
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/backup/full

# 增量备份1（基于全量LSN）
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full

# 增量备份2（基于上一次增量）
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/backup/inc2 \
  --incremental-basedir=/backup/inc1
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10

  • 恢复步骤：

    1. 准备全量：

      xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/full
      1

    2. 合并增量：

      # 合并增量1
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# 合并增量2（最后一步去掉--apply-log-only）
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc2
      1
      2
      3
      4
      5
      6
      7

    3. 复制数据并启动：

      xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
      1
      2
      3

# 三、迁移方案：同版本手动复制数据目录

# 1、MySQL5（5.7.44→5.7.44）

# 1. 停止数据库服务

  • 系统环境

    # 停止MySQL服务
systemctl stop mysql
    1
    2

  • 容器环境

    # 停止Docker MySQL容器
docker stop MySQL容器名称
    1
    2
# 2. 备份源数据目录

  • 可选，但强烈推荐

    # 备份源数据目录
tar -cvf /backup/mysql_data.tar /var/lib/mysql
    1
    2
# 3. 迁移数据目录

以下命令排除临时文件、日志文件等，仅迁移核心数据：

rsync -avzP --delete --exclude='performance_schema' \
           --exclude='sys' \
           --exclude='ib_logfile*' \
           --exclude='binlog.*' \
           --exclude='slow_query.log' \
           --exclude='*.err' \
           --exclude='ibtmp1' \
           --exclude='*.tmp' \
           --exclude='*.sock' \
           --exclude='*.pid' \
           --exclude='auto.cnf' \
           /var/lib/mysql/ user@new_server:/var/lib/mysql/
1
2
3
4
5
6
7
8
9
10
11
12

参数解释：

  • -a（归档模式）： 等价于 -rlptgoD，递归同步子目录，并保留文件权限、时间戳、属主属组、设备文件及符号链接等所有属性。这是备份场景的首选参数，确保数据完整性。
  • -v（详细输出）： 实时显示同步过程中的文件列表和状态，便于监控传输进度和调试问题。
  • -z（压缩传输）： 传输时对文件数据进行压缩（如文本类SQL文件），显著减少网络带宽占用。
  • -P（进度显示 + 断点续传）： 等同于 --partial --progress，支持中断后继续传输未完成文件，并显示实时进度条，对大文件传输至关重要。
  • --delete（删除目标多余文件）： 严格镜像源目录，​删除目标目录中存在但源目录中已缺失的文件。需谨慎使用，避免误删重要数据。

以下是MySQL 5.7.44通过拷贝数据目录迁移时需排除的文件和路径的整理，按类别分类说明：

  • 必须排除的文件
类别 文件/路径 原因说明
日志文件 ib_logfile0, ib_logfile1 InnoDB Redo日志文件，与当前实例事务状态绑定，直接拷贝会导致InnoDB崩溃，迁移后由新实例自动生成。
binlog.* 如binlog.000001,binlog.index，二进制日志记录操作历史，包含事务日志和复制信息，直接拷贝可能造成主从复制混乱。
slow_query.log 慢查询日志，记录执行时间较长的查询，迁移后由新实例自动生成。
*.err 错误日志，记录运行时的错误信息，迁移后由新实例自动生成。
临时文件 ibtmp1 InnoDB临时表空间文件，迁移后由新实例自动重建。
*.tmp 残留临时文件，直接拷贝可能导致启动失败。
套接字 mysql.sock 套接字文件，进程间通信文件，启动时动态生成。
进程文件 mysqld.pid, *.pid 进程ID文件，记录运行状态，迁移后冲突。
标识文件 auto.cnf 服务器UUID，存储实例唯一标识，新环境需要生成自己的 UUID，直接拷贝会导致多实例冲突。
  • 必须排除的目录
目录 处理方式
sys/ 系统诊断视图数据库，MySQL 5.7引入，与服务器配置绑定，目标环境需删除旧目录后再启动。
performance_schema/ 系统性能监控数据库，新实例启动时自动重建，目标环境需删除旧目录后再启动。
  • 必须迁移的目录
文件/目录 说明
mysql/ 系统权限数据库，包含用户权限元数据、存储过程/函数定义、事件调度器配置等，需完整拷贝。
ibdata1 InnoDB系统表空间文件，存储元数据（表结构、undo日志）和共享表空间。若源和目标版本一致且存储引擎配置相同，需完整拷贝，避免数据丢失。
自定义数据库目录 如my_database/（包含.ibd表数据文件，.frm表结构文件），需完整拷贝。
# 4. 配置检查

  • 需确认源库与目标库的 my.cnf 配置中以下参数一致：

    [mysqld]
innodb_file_per_table=ON  # 必须与源库一致
character-set-server=utf8mb4
lower_case_table_names=1   # 表名大小写敏感设置，必须一致
    1
    2
    3
    4

    • innodb_file_per_table： 控制 InnoDB 表的存储方式

      • ON：每个表有独立的 .ibd 数据文件（如 users.ibd）
      • OFF：所有表数据存储在共享的 ibdata1 文件中

      迁移时必须保持一致的原因：数据文件结构兼容性

      • 源库为 ON → 目标库必须 ON，否则 MySQL 无法识别独立的 .ibd 文件
      • 源库为 OFF → 目标库必须 OFF，否则会丢失共享表空间数据

    • character-set-server：定义数据库默认字符集

      迁移时必须一致的原因：数据完整性保护

      场景 源库字符集 目标库字符集 结果
      安全 utf8mb4 utf8mb4 ✅ 数据无损
      危险 utf8mb4 latin1 ❌❌ 中文/emoji 变乱码
      危险 latin1 utf8mb4 ❌❌ 乱码无法恢复

    • lower_case_table_names：表名大小写敏感设置（0：区分大小写；1：不区分，以小写存储）

      迁移时必须一致的原因：表名识别兼容性

      源库设置 目标库设置 结果
      0 1 ❌ 可能找不到表（大小写敏感）
      1 0 ❌ 可能找不到表（大小写敏感）
      相同值 相同值 ✅ 正常

  • 如果源库使用了其他存储引擎（如MyISAM），确保目标库支持并启用。

# 5. 权限修复

  • 系统环境

    chown -R mysql:mysql /path/to/target/
    1

  • 容器环境

    # 999是Docker内MySQL用户UID
chown -R 999:999 /path/to/target/
    1
    2

    • 如果宿主机开启了SELinux，设置安全上下文：

      chcon -R system_u:object_r:container_file_t:s0 /path/to/target
      1

      或者在运行容器时使用z选项挂载卷：

      docker run -v /path/to/target:/var/lib/mysql:z ...
      1

    • 对于AppArmor，确保Docker容器有权限访问目标目录。

# 6. 验证数据完整性

  • 容器环境

    # 进入容器MySQL检查
docker exec -it MySQL容器名称 mysql -uroot -p密码
    1
    2
    -- 确认版本
SHOW VARIABLES LIKE 'version'; 

-- 确认库存在
SHOW DATABASES;   

-- 验证系统表（如用户表）是否存在
SELECT COUNT(*) FROM mysql.user;

-- 验证关键用户数据库的表
USE 用户数据库;
SHOW TABLES;
SELECT COUNT(*) FROM 关键表;

-- 检查InnoDB状态
SHOW ENGINE INNODB STATUS;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
# 7. 日志与错误排查bash

查看日志

  • 容器环境

    # 启动失败时检查容器日志
docker logs MySQL容器名称
    1
    2

错误排查

错误信息 原因 解决方案
Different lower_case_table_names settings 大小写敏感配置不一致 目标库my.cnf添加：lower_case_table_names=1（与源库一致）
Table 'mysql.user' doesn't exist 系统表损坏或缺失 从备份恢复mysql系统数据库，或运行mysql_install_db重新初始化系统表（注意：会覆盖现有数据，慎用）
InnoDB: Table flags are 0 in the data dictionary 表空间文件与数据字典不匹配 运行mysql_upgrade修复，或使用ALTER TABLE ... DISCARD TABLESPACE和ALTER TABLE ... IMPORT TABLESPACE重新导入表
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' 套接字文件路径错误或权限问题 检查my.cnf中socket配置，确保目录存在且权限正确
InnoDB: The log sequence number in ibdata files does not match redo log不匹配 删除ib_logfile*文件，重启MySQL，让InnoDB重建redo log（注意：此操作有风险，确保无数据丢失）

补充重要注意事项：

  1. 运行mysql_upgrade：迁移后如果遇到系统表不兼容，可以运行（注意：在MySQL 5.7中，mysql_upgrade会检查并升级系统表）

    # 系统环境
mysql_upgrade -u root -p

# 容器环境
docker exec -it MySQL容器名称 mysql_upgrade -u root -p
    1
    2
    3
    4
    5

  2. 避免跨大版本迁移：虽然同是5.7，但小版本升级（如5.7.30到5.7.44）通常兼容，但仍需测试。

  3. GTID一致性：如果源库启用了GTID，迁移后需验证GTID状态（MySQL 5.6+支持）

    SHOW GLOBAL VARIABLES LIKE 'gtid_executed';
    1

# 2、MySQL8（8.0.39→8.0.39）

# 1. 停止数据库服务

  • 系统环境

    systemctl stop mysql
    1

  • 容器环境

    docker stop MySQL容器名称
    1
# 2. 备份源数据目录
# 打包源数据目录，将 /backup/data_bak.tar 替换为实际的路径和文件名
tar -cvf /backup/data_bak.tar /var/lib/mysql
1
2
# 3. 迁移数据目录
rsync -avzP --delete --exclude='performance_schema' \
           --exclude='sys' \
           --exclude='ib_logfile*' \
           --exclude='#innodb_redo/*' \
           --exclude='binlog.*' \
           --exclude='slow_query.log' \
           --exclude='*.err' \
           --exclude='ibtmp1' \
           --exclude='#ib_16384_*.dblwr' \
           --exclude='#innodb_tmp/*' \
           --exclude='*.tmp' \
           --exclude='*.sock' \
           --exclude='*.pid' \
           --exclude='auto.cnf' \
           /var/lib/mysql/ user@new_server:/var/lib/mysql/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

以下是MySQL 8.0.39通过拷贝数据目录迁移时需排除的文件和路径的整理，按类别分类说明：

  • 必须排除的文件
类别 文件/路径 原因说明
日志文件 ib_logfile0,
ib_logfile1
#innodb_redo		 InnoDB Redo日志文件，与当前实例事务状态绑定，直接拷贝会导致InnoDB崩溃，迁移后由新实例自动生成。
MySQL 8.0.30之前，redo log文件是ib_logfile0和ib_logfile1；
MySQL 8.0.30之后，redo log文件可以配置为动态文件，默认为#innodb_redo目录
binlog.*
 如binlog.000001,binlog.index，二进制日志记录操作历史，包含事务日志和复制信息，直接拷贝可能造成主从复制混乱。
slow_query.log 慢查询日志，记录执行时间较长的查询，迁移后由新实例自动生成。
*.err 错误日志，记录运行时的错误信息，迁移后由新实例自动生成。
临时文件 ibtmp1 InnoDB临时表空间文件，迁移后由新实例自动重建。
#ib_16384_*.dblwr 如#ib_16384_0.dblwr,#ib_16384_1.dblwr，双写缓冲文件，InnoDB运行时生成的临时文件，迁移后由新实例自动生成。
#innodb_temp/ InnoDB临时表数据目录，无需迁移。
*.tmp 残留临时文件，直接拷贝可能导致启动失败。
套接字 mysql.sock, mysqlx.sock 套接字文件，进程间通信文件，启动时动态生成。用于本地通信，迁移后由新实例自动生成
进程文件 mysqld.pid,
*.pid		 进程ID文件，记录运行状态，迁移后冲突。进程ID文件，记录MySQL进程ID，重启后自动创建。
标识文件 auto.cnf 服务器UUID，存储实例唯一标识，新环境需要生成自己的 UUID，直接拷贝会导致多实例冲突。
安全文件 private_key.pem, *.pem SSL/RSA证书，若目标环境有独立证书则需排除。或者迁移后需重新配置或生成。
  • 必须排除的目录
目录 处理方式
sys/ 系统诊断视图数据库，与服务器配置绑定系统诊断数据库，与服务器配置绑定，不可迁移。
performance_schema/ 系统性能监控数据库，新实例启动时自动重建，目标环境需删除旧目录后再启动。
information_schema/ 系统虚拟数据库，实际不存在于数据目录，无需处理。
  • 必须迁移的目录
文件/目录 说明
mysql/ 系统权限数据库，包含用户权限元数据、存储过程/函数定义、事件调度器配置等，需完整拷贝。
ibdata1 InnoDB系统表空间文件，存储元数据（表结构、undo日志）和共享表空间。若源和目标版本一致且存储引擎配置相同，需完整拷贝，避免数据丢失。
undo_* 默认配置innodb_file_per_table=ON，表示使用独立undo表空间，此时表数据和表结构存于undo_*目录下 .ibd 表数据文件，.frm表结构文件中。
自定义数据库目录 如my_database/（包含.ibd表数据文件），需完整拷贝。
# 4. 配置检查

  • 需确认源库与目标库的 my.cnf 配置中以下参数一致：

    [mysqld]
innodb_file_per_table=ON  # 必须与源库一致
character-set-server=utf8mb4
    1
    2
    3

    • innodb_file_per_table： 控制 InnoDB 表的存储方式

      • ON：每个表有独立的 .ibd 数据文件（如 users.ibd）
      • OFF：所有表数据存储在共享的 ibdata1 文件中

      迁移时必须保持一致的原因：数据文件结构兼容性

      • 源库为 ON → 目标库必须 ON，否则 MySQL 无法识别独立的 .ibd 文件

      • 源库为 OFF → 目标库必须 OFF，否则会丢失共享表空间数据

    • character-set-server：定义数据库默认字符集

      迁移时必须一致的原因：数据完整性保护

      场景 源库字符集 目标库字符集 结果
      安全 utf8mb4 utf8mb4 ✅ 数据无损
      危险 utf8mb4 latin1 ❌ 中文/emoji 变乱码
      危险 latin1 utf8mb4 ❌ 乱码无法恢复

  • 如果源库启用了表加密（default_table_encryption=ON）：

    • 确认加密状态

      SHOW VARIABLES LIKE 'default_table_encryption';
SELECT TABLE_SCHEMA, TABLE_NAME, ENCRYPTION 
FROM information_schema.TABLES 
WHERE ENCRYPTION='YES';
      1
      2
      3
      4

    • 备份源库的密钥文件（默认在数据目录下，如*.pem），将密钥文件复制到目标服务器相同路径，确保文件权限：

      chmod 600 /var/lib/mysql/*.pem
      1

    • 加密数据库特殊处理

      • 如果启用keyring_file插件，迁移密钥文件（查看源配置文件中的keyring_file_data配置项，默认可能为keyring文件）：

        # 迁移密钥文件（路径查看SHOW VARIABLES LIKE 'keyring_file_data'）
cp /path/to/source_keyring /目标路径/
        1
        2

      • 在目标服务器的配置文件中，确保加载keyring插件：

        [mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring
        1
        2
        3
# 5. 权限修复

  • 系统环境

    chown -R mysql:mysql /path/to/target/
    1

  • 容器环境

    chown -R 999:999 /path/to/target/
    1

    • 如果宿主机开启了SELinux，设置安全上下文：

      chcon -R system_u:object_r:container_file_t:s0 /path/to/target
      1

      或者在运行容器时使用z选项挂载卷：

      docker run -v /path/to/target:/var/lib/mysql:z ...
      1

    • 对于AppArmor，确保Docker容器有权限访问目标目录。

# 6. 验证数据完整性

  • 容器环境

    # 进入容器MySQL检查
docker exec -it MySQL容器名称 mysql -uroot -p密码
    1
    2
    -- 确认版本
SHOW VARIABLES LIKE 'version'; 

-- 确认库存在
SHOW DATABASES;   

-- 验证数据字典完整性
SELECT COUNT(*) FROM mysql.tables; 

-- 验证redo log状态
SHOW ENGINE INNODB STATUS;

-- 验证加密表状态
SELECT TABLE_NAME, ENCRYPTION 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('sys','performance_schema');

-- 验证undo表空间
SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
WHERE SPACE_TYPE='Undo';
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
# 7. 日志与错误排查

查看日志

  • 容器环境

    # 启动失败时检查容器日志
docker logs MySQL容器名称
    1
    2

错误排查

错误信息 原因 解决方案
Different lower_case_table_names settings 大小写敏感配置不一致 目标库my.cnf添加：lower_case_table_names=1
SSL error: Unable to get certificate 缺失加密文件 复制源库*.pem文件并设权限
Table encryption differ from schema encryption 表加密状态不一致 运行：ALTER TABLE 表名 ENCRYPTION='Y';
ER_UNDO_TABLESPACE_NOT_EXIST 缺失undo表空间 重建：CREATE UNDO TABLESPACE
ER_CANNOT_FIND_KEYRING_PLUGIN 密钥环未加载 配置文件中添加early-plugin-load=keyring_file.so
ER_TABLE_EXISTS_ERROR 数据字典冲突 执行：mysql_upgrade --force
ER_DATA_DICT_BOOTSTRAP 数据字典初始化失败 执行mysqld --initialize-insecure重建数据字典
ER_REDO_LOG_VERSION redo log版本不兼容 降级MySQL版本或重建实例
ER_TABLESPACE_EXISTS undo表空间已存在 DROP UNDO TABLESPACE tablespace_name
ER_CANNOT_FIND_KEY_IN_KEYRING 密钥环加载失败 检查early-plugin-load配置路径

补充重要注意事项：

  1. GTID一致性：若启用GTID，迁移后需验证GTID状态

    SHOW GLOBAL VARIABLES LIKE 'gtid_executed';
    1

  2. 缓冲池预热：大库迁移后建议预热InnoDB缓冲池

    mysql -uroot -p -e "SET GLOBAL innodb_buffer_pool_dump_now=ON;"
    1

  3. 性能基线对比：迁移后需对比关键性能指标

    -- 迁移前后对比
SHOW GLOBAL STATUS WHERE Variable_name IN 
('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests');
    1
    2
    3

这些优化确保文档覆盖MySQL 8.0特有的迁移风险，提供更完整的操作指引和问题解决方案，显著降低迁移失败风险。

上次更新时间: 8/7/2025, 6:25:53 PM

MySQL运维：配置体系