为什么写这篇：MySQL 备份有 4 个维度——全量逻辑备份（mysqldump）、物理文件直抽（ibd2sql）、增量恢复（binlog2sql）、生产定时任务编排。任何一种出故障都能从其他三种救回来，才算合格的灾备体系。
适用读者：需要为 MySQL 设计完整备份方案的运维 / DBA。
前置知识：会用 Linux shell、知道 cron 表达式、了解 MySQL 基础。
目录
- mysqldump：通用逻辑备份
- 快速导入导出：sql 文件在容器内流转
- ibd2sql：从 .ibd 文件直接抽数据
- binlog 与 binlog2sql：增量恢复
- 定时备份 + 远程上传脚本
- 恢复场景实战：从全量 + binlog 恢复某张表
- Docker / K8s 环境的备份特例
1. mysqldump：通用逻辑备份
1.1 核心命令模板
1
2
3
4
5
6
7
8
| mysqldump \
--quick \
--events \
--all-databases \
--flush-logs \
--delete-master-logs \
--single-transaction \
> /backup/all_${dumpdate}.sql
|
| 参数 | 含义 | 是否必须 |
|---|
--quick, -q | 一行一行从服务器取，不缓存到内存 | 大表必须 |
--events, -E | 导出事件 | 重要 |
--all-databases, -A | 导出全部数据库 | 全量备份时用 |
--flush-logs | 导出前刷新 binlog | 重要 |
--delete-master-logs | 主库备份后清理已备份的 binlog | 减少磁盘占用 |
--single-transaction | 用一致快照导出（仅 InnoDB） | 重要 |
关于 --flush-logs 与 --delete-master-logs：使用 --all-databases 时，会逐个数据库刷新日志——但只有配合 --lock-all-tables 或 --master-data 时所有表会同时锁定。要保证"导出 + 日志刷新"原子性，请同时加 --master-data 或 --lock-all-tables。
1.2 排除大表 / 只导结构 / 只导数据
1
2
3
4
5
6
7
8
9
10
11
12
13
| # 排除表
docker exec mysql /usr/bin/mysqldump \
-u root -p'{{REDACTED}}' \
industry_iot_dev \
--ignore-table=industry_iot_dev.location_historydata \
--ignore-table=industry_iot_dev.base_log \
> /backup/safety-20230904.sql
# 只导数据（不带 CREATE TABLE）
mysqldump -t -u root -p'{{REDACTED}}' industry_iot_dev --tables test_sync > test_sync.sql
# 只导结构（不带 INSERT）
mysqldump -d -u root -p'{{REDACTED}}' industry_iot_dev > schema.sql
|
1.3 Docker 容器内备份
1
2
3
4
5
6
7
8
9
10
11
12
13
| # 在容器内 mysqldump 出来
docker exec mysql /usr/bin/mysqldump \
-u root -p'{{REDACTED}}' \
--single-transaction --quick \
industry_iot_dev \
--ignore-table=industry_iot_dev.location_historydata \
> /home/docker/mysql/bak/safety-20231212.sql
# 再用 docker cp 把文件拷出来
docker cp mysql:/tmp/safety-20231212.sql /home/docker/mysql/bak/
# 或直接管道（容器内）
docker exec mysql bash -c "mysqldump -uroot -p'{{REDACTED}}' industry_iot_dev" > backup.sql
|
坑点：mysqldump 默认会写锁。如果容器跑的是生产库，必须加 --single-transaction，否则会阻塞业务写入。
2. 快速导入导出：sql 文件在容器内流转
2.1 标准做法（推荐）
1
2
3
4
5
| # 1. 把 sql 文件复制进容器
docker cp backup.sql mysql:/tmp/backup.sql
# 2. 容器内执行导入
docker exec -it mysql bash -c "mysql -uroot -p'{{REDACTED}}' industry_iot_dev < /tmp/backup.sql"
|
2.2 关 binlog + 事务加速大文件导入
1
2
3
4
5
6
7
8
9
10
11
12
13
| -- 关闭 binlog（导入期间不写 binlog，提速 30%）
SET sql_log_bin = OFF;
-- 关闭自动提交
SET autocommit = 0;
USE industry_iot_dev;
START TRANSACTION;
SOURCE /tmp/backup.sql;
COMMIT;
-- 恢复
SET sql_log_bin = ON;
SET autocommit = 1;
|
SET sql_log_bin = OFF 必须是超级用户才有权限，且主从架构下从库别这么干——会导致从库 binlog 缺失，主从不一致。
2.3 Navicat 数据传输
开发机/测试机之间最省事的备份恢复是 Navicat 的"数据传输"功能：
- 工具 → 数据传输 → 选源库 + 目标库 → 全勾选项（结构 + 数据）
- 优点：自带进度条、可中断、跨 MySQL 版本兼容
坑点：表多时会卡死——Navicat 单连接执行所有 DDL。改成 1 张表 1 次传输。
3. ibd2sql：从 .ibd 文件直接抽数据
ibd2sql 是国产开源工具（github.com/ddcw/ibd2sql），不需要 MySQL 实例，直接从 .ibd 文件里读 InnoDB 索引页、解析出行记录。
3.1 适用场景
| 场景 | 是否适用 |
|---|
| MySQL 实例起不来（磁盘损坏、配置错误） | ✓ 唯一能救命的办法 |
| 想不锁表地查历史数据 | ✓ 不会动原文件 |
| 想审计某张表的删除记录 | ✓ 配合 .ibd 副本可读 |
| 普通日常备份 | ✗ 不如 mysqldump 简单 |
3.2 安装与使用
1
2
3
4
5
6
7
8
9
10
11
12
| # 安装 Python3（一般系统都有）
apt install -y python3
# 下载最新版本
wget https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.10.tar.gz
tar -xzf v1.10.tar.gz
cd ibd2sql-1.10
# 直接抽数据
python3 main.py /var/lib/mysql/your_db/your_table.ibd \
--sql \
--output /tmp/your_table.sql
|
默认会输出 INSERT 语句，直接 mysql 客户端执行即可恢复。
4. binlog 与 binlog2sql：增量恢复
4.1 什么是 binlog
binlog（二进制日志）记录了所有修改数据的 SQL，主要用于主从复制和增量恢复。
1
2
3
4
| # 查看 binlog
docker exec -it mysql bash
cd /var/lib/mysql
mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000013
|
4.2 反解 binlog 找某张表的变更
1
2
3
4
5
6
| # 配合 grep 找到某张表的所有 DML
mysqlbinlog /var/lib/mysql/bin.000003 \
--database industry_iot \
--base64-output=decode-rows -vv \
--skip-gtids=true \
| grep -C 1 -i "risk_task_ctrl" > sql.log
|
4.3 binlog2sql：把 binlog 反解成可执行 SQL
1
2
3
4
5
6
7
8
9
10
11
| pip install binlog2sql
# 关键场景：误删一张表后的精准恢复
python binlog2sql.py \
-h127.0.0.1 -P3306 -uroot -p'{{REDACTED}}' \
-d industry_iot \
-t risk_task_ctrl \
--start-file='binlog.000003' \
--start-datetime='2016-12-13 20:25:00' \
--stop-datetime='2016-12-13 20:30:00' \
> recovery.sql
|
| 参数 | 含义 |
|---|
--start-file | 从哪个 binlog 文件开始 |
--start-datetime | 起始时间（精确到秒） |
--stop-datetime | 结束时间 |
-t | 指定表（不加则整个库） |
-d | 指定库 |
典型误删恢复流程：
- 立即发现误操作 → 不要慌，先看
SHOW MASTER STATUS 记录当前 binlog 位置 - 锁表：
FLUSH TABLES WITH READ LOCK;（防止新写入覆盖现场） - 用
binlog2sql 生成反向 SQL（默认生成的就是回滚 SQL） - 应用回滚 SQL
- 解锁：
UNLOCK TABLES;
4.4 binlog 模式与 ROW vs STATEMENT
1
2
3
4
5
6
7
8
9
10
11
| -- 查看 binlog 模式
SHOW VARIABLES LIKE 'binlog_format';
-- ROW 推荐（基于行的变更，主从一致性好）
-- STATEMENT 默认（基于 SQL 文本，主从不一致风险大）
-- MIXED 混合
-- 推荐改成 ROW
SET GLOBAL binlog_format = 'ROW';
-- 同时开启 binlog_rows_query_log_events（把原始 SQL 也记到 binlog 里）
SET GLOBAL binlog_rows_query_log_events = ON;
|
ROW 模式 + binlog_rows_query_log_events = ON 让你既能看到行级变化，又能直接看到原 SQL——调 bug 必备。
4.5 binlog 重置
1
2
| # 慎用！会清空所有 binlog
mysql -uroot -p'{{REDACTED}}' -e "RESET MASTER;"
|
5. 定时备份 + 远程上传脚本
5.1 cron 表达式速查
1
2
3
4
5
6
7
| * * * * *
- - - - -
| | | | +----- 星期中星期几 (0 - 6) (星期天 为0)
| | | +---------- 月份 (1 - 12)
| | +--------------- 一个月中的第几天 (1 - 31)
| +-------------------- 小时 (0 - 23)
+------------------------- 分钟 (0 - 59)
|
5.2 完整备份脚本（保留 7 天 + 上传 FTP）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| #!/usr/bin/env bash
# safety-local-mysqlbak.sh
set -e
DATE=$(date +%Y%m%d%H%M%S)
BACK_DATA=safety-${DATE}.sql
BAK_PATH=/home/docker/mysql/bak
if [ ! -d ${BAK_PATH} ]; then
mkdir -p ${BAK_PATH}
fi
# 1. 备份
docker exec mysql /usr/bin/mysqldump \
-u root -p'{{REDACTED}}' \
--single-transaction --quick \
industry_iot_dev \
--ignore-table=industry_iot_dev.location_historydata \
> ${BAK_PATH}/${BACK_DATA}
# 2. 删除 7 天前的
find ${BAK_PATH} -name "*.sql" -mtime +7 | xargs rm -rf
# 3. 保留最新副本软链
cp ${BACK_DATA} ${BAK_PATH}/safety_latest.sql
# 4. 上传 FTP（内网示例）
curl -u www:'{{REDACTED}}' \
-T ${BAK_PATH}/safety_latest.sql \
ftp://internal.example.com/tools/
# 5. 远程异地备份（公司机房示例）
curl -u www:'{{REDACTED}}' \
-T /home/docker/mysql/bak/safety-${DATE}.sql \
ftp://internal.example.com:25421/tools/
|
5.3 注册到 crontab
1
2
3
4
5
6
7
8
9
10
| chmod +x /home/docker/mysql/backupmysql.sh
# 编辑定时任务
crontab -e
# 每天凌晨 0 点执行
0 0 * * * /home/docker/mysql/backupmysql.sh
# 或从远程拉脚本执行（参数：保留天数）
0 0 * * * /usr/bin/curl http://internal.example.com:9091/file/sh/safety-local-mysqlbak.sh | bash -s 7
|
参数化 cron：用 (crontab -l | grep -v "$cron_job"; echo "$cron_job") | crontab - 这种"过滤+追加"模式可以幂等地更新定时任务，不会重复添加。
6. 恢复场景实战：从全量 + binlog 恢复某张表
场景：某天发现 risk_task 表在 14:00 误删了某些行，备份是凌晨 3:00 的全量。
恢复步骤：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| # 1. 找到 14:00 前最近的 binlog 位置
mysqlbinlog /var/lib/mysql/bin.000003 --base64-output=decode-rows -vv \
--skip-gtids=true | grep -C 3 "DELETE FROM risk_task"
# 2. 生成风险点 03:00 ~ 14:00 之间 risk_task 表的完整变更
python binlog2sql.py \
-uroot -p'{{REDACTED}}' \
-d industry_iot -t risk_task \
--start-file='binlog.000003' \
--start-datetime='2024-06-15 03:00:00' \
--stop-datetime='2024-06-15 14:00:00' \
--sql \
> /tmp/risk_task_recovery.sql
# 3. 先在测试库跑一遍
mysql -uroot -p'{{REDACTED}}' test_industry_iot < /tmp/risk_task_recovery.sql
# 4. 在生产库上找回 03:00 后被误删的数据
# （一般只取 DELETE 的反向 SQL，过滤掉 UPDATE）
|
前提：你的 binlog 一定要是 ROW 模式 + binlog_rows_query_log_events = ON + binlog 文件没被清理。
7. Docker / K8s 环境的备份特例
7.1 Docker 容器备份
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| # 备份（容器内）
docker exec mysql /usr/bin/mysqldump \
-u root -p'{{REDACTED}}' \
--single-transaction --skip-lock-tables --quick \
industry_iot_dev \
> /home/iot.sql
# 重建数据库
cat << "EOF" | docker exec -i mysql /usr/bin/mysql -u root --password='{{REDACTED}}'
DROP DATABASE IF EXISTS `industry_iot_dev`;
CREATE DATABASE `industry_iot_dev` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
EOF
# 导入
cat /home/iot.sql | docker exec -i mysql /usr/bin/mysql \
-u root --password='{{REDACTED}}' industry_iot_dev
|
7.2 K8s 备份
1
2
3
4
5
6
7
8
9
| # 通过 kubectl exec 在 Pod 内执行
kubectl exec -i "$(kubectl get pods -n mydb | grep mysql | awk '{print $1}')" \
-n mydb \
-- bash -c "/usr/bin/mysqldump --defaults-file=/etc/mysql/conf.d/my.cnf industry_iot_dev" \
> safety.sql
# 传到 MinIO 备份
mc rm --versions --force minio-server/file/dev/safety/safety.sql
mc cp /file/safety.sql minio-server/file/dev/safety/
|
7.3 K8s 单表导入
1
2
| cat risk_task.sql | kubectl exec -i $(kubectl get pods -n mydb | grep mysql | awk '{print $1}') \
-n mydb -- bash -c "/usr/bin/mysql -u root --password='{{REDACTED}}' test"
|
7.4 导入时报 ASCII ‘\0’ 错误
1
| ERROR: ASCII '\0' appeared in the statement
|
mysqldump 默认不识别二进制 0 字符。加 --binary-mode=1：
1
2
3
4
5
| cat /home/docker/mysql/bak/safety-20240504080004.sql | \
docker exec -i mysql /usr/bin/mysql \
--binary-mode=1 \
-u root --password='{{REDACTED}}' \
industry_iot_dev
|
经验总结
| 备份类型 | 优点 | 缺点 | 适用 |
|---|
| mysqldump 逻辑备份 | 跨版本兼容、可读、可编辑 | 大库慢、占内存 | 中小库 / 跨版本迁移 |
| XtraBackup 物理热备 | 速度快、增量 | 配置复杂 | 大库 / 高频备份 |
| binlog 增量 | 时间点恢复 | 依赖全量备份 | 误删救命 |
| ibd2sql 直抽 .ibd | 实例起不来也能读 | 工具年轻 | 灾难恢复 |
铁律：只做一种备份 = 没有备份。生产环境至少 2 种备份组合（如：每天全量 + 持续 binlog + 每周异地同步）。
下一步
- 数据同步 Canal / Debezium：见《MySQL 实时同步实战》
- 索引与 SQL 优化：见《MySQL 索引优化实战》
- 权限与安全：见《MySQL 用户权限与安全实战》
2024+ 视角：MySQL Shell + MySQL Operator + PIT 恢复
MySQL Shell（mysqlsh）—— 推荐替代 mysql/mysqldump
mysqlsh 是 8.0.13+ 引入的多语言客户端 + 工具集：
1
2
3
4
5
6
7
8
| # 启动 mysqlsh（默认 SQL 模式）
mysqlsh --uri=user@10.0.0.10:3306
# 切到 JS 模式
mysqlsh --uri=user@10.0.0.10:3306 --js
# 切到 Python 模式
mysqlsh --uri=user@10.0.0.10:3306 --py
|
核心价值：内置 util 模块提供并行 + 一致性的备份恢复能力。
util.dumpInstance() 替代 mysqldump
1
2
3
4
5
6
7
8
9
10
| // mysqlsh JS 模式
util.dumpInstance("/backup/full_dump", {
threads: 8, // 并行 8 线程
compression: "zstd", // zstd 压缩（比 gzip 快 3 倍）
chunking: true, // 分块大表
showProgress: true,
consistent: true, // FLUSH TABLES WITH READ LOCK
ddlOnly: false,
dataOnly: false
});
|
优势：
- 8 线程并行导出，比 mysqldump 快 5-10 倍
- 自动分块大表（>50GB 也能稳定）
- 断点续传：导出失败可
--resumeFrom 续传 - 支持 zstd 压缩（比 gzip 快 3 倍、压缩比高 10%）
util.loadDump() 导入
1
2
3
4
5
6
7
8
9
| util.loadDump("/backup/full_dump", {
threads: 8,
showProgress: true,
ignoreVersion: true, // 跨大版本恢复
loadUsers: true, // 同时导入用户
loadDdl: true,
loadData: true,
skipBinlog: true // 从库导入时跳过 binlog
});
|
MySQL Operator for Kubernetes（2024+ GA）
1
2
3
4
5
6
7
8
9
10
11
12
13
| # mysql-innodbcluster.yaml
apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
name: my-cluster
spec:
instances: 3
baseServerId: 1000
tls:
useSelfSigned: true
router:
instances: 2
secretName: mysql-cluster-secret
|
优势：
- 3 节点 InnoDB Cluster 自动部署（Group Replication）
- MySQL Router 自动 sidecar
- 滚动升级、备份、监控一应俱全
- 对标 Percona Operator for MySQL
2024+ 备份恢复工具矩阵
| 工具 | 特点 | 2024+ 适用 |
|---|
| mysqldump | 老牌、单线程 | 小库 / 跨版本兼容 |
| mysql-shell util | 并行、zstd、断点续传 | 生产首选 |
| mydumper / myloader | C 写的并行工具 | 大库快速备份 |
| Percona XtraBackup 8.x | 物理热备 | TB 级库 + 快速恢复 |
| MySQL Enterprise Backup (MEB) | 商业 | 企业版特性 |
| TiDB DM / OceanBase OMS | 异构同步 | MySQL → TiDB / OB |
PIT（Point-In-Time）恢复的标准流程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| # 1. 全量备份
mysqlsh --uri=user@db-host -e "util.dumpInstance('/backup/2024-06-15-0300')"
# 2. 恢复全量到临时库
mysqlsh --uri=user@temp-host -e "util.loadDump('/backup/2024-06-15-0300')"
# 3. 解析 binlog 到 PIT
mysqlbinlog --start-datetime='2024-06-15 03:00:00' \
--stop-datetime='2024-06-15 14:00:00' \
--read-from-remote-server \
--host=db-host --user=repl \
binlog.000003 binlog.000004 \
> /tmp/pit.sql
# 4. 应用到临时库
mysql -u root -p temp_db < /tmp/pit.sql
# 5. 切换业务（DNS / 负载均衡切到 temp_db）
|
Cloud-Native 备份新方案
- AWS RDS/Aurora：自动备份 + PIT 恢复（5 分钟内任意点）
- 阿里云 RDS：跨地域备份 + 备份集查询
- 腾讯云 CynosDB：binlog 实时上传 COS，30 天可回放
2024+ 安全加固
1
2
3
4
5
6
7
8
9
10
11
12
13
| -- 8.0+ 强制 TLS 连接
ALTER USER 'app_user'@'%' REQUIRE SSL;
-- 8.0+ 密码强度策略
SET PERSIST validate_password.policy = STRONG;
SET PERSIST validate_password.length = 12;
-- 8.0+ 双因素认证
ALTER USER 'admin'@'%' ADD FACTOR 2;
-- 8.0.27+ 审计日志
SET PERSIST audit_log_format = JSON;
SET PERSIST audit_log_policy = ALL;
|
实战坑（2024+）
- mysqlsh
util.dumpInstance 默认不导出 mysql.user，要用 --users flag - **跨大版本恢复（5.7 → 8.4）**必须
mysql_upgrade，且要先检查是否有保留字冲突 - MySQL Operator 2.x 部署后默认开启 MySQL Router sidecar——K8s service 指向 router 而非直接指向 MySQL Pod
- MySQL HeatWave（Oracle 云）支持"自动 binlog 解析 + 一致性快照导出"——但仅限 Oracle Cloud
