# MySQL 备份恢复技术手册 \> \*\*适用范围\*\*：MySQL 5.7 / 8.0 的全量、增量、部分备份与恢复，以及基于 binlog 的应急恢复。 \> \*\*前置条件\*\*： \> - 具备源库与目标库的 root 账号及相应权限，密码统一以 \`\<密码\>\` 传入。 \> - 目标机已安装对应版本的备份工具（xtrabackup / mysql-shell / mydumper），docker 部署需具备镜像拉取权限。 \> - 执行前已完成一次可用的全量备份，并确认备份文件所在磁盘空间充足。 \> - 涉及增量恢复时，必须先存在全量备份作为基础。 ## 目录 1. \[备份方法对比与选型\](#1-备份方法对比与选型) 2. \[XtraBackup 备份恢复\](#2-xtrabackup-备份恢复) 3. \[mysqldump 备份恢复\](#3-mysqldump-备份恢复) 4. \[mysql-shell 备份恢复\](#4-mysql-shell-备份恢复) 5. \[mydumper 备份恢复\](#5-mydumper-备份恢复) 6. \[Binlog 应急恢复\](#6-binlog-应急恢复) 7. \[附件：备份恢复速度对比\](#7-附件备份恢复速度对比) ## 1. 备份方法对比与选型 ### 1.1 能力对比 \| 备份方法 \| 物理/逻辑 \| 全量/增量 \| 部分恢复 \| 备份大小 \| 备份速度 \| 恢复速度 \| binlog \| 网络备份 \| 格式 \| \|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\| \| xtrabackup \| 物理 \| 全量+增量 \| 很麻烦 \| 大 \| 快 \| 快 \| 支持 \| 不支持 \| ibd 数据文件 \| \| mysqldump \| 逻辑 \| 全量 \| 不支持 \| 小 \| 慢 \| 慢 \| 否 \| 支持 \| sql \| \| mysql-shell \| 逻辑 \| 全量+增量 binlog \| 支持 \| 小 \| 快 \| 慢 \| 支持 \| 支持 \| csv, tsv \| \| mydumper \| 逻辑 \| 全量+增量 binlog \| 支持 \| 小 \| 快 \| 慢 \| 支持 \| 支持 \| csv, tsv, sql, blob \| ### 1.2 选型建议 \| 场景 \| 推荐工具 \| \|---\|---\| \| 有增量需求 \| 优先 xtrabackup \| \| 异机恢复、数据迁移 \| mysql-shell、mydumper \| \| 数据量小 \| mysqldump（无需安装额外工具） \| ### 1.3 性能与注意事项 - 本地备份速度：mysql-shell \> xtrabackup \> mydumper \> mysqldump（参考\[附件\](#7-附件备份恢复速度对比)）。 - 本地恢复速度：xtrabackup \> mysql-shell ≈ mydumper \> mysqldump（参考附件）。 - 网络恢复速度：mysql-shell ≈ mydumper \> xtrabackup \> mysqldump。xtrabackup 不支持网络备份，且文件较大，传输与磁盘写入都很耗时。 - 备份大小（均开启压缩）：mydumper \< mysql-shell \< mysqldump \< xtrabackup（参考附件）。 - 操作难度：xtrabackup 最麻烦，需要编写较多命令，其他几种差异不大。 - 三种工具均支持 MySQL 5.7、8.0，其中 xtrabackup 需使用与目标库匹配的版本。 - mysql-shell 还支持 OSS 等多种存储介质；支持 binlog，可用于建从库或不停机迁移。 ## 2. XtraBackup 备份恢复 ### 2.1 MySQL 8.0 #### 2.1.1 全量备份恢复 ##### 2.1.1.1 源库全量备份 可执行全量备份（物理备份），也可选择近期的全量备份。备份命令分为 docker 与二进制两种，按部署方式选择。 \*\*docker 方式\*\* \`\`\`bash # 先全量备份 # 说明：--user mysql:input 解决目录权限问题，若对应数据目录用户为 mysql 则不需要加 # 说明：--parallel 4 并发备份，4 线程 # 说明：--compress=zstd --compress-threads=4 使用 zstd 压缩，并发度 4 docker run \\ --name \<备份容器名\> \\ --user mysql:input \\ -v /backup/mysql/:/backup/mysql/ \\ --rm \\ --network host \\ -t \\ -e TZ=Asia/Shanghai \\ -w /backup/mysql/ \\ --volumes-from \\ --entrypoint=/usr/bin/xtrabackup \\ "\<私有仓库\>/percona/percona-xtrabackup:8.0.35" \\ --defaults-file="/etc/mysql/conf.d/my.cnf" \\ --backup \\ --parallel 4 \\ --user="root" \\ --password="\<密码\>" \\ --compress=zstd --compress-threads=4 \\ --target-dir="mysql-backup-manual-20250509-full" \>\> mysql-backup-manual-20250509-full.log \`\`\` \*\*二进制形式\*\* \`\`\`bash xtrabackup \\ --defaults-file="/etc/my.cnf" \\ --backup \\ --parallel 4 \\ --user="root" \\ --password="\<密码\>" \\ --compress=zstd --compress-threads=4 \\ --target-dir="mysql-backup-manual-$(date +%F)-full" \>\> mysql-backup-manual-$(date +%F)-full.log \`\`\` ##### 2.1.1.2 目标库恢复 恢复同样分两种方式，\*\*建议使用二进制形式\*\*：容器部署场景也推荐在宿主机上执行、使用映射目录，比 docker 命令快很多（容器内映射目录会被识别为不同磁盘，移动时可能以复制+删除方式实现，额外消耗 IO）。 \*\*二进制形式（推荐）\*\* \`\`\`bash # 1、解压 xtrabackup \\ --parallel 4 \\ --decompress --compress-threads=4 --remove-original \\ --target-dir="mysql-backup-manual-$(date +%F)-full" # 2、准备 xtrabackup \\ --prepare \\ --parallel 4 \\ --target-dir="mysql-backup-manual-$(date +%F)-full" # 3、恢复 xtrabackup \\ --move-back \\ --datadir=/data/mysql \\ --target-dir="mysql-backup-manual-$(date +%F)-full" \`\`\` \*\*docker 形式\*\* \`\`\`bash # 1、解压 docker run \\ --name \<备份容器名\> \\ --user root:input \\ -v /opt/mysql/backup:/backup/mysql/ \\ --rm \\ --network host \\ -t \\ -e TZ=Asia/Shanghai \\ -w /backup/mysql/ \\ --entrypoint=/usr/bin/xtrabackup \\ "\<私有仓库\>/percona/percona-xtrabackup:8.0.35" \\ --parallel 4 \\ --decompress --compress-threads=4 --remove-original \\ --target-dir="mysql-backup-manual-20250509-full" # 2、准备 docker run \\ --name \<备份容器名\> \\ --user root:input \\ -v /opt/mysql/backup:/backup/mysql/ \\ --rm \\ --network host \\ -t \\ -e TZ=Asia/Shanghai \\ -w /backup/mysql/ \\ --entrypoint=/usr/bin/xtrabackup \\ "\<私有仓库\>/percona/percona-xtrabackup:8.0.35" \\ --prepare \\ --parallel 4 \\ --target-dir="mysql-backup-manual-20250509-full" # 3、恢复 docker run \\ --name \<备份容器名\> \\ --user root:input \\ -v /opt/mysql/backup:/backup/mysql/ \\ --rm \\ --network host \\ -t \\ -e TZ=Asia/Shanghai \\ -w /backup/mysql/ \\ --volumes-from \\ --entrypoint=/usr/bin/xtrabackup \\ "\<私有仓库\>/percona/percona-xtrabackup:8.0.35" \\ --defaults-file="/etc/mysql/conf.d/my.cnf" \\ --move-back \\ --target-dir="mysql-backup-manual-20250509-full" \`\`\` #### 2.1.2 部分备份恢复 ##### 2.1.2.1 源库备份 备份方法与全量备份恢复类似，仅需增加 \`--databases\` 或 \`--tables\` 选项指定需要导出的库或表。 \`\`\`bash xtrabackup \\ --defaults-file="/etc/mysql/conf.d/my.cnf" \\ --backup \\ --parallel 4 \\ --user="root" \\ --password="\<密码\>" \\ --databases="\<库名1\> \<库名2\> \<库名3\>" \\ --compress=zstd --compress-threads=4 \\ --target-dir="mysql-backup-manual-20250730-partial" \`\`\` \> 官方参考： \*\*附：方便使用的脚本\*\* 二进制部署的 MySQL： \`\`\`bash #!/bin/bash # manual-backup-partial-percona.sh if \[ $# -lt 1 \]; then echo "错误：缺少参数" echo "用法：$0 db1 db2 ..." echo "示例：$0 \<库名1\> \<库名2\>" exit 1 fi today=$(date +%Y%m%d) xtrabackup \\ --defaults-file="/etc/mysql/conf.d/my.cnf" \\ --backup \\ --parallel 4 \\ --user="root" \\ --password="\<密码\>" \\ --databases="\<库名\>" \\ --compress=zstd --compress-threads=4 \\ --target-dir="mysql-backup-manual-${today}-partial" \>\> mysql-backup-manual-${today}-partial.log \`\`\` docker 部署的 MySQL： \`\`\`bash #!/bin/bash # manual-backup-partial-percona-docker.sh if \[ $# -lt 1 \]; then echo "错误：缺少参数" echo "用法：$0 db1 db2 ..." echo "示例：$0 \<库名1\> \<库名2\>" exit 1 fi today=$(date +%Y%m%d) docker run \\ --name \<备份容器名\> \\ --user mysql:input \\ -v /backup/mysql/:/backup/mysql/ \\ --rm \\ --network host \\ -t \\ -e TZ=Asia/Shanghai \\ -w /backup/mysql/ \\ --volumes-from \\ --entrypoint=/usr/bin/xtrabackup \\ "\<私有仓库\>/percona/percona-xtrabackup:8.0.35" \\ --defaults-file="/etc/mysql/conf.d/my.cnf" \\ --backup \\ --parallel 4 \\ --user="root" \\ --password="\<密码\>" \\ --databases="$\*" \\ --compress=zstd --compress-threads=4 \\ --target-dir="mysql-backup-manual-${today}-partial" \>\> mysql-backup-manual-${today}-partial.log \`\`\` ##### 2.1.2.2 目标库恢复 此处只展示二进制形式（推荐）。docker 形式存在额外 IO，不建议使用；确需使用时可参考全量备份恢复章节，步骤类似。 与全量备份恢复的区别： 1. 准备（prepare）时需要增加 \`--export\` 参数。 2. 恢复时需要手动移动文件并导入表空间。 详细步骤如下。 \*\*一、传输备份目录\*\* \*\*二、解压及准备\*\* \`\`\`bash # 1、解压 xtrabackup \\ --parallel 4 \\ --decompress --compress-threads=4 --remove-original \\ --target-dir="mysql-backup-manual-20250509-full" # 2、准备 xtrabackup \\ --prepare --export \\ --parallel 4 \\ --target-dir="mysql-backup-manual-20250509-full" \`\`\` \*\*三、恢复\*\* 1. 生成丢弃、导入表空间文件的 SQL 语句： \`\`\`sql SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' DISCARD TABLESPACE;') FROM information_schema.tables WHERE table_schema = '\<库名\>'; SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' IMPORT TABLESPACE;') FROM information_schema.tables WHERE table_schema = '\<库名\>'; \`\`\` 2. 丢弃原有表空间文件： \`\`\`sql -- 执行第一步生成的丢弃表空间的语句 ALTER TABLE \<库名\>.\<表名\> DISCARD TABLESPACE; ALTER TABLE \<库名\>.\<表名\> DISCARD TABLESPACE; -- 省略其他...... \`\`\` 3. 移动备份的文件并修复权限： \`\`\`bash mv /home/backup/mysql-backup-manual-20250730-partial/\<库名\>/\* /data/mysql/data/\<库名\>/ chown -R polkitd:input /data/mysql/data/\<库名\>/ \`\`\` 4. 导入新的表空间文件： \`\`\`sql -- 执行第一步生成的导入表空间的语句 ALTER TABLE \<库名\>.\<表名\> IMPORT TABLESPACE; ALTER TABLE \<库名\>.\<表名\> IMPORT TABLESPACE; -- 省略其他...... \`\`\` #### 2.1.3 增量备份恢复 ##### 2.1.3.1 源库全量备份 增量备份之前必须存在一个已有备份作为基础，其中第一个必须是全量备份。方法与\[全量备份\](#2411-源库全量备份)一致。 ##### 2.1.3.2 源库增量备份 增量备份相对全量，多一个 \`--incremental-basedir\` 选项，用于指定在哪个备份的基础上做备份。 \`\`\`bash xtrabackup \\ --defaults-file="/etc/my.cnf" \\ --backup \\ --parallel 4 \\ --host= \\ --user="root" \\ --password="\<密码\>" \\ --compress=zstd --compress-threads=4 \\ --target-dir="mysql-backup-manual-20250521-incr1" \\ --incremental-basedir="mysql-backup-manual-20250425-full" \`\`\` ##### 2.1.3.3 目标库恢复 恢复需按顺序进行：全部解压 =\> 准备（全量、增1、增2......）=\> 恢复。 与全量相比，准备命令有如下差异： 1. 除最后一个备份的 prepare 外，前面每个备份在 prepare 时都需增加 \`--apply-log-only\` 选项。 2. 除第一个备份的 prepare 外，后面每个备份在 prepare 时都需增加 \`--incremental-dir=\` 选项指定需要应用的增量。 \`\`\`bash # 1、省略解压 # 2、准备 # 2.1 第一个备份：全量 xtrabackup \\ --prepare --apply-log-only \\ --parallel 4 \\ --target-dir="mysql-backup-manual-20250425-full" # 2.2 第二个备份：增量 1 xtrabackup \\ --prepare --apply-log-only \\ --parallel 4 \\ --target-dir="mysql-backup-manual-20250425-full" \\ --incremental-dir="mysql-backup-manual-incr1" # 2.3 第三个备份：增量 2（最后一个） xtrabackup \\ --prepare \\ --parallel 4 \\ --target-dir="mysql-backup-manual-20250425-full" \\ --incremental-dir="mysql-backup-manual-incr2" # 3、恢复 xtrabackup \\ --move-back \\ --datadir=/opt/mysql/data \\ --target-dir="mysql-backup-manual-20250425-full" \`\`\` ### 2.2 MySQL 5.x MySQL 5.x 使用 xtrabackup 备份恢复的方法与 8.0 基本一致，区别如下： \| 差异项 \| 说明 \| \|---\|---\| \| 版本 \| 使用 xtrabackup 2.4 \| \| 命令 \| 压缩参数由 \`--compress=zstd\` 改为 \`--compress\` \| #### 2.2.1 全量备份恢复 ##### 2.2.1.1 源库全量备份 备份命令分为 docker 与二进制两种，按部署方式选择。 二进制形式的备份中 \`--host\` 选项非必须；若报找不到 socket 文件，可加上该选项。 \`\`\`bash xtrabackup \\ --defaults-file="/etc/my.cnf" \\ --backup \\ --parallel 4 \\ --host= \\ --user="root" \\ --password="\<密码\>" \\ --compress --compress-threads=4 \\ --target-dir="mysql-backup-manual-20250521-full" \`\`\` docker 方式备份： \`\`\`bash # 全量备份 docker run \\ --name \<备份容器名\> \\ -v /home/backup/data/manual:/home/backup/data/manual \\ --rm \\ --network host \\ -t \\ -e TZ=Asia/Shanghai \\ -w /home/backup/data/manual \\ --volumes-from \\ --entrypoint=/usr/bin/xtrabackup \\ "\<私有仓库\>/perconalab/percona-xtrabackup:2.4.23" \\ --defaults-file="/etc/mysql/conf.d/my.cnf" \\ --backup \\ --parallel 4 \\ --user="root" \\ --password="\<密码\>" \\ --compress --compress-threads=4 \\ --target-dir="mysql-backup-manual-20250425-full" \`\`\` ##### 2.2.1.2 目标库恢复 恢复同样分两种方式，\*\*建议使用二进制形式\*\*：容器部署场景也推荐在宿主机上执行、使用映射目录，比 docker 命令快很多。 二进制形式（推荐）： \`\`\`bash # 1、解压 xtrabackup \\ --parallel 4 \\ --decompress --compress-threads=4 --remove-original \\ --target-dir="mysql-backup-manual-20250425-full" # 2、准备 xtrabackup \\ --prepare \\ --parallel 4 \\ --target-dir="mysql-backup-manual-20250425-full" # 3、恢复 xtrabackup \\ --move-back \\ --datadir=/opt/mysql/data \\ --target-dir="mysql-backup-manual-20250425-full" \`\`\` docker 形式： \`\`\`bash # 先解压 docker run \\ --name \<备份容器名\> \\ -v /home/backup/data/manual:/home/backup/data/manual \\ --rm \\ --network host -t \\ -e TZ=Asia/Shanghai \\ -w /home/backup/data/manual \\ "\<私有仓库\>/perconalab/percona-xtrabackup:2.4.23" \\ xtrabackup --parallel 4 --decompress \\ --target-dir="mysql-backup-manual-20250425-full" # 再准备（Prepare） docker run \\ --name \<备份容器名\> \\ -v /home/backup/data/manual:/home/backup/data/manual \\ --rm --network host -t \\ -e TZ=Asia/Shanghai \\ -w /home/backup/data/manual \\ "\<私有仓库\>/perconalab/percona-xtrabackup:2.4.23" \\ xtrabackup --prepare \\ --target-dir="mysql-backup-manual-20250425-full" # 恢复数据 docker run \\ --name \<备份容器名\> \\ -v /home/backup/data/manual:/home/backup/data/manual \\ --rm --network host -t \\ -e TZ=Asia/Shanghai \\ -w /home/backup/data/manual \\ --volumes-from \\ "\<私有仓库\>/perconalab/percona-xtrabackup:2.4.23" \\ xtrabackup --move-back \\ --defaults-file=/opt/mysql/conf/my.cnf \\ --target-dir="mysql-backup-manual-20250425-full" \`\`\` #### 2.2.2 增量备份恢复 相比 8.0 版本的增量备份，流程一致，只是参数略有不同。 ##### 2.2.2.1 源库全量备份 增量备份之前必须存在一个已有备份作为基础，其中第一个必须是全量备份。方法与\[全量备份\](#2211-源库全量备份)一致。 ##### 2.2.2.2 源库增量备份 \`\`\`bash xtrabackup \\ --defaults-file="/etc/my.cnf" \\ --backup \\ --parallel 4 \\ --host= \\ --user="root" \\ --password="\<密码\>" \\ --compress --compress-threads=4 \\ --target-dir="mysql-backup-manual-20250521-incr1" \\ --incremental-basedir="mysql-backup-manual-20250425-full" \`\`\` ##### 2.2.2.3 目标库恢复 顺序：全部解压 =\> 准备（全量、增1、增2......）=\> 恢复。 \`\`\`bash # 1、省略解压 # 2、准备 # 2.1 第一个备份：全量 xtrabackup \\ --prepare --apply-log-only \\ --parallel 4 \\ --target-dir="mysql-backup-manual-20250425-full" # 2.2 第二个备份：增量 1 xtrabackup \\ --prepare --apply-log-only \\ --parallel 4 \\ --target-dir="mysql-backup-manual-20250425-full" \\ --incremental-dir="mysql-backup-manual-incr1" # 2.3 第三个备份：增量 2（最后一个） xtrabackup \\ --prepare \\ --parallel 4 \\ --target-dir="mysql-backup-manual-20250425-full" \\ --incremental-dir="mysql-backup-manual-incr2" # 3、恢复 xtrabackup \\ --move-back \\ --datadir=/opt/mysql/data \\ --target-dir="mysql-backup-manual-20250425-full" \`\`\` ## 3. mysqldump 备份恢复 ### 3.1 全量备份恢复 #### 3.1.1 源库备份 \*\*docker\*\* \`\`\`bash #!/bin/bash # mysql-dump-all-in-docker.sh docker run --rm --name mysql-backup --net=host \\ --volumes-from \\ -e TZ=Asia/Shanghai \\ -v /backup/mysql:/backup/mysql \\ -w /backup/mysql/dump/ \\ "\<私有仓库\>/mysql:8.0.44" \\ /usr/bin/mysqldump -uroot -p'\<密码\>' --all-databases --single-transaction \\ --ignore-table=\<库名\>.\<表名\> \| gzip \> "mysql-fullbak-$(date +%Y%m%d)".sql.gz \`\`\` \*\*二进制\*\* \`\`\`bash # gzip 压缩 mysqldump -uroot -p --all-databases --single-transaction \\ --ignore-table=\<库名\>.\<表名\> \| gzip \> "mysql-fullbak-$(date +%Y%m%d)".sql.gz # zstd 压缩（建议） mysqldump -uroot -p --all-databases --single-transaction \\ --ignore-table=\<库名\>.\<表名\> \| zstd -z -c \> "mysql-fullbak-$(date +%Y%m%d)".sql.zst \`\`\` #### 3.1.2 目标库恢复 \`\`\`bash # gzip 压缩 gunzip -c mysql-fullbak-20251227.sql.gz \| mysql -h -P3306 -p\<密码\> # zstd 压缩（建议） zstd -d -c mysql-fullbak-20251227.sql.zst \| mysql -h -P3306 -p\<密码\> \`\`\` ### 3.2 部分备份恢复 \> ⚠️ 注意：可选择性备份，但不支持在已有备份的基础上做选择性恢复，只能完整恢复备份的范围。 使用方法与全量备份几乎一致，只是指定数据库名或表名： \`\`\`bash # 导出 mysqldump -uroot -p\<密码\> \<库名\> --single-transaction \| gzip \> "mysql-fullbak-$(date +%Y%m%d)".sql.gz # 导入 gunzip -c mysql-fullbak-20251227.sql.gz \| mysql -h -P3306 -p\<密码\> \<库名\> \`\`\` ## 4. mysql-shell 备份恢复 mysql-shell 是 MySQL 官方工具，相比 MySQL 命令行提供更多功能，其中的备份恢复工具效率高且实用。 \> 官方文档： ### 4.1 全量备份恢复 \*\*备份\*\* \> Note：禁用 \`tzUtc\`，否则导出的数据中时间会转为 UTC 时间，导致导入后慢 8 小时。 \`\`\`bash mysqlsh -h 127.0.0.1 --js -e "util.dumpInstance('./mysqlsh-dump-full-$(date +%Y%m%d)', {threads:4,tzUtc:false})" \`\`\` \*\*恢复\*\* \> Note：启用 \`skipBinlog\` 可加快恢复速度。 \`\`\`bash mysqlsh 127.0.0.1:3306 --sql -e "set global local_infile=1"; mysqlsh -h 127.0.0.1 --js -e "util.loadDump('./mysqlsh-dump-full-20251227', {threads:4,analyzeTables:'on',skipBinlog:true})" mysqlsh -h 127.0.0.1 --js -e "util.loadDump('./mysqlsh-dump-full-20251227', {threads:4,analyzeTables:'on',skipBinlog:true,resetProgress:true})" \`\`\` \*\*docker 方式恢复\*\* \`\`\`bash # 全量备份 docker run --rm -it --name mysql-shell \\ -v /home/\<目录\>/backup:/backup \\ -w /backup \\ "\<私有仓库\>/mysql-shell:8.4.5" \\ mysqlsh -h --js -e "util.dumpInstance('./mysqlsh-dump-full-$(date +%Y%m%d)', {threads:4,tzUtc:false})" # 部分备份 docker run --rm -it --name mysql-shell \\ -v /home/\<目录\>/backup:/backup \\ -w /backup \\ "\<私有仓库\>/mysql-shell:8.4.5" \\ mysqlsh -h --js -e "util.dumpInstance('./mysqlsh-dump-full-$(date +%Y%m%d)', {threads:4,tzUtc:false})" # 恢复 docker run --rm -it --name mysql-shell \\ -v /home/\<目录\>/restore:/restore \\ -w /restore \\ "\<私有仓库\>/mysql-shell:8.4.5" \\ mysqlsh -h --js -e "util.loadDump('./mysqlsh-dump-full-20251227', {threads:4,analyzeTables:'on',skipBinlog:true})" \`\`\` ### 4.2 部分备份恢复 备份和恢复时均支持筛选数据库和/或表，以实现部分备份或部分恢复。 \*\*部分备份\*\* 1. 使用 \`dumpSchemas\`、\`dumpTables\` 导出需要的库或表。 2. 使用 \`includeTables\`、\`excludeTables\` 筛选表。 \`\`\`bash # 备份某个库，同时排除某个表，排除例程 mysqlsh -h 127.0.0.1 --js -e "util.dumpSchemas(\['\<库名\>'\], './$(date +%F)-\<库名\>', {threads:4,tzUtc:false,routines:false,excludeTables:\['\<库名\>.\<表名\>'\]})" # 备份某个表，不启用压缩 mysqlsh -h 127.0.0.1 --js -e "util.dumpTables('\<库名\>', \['\<表名\>'\], './$(date +%F)-\<库名\>', {threads:4,tzUtc:false,compression:'none'})" \`\`\` \*\*部分恢复\*\* 使用 \`includeTables\`、\`excludeTables\` 筛选表。 \`\`\`bash # 选择只导入两个数据库，排除指定表，例程只包含指定例程 mysqlsh :3306 -u'root' --js -e "util.loadDump('./20260103-002001', {threads:8,analyzeTables:'on',ignoreVersion:true,includeSchemas:\['\<库名1\>','\<库名2\>'\],excludeTables:\['\<库名1\>.\<表名\>'\],includeRoutines:\['\<库名2\>.\<例程名\>'\],ignoreExistingObjects:true})" \`\`\` ## 5. mydumper 备份恢复 mydumper 是一个开源的、支持多线程的类 MySQL 数据库逻辑备份工具，支持 MySQL、MariaDB、Percona Server 等发行版。 \> 官方参考文档： ### 5.1 全量备份恢复 \*\*备份\*\* \`\`\`bash # 多线程快速导出整个库，附带压缩 mydumper -u root -h 127.0.0.1 --ask-password --all-tablespaces --chunk-filesize 128 \\ --compress zstd --compress-protocol --skip-tz-utc --threads 4 --format LOAD_DATA \\ --outputdir /backup/mydumper/all-$(date +%F) \`\`\` \*\*全量恢复\*\* \`\`\`bash # 直接恢复 myloader --directory=export-20110614-094953 -h 127.0.0.1 -u root --ask-password # 覆盖恢复，并指定线程数 myloader --directory export-20110614-094953 \\ -h 127.0.0.1 -u root --ask-password \\ --drop-table --threads 4 # 覆盖恢复，并指定线程数和数据库 myloader --directory export-20110614-094953 \\ -h 127.0.0.1 -u root --ask-password \\ --drop-table --threads 4 --database \<库名\> \`\`\` ### 5.2 部分备份恢复 使用 \`--regex\` 或 \`--table-list\` 过滤出需要的表，这两个选项在导出、导入时都可用，详细参考前文或官方文档。 \`\`\`bash ## 备份 # 导出时排除数据库：mysql、test mydumper --regex '\^(?!(mysql\\.\|test\\.))' # 导出指定数据库 mydumper --regex '\^(mysql\\.\|test\\.)' # 导出指定的表 mydumper --regex '\^(db1\\.table1$\|db2\\.table2$)' \`\`\` ## 6. Binlog 应急恢复 以上所有备份方法，无论物理备份还是逻辑，都是定时定点的备份手段，无法恢复到任意时刻。binlog 是数据库的二进制日志，记录了数据库的所有操作，可以结合全量备份手段，将数据库恢复至任意时刻。 - \*\*优势\*\*：可实现任意时刻恢复。 - \*\*局限\*\*：占用空间大、转换效率低、需结合全量备份。 - 因上述局限性，binlog 往往只保留很短的时间（一般 14 天），常用于数据异常丢失的应急恢复。 - \*\*前提\*\*：需提前开启 binlog 并设置好保留时间。 \`\`\`properties \[mysqld\] log_bin = ON binlog_format = ROW expire_logs_days = 14 \`\`\` ### 6.1 案例一：全量恢复 因误操作导致数据丢失，将整个数据库恢复至误操作之前的时刻。 操作步骤： 1. 通过 binlog 检索定位误操作语句。 2. 确认误操作详情，找到结束位置。 3. 临时库恢复上一个全量备份数据。 4. 应用 binlog 数据恢复到误操作之前的时刻。 5. 提取丢失数据，恢复至生产。 \`\`\`bash # 1、检索定位误操作语句（开始位置从上次全量备份中获取） # 如果从上次备份到现在已产生多个 binlog 文件，则每个文件都需要这样检索 mysqlbinlog --no-defaults --skip-gtids --start-position 473234668 --base64-output=DECODE-ROWS -v \\ /data/mysqldata/mysql/data/mysql-bin.000009 \| grep -i -E 'DROP\|TRUNCATE' mysqlbinlog --no-defaults --skip-gtids --start-position 473234668 --base64-output=DECODE-ROWS -v \\ /data/mysqldata/mysql/data/mysql-bin.000009 \| grep -i -E 'DELETE FROM.\*\<表名\>.\*' # 2、确认误操作详情，找到结束位置（对应误操作前的位置），确定不包含误操作的语句 mysqlbinlog --skip-gtids --start-position 473234668 --stop-position 1827 --base64-output=DECODE-ROWS -v \\ /data/mysqldata/mysql/data/mysql-bin.000009 \| less mysqlbinlog --skip-gtids --start-position 473234668 --stop-position 1827 --base64-output=DECODE-ROWS -v \\ /data/mysqldata/mysql/data/mysql-bin.000009 \| tail -n 50 # 3、找一个空的 MySQL 导入上一个全量数据（以 mysql-shell 备份为例） mysqlsh :3306 --sql -e "set global local_infile=1"; mysqlsh :3306 --js -e "util.loadDump('/data/backup/dump/20260126-002001/', {threads:8,analyzeTables:'on',skipBinlog:true})" # 4、应用 binlog 数据恢复到目标位置 # 4.1 先将 binlog 转为可执行 SQL（这里涉及两个 binlog 文件） mysqlbinlog --skip-gtids --start-position 473234668 \\ /data/mysqldata/mysql/data/mysql-bin.000009 \> restore-000009.sql mysqlbinlog --skip-gtids --stop-position 202814243 \\ /data/mysqldata/mysql/data/mysql-bin.000010 \> restore-000010.sql # 4.2 恢复到数据库（一定要加 max-allowed-packet） mysql --max-allowed-packet=1G -h127.0.0.1 -P3306 -p\<密码\> \< restore-000009.sql mysql --max-allowed-packet=1G -h127.0.0.1 -P3306 -p\<密码\> \< restore-000010.sql # 5、提取丢失数据到生产库，省略 \`\`\` ### 6.2 案例二：部分恢复 案例一存在以下问题： 1. 恢复时间很慢。 2. 容错率较低：binlog 文件很大，全部转换为 SQL 文件后体积很大，转换慢、执行也慢，且执行时不支持事务，中途报错则需要从全量恢复重新开始。 当已经确定数据丢失的范围，且已采用 mysql-shell、mydumper 等手段时，可通过部分恢复大幅提升恢复效率。 操作步骤： 1. 通过 binlog 检索定位误操作语句（与案例一相同）。 2. 确认误操作详情，找到结束位置（与案例一相同）。 3. 临时库恢复上一个备份中的部分库或表。 4. 应用部分 binlog 数据恢复至误操作之前的时刻。 5. 提取丢失数据，恢复至生产（与案例一相同）。 以下只列出与案例一不同的步骤： \`\`\`bash # 3、找一个空的 MySQL 导入指定的库 mysqlsh :3306 --sql -e "set global local_infile=1"; mysqlsh :3306 --js -e "util.loadDump('/data/backup/dump/20260126-002001/', {threads:8,analyzeTables:'on',skipBinlog:true,includeSchemas:\['\<库名\>'\]})" # 4.1 先将 binlog 转为可执行 SQL（筛选指定库的记录） mysqlbinlog --database \<库名\> --skip-gtids --start-position 473234668 \\ /data/mysqldata/mysql/data/mysql-bin.000009 \> restore-000009-\<库名\>.sql mysqlbinlog --database \<库名\> --skip-gtids --stop-position 202814243 \\ /data/mysqldata/mysql/data/mysql-bin.000010 \> restore-000010-\<库名\>.sql \`\`\` 如果只需要某张表的数据，mysqlbinlog 不直接支持，可通过复杂筛选实现： \`\`\`bash mysqlbinlog --database \<库名\> --skip-gtids --stop-position 202814243 \\ /data/mysqldata/mysql/data/mysql-bin.000010 \| \\ awk '/### (INSERT\|UPDATE\|DELETE).\*\`seata\`\\.\`\<表名\>\`/{in_block=1; block="";} in_block {block=block $0 "\\n"; if($0 \~ /\^# at/){print block; in_block=0;}}' \| \\ sed -e 's/\^### //' -e 's/\^# at .\*/;/' -e '/\^$/d' \\ -e 's/@1/lock_key/g' -e 's/@2/client_id/g' -e 's/@3/expire/g' \\ \> final_exec.sql \`\`\` \> ⚠️ 注意： \> - 替换其中的表名。 \> - 替换其中的列名；如列名更多，需继续往后追加 \`-e 's/@N/列名/g'\`（完整列名可通过 \`DESC 表名\` 获取）。 ## 7. 附件：备份恢复速度对比 \*\*场景\*\*：单个数据库，多个表，数据量较少。 \*\*备份环境\*\*：docker 运行的 MySQL 8.0.44，数据大小 5.9GB，数据目录 ssd raid5，备份目录 hdd raid5，服务器 64c / 128t / 512g。 \*\*备份命令：\*\* \`\`\`bash # mysqldump mysqldump -h127.0.0.1 -uroot --ask-password \<库名\> --single-transaction \| zstd -z -c \> "\<库名\>-$(date +%Y%m%d)".sql.zst # mysql-shell mysqlsh -h 127.0.0.1 --js -e "util.dumpSchemas(\['\<库名\>'\], '/backup/mysqlsh-dump/\<库名\>-$(date +%F)-csv', {threads:4,tzUtc:false,dialect:'csv-unix'})" # mydumper mydumper -u root -h 127.0.0.1 --ask-password --all-tablespaces --chunk-filesize 128 \\ --compress zstd --compress-protocol --skip-tz-utc --threads 4 --format CSV \\ --outputdir /backup/mydumper/\<库名\>-$(date +%F)-csv --regex '\^(\<库名\>\\.)' # xtrabackup docker run \\ --name \<备份容器名\> \\ --user 999:999 \\ -v /backup/mysql/:/backup/mysql/ \\ --rm \\ --network host \\ -t \\ -e TZ=Asia/Shanghai \\ -w /backup/mysql/ \\ --volumes-from \\ --entrypoint=/usr/bin/xtrabackup \\ "\<私有仓库\>/percona/percona-xtrabackup:8.0.35" \\ --defaults-file="/etc/mysql/conf.d/my.cnf" \\ --backup \\ --parallel 4 \\ --user="root" \\ --password="\<密码\>" \\ --databases="\<库名\>" \\ --compress=zstd --compress-threads=4 \\ --target-dir="mysql-backup-manual-$(date +%F)-partial" \`\`\` \*\*恢复命令：\*\* \`\`\`bash # mysqldump zstd -d -c \<库名\>-20251227.sql.zst \| mysql -h -P3306 -p\<密码\> # mysql-shell mysqlsh -h 127.0.0.1 --js -e "util.loadDump('/backup/mysqlsh-dump/\<库名\>-$(date +%F)-csv', {threads:4,analyzeTables:'on',skipBinlog:true})" # myloader myloader --directory \<库名\>-2026-02-25-csv \\ -h 127.0.0.1 -u root --ask-password \\ --drop-table --threads 4 --database \<库名\> # xtrabackup：参考全量备份恢复 \`\`\` \*\*结果对比：\*\* \| 备份工具 \| 格式 \| 备份大小 \| 备份速度 \| 恢复速度 \| \|---\|---\|---\|---\|---\| \| mysqldump \| default（insert） \| 168 MB \| 35s \| 311s \| \| mydumper \| csv \| 171 MB \| 97s \| 47s \| \| mysqlsh \| csv \| 249 MB \| 7s \| 48s \| \| mysqlsh \| default（类似 tsv） \| 232 MB \| 6s \| 43s \| \| mydumper \| default（insert） \| 168 MB \| 86s \| 59s \| \| mydumper \| load_data \| 167 MB \| 81s \| 47s \| \| xtrabackup \| raw（ibd） \| 1402 MB \| 18s \| 32s \| \*\*结论：\*\* - 本地备份速度：mysql-shell \> xtrabackup \> mydumper \> mysqldump（数据量越大越明显）。 - 本地恢复速度：xtrabackup \> mysql-shell ≈ mydumper \> mysqldump。 - 网络恢复速度：mysql-shell ≈ mydumper \> xtrabackup \> mysqldump（数据量越大越明显；xtrabackup 不支持网络备份，需单独传文件，传输与写入都很耗时）。 - 均开启压缩时，备份大小：mydumper \< mysql-shell \< mysqldump \< xtrabackup。 \*\*备注：\*\* - 因时间限制，未做更大数据量的备份恢复测试。理论上 mysqldump 不支持多线程，数据量越大越慢。 - 操作上 xtrabackup 最复杂：备份 \> 传输 \> 解压 \> 准备 \> 停止 \> 恢复 \> 修改权限 \> 启动；若是部分备份恢复，还需手动逐个处理表空间。 ## 常见问题 / 注意事项 - \*\*docker 备份容器名与 MySQL 容器名\*\*：docker 方式依赖 \`--volumes-from \` 获取数据卷，需先确认容器名。 - \*\*恢复优先用二进制形式\*\*：容器内的目录映射在移动文件时会产生额外 IO。 - \*\*部分备份恢复必须配合 \`--export\`\*\*：准备阶段缺少该参数将无法导入表空间。 - \*\*增量恢复顺序不可颠倒\*\*：解压 =\> 准备（全量、增1、增2......）=\> 恢复；中间任一步失败都需从头重来。 - \*\*mysql-shell 时间戳\*\*：备份时务必 \`tzUtc:false\`，否则导入后时间偏差 8 小时。 - \*\*mysqldump 不支持部分恢复\*\*：只能完整恢复备份范围内的数据。 - \*\*binlog 恢复耗时\*\*：binlog 转 SQL 后体积大、耗时长，且不具事务特性，执行中断需从全量重新开始。 - \*\*密码不要写入历史\*\*：命令行中的密码建议从交互式提示或环境变量读取，脚本中统一替换为 \`\<密码\>\`。 --- \> 本文档已脱敏：所有内网地址、域名、主机名、容器名、密码、库名与项目名称均已替换为占位符，执行前请按实际环境替换。