MySQL 备份与恢复

本文合并自：06-备份与恢复.md、2-5-MySQL索引事务与备份恢复.md

适用环境：CentOS 6 / CentOS 7 + MySQL 5.5/5.6/5.7 或 MariaDB 5.5/10.x 核心内容：备份理论与策略、mysqldump 逻辑备份、mydumper/myloader 多线程备份、binlog 与基于时间点恢复（PITR）、xtrabackup/innobackupex 物理热备、备份自动化脚本与排错 相关文档：索引、事务、锁、执行计划与状态查询见 11-04-索引事务与状态查询.md；Shell 脚本语法见 06-01-Shell脚本编程.md；系统级备份工具见 03-03-备份与恢复.md

目录

章节 主题 关键内容
一 备份基础理论 故障类型、物理/逻辑备份、全量/增量/差异、冷热温备、RPO/RTO、策略设计
二 mysqldump 逻辑备份与恢复 全部选项详解、导出文件结构、恢复方式、分库分表脚本、大库替代方案
三 mydumper / myloader 多线程逻辑备份 编译安装、参数、正则备份、恢复
四 binlog 与基于时间点恢复 三种格式、参数配置、mysqlbinlog、误删数据完整恢复演练、GTID
五 物理备份 冷备、LVM 快照、XtraBackup 全量/增量、表空间传输
六 自动化与运维 备份脚本、crontab、校验与恢复演练、异地同步、排错清单、练习

一、备份基础理论

1.1 为什么要备份

数据是企业的核心资产。数据库可以重装、服务器可以更换，但数据一旦丢失且无备份，业务基本等于终止。备份的唯一目的是：在下列任意一种故障发生后，把数据恢复到可接受的状态。

故障类型 典型场景 备份能否救 说明
硬件故障 磁盘坏道、RAID 双盘同时损坏、电源击穿主板、内存故障导致数据页损坏 能 RAID 只解决单盘冗余，RAID 不是备份；坏块可能已经写进数据文件
误操作 DROP DATABASE、DROP TABLE 打错名、UPDATE/DELETE 忘写 WHERE、误删 datadir 能 运维事故第一大来源。需要「全备 + binlog」才能恢复到误操作前一刻
SQL 注入与攻击 注入后 DROP 表、拖库后删库勒索、内部人员恶意删除 能 备份文件本身也是攻击目标，必须异地 + 权限隔离
软件缺陷 MySQL 崩溃导致表损坏、升级失败、文件系统 bug 能 InnoDB 崩溃恢复可解决大部分，但页损坏需从备份取
机房灾难 火灾、水淹、断电、地震、机房被整体断网/查封 仅异地备份能救 同机房备份与生产同生共死，必须异地/异城保存一份

运维铁律三条： ① 没有验证过恢复的备份，等于没有备份； ② 备份必须至少两份、且至少一份在异地（3-2-1 原则：3 份数据、2 种介质、1 份异地）； ③ 误操作是最高频事故，因此全量备份 + 二进制日志是最低配置，缺任何一半都无法做到时间点恢复。

1.2 备份方法总览

方法 说明
bin-log 通过二进制日志备份，主要通过 MySQL 主从复制实现。建议将 bin-log 实时备份至远程服务器，以防机器故障导致日志丢失
mysqldump MySQL 自带逻辑备份工具，单线程导出 SQL 文本
mydumper 多线程逻辑备份工具，按表并行导出，速度远快于 mysqldump
Xtrabackup Percona 物理热备工具，支持 InnoDB 热备（不锁表）
冷备（cp/tar） 停库后直接拷贝数据目录，最简单也最可靠，但需停机
LVM 快照 文件系统级快照，秒级完成，锁表时间极短
主从复制 严格说是高可用而非备份（误删会同步到从库），但可用于从库上做备份以免影响主库

1.3 物理备份与逻辑备份

对比项 物理备份 逻辑备份
原理 直接拷贝数据文件（.ibd、.frm、ibdata1、redo log 等二进制文件） 把数据导出为 CREATE TABLE + INSERT 等 SQL 文本
代表工具 cp/tar 冷备、LVM 快照、XtraBackup/innobackupex mysqldump、mysqlpump、mydumper、SELECT ... INTO OUTFILE
备份速度 快（顺序读文件，与数据量线性相关） 慢（需逐行取出、格式化成 SQL）
恢复速度 快（拷回去即可） 慢（要逐条解析执行 SQL、重建索引）
备份体积 大（含索引、碎片、预分配空间） 小（只有数据，压缩比高）
可读性 二进制，不可读、不可编辑 纯文本，可用 vim/grep/sed 编辑，可只挑一张表恢复
跨版本 / 跨平台 差（版本、字节序、页格式必须兼容） 好，5.5 导出可导入 8.0、可导入其他数据库
跨存储引擎 不行 可以（导出后改 ENGINE= 再导入）
是否需停机 冷备需要；XtraBackup 对 InnoDB 不需要 不需要，但可能长时间持锁
一致性保证 靠停库、快照或 redo log 靠 --single-transaction（InnoDB）或锁表（MyISAM）
适用规模 大库（几十 GB 以上）首选 中小库（几 GB 以内）、迁移、升级、抽取单表

选择原则：日常大库用物理备份（XtraBackup）保证 RTO，同时用逻辑备份保留一份可读快照方便单表恢复与跨版本迁移。两者不是二选一。

1.4 全量备份、增量备份与差异备份

类型 备份内容 备份体积/耗时 恢复链 恢复复杂度
全量备份（Full） 备份全部数据 最大、最慢 只需最后一次全备 最简单
增量备份（Incremental） 只备份自上一次备份（全备或增备）以来的变化 最小、最快 全备 + 所有增备，顺序应用 最复杂，链条断一环即失败
差异备份（Differential） 只备份自最近一次全备以来的全部变化 介于两者之间，越往后越大 全备 + 最后一次差备 中等

恢复链示意（周日全备，周一至周六每日一次）：

增量备份（Incremental）
  Sun全备 → Mon增 → Tue增 → Wed增 → Thu增 → Fri增 → Sat增
  周六故障需恢复：Sun全备 + Mon + Tue + Wed + Thu + Fri + Sat（7 步，任一份损坏则后面全废）

差异备份（Differential）
  Sun全备 → Mon差(Sun→Mon) → Tue差(Sun→Tue) → ... → Sat差(Sun→Sat)
  周六故障需恢复：Sun全备 + Sat差（2 步，最省心）

MySQL 实践中最常见的组合
  每周日 全备（mysqldump -F 或 xtrabackup 全量）
  + 每日  binlog 归档（等价于增量备份，可精确到秒）

MySQL 中三种备份的实现载体：

类型 逻辑备份实现 物理备份实现
全量 mysqldump -A / mydumper xtrabackup --backup / 冷备
增量 binlog（配合 -F 每次全备后切换日志） xtrabackup --incremental --incremental-basedir=上次备份目录（基于 LSN）
差异 binlog（从全备时刻起的所有日志） --incremental-basedir 始终指向全备目录

1.5 热备、温备与冷备

类型 数据库状态 业务影响 实现方式 适用场景
冷备（Cold Backup） 完全停止服务 业务中断 systemctl stop mysqld 后 cp -a/tar 数据目录 允许停机的窗口期、迁移、版本升级前留底、最可靠的一次性备份
温备（Warm Backup） 运行中，但加全局读锁，只能读不能写 只读降级，写请求全部阻塞 FLUSH TABLES WITH READ LOCK + 拷贝/快照；mysqldump --lock-all-tables MyISAM 引擎（不支持事务只能锁）、LVM 快照（锁 1~2 秒即可）
热备（Hot Backup） 运行中，读写均正常 几乎无影响（仅额外 IO） xtrabackup（InnoDB redo 跟踪）、mysqldump --single-transaction（InnoDB MVCC 快照） 7×24 生产库的日常备份，首选

引擎决定了能做到哪一级：

引擎 冷备 温备 热备
InnoDB 支持 支持 支持（--single-transaction 或 xtrabackup）
MyISAM 支持 支持 不支持（无事务、无 redo，必须锁表）

因此「表要用 InnoDB」不只是为了事务和行锁，也是为了能做在线热备。混合引擎的库（例如 mysql 系统库在 5.7 及之前是 MyISAM）无法整库热备，这也是 mysqldump --single-transaction 备份 mysql 库时一致性不完全可靠的原因。

1.6 RPO 与 RTO

设计任何备份方案前，必须先和业务方确认这两个指标，否则方案无从评价。

指标 全称 含义 通俗理解 由什么决定
RPO Recovery Point Objective 恢复点目标 允许丢失多少数据（时间跨度） 「最多能丢几分钟的数据」 备份/日志的频率。只有每日全备则 RPO=24h；有 binlog 实时同步到远端则 RPO≈0
RTO Recovery Time Objective 恢复时间目标 允许多久恢复完成（业务中断时长） 「最多能停机多久」 备份方式与数据量。逻辑备份恢复慢，物理备份快；有热备从库可秒级切换

不同方案的量级参考：

方案 RPO RTO
每日一次 mysqldump 全备，无 binlog 最坏 24 小时 小时级（100GB 逻辑恢复可能 5~10 小时）
每日全备 + binlog 本地保留 秒级（若磁盘未损坏） 小时级
每日全备 + binlog 实时传到异地 秒级（磁盘损坏也不丢） 小时级
每日 xtrabackup 全备 + binlog 秒级 分钟~小时级（拷回文件即可）
主从复制 + 延迟从库 + 每日备份 ≈0 秒级（切换 VIP）

关键结论：RPO 靠 binlog，RTO 靠物理备份和从库。花钱前先问业务要 RPO/RTO，再选方案，不要一上来就讨论工具。

1.7 备份策略设计

维度 建议 理由
备份频率 全备每日或每周一次（数据量决定）；binlog 连续归档，每 5~15 分钟同步一次到远端 全备频率决定恢复步数，binlog 频率决定 RPO
备份时间 业务低峰（一般 02:00~05:00），避开月结、大促、批量任务 备份消耗 IO 和连接，可能拖慢业务
保留周期 常见：日备保留 7 天、周备保留 4 周、月备保留 12 个月；binlog 保留至少覆盖到最老的可用全备之后 误操作往往数天后才被发现；只保留一天等于没有回退空间
异地存储 至少一份传到另一机房/对象存储（OSS/S3）；本地磁盘 + 远程各一份 机房级灾难
权限隔离 备份目录 chmod 700，属主为专用备份用户；对象存储用只写不删的凭据 防勒索病毒/攻击者连备份一起删
加密与压缩 敏感数据备份文件加密（gpg/openssl enc）；压缩节省 60%~80% 空间 备份文件明文含全部业务数据，泄露即拖库
备份验证 每次备份后校验文件大小、gzip -t、tail 检查结尾标记、记录 MD5 磁盘满导致的截断备份是最常见的「假备份」
恢复演练 每季度至少一次在独立机器上完整恢复并校验行数，形成书面记录 没演练过的方案在真出事时 100% 出问题
减少对业务影响 优先在从库上做备份；主库只保留 binlog 归档；备份时用 ionice/nice/--rate-limit 限速 备份是顺序大 IO，会挤占 Buffer Pool 和磁盘带宽
监控告警 备份成功/失败、耗时、体积同比、最新备份年龄都要进监控，失败必须告警到人 静默失败的备份最危险

从库备份的注意点：

# 在从库上备份，需同时记录从库的复制位点，恢复后才能重新接主
mysqldump -uroot -p -A --single-transaction --dump-slave=2 > /myback/all.sql
# --dump-slave=2 以注释形式写入 CHANGE MASTER TO（对应主库位点）
# --apply-slave-statements 备份的是 slave 时，导入前自动停止 slave，完成后自动启动

1.8 备份前的检查清单

执行任何备份（尤其是首次上线或大库）之前逐项确认：

□ 1. 磁盘空间：备份目标目录剩余空间 > 预估备份体积 × 1.5
      df -h /myback ; du -sh /var/lib/mysql
□ 2. 备份账号权限：
      逻辑备份需 SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, RELOAD, REPLICATION CLIENT, EVENT
      物理备份需 RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS, SUPER
□ 3. 引擎确认：是否存在 MyISAM 表（决定能否热备）
      SELECT table_schema, table_name, engine FROM information_schema.tables
      WHERE engine='MyISAM' AND table_schema NOT IN ('mysql','information_schema','performance_schema');
□ 4. binlog 是否已开启（log_bin=ON），server_id 是否已设置
□ 5. 是否存在长事务 / 长查询（会导致 FTWRL 卡死或 --single-transaction 快照过旧）
      SELECT * FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND,trx_started,NOW())>60;
      SHOW FULL PROCESSLIST;
□ 6. 字符集确认：库/表字符集与备份时 --default-character-set 一致
      SHOW VARIABLES LIKE 'character_set%';
□ 7. 是否有存储过程、函数、触发器、事件（默认 mysqldump 不导 routines/events）
□ 8. 备份窗口是否与业务批量任务、其他备份任务冲突（避免 IO 打满）
□ 9. 时钟同步（ntpd/chronyd 正常），否则按时间点恢复会找错位置
□ 10. 恢复目标环境已就绪：版本 ≥ 源版本，字符集/排序规则一致，磁盘足够

二、mysqldump 逻辑备份与恢复

mysqldump 是 MySQL/MariaDB 自带的逻辑备份客户端，本质是「连上数据库，把数据查出来，拼成 SQL 文本输出到标准输出」。因此它可以重定向到文件、管道给 gzip、甚至直接管道给另一台数据库。

2.1 常用参数详解

2.1.1 连接与通用参数

参数 缩写 说明
--user -u 连接数据库用户名
--password -p 连接数据库密码。-p 后紧跟密码不能有空格（-p123456），推荐留空交互输入或写进 ~/.my.cnf
--host -h 连接的主机，默认 localhost
--port -P 连接数据库端口号，默认 3306
--socket -S 指定 mysql socket 文件位置，默认 /tmp/mysql.sock（CentOS 7 的 MariaDB 常为 /var/lib/mysql/mysql.sock）
--protocol 使用的连接协议，如 tcp、socket、pipe、memory（原文档亦列出 udp）
--defaults-file 指定配置文件，避免命令行明文密码
--version -V 输出 mysqldump 版本信息并退出
--help 显示帮助信息并退出
--force 导出过程中忽略 SQL 错误，继续导出
--log-error 将导出错误信息写入指定文件
--verbose -v 输出详细过程

密码安全写法：命令行带密码会出现在 ps -ef 和 ~/.bash_history 中。生产写法：

# ~/.my.cnf  权限必须 chmod 600
[mysqldump]
user=backup
password=YourPass
host=127.0.0.1

之后直接 mysqldump -A > all.sql 即可，无需再写 -u -p。

2.1.2 备份范围参数

参数 缩写 说明
--all-databases -A 导出全部数据库（全备），含 mysql 系统库
--databases -B 导出指定数据库，可指定多个；输出中会带 CREATE DATABASE 和 USE 语句
（直接写库名 表名） mysqldump db t1 t2：只导出 db 库的 t1、t2 表，输出中不含建库和 USE 语句
--tables 导出指定表，须配合 -B 使用（-B db --tables t1）
--ignore-table 不导出指定表，格式 库名.表名；忽略多个表需多次使用该参数
--where -w 添加条件语句，只导出符合条件的数据
--no-data -d 仅导出表结构，不导出数据
--no-create-info -t 仅导出数据，不导出表结构（建表语句）
--no-create-db -n 导出数据但不导出建库语句（CREATE DATABASE）

-B 与直接写库名的区别，是恢复时最容易踩的坑：

# 写法一：带 -B，dump 文件里有 CREATE DATABASE IF NOT EXISTS `m2`; USE `m2`;
mysqldump -uroot -p -B m2 > /myback/m2.sql
mysql -uroot -p < /myback/m2.sql          # 直接恢复，自动建库并进入 m2

# 写法二：不带 -B，dump 文件里只有建表和 INSERT
mysqldump -uroot -p m2 > /myback/m2_tables.sql
mysql -uroot -p m2 < /myback/m2_tables.sql   # 必须手动指定目标库，且库要已存在
# 好处：可以恢复到另一个库名，做数据克隆或改名
mysql -uroot -p m2_new < /myback/m2_tables.sql

2.1.3 一致性与锁参数（最关键）

参数 缩写 说明
--single-transaction InnoDB 热备的核心参数。开始时执行 START TRANSACTION WITH CONSISTENT SNAPSHOT，在一个可重复读事务的一致性快照中导出，全程不锁表，业务读写不受影响
--lock-all-tables -x 导出前对所有库所有表加全局读锁（内部即 FLUSH TABLES WITH READ LOCK）直到导出结束，保证一致性，但全库只读（温备）
--lock-tables -l 默认开启（--opt 含）。逐库锁表读，只保证单库内一致，库之间不一致；MyISAM 用它
--skip-lock-tables 关闭锁表，无一致性保证，仅用于允许脏读的场景
--add-locks 导出的 SQL 中在 INSERT 前后加 LOCK TABLES/UNLOCK TABLES，加快导入速度（默认开启，可用 --skip-add-locks 关闭）
--flush-logs -F 导出前刷新（切换）bin-log。一次导出多个库会多次刷新 bin-log；同时使用 --lock-all-tables 或 --master-data 时不会多次刷新
--master-data[=1\|2] 在 dump 文件中记录当前 binlog 文件名与位置。=1 写成可执行的 CHANGE MASTER TO ...；=2 写成注释形式。做时间点恢复和搭从库必加
--dump-slave[=1\|2] 备份从库时记录主库的位点（而非从库自身位点）
--apply-slave-statements 备份的是 slave 时，导入前自动 STOP SLAVE，完成后自动 START SLAVE
--safe-slave-backup 备份前停止 SQL 线程，保证 slave 状态一致

一致性原理与引擎差异（面试与生产都必须讲清）：

参数 InnoDB MyISAM 是否锁业务 一致性
--single-transaction 有效，MVCC 快照读，导出期间看到的是同一时刻的数据 无效（MyISAM 不支持事务，导出的是各表不同时刻的数据） 不锁 InnoDB 完全一致
--lock-all-tables 有效 有效 全库只读，写请求全部阻塞 全库一致
--lock-tables（默认） 有效但降级 有效 逐库只读 仅单库内一致 
# InnoDB 库标准热备命令（生产模板）
mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs \
          --routines --triggers --events --hex-blob \
          --default-character-set=utf8 -A > /myback/all_$(date +%F).sql

# 含 MyISAM 表的库只能温备
mysqldump -uroot -p --lock-all-tables --master-data=2 -A > /myback/all.sql

重要陷阱：--single-transaction 与 --lock-all-tables 互斥，同时写只有后者生效。 --single-transaction 期间若执行了 DDL（ALTER/TRUNCATE/DROP），会导致备份出错或不一致，因为 DDL 会隐式提交并使快照失效。备份窗口内禁止 DDL。 --single-transaction 会开启一个长事务，若备份耗时数小时，undo 无法清理、ibdata1 会膨胀，历史版本链变长使查询变慢——这是大库不适合用 mysqldump 的重要原因。

2.1.4 对象与内容控制参数

参数 缩写 说明
--routines -R 导出存储过程和函数（默认不导出，极易遗漏）
--triggers 导出触发器（默认开启，可用 --skip-triggers 关闭）
--events -E 导出事件调度器中的事件（默认不导出）
--hex-blob 把 BINARY/VARBINARY/BLOB/BIT 列导成十六进制（如 0x1F8B），防止二进制数据在文本传输中损坏，有二进制列时必加
--default-character-set 设置导出字符集，默认 utf8。必须与库字符集一致，否则中文乱码
--set-charset 在 dump 文件中写入 SET NAMES（默认开启）
--add-drop-database 导入时先 DROP DATABASE IF EXISTS（危险，会删掉目标库）
--add-drop-table 导入时先 DROP TABLE IF EXISTS（默认开启，可用 --skip-add-drop-table 关闭）
--add-drop-trigger 导入时先删除同名触发器
--complete-insert -c INSERT 语句中列出所有列名，可读性好、列顺序变化也能导入，但文件更大
--extended-insert -e 多行合并为一条 INSERT（默认开启），导入速度提升数倍；关闭后每行一条 INSERT，便于 grep 单行修复
--compact 精简输出：去掉注释、SET 语句、结构说明，只留核心 SQL，便于阅读和 diff（不适合直接用于生产恢复）
--compress -C 客户端与服务端之间传输压缩（远程备份省带宽，不是压缩输出文件）
--quick -q 逐行取数据而非一次性读入内存（默认开启，大表必需）
--set-gtid-purged=OFF/ON/AUTO 控制是否在 dump 中写入 SET @@GLOBAL.GTID_PURGED='...'。恢复到已有 GTID 的实例时必须 =OFF，否则报错；搭建从库时用 =ON
--dump-date 导出文件中添加导出时间，默认开启（可用 --skip-dump-date 关闭，便于 diff 两次备份）
--xml -X 将数据导出为 XML 格式
--tab=目录 -T 每表导出为 .sql（结构）+ .txt（TAB 分隔数据），可配合 LOAD DATA INFILE 高速导入
--opt 默认开启的组合选项，等价于 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset。用 --skip-opt 可一次性全部关闭
--disable-keys -K 在导入数据前后加 ALTER TABLE ... DISABLE/ENABLE KEYS，导入完成后一次性重建索引，比逐行维护索引快得多（仅对 MyISAM 有效）
--create-options 建表语句中保留 ENGINE、AUTO_INCREMENT、CHARSET 等选项（默认开启）
--max-allowed-packet 客户端单个包上限，大 BLOB 行导出/导入报错时调大，如 --max-allowed-packet=512M
--net-buffer-length 每条 extended-insert 的目标长度，默认 1M，调大可减少语句条数

2.2 命令示例汇总

全备所有数据库：

mysqldump -uroot -p -A > /mysqlback/mysql_all.sql

全备到指定目录：

mysqldump -uroot -p -A > /myback/mysql_all_back.sql

导出指定数据库（可多个）：

mysqldump -uroot -p -B m2 test mysql a b

使用 tcp 协议导出：

mysqldump -uroot -p -B m2 --protocol=tcp

仅导出数据不导出表结构：

mysqldump -uroot -p -B m2 -t

仅导出表结构不导出数据：

mysqldump -uroot -p -A -d

导出指定表：

mysqldump -uroot -p -B m2 --tables test

按条件导出：

mysqldump -uroot -p -B m2 --where="user='root'"
# 更多示例：只导最近 7 天数据、只导前 1000 行
mysqldump -uroot -p m2 orders --where="create_time >= '2026-08-01'" > /myback/orders_aug.sql
mysqldump -uroot -p m2 orders --where="1=1 LIMIT 1000" > /myback/orders_sample.sql

导出前刷新 bin-log：

mysqldump -uroot -p -B m2 -F

锁定所有表后导出：

mysqldump -uroot -p -B m2 --lock-all-tables

错误日志写入文件：

mysqldump -uroot -p -B m2 --log-error=/root/mysqldump_err.log

导出为 XML：

mysqldump -uroot -p -B m2 --xml

忽略多张表：

mysqldump -uroot -p -B m2 --ignore-table=m2.a --ignore-table=m2.b

导出时直接压缩（省 70% 空间，生产常用）：

mysqldump -uroot -p --single-transaction -A | gzip > /myback/all_$(date +%F).sql.gz
# 恢复
gunzip < /myback/all_2026-08-22.sql.gz | mysql -uroot -p
zcat /myback/all_2026-08-22.sql.gz | mysql -uroot -p

不落地直接迁移到另一台（跨机克隆）：

mysqldump -uroot -p --single-transaction -B m2 | mysql -uroot -p -h 192.168.2.20

只导出存储过程与函数（不导数据）：

mysqldump -uroot -p -B m2 --routines --no-create-info --no-data --no-create-db --skip-opt > /myback/routines.sql

2.3 导出文件的内容结构解读

打开一个 dump 文件，从头到尾依次是：

-- MySQL dump 10.14  Distrib 5.5.60-MariaDB, for Linux (x86_64)
-- Host: localhost    Database: m2
-- ------------------------------------------------------
-- Server version       5.5.60-MariaDB

-- ① 保存客户端环境，恢复完再还原回去
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
-- ② 导入期间关闭唯一性检查与外键检查以提速（mysqldump 自动加）
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- ③ --master-data=2 生成的位点信息（注释形式，这是做 PITR 的起点）
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=245;

-- ④ 建库（仅 -B 或 -A 时出现）
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `m2` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `m2`;

-- ⑤ 逐表：DROP → CREATE → 锁表 → 关索引 → INSERT → 开索引 → 解锁
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(2) DEFAULT NULL,
  `name` char(10) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES (1,'zhangsan','f'),(2,'lisi','m');   -- extended-insert 合并成一条
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;

-- ⑥ 还原客户端环境
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

-- Dump completed on 2026-08-22 10:15:32       ← ⑦ 结尾标记

要点：

  • /*!40101 ... */ 是版本化注释：只有版本号 ≥ 4.1.1 的服务器才执行，其他数据库当普通注释跳过，因此保证了跨版本兼容。
  • -- Dump completed on ... 是备份完整性的标志。校验备份最快的方法：
    tail -1 /myback/all.sql | grep -q "Dump completed" && echo OK || echo "备份不完整！"
# 压缩包
zcat /myback/all.sql.gz | tail -1
  • --master-data 生成的 CHANGE MASTER TO 行记录了全备结束时刻的 binlog 位置，是增量恢复的起点，必须保留。

2.4 恢复方式

2.4.1 四种基本恢复方法

# 方法一：shell 重定向（最常用，适合 -A / -B 的备份）
mysql -uroot -p < /myback/all.sql
mysql < /myback/all.sql                       # 若 ~/.my.cnf 已配好凭据

# 方法二：指定目标库恢复（备份时未加 -B）
mysql -uroot -p m2 < /myback/m2_tables.sql
mysql -uroot -p -D m2 < /myback/m2_tables.sql

# 方法三：登录后用 source（可看到执行过程，适合交互排错）
mysql -uroot -p
mysql> SET NAMES utf8;
mysql> CREATE DATABASE IF NOT EXISTS m2 DEFAULT CHARSET utf8;
mysql> USE m2;
mysql> SOURCE /myback/m2_tables.sql;
mysql> \. /myback/m2_tables.sql               -- source 的简写

# 方法四：压缩包直接恢复（不解压落地，省磁盘）
zcat /myback/all.sql.gz | mysql -uroot -p
gunzip -c /myback/all.sql.gz | mysql -uroot -p

mysql < 与 source 的差异：

对比 mysql < file SOURCE file
执行位置 shell 中，客户端逐条读取发送 已登录的 mysql 会话内
出错行为 默认遇错即停（可加 -f 强制继续） 默认报错后继续执行后续语句
进度可见 不显示，安静执行 逐条打印 Query OK，海量语句会刷屏拖慢
大文件性能 更快，推荐 慢（每条都回显）
路径 shell 路径，支持管道 服务端客户端所在机器的路径，不支持管道

2.4.2 恢复单库、单表的技巧

# ① 从全备中提取单个库（全备是 -A 生成的，库之间由 USE 语句分隔）
sed -n '/^-- Current Database: `m2`/,/^-- Current Database: `next_db`/p' all.sql > m2.sql

# ② 更通用：用 awk 按 USE 语句切分
awk '/^USE `m2`/,/^USE `(?!m2)/' all.sql > m2.sql       # gawk 正则受限，实际常用下面这条
sed -n "/^-- Current Database: \`m2\`/,/^-- Current Database:/p" all.sql | head -n -1 > m2.sql

# ③ 从全备中提取单张表的结构
sed -n '/^CREATE TABLE `a`/,/^) ENGINE=/p' all.sql

# ④ 从全备中提取单张表的数据（结构 + INSERT）
sed -n '/^-- Table structure for table `a`/,/^-- Table structure for table/p' all.sql > a.sql

# ⑤ 只抓某表的 INSERT 行
grep -n "^INSERT INTO \`a\`" all.sql > a_data.sql

# ⑥ 恢复到临时库后再挑数据（最安全，强烈推荐的生产做法）
mysql -uroot -p -e "CREATE DATABASE recover_tmp DEFAULT CHARSET utf8;"
mysql -uroot -p recover_tmp < m2.sql
mysql -uroot -p -e "INSERT INTO m2.a SELECT * FROM recover_tmp.a WHERE id=3;"
mysql -uroot -p -e "DROP DATABASE recover_tmp;"

生产红线：绝不在生产库上直接 mysql < all.sql 做单表恢复。全备里含 DROP TABLE IF EXISTS 甚至 DROP DATABASE，会把误删之后产生的正常数据一起覆盖掉。正确姿势永远是「恢复到临时库 → 校验 → 只搬需要的数据」。

2.4.3 加速恢复：关闭 binlog、外键与唯一性检查

逻辑恢复慢的三大原因：逐条执行 SQL、每行维护索引、每条语句写 binlog。对应的加速手段：

-- 在恢复会话中执行（登录 mysql 后先设置，再 source）
SET SESSION SQL_LOG_BIN = 0;        -- 恢复不写 binlog，减少一半 IO（注意：有从库时会导致主从不一致！）
SET FOREIGN_KEY_CHECKS = 0;         -- 关闭外键检查，同时解决「父表还没恢复」的报错
SET UNIQUE_CHECKS = 0;              -- 关闭唯一性检查（前提是确信数据本身无重复）
SET AUTOCOMMIT = 0;                 -- 关闭自动提交，批量后统一 COMMIT
SET GLOBAL innodb_flush_log_at_trx_commit = 2;   -- 临时降低刷盘频率（恢复完必须改回 1）

SOURCE /myback/all.sql;

COMMIT;
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
SET SESSION SQL_LOG_BIN = 1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;   -- 务必还原，否则宕机丢数据

命令行一次性写法：

( echo "SET SQL_LOG_BIN=0; SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; SET AUTOCOMMIT=0;" ; \
  cat /myback/all.sql ; \
  echo "COMMIT; SET UNIQUE_CHECKS=1; SET FOREIGN_KEY_CHECKS=1;" ) | mysql -uroot -p

配置文件层面的临时提速（恢复大库前改，恢复后改回）：

[mysqld]
innodb_buffer_pool_size = 物理内存的 60%~70%
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M          # 大 redo 减少 checkpoint 次数
sync_binlog = 0
skip-log-bin                         # 恢复期间彻底关闭 binlog（无从库时）
max_allowed_packet = 512M
net_read_timeout = 3600

外键检查关闭还有一个功能性作用：dump 文件里表的顺序是字母序，子表可能排在父表之前，若不关外键检查会直接报 errno 150 / Cannot add or update a child row。

2.5 分库分表导出脚本

按库、按表拆成独立文件，恢复时可精确挑选，也便于并行：

#!/bin/bash
# 按库分别导出，每库一个文件
USER=root
PASS=YourPass
BACKDIR=/myback/$(date +%F)
mkdir -p $BACKDIR

# 取出所有业务库（排除系统库）
DBS=$(mysql -u$USER -p$PASS -N -e "SHOW DATABASES;" \
      | grep -Ev "^(information_schema|performance_schema|mysql|sys|test)$")

for db in $DBS; do
    echo "正在备份库：$db"
    mysqldump -u$USER -p$PASS --single-transaction --master-data=2 \
              --routines --triggers --events --hex-blob \
              --default-character-set=utf8 -B "$db" | gzip > "$BACKDIR/${db}.sql.gz"
done
#!/bin/bash
# 按表分别导出（大库定位单表恢复极方便）
USER=root; PASS=YourPass; DB=m2
BACKDIR=/myback/$(date +%F)/$DB
mkdir -p $BACKDIR

TABLES=$(mysql -u$USER -p$PASS -N -e "SHOW TABLES FROM $DB;")
for tb in $TABLES; do
    mysqldump -u$USER -p$PASS --single-transaction "$DB" "$tb" | gzip > "$BACKDIR/${tb}.sql.gz"
done

# 结构与数据分离导出，便于比对结构变更
mysqldump -u$USER -p$PASS -d -B $DB > $BACKDIR/${DB}_schema.sql   # 只有结构
mysqldump -u$USER -p$PASS -t -B $DB > $BACKDIR/${DB}_data.sql     # 只有数据

并行导出多个库（提升速度，注意别把 IO 打满）：

echo "$DBS" | xargs -n1 -P4 -I{} sh -c \
  'mysqldump -uroot -pYourPass --single-transaction -B {} | gzip > /myback/{}.sql.gz'

2.6 mysqldump 的性能问题与大库替代方案

mysqldump 的固有短板：

问题 说明
单线程 只有一个连接串行导出，无法利用多核与多磁盘
恢复更慢 恢复要逐条 INSERT 并重建全部索引，经验值：导出 1 小时的库，恢复可能要 4~10 小时
长事务副作用 --single-transaction 期间 undo 不能清理，ibdata1 膨胀、历史链变长
不适合超大库 一般界线：< 50GB 用 mysqldump，> 50GB 用物理备份（XtraBackup）
DDL 冲突 备份窗口内的 DDL 会破坏快照

替代方案对比：

工具 类型 特点 适用
mydumper / myloader 逻辑，多线程 按表/按块并行导出与导入，自带压缩、正则筛选、记录 binlog 位点；速度可达 mysqldump 的数倍。见本文第三章 中大型库的逻辑备份/迁移
mysqlpump 逻辑，多线程 MySQL 5.7 官方自带（mysqlpump），支持并行、进度显示、--exclude-*/--include-* 过滤、导出后压缩（ZLIB/LZ4）。缺点：不支持一致性快照跨表（早期版本），MySQL 8.0 起官方转向 mysqlshell 5.7 环境的快速逻辑导出
MySQL Shell util.dumpInstance() 逻辑，多线程 MySQL 8.0 官方推荐，支持并行 dump/load、断点续传、直传 OSS/S3 8.0 生产环境
XtraBackup 物理，热备 大库首选，支持增量、压缩、并行、流式传输。见本文第五章 100GB ~ TB 级
LVM 快照 物理 秒级完成，锁表 1~2 秒 数据目录在 LVM 上的场景 
# mysqlpump 示例（MySQL 5.7）
mysqlpump -uroot -p --default-parallelism=4 --all-databases > /myback/all_pump.sql
mysqlpump -uroot -p --exclude-databases=mysql,sys --compress-output=LZ4 \
          --result-file=/myback/all.lz4
# 只导指定库的指定表
mysqlpump -uroot -p --include-tables=a,b m2 > /myback/m2_ab.sql

2.7 实操演练：mysqldump + binlog 增量备份与恢复

以上 mysqldump 备份均为全备方式。实现增量备份最简单的方法是通过 mysqldump 配合 bin-log。

原理： 使用 --flush-logs（-F）参数，每次全备后刷新一次 bin-log，全备后的新操作都记录到新的 bin-log 中，从而实现「全备文件 + 一个完整的新 binlog 文件 = 全量 + 增量」。

第 1 步：准备备份目录与测试数据

mkdir /myback
create database m2;
create table m2.a (id int(2),name char(10),sex char(1));
insert into m2.a values (1,'zhangsan','f'),(2,'lisi','m');
select * from m2.a;
+------+----------+------+
| id   | name     | sex  |
+------+----------+------+
|    1 | zhangsan | f    |
|    2 | lisi     | m    |
+------+----------+------+

第 2 步：第一次全备（同时切换 binlog）

mysqldump -uroot -p -B m2 -F > /myback/all.sql
# 确认切换后的新日志文件（后续所有变更都记在这里）
mysql -uroot -p -e "SHOW MASTER STATUS;"

第 3 步：全备后产生新数据（这部分只存在于 binlog 中）

insert into m2.a values (3,'wangwu','f');

第 4 步：将最新的 bin-log 备份出去

cp /var/lib/mysql/mysql-bin.000002 /myback

生产中此处应先 FLUSH LOGS 让当前日志封口，再拷贝已封口的日志，避免拷到正在写入的文件。

第 5 步：模拟误删库

drop database m2;

第 6 步：恢复全备

mysql < /myback/all.sql
select * from m2.a;
+------+----------+------+
| id   | name     | sex  |
+------+----------+------+
|    1 | zhangsan | f    |
|    2 | lisi     | m    |
+------+----------+------+

此时只回到了全备时刻，wangwu 还没有。

第 7 步：恢复增量备份（应用 binlog）

mysqlbinlog /myback/mysql-bin.000002 | mysql -p
select * from m2.a;
+------+----------+------+
| id   | name     | sex  |
+------+----------+------+
|    1 | zhangsan | f    |
|    2 | lisi     | m    |
|    3 | wangwu   | f    |
+------+----------+------+

数据完整恢复，演练结束。

注意：本例中 drop database m2; 这条语句也被记录在 mysql-bin.000002 里。之所以直接回放没出问题，是因为 drop 发生在拷贝 binlog 之后，拷走的副本里不含它。若日志里含误操作语句，必须用 --start-position/--stop-position 跳过——完整方法见第四章 4.7。

第 8 步：验证清单

-- 库表是否齐全
SHOW DATABASES;
SHOW TABLES FROM m2;
-- 行数是否与故障前一致
SELECT COUNT(*) FROM m2.a;
-- 表结构、索引是否完整
SHOW CREATE TABLE m2.a\G
-- 存储过程/函数/触发器/事件是否恢复（备份时若漏加 -R -E 此处会为空）
SHOW PROCEDURE STATUS WHERE Db='m2';
SHOW TRIGGERS FROM m2;
SHOW EVENTS FROM m2;
-- 表是否有损坏
CHECK TABLE m2.a;

三、mydumper / myloader 多线程逻辑备份

mydumper 是第三方多线程逻辑备份工具，弥补了 mysqldump 单线程的缺陷：按表（大表还可按行块）并行导出，自带压缩与正则筛选，并在 metadata 文件中记录 binlog 位点。

3.1 安装 mydumper

安装依赖：

yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel gcc gcc-c++ -y
yum install -y cmake

下载源码包：

wget https://launchpadlibrarian.net/185032423/mydumper-0.6.2.tar.gz
# 或内网源
wget http://192.168.2.254/mysql/pack/mydumper-0.6.2.tar.gz

编译安装：

tar xf ./mydumper-0.6.2.tar.gz -C /usr/src/
cd /usr/src/mydumper-0.6.2
mkdir ./bin && cd bin
cmake ../
make
ll mydumper myloader
-rwxr-xr-x 1 root root 184624 mydumper
-rwxr-xr-x 1 root root  53824 myloader

看到这两个文件即表示编译成功。可再做软链接方便调用：

ln -s /usr/src/mydumper-0.6.2/bin/mydumper /usr/bin/mydumper
ln -s /usr/src/mydumper-0.6.2/bin/myloader /usr/bin/myloader

3.2 mydumper 常用参数

参数 缩写 说明
--host -h 主机
--user -u 用户（-u 与用户名之间必须有空格）
--password -p 密码
--port -P 端口
--socket -S socket 文件
--outputdir -o 备份到指定目录
--database -B 备份指定数据库
--tables-list -T 备份指定表，多个以逗号分隔
--compress -c 启用压缩备份（生成 .gz）
--regex -x 使用正则匹配备份表或库
--build-empty-files -e 表无数据时仍备份空文件
--no-schemas -m 只备份表数据，不备份表结构
--logfile -L 备份错误信息追加到指定文件
--kill-long-queries -K 备份时杀掉长查询
--lock-all-tables 备份前锁定所有表，完成后自动解锁
--threads -t 并发线程数，默认 4，一般设为 CPU 核数
--rows -r 按行数切分大表，实现单表内并行
--long-query-guard 长查询判定阈值（秒），默认 60
--no-locks 不加锁（无一致性保证，但完全不影响业务）

3.3 mydumper 使用示例

备份指定数据库到目录：

mydumper -u root -p -B m2 -o /myback
# 或
mydumper -u root -p --database=m2 --outputdir=/myback

注意：-u 选项和用户名之间必须有一个以上空格，否则会报错。

备份目录内容：

m2.a-schema.sql   存放表结构
m2.a.sql          存放表数据
metadata          存放 master 日志名及初始位置

metadata 文件内容示例（做增量恢复的起点）：

Started dump at: 2026-08-22 02:00:01
SHOW MASTER STATUS:
        Log: mysql-bin.000006
        Pos: 1245
        GTID:
Finished dump at: 2026-08-22 02:03:47

备份指定表：

mydumper -u root -p -B m2 -T b -o /myback

压缩备份：

mydumper -u root -p -B m2 -c -o /myback

备份后文件为 .gz 压缩格式。

正则匹配备份：

# 只备份 mysql 开头的表
mydumper -u root -p -B m2 -o /myback -x "mysql_*"

# 同时备份 mysql_* 和 *_master 的表
mydumper -u root -p -B m2 -o /myback -x "m2.mysql_*|m2.*_master"

# 备份 a 开头的表
mydumper -B m2 -o /myback/ -x 'a.'

空表也生成备份文件：

mydumper -u root -p -B m2 -T abc -e -o /myback

不备份表结构：

mydumper -B m2 -m -o /myback/

备份时记录日志：

mydumper -u root -p -B m2 -o /myback -L /myback/sql_back.log

杀掉长查询后备份：

mydumper -u root -p -B m2 -o /myback -K

工作环境中难免存在长查询，长查询存在时无法锁表，可能导致数据不一致。备份时建议先杀掉长查询、锁表后再备份。

锁表备份：

mydumper -u root -p -B m2 -o /myback --lock-all-tables

多线程 + 分块 + 压缩的生产写法：

mydumper -u root -p -h 127.0.0.1 -B m2 -t 8 -r 500000 -c \
         -o /myback/$(date +%F) -L /myback/mydumper.log

3.4 myloader 数据恢复

常用参数：

参数 缩写 说明
--host -h 指定还原主机
--user -u 指定用户
--password -p 用户密码
--port -P 指定端口
--socket -S 指定 socket 文件路径
--directory -d 指定备份文件存放路径
--overwrite-tables -o 恢复时表存在则先 drop（备份中需包含表结构）
--database -B 指定还原到哪个数据库
--enable-binlog -e 还原时一并还原二进制日志
--threads -t 并发恢复线程数
--queries-per-transaction -q 每个事务包含的语句数，默认 1000

恢复示例：

# 还原 /myback 目录下所有备份数据
myloader -u root -p -d /myback

# 恢复时先 drop 已存在的表
myloader -u root -p -o -d /myback

# 还原到指定数据库
myloader -u root -p -B m2 -d /myback

# 还原时一并恢复二进制日志
myloader -u root -p -e -d /myback

# 多线程恢复
myloader -u root -p -d /myback -t 8 -o -B m2

四、binlog 与基于时间点恢复（PITR）

4.1 二进制日志的作用

二进制日志（binary log，简称 binlog）记录所有引起数据变化的语句或行变更（INSERT/UPDATE/DELETE/DDL），不记录纯查询（SELECT/SHOW）。它工作在 Server 层，与引擎无关。

三个核心日志的对比（初学者最易混淆）：

日志 层次 记录内容 用途
undo log InnoDB 引擎层 修改前的数据（逻辑日志） 事务回滚 + MVCC 读旧版本
redo log InnoDB 引擎层 数据页的物理修改（ib_logfile*） 崩溃恢复（crash recovery），保证已提交事务不丢
binlog Server 层 逻辑 SQL 或行变更 主从复制 + 时间点恢复（PITR）

binlog 的三大用途：

用途 说明
增量备份 / 时间点恢复 全备只能回到备份时刻，binlog 让你能恢复到故障前一秒
主从复制 主库把 binlog 发给从库，从库重放实现数据同步（Binlog Dump 线程）
审计与数据订阅 通过解析 binlog 追查谁改了数据；Canal/Maxwell/Debezium 等据此做实时数据同步到 ES/Kafka

因为 DDL 不能回滚（DROP TABLE 打错表名无法 ROLLBACK），且 MySQL 默认 autocommit=1 使误删无法回滚，「全备 + binlog」是误操作唯一的救命通道。事务与自动提交的细节见 11-04-索引事务与状态查询.md。

4.2 三种 binlog 格式

格式 记录内容 优点 缺点
STATEMENT（SBR） 记录原始 SQL 语句本身 日志量最小；可读性最好；只记一条语句即可代表百万行变更 不安全：含 NOW()、UUID()、RAND()、LAST_INSERT_ID()、用户自定义函数、LIMIT 无序更新等不确定性语句时，主从/恢复后结果不同；RC 隔离级别下可能主从不一致
ROW（RBR，默认且推荐） 记录每一行修改前后的镜像（before image / after image） 绝对安全，与语句无关，恢复/复制结果必然一致；可做数据审计、可反向生成回滚 SQL（闪回） 日志量大（UPDATE t SET x=1 影响百万行会产生百万条记录）；需 --base64-output=decode-rows -v 才能读懂
MIXED（MBR） 默认用 STATEMENT，MySQL 判定语句「不安全」时自动切换为 ROW 兼顾体积与安全 切换规则由 MySQL 内部判定，行为不够可预期；排查问题时两种格式混杂

选择建议：

生产统一使用 ROW。理由：
  ① 数据一致性优先于日志体积（磁盘便宜，数据无价）；
  ② 隔离级别改 RC（降低死锁）的前提就是 binlog_format=ROW；
  ③ 误删数据的「闪回恢复」只有 ROW 格式能做（有前后镜像才能反向生成 SQL）；
  ④ Canal 等数据订阅工具只支持 ROW。
MySQL 5.7.7 起默认即为 ROW；5.6 及 MariaDB 部分版本默认 STATEMENT，需手工修改。
-- 查看与修改（不重启，只对新会话/新事务生效）
SHOW VARIABLES LIKE 'binlog_format';
SET GLOBAL binlog_format = 'ROW';       -- 全局，需 SUPER 权限；已有连接不受影响
SET SESSION binlog_format = 'ROW';      -- 仅当前会话

4.3 开启与配置 binlog

CentOS 7 / MySQL 5.7 / MariaDB 10.x：

# /etc/my.cnf 或 /etc/my.cnf.d/server.cnf
[mysqld]
# ① 开启 binlog：值为文件名前缀，可含路径
log_bin = /var/lib/mysql/mysql-bin
# MariaDB 亦可写 log-bin=mysql-bin
# ② server_id 必须设置且全局唯一，MySQL 5.7 开 binlog 时不设会启动失败
server_id = 1
# ③ 格式
binlog_format = ROW
binlog_row_image = FULL          # FULL(默认,记录所有列) / MINIMAL(只记主键与改动列) / NOBLOB
# ④ 单个 binlog 文件大小上限，写满自动切换，默认 1G
max_binlog_size = 512M
# ⑤ 自动清理策略（二者只用其一）
expire_logs_days = 7                     # MySQL 5.7 / MariaDB：保留天数，0 为不自动删
# binlog_expire_logs_seconds = 604800    # MySQL 8.0：秒为单位，优先级高于 expire_logs_days
# ⑥ 刷盘策略
sync_binlog = 1                  # 1=每次提交都 fsync（最安全，生产必须）
# ⑦ binlog 缓存
binlog_cache_size = 1M           # 每个会话的事务 binlog 缓存
max_binlog_cache_size = 2G
# ⑧ 只记录/忽略指定库（谨慎使用，跨库语句会漏记）
# binlog_do_db = m2
# binlog_ignore_db = mysql
# ⑨ 让从库也记录自己重放的日志（级联复制或从库做备份时必须）
log_slave_updates = 1

CentOS 6 / MySQL 5.5 的差异：

# CentOS 6 上服务名与配置文件路径
# 配置文件：/etc/my.cnf（同 7）；服务管理用 SysV 脚本
[mysqld]
log-bin=mysql-bin                # 5.5 常见写法用连字符
server-id=1
binlog_format=MIXED              # 5.5/5.6 默认 STATEMENT，建议至少改 MIXED，能改 ROW 更好
expire_logs_days=7
# 5.5 没有 binlog_row_image、没有 GTID（5.6 才引入）、没有 binlog_expire_logs_seconds
# CentOS 6：SysV 方式重启并设置开机自启
service mysqld restart
chkconfig mysqld on
chkconfig --list mysqld

# CentOS 7：systemd 方式
systemctl restart mysqld         # MariaDB 为 systemctl restart mariadb
systemctl enable mysqld
systemctl status mysqld

版本差异汇总表：

特性 MySQL 5.5 / CentOS 6 MySQL 5.6 MySQL 5.7 / CentOS 7 MySQL 8.0 MariaDB 10.x
默认 binlog_format STATEMENT STATEMENT ROW（5.7.7+） ROW 10.2 起 MIXED，10.5+ 建议 ROW
开 binlog 是否强制 server_id 否（默认 0 也能启动） 否 是，不设启动失败 是 否，但强烈建议设
binlog_row_image 无 有 有 有 10.1+ 有
GTID 无 有（gtid_mode） 有 默认可开 自有 GTID 实现，格式不同
expire_logs_days 有 有 有（8.0 起废弃） 用 binlog_expire_logs_seconds 有
服务管理 service / chkconfig — systemctl systemctl systemctl mariadb
系统库引擎 MyISAM MyISAM MyISAM（8.0 才转 InnoDB） InnoDB Aria/MyISAM

验证是否开启成功：

SHOW VARIABLES LIKE 'log_bin';           -- ON 表示已开启
SHOW VARIABLES LIKE 'log_bin_basename';  -- 日志路径前缀
SHOW VARIABLES LIKE 'binlog%';
SHOW VARIABLES LIKE 'server_id';
SELECT @@sql_log_bin;                    -- 当前会话是否写 binlog

4.4 binlog 文件与索引文件

ls -l /var/lib/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 1073741952 mysql-bin.000001   ← 已写满并切换
-rw-rw---- 1 mysql mysql     245    mysql-bin.000002   ← 正在写入的当前日志
-rw-rw---- 1 mysql mysql      57    mysql-bin.index    ← 索引文件：所有 binlog 的清单
cat /var/lib/mysql/mysql-bin.index
/var/lib/mysql/mysql-bin.000001
/var/lib/mysql/mysql-bin.000002

严禁用 rm 直接删除 binlog 文件！mysql-bin.index 中仍保留记录，会导致 SHOW BINARY LOGS 报错、PURGE 失败、从库找不到日志而复制中断。必须用 PURGE BINARY LOGS 命令删除（见 4.6）。

4.5 binlog 查看命令

-- ① 列出服务器上所有 binlog 及大小
SHOW BINARY LOGS;
SHOW MASTER LOGS;                    -- 同义词
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       245 |
| mysql-bin.000002 |      1893 |
+------------------+-----------+
-- ② 查看当前正在写入的日志与位置（做备份/搭从库/PITR 的起点）
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1893 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
-- ③ 在线查看日志中的事件（不用退出 mysql）
SHOW BINLOG EVENTS;                                   -- 默认看第一个日志文件
SHOW BINLOG EVENTS IN 'mysql-bin.000002';
SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 245;
SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 245 LIMIT 0,20;   -- 分页，日志大时必用
+------------------+-----+-------------+-----------+-------------+-------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                |
+------------------+-----+-------------+-----------+-------------+-------------------------------------+
| mysql-bin.000002 |   4 | Format_desc |         1 |         245 | Server ver: 5.7.36-log, Binlog ver:4|
| mysql-bin.000002 | 245 | Query       |         1 |         321 | BEGIN                               |
| mysql-bin.000002 | 321 | Table_map   |         1 |         369 | table_id: 108 (m2.a)                |
| mysql-bin.000002 | 369 | Write_rows  |         1 |         413 | table_id: 108 flags: STMT_END_F     |
| mysql-bin.000002 | 413 | Xid         |         1 |         444 | COMMIT /* xid=63 */                 |
| mysql-bin.000002 | 444 | Query       |         1 |         556 | drop database m2                    |
+------------------+-----+-------------+-----------+-------------+-------------------------------------+

关键字段与事件类型：

字段 含义
Pos 事件的起始位置
End_log_pos 事件的结束位置，也就是下一个事件的 Pos
Event_type 事件类型，见下
Info 事件内容摘要（ROW 格式的行事件此处看不到具体数据，需 mysqlbinlog -v）
事件类型 含义
Format_desc 日志文件头（每个文件开头都有，位置 4 ~ 245 左右），位置 4 是所有 binlog 的有效起点
Query SQL 语句事件：BEGIN、DDL、STATEMENT 格式的 DML
Table_map ROW 格式中声明后续行事件操作哪张表
Write_rows / Update_rows / Delete_rows ROW 格式的插入 / 更新 / 删除
Xid 事务提交标记（COMMIT），PITR 的 stop-position 应落在 Xid 结束处
Rotate 日志切换，指向下一个文件
Gtid / Previous_gtids GTID 相关事件

4.6 FLUSH LOGS 切换与 PURGE 清理

-- 切换日志：关闭当前 binlog 并新建一个（编号 +1）
FLUSH LOGS;
FLUSH BINARY LOGS;                -- 只切 binlog，不动其他日志（推荐，5.5+）
# 命令行等价写法
mysqladmin -uroot -p flush-logs
mysql -uroot -p -e "FLUSH LOGS;"

FLUSH LOGS 的运维价值：让当前日志封口，之后拷贝这个已封口的文件绝对安全，这就是「全备 + -F」实现增量备份的原理。此外，mysqldump -F、xtrabackup、重启服务、日志写满 max_binlog_size 都会触发切换。

-- 清理 binlog（唯一正确的删除方式）
PURGE BINARY LOGS TO 'mysql-bin.000005';               -- 删除该文件之前的所有日志（不含它本身）
PURGE BINARY LOGS BEFORE '2026-08-15 00:00:00';        -- 删除该时间点之前的日志
PURGE MASTER LOGS TO 'mysql-bin.000005';               -- 同义词
RESET MASTER;                                          -- 删除所有 binlog 并从 000001 重新开始

清理的风险：

风险 说明
破坏恢复链 一旦删除了「最老可用全备之后」的日志，就无法做时间点恢复。清理前必须确认：保留的 binlog 起点 ≤ 最老要保留的全备位点
中断主从复制 从库还没读到的日志被删 → Got fatal error 1236 ... could not find first log file name，只能重建从库。删除前必须检查所有从库的 Relay_Master_Log_File
RESET MASTER 极度危险 会清空全部 binlog 并重置计数，主库上执行等于自断退路；只可在初始化新实例或明确无从库、无恢复需求时使用
rm 删除后遗症 index 文件残留导致 SHOW BINARY LOGS 与实际不符，需手工编辑 mysql-bin.index 并重启修复 
-- 清理前的安全检查
SHOW BINARY LOGS;                 -- 现有日志
SHOW SLAVE HOSTS;                 -- 有哪些从库
-- 在每个从库上执行，确认已消费到哪个文件
SHOW SLAVE STATUS\G               -- 看 Master_Log_File / Relay_Master_Log_File

4.7 mysqlbinlog 工具

mysqlbinlog 把二进制日志解析成可读的 SQL 文本，输出到标准输出，因此既能查看也能管道给 mysql 执行。

# 基本查看
mysqlbinlog /var/lib/mysql/mysql-bin.000002
mysqlbinlog /var/lib/mysql/mysql-bin.000002 | less

# 解析 ROW 格式（必须加这两个参数才能看到具体行数据）
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000002
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002   # -vv 额外显示列类型与元信息

常用参数：

参数 说明
--start-position=N 从位置 N 开始解析（必须是某个事件的起始位置，不能是中间）
--stop-position=N 解析到位置 N 结束
--start-datetime='YYYY-MM-DD HH:MM:SS' 从该时间开始
--stop-datetime='YYYY-MM-DD HH:MM:SS' 到该时间结束
--database=库名 / -d 只输出指定库的事件（ROW 格式可靠；STATEMENT 格式下跨库语句会被漏掉）
--base64-output=decode-rows 把 base64 编码的行事件解码（不解码则显示为一串乱码 base64）
-v / -vv 把行事件还原成伪 SQL（### INSERT INTO ...）/ 额外显示类型注释
--skip-gtids 输出时忽略 GTID 事件，避免恢复到同实例时 GTID 冲突
--read-from-remote-server / -R 从远程服务器直接读取 binlog（配合 -h -u -p），无需登录对端
--raw 以二进制原样保存（配合 -R 实现 binlog 实时远程备份）
--stop-never 持续监听主库，实时拉取新 binlog（配合 -R --raw 做异地实时归档，RPO≈0）
--result-file=前缀 / -r 指定输出文件（--raw 时为文件名前缀）
--to-last-log 读到最后一个日志文件为止
--set-charset=utf8 在输出中加入 SET NAMES utf8，防止恢复时中文乱码
--disable-log-bin 输出中加入 SET sql_log_bin=0，恢复时不再写新 binlog
--verify-binlog-checksum 校验日志校验和，检测文件损坏

按位置与时间恢复的四种典型用法：

# ① 通过起始位置恢复
mysqlbinlog --start-position=4 --stop-position=798 mysql_bin.000001 | mysql -uroot -p

# ② 通过时间范围恢复
mysqlbinlog --start-datetime='2018-09-20 00:00:00' \
            --stop-datetime='2018-09-30 12:00:00' mysql_bin.000001 | mysql -uroot -p

# ③ 只恢复某个库
mysqlbinlog --database=m2 --start-position=245 mysql-bin.000002 | mysql -uroot -p

# ④ 先导出为 SQL 文件，人工检查/编辑后再执行（生产强烈推荐）
mysqlbinlog --start-position=245 --stop-position=1893 mysql-bin.000002 > /myback/inc.sql
vim /myback/inc.sql          # 删掉误操作语句、确认无 DROP
mysql -uroot -p < /myback/inc.sql

多个日志文件必须一次性、按顺序传给 mysqlbinlog：

# 正确：一条命令处理多个文件，事务跨文件也不会断
mysqlbinlog mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 | mysql -uroot -p

# 错误：分多次执行，跨文件的事务会被截断，且临时变量丢失
mysqlbinlog mysql-bin.000002 | mysql -uroot -p
mysqlbinlog mysql-bin.000003 | mysql -uroot -p    # ← 有风险

# 批量按顺序拼接
mysqlbinlog mysql-bin.00000[2-9] > /myback/all_inc.sql

binlog 实时远程归档（把 RPO 降到秒级，生产强烈建议部署）：

# 在备份机上持续拉取主库的 binlog，写到本地目录
cd /binlog_backup
mysqlbinlog -R --raw --stop-never --host=192.168.2.10 --port=3306 \
            -u repl -p'ReplPass' mysql-bin.000001 &
# 说明：从 mysql-bin.000001 开始，持续跟随主库写入，主库切换日志时本地也自动生成新文件

4.8 误删数据的完整恢复演练（全备 + binlog 时间点恢复）

场景：每天 02:00 做一次全备；10:30 运维在生产库上执行了 DELETE FROM m2.a; 忘写 WHERE（或 drop database m2;），10:35 被发现。目标：恢复到 10:30 误操作前的状态，且 10:00~10:30 之间的正常业务数据不能丢。

第 0 步：止损（最重要，也最容易被忽略）

□ 立刻停止应用写入：下线应用 / 把库设为只读，防止新数据覆盖、防止误操作被更多变更掩埋
    SET GLOBAL read_only = ON;         -- 普通用户只读（super 用户仍可写）
    SET GLOBAL super_read_only = ON;   -- MySQL 5.7+：连 super 也只读
□ 立即备份当前的 binlog 与 datadir（保命快照，防止恢复操作把现场破坏掉）
    FLUSH LOGS;  然后 cp -a /var/lib/mysql/mysql-bin.* /safe/
□ 严禁在生产库上直接 mysql < all.sql（全备里的 DROP 会毁掉误操作后的正常数据）
□ 优先在一台空闲机器 / 临时实例上恢复，校验后再回搬数据

第 1 步：准备实验环境与初始数据

-- 建库建表
CREATE DATABASE m2 DEFAULT CHARSET utf8;
CREATE TABLE m2.a (id INT(2), name CHAR(10), sex CHAR(1));
INSERT INTO m2.a VALUES (1,'zhangsan','f'),(2,'lisi','m');
SELECT * FROM m2.a;

第 2 步：02:00 的全备（记录位点）

# 方式一：切换日志式全备（增量就是新的整个日志文件）
mysqldump -uroot -p --single-transaction -B m2 -F > /myback/all.sql

# 方式二：记录位点式全备（推荐，位点写在 dump 文件里）
mysqldump -uroot -p --single-transaction --master-data=2 -B m2 > /myback/all.sql

# 查看全备对应的 binlog 位点，这是增量恢复的起点
grep "CHANGE MASTER TO" /myback/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245;

第 3 步：模拟白天的正常业务变更（必须保住的数据）

INSERT INTO m2.a VALUES (3,'wangwu','f');
INSERT INTO m2.a VALUES (4,'zhaoliu','m');
UPDATE m2.a SET sex='m' WHERE id=1;

第 4 步：模拟 10:30 的误操作

DELETE FROM m2.a;          -- 忘写 WHERE，全表清空
-- 或者更严重的
-- DROP DATABASE m2;

第 5 步：紧急止损并归档 binlog

SET GLOBAL read_only = ON;
FLUSH LOGS;                -- 让含误操作的日志封口，新变更写入下一个文件
mkdir -p /safe
cp -a /var/lib/mysql/mysql-bin.* /safe/
ls /safe/

第 6 步：找到误操作语句的 position（核心步骤）

-- 方法一：在 mysql 里翻日志，定位 DELETE / DROP 所在事件
SHOW BINLOG EVENTS IN 'mysql-bin.000002';
+------------------+------+-------------+-------------+---------------------------------+
| Log_name         | Pos  | Event_type  | End_log_pos | Info                            |
+------------------+------+-------------+-------------+---------------------------------+
| mysql-bin.000002 |    4 | Format_desc |         245 | Server ver: 5.7.36-log          |
| mysql-bin.000002 |  245 | Query       |         321 | BEGIN                           |
| mysql-bin.000002 |  321 | Table_map   |         369 | table_id: 108 (m2.a)            |
| mysql-bin.000002 |  369 | Write_rows  |         413 | table_id: 108 (id=3 wangwu)     |
| mysql-bin.000002 |  413 | Xid         |         444 | COMMIT /* xid=63 */             |
| mysql-bin.000002 |  444 | Query       |         520 | BEGIN                           |
| mysql-bin.000002 |  520 | Table_map   |         568 | table_id: 108 (m2.a)            |
| mysql-bin.000002 |  568 | Write_rows  |         612 | table_id: 108 (id=4 zhaoliu)    |
| mysql-bin.000002 |  612 | Xid         |         643 | COMMIT /* xid=64 */             |
| mysql-bin.000002 |  643 | Query       |         719 | BEGIN                           |
| mysql-bin.000002 |  719 | Table_map   |         767 | table_id: 108 (m2.a)            |
| mysql-bin.000002 |  767 | Update_rows |         820 | table_id: 108 (id=1 sex f->m)   |
| mysql-bin.000002 |  820 | Xid         |         851 | COMMIT /* xid=65 */             |
| mysql-bin.000002 |  851 | Query       |         927 | BEGIN                           |   ← 误操作事务开始
| mysql-bin.000002 |  927 | Table_map   |         975 | table_id: 108 (m2.a)            |
| mysql-bin.000002 |  975 | Delete_rows |        1080 | table_id: 108 flags: STMT_END_F |   ← 误删！
| mysql-bin.000002 | 1080 | Xid         |        1111 | COMMIT /* xid=66 */             |   ← 误操作事务结束
+------------------+------+-------------+-------------+---------------------------------+
# 方法二：用 mysqlbinlog 解析并搜索关键字，同时看到时间戳
mysqlbinlog --base64-output=decode-rows -v /safe/mysql-bin.000002 | grep -n -B5 -i "DELETE FROM"
mysqlbinlog --base64-output=decode-rows -v /safe/mysql-bin.000002 | grep -n -i -E "drop|truncate|delete"

# 方法三：按时间窗口缩小范围（已知误操作大约 10:30 发生）
mysqlbinlog --base64-output=decode-rows -v \
            --start-datetime='2026-08-22 10:00:00' \
            --stop-datetime='2026-08-22 10:35:00' /safe/mysql-bin.000002 | less

解析出的关键片段（ROW 格式）：

# at 851
#260822 10:30:12 server id 1  end_log_pos 927 CRC32 0x9f3c1a2b  Query   thread_id=45
BEGIN
# at 975
#260822 10:30:12 server id 1  end_log_pos 1080 Delete_rows: table id 108 flags: STMT_END_F
### DELETE FROM `m2`.`a`
### WHERE
###   @1=1
###   @2='zhangsan'
###   @3='m'
### DELETE FROM `m2`.`a`
### WHERE
###   @1=2
### ...
# at 1080
#260822 10:30:12 server id 1  end_log_pos 1111 CRC32 0x1a2b3c4d  Xid = 66
COMMIT/*!*/;

结论（记牢这两个数字）：

  • 误操作事务的起始位置 = 851（BEGIN 的 Pos）→ 这是重放时的 --stop-position。
  • 误操作事务的结束位置 = 1111（Xid 的 End_log_pos）→ 这是跳过误操作后继续重放的 --start-position。

位置必须落在事件边界上：--stop-position 取误操作 BEGIN 的 Pos，--start-position 取误操作 COMMIT 的 End_log_pos。取到事件中间会报 Error in Log_event::read_log_event() 或产生不完整事务。

第 7 步：恢复全备（到临时实例或已清空的目标库）

# 若是 DROP DATABASE，恢复前无需处理；若是 DELETE，为避免主键冲突先清理目标库
mysql -uroot -p -e "DROP DATABASE IF EXISTS m2;"

# 恢复全备 → 回到 02:00 的状态
mysql -uroot -p < /myback/all.sql
SELECT * FROM m2.a;
+------+----------+------+
| id   | name     | sex  |
+------+----------+------+
|    1 | zhangsan | f    |
|    2 | lisi     | m    |
+------+----------+------+

第 8 步：应用 binlog，跳过误操作语句

方案 A —— 两段式重放（保留误操作之后的正常数据，最完整）：

# 第一段：从全备位点 245 → 误操作前 851
mysqlbinlog --start-position=245 --stop-position=851 /safe/mysql-bin.000002 > /myback/inc1.sql

# 第二段：从误操作事务之后 1111 → 日志结尾（含后续日志文件）
mysqlbinlog --start-position=1111 /safe/mysql-bin.000002 > /myback/inc2.sql
mysqlbinlog /safe/mysql-bin.000003 >> /myback/inc2.sql     # 若已切换过日志

# 人工检查两个文件，确认不含 DELETE/DROP
grep -i -E "delete from|drop |truncate" /myback/inc1.sql /myback/inc2.sql

# 依次执行
mysql -uroot -p < /myback/inc1.sql
mysql -uroot -p < /myback/inc2.sql

方案 B —— 只恢复到误操作前（最简单，会丢弃误操作之后的所有变更）：

mysqlbinlog --start-position=245 --stop-position=851 /safe/mysql-bin.000002 | mysql -uroot -p
# 或按时间
mysqlbinlog --start-datetime='2026-08-22 02:00:00' \
            --stop-datetime='2026-08-22 10:30:11' /safe/mysql-bin.000002 | mysql -uroot -p
# 注意：stop-datetime 要早于误操作时间；同一秒内多个事务时时间无法精确区分，必须用 position

方案 C —— 导出为 SQL 后手工删除误操作语句（一段式，适合误操作夹在大量正常语句中间）：

mysqlbinlog --start-position=245 /safe/mysql-bin.000002 > /myback/inc_all.sql
vim /myback/inc_all.sql
# 定位到 "# at 851" 到 "# at 1111" 之间的整个事务，连同 BEGIN/COMMIT 一并删除
mysql -uroot -p < /myback/inc_all.sql

第 9 步：验证数据

SELECT * FROM m2.a;
+------+----------+------+
| id   | name     | sex  |
+------+----------+------+
|    1 | zhangsan | m    |     ← UPDATE 已重放
|    2 | lisi     | m    |
|    3 | wangwu   | f    |     ← 全备后的新数据已找回
|    4 | zhaoliu  | m    |
+------+----------+------+
-- 完整验证清单
SELECT COUNT(*) FROM m2.a;                 -- 行数与故障前对账
CHECK TABLE m2.a;                          -- 表无损坏
SHOW CREATE TABLE m2.a\G                   -- 结构、索引、字符集
SELECT MAX(id), MAX(update_time) FROM m2.a; -- 最大主键/最新时间是否到误操作前一刻
-- 与业务方核对关键业务数据（订单数、金额合计），核对通过后再放开写入
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

第 10 步：事后复盘

□ 恢复过程与耗时记录归档（用于评估真实 RTO）
□ 分析根因：为何能在生产执行无 WHERE 的 DELETE
□ 加固措施：
    - mysql 客户端启用安全模式：mysql -U / --safe-updates（无 WHERE 的 UPDATE/DELETE 直接报错）
      写进 ~/.my.cnf：[mysql] safe-updates
    - 生产账号权限收敛：应用账号不给 DROP；DBA 用跳板机 + 审计
    - 上线延迟从库（延迟 1 小时），误操作可从延迟库直接取数
      CHANGE MASTER TO MASTER_DELAY=3600;
    - 引入 SQL 审核平台（Archery/Yearning），所有变更走工单
    - 定期恢复演练

4.9 GTID 模式下的恢复注意事项

GTID（Global Transaction Identifier，全局事务标识）给每个事务一个全局唯一编号 server_uuid:序号，复制与恢复不再依赖「文件名 + position」。

# MySQL 5.6+ 开启 GTID
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
log_slave_updates = 1
SHOW VARIABLES LIKE 'gtid_mode';
SELECT @@global.gtid_executed;         -- 本实例已执行的所有 GTID 区间
SELECT @@global.gtid_purged;           -- 已被清理掉的 GTID 区间
SHOW MASTER STATUS;                    -- Executed_Gtid_Set 列

恢复时的四个坑：

问题 现象 解决
dump 文件里的 GTID_PURGED 冲突 恢复时报 @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty 备份时用 --set-gtid-purged=OFF；或恢复前 RESET MASTER 清空 GTID 状态（确认无从库）
binlog 重放时事务被跳过 mysqlbinlog \| mysql 执行后数据没变化，因为该 GTID 已在 gtid_executed 中，MySQL 认为「已执行过」直接忽略 加 --skip-gtids：mysqlbinlog --skip-gtids --start-position=... \| mysql -uroot -p
跳过某个坏事务 复制中断在某 GTID 注入空事务：SET GTID_NEXT='uuid:序号'; BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC';
GTID 与 position 混用 搭从库时 MASTER_LOG_FILE 与 MASTER_AUTO_POSITION=1 冲突 GTID 模式统一用 CHANGE MASTER TO MASTER_AUTO_POSITION=1 
# GTID 模式下做 PITR 的标准写法
mysqldump -uroot -p -A --single-transaction --master-data=2 --set-gtid-purged=OFF > all.sql
mysql -uroot -p < all.sql
mysqlbinlog --skip-gtids --start-position=245 --stop-position=851 mysql-bin.000002 | mysql -uroot -p

MariaDB 的 GTID 实现与 MySQL 完全不同（格式为 域ID-服务器ID-序列号，如 0-1-1234，变量为 gtid_slave_pos、gtid_binlog_pos），两者不能互相复制，恢复操作也不通用。跨产品迁移只能用逻辑备份。

4.10 其他日志简述

日志 作用 关键参数
错误日志（Error Log） 记录启动/关闭过程、启动失败原因、崩溃恢复、复制错误、死锁摘要。排障第一个要看的文件 log_error=/var/log/mysqld.log、log_warnings（5.7+ 为 log_error_verbosity=1/2/3）
慢查询日志（Slow Query Log） 记录执行时间超过阈值的语句，索引优化的主要输入 slow_query_log=1、slow_query_log_file、long_query_time=1、log_queries_not_using_indexes、min_examined_row_limit、log_slow_admin_statements；MariaDB 专有 log_slow_verbosity=query_plan,explain
通用查询日志（General Query Log） 记录所有连接与语句（含 SELECT）。信息最全但性能损耗极大、日志暴涨，仅用于短时排查，用完立即关 general_log=1、general_log_file、log_output=FILE 或 TABLE
二进制日志（Binary Log） 见本章前文：复制与 PITR log_bin、binlog_format、sync_binlog
中继日志（Relay Log） 仅从库有。IO 线程把主库 binlog 拉过来先落地为 relay log，SQL 线程再从中读取重放。格式与 binlog 相同，可用 mysqlbinlog 解析 relay_log=mysqld-relay-bin、relay_log_purge=1（默认自动清理）、relay_log_recovery=1（崩溃后自动重建，5.6+ 建议开）
redo log / undo log InnoDB 引擎层日志，崩溃恢复与回滚/MVCC，用户不直接操作 innodb_log_file_size、innodb_flush_log_at_trx_commit、innodb_undo_tablespaces 
-- 快速查看各日志开关与位置
SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'general_log%';
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'relay_log%';

-- 临时开关（无需重启）
SET GLOBAL general_log = ON;      -- 排查完立刻 OFF
SET GLOBAL general_log = OFF;
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
# 日志轮转（错误日志/慢日志被 mv 后需 flush 才会重新创建）
mv /var/log/mysql/mysql-slow.log /var/log/mysql/mysql-slow.log.$(date +%F)
mysqladmin -uroot -p flush-logs
# 或交给 logrotate 管理（/etc/logrotate.d/mysql）

慢查询日志的完整分析方法（mysqldumpslow 全部参数、pt-query-digest、SHOW PROCESSLIST 实时定位、EXPLAIN 执行计划解读）详见 11-04-索引事务与状态查询.md。

五、物理备份

5.1 冷备：停库直接拷贝 datadir

最简单、最可靠、也最容易被轻视的备份方式。适合迁移、升级前留底、允许停机的场景。

完整步骤：

# ① 确认数据目录位置
mysql -uroot -p -e "SHOW VARIABLES LIKE 'datadir';"
grep -E "^datadir" /etc/my.cnf
# 默认 /var/lib/mysql

# ② 检查磁盘空间足够
du -sh /var/lib/mysql
df -h /myback

# ③ 优雅停库（必须等进程完全退出，确保脏页刷盘、日志 checkpoint 完成）
systemctl stop mysqld            # CentOS 7；MariaDB 为 systemctl stop mariadb
# CentOS 6：service mysqld stop
ps -ef | grep -v grep | grep mysqld     # 确认无残留进程
# 更稳妥：停库前先执行一次全量刷盘
# mysql -uroot -p -e "SET GLOBAL innodb_fast_shutdown=0;"   然后再停，保证 undo 全部清理

# ④ 拷贝（-a 保留权限、属主、时间戳、软链接，关键！）
cp -a /var/lib/mysql /myback/mysql_$(date +%F)
# 或打包压缩（同时保留权限）
tar -zcvpf /myback/mysql_$(date +%F).tar.gz -C /var/lib mysql
# 顺便把配置文件一起备份，否则恢复后参数不一致
cp -a /etc/my.cnf /etc/my.cnf.d /myback/mysql_$(date +%F)_conf

# ⑤ 启动数据库
systemctl start mysqld

冷备恢复：

systemctl stop mysqld
# 保留现场（不要直接删！）
mv /var/lib/mysql /var/lib/mysql.bak.$(date +%s)
# 还原
tar -zxvpf /myback/mysql_2026-08-22.tar.gz -C /var/lib
# 或 cp -a /myback/mysql_2026-08-22 /var/lib/mysql

# ⑥ 恢复权限与属主（最高频的失败原因！）
chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql
# CentOS 7 还需恢复 SELinux 上下文
restorecon -Rv /var/lib/mysql
# 若数据目录不在默认位置，需先加规则
# semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?" && restorecon -Rv /data/mysql

systemctl start mysqld
tail -50 /var/log/mysqld.log          # 启动失败第一时间看错误日志
mysql -uroot -p -e "SHOW DATABASES;"

注意事项：

注意点 说明
必须完全停库 运行中直接 cp 数据文件，会拷到写了一半的页，恢复后表损坏。这不是「热备」，是「破备」
必须用 cp -a / tar -p 丢失属主会导致 mysqld 无法读写数据目录，报 Permission denied 启动失败
必须整目录拷贝 不能只拷 .ibd 文件——InnoDB 的表空间 ID 记录在 ibdata1 里，单独拷 .ibd 无法识别（除非用表空间传输，见 5.5）
*别忘 `ib_logfile与ibdata1`** 这三类文件是一致的整体，缺一个即无法启动
版本要匹配 恢复到不同大版本可能需 mysql_upgrade；跨大版本降级基本不可行
auto.cnf 的坑 该文件保存 server_uuid，克隆到另一台后必须删除 auto.cnf 让其重新生成，否则主从复制会因 UUID 相同而失败
配置文件同步 innodb_log_file_size、innodb_data_file_path 等参数与数据文件强绑定，配置不一致会启动失败
非 yum 版的坑 如果是非 yum 版 MySQL（源码编译/二进制解压），删除数据目录后将无法启动，需要重新初始化（mysqld --initialize 或 mysql_install_db）

5.2 基于文件系统快照的备份（LVM 快照）

LVM 快照利用写时复制（COW），瞬间创建一个数据目录的时间点视图，锁表时间只有 1~2 秒，是「几乎热备」的物理备份方案。前提：datadir 必须位于 LVM 逻辑卷上，且 VG 中有剩余空闲空间。

完整流程：

# ① 前置检查：datadir 在哪个 LV 上，VG 剩余空间是否充足
df -h /var/lib/mysql
lvs ; vgs
# VG 剩余空间建议 ≥ 备份期间的数据变化量（一般给 LV 的 10%~20%）
-- ② 加全局读锁（此后只能读，写请求阻塞。务必确认无长事务，否则会卡死）
SELECT * FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND,trx_started,NOW())>10;
FLUSH TABLES WITH READ LOCK;          -- 简称 FTWRL，全库只读
-- ③ 记录位点（恢复后接主库/做增量都要用），此会话不要退出，否则锁会释放！
SHOW MASTER STATUS;
# ④ 另开一个终端，立即创建快照（1~2 秒完成）
lvcreate -L 5G -s -n mysql_snap /dev/vg_data/lv_mysql
#   -L 快照空间大小   -s 快照   -n 快照名   最后是源 LV 路径
-- ⑤ 回到第一个会话，立即释放锁（业务恢复正常）
UNLOCK TABLES;
# ⑥ 挂载快照并拷贝数据到备份目标（此时业务已正常，慢慢拷）
mkdir -p /mnt/snap
mount -o ro /dev/vg_data/mysql_snap /mnt/snap      # XFS 需加 -o nouuid,ro
tar -zcvpf /myback/mysql_snap_$(date +%F).tar.gz -C /mnt/snap .
# 顺便把位点信息存下来
echo "mysql-bin.000006  1245" > /myback/mysql_snap_$(date +%F).position

# ⑦ 卸载并删除快照（快照存在期间会持续消耗 IO 与 COW 空间，必须及时删）
umount /mnt/snap
lvremove -f /dev/vg_data/mysql_snap

注意事项：

  • FLUSH TABLES WITH READ LOCK 会等待所有正在执行的语句结束。若有一个跑了半小时的大查询，FTWRL 会一直挂着，同时新来的所有查询都被它阻塞（因为 MDL 请求排队）→ 业务瞬间雪崩。执行前必须确认无长查询/长事务。
  • 快照空间被写满后快照立即失效（lvs 显示 Invalid snapshot），备份作废。
  • 快照恢复出的数据处于「崩溃状态」，启动时 InnoDB 会自动做 crash recovery，这是正常的。
  • MySQL 5.6+ 可用 FLUSH TABLES WITH READ LOCK 的轻量替代：先 FLUSH TABLES 再 FTWRL，缩短锁持有时间。

LVM 的创建、扩容、快照原理与命令详见 03-01-磁盘管理与文件系统.md。

5.3 Xtrabackup / innobackupex 物理热备

Percona XtraBackup 是开源的 MySQL 物理热备工具，通过持续拷贝 redo log 实现 InnoDB 表在线备份不锁表。

5.3.1 xtrabackup 与 innobackupex 的区别

工具 说明
xtrabackup 可对 InnoDB 表进行热备份（不加锁，备份期间数据可读写），但不能备份 MyISAM 表
innobackupex 可同时备份 InnoDB 和 MyISAM 表，但 MyISAM 表仍不能热备（必须加锁后才能备份）

版本演进：innobackupex 是 xtrabackup 的 perl 封装脚本。XtraBackup 2.4 起两者功能合并，innobackupex 成为 xtrabackup 的软链接并被标记为废弃；8.0 起 innobackupex 被彻底移除，只能用 xtrabackup。此外 XtraBackup 2.4 只支持 MySQL 5.7 及以下，MySQL 8.0 必须用 XtraBackup 8.0（大版本必须对应），MariaDB 10.3+ 请改用官方的 mariabackup（用法与 innobackupex 几乎一致）。

5.3.2 安装 xtrabackup

下载二进制包：

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/tarball/percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt145.tar.gz

安装依赖：

yum install libaio libaio-devel -y
# 完整依赖（perl 模块用于 innobackupex）
yum install -y libaio libaio-devel perl perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL rsync

解压并创建软链接：

tar xf percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt145.tar.gz -C /usr/local/
ln -s /usr/local/percona-xtrabackup-2.4.12-Linux-x86_64/ /usr/local/xtrabackup
ln -s /usr/local/xtrabackup/bin/xtrabackup /usr/bin/xtrabackup
ln -s /usr/local/xtrabackup/bin/innobackupex /usr/bin/innobackupex
# 验证
xtrabackup --version
innobackupex --version

yum 方式安装（更省事）：

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-xtrabackup-24

备份账号最小权限：

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'BkpPass';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT, SUPER ON *.* TO 'bkpuser'@'localhost';
FLUSH PRIVILEGES;

5.3.3 Xtrabackup 常用参数

参数 说明
--defaults-file 指定 MySQL 主配置文件路径，自动读取 datadir、basedir 等（必须是第一个参数）
--defaults-extra-file 除主配置文件外，额外读取指定配置文件
--target-dir 指定备份存放路径，不指定则备份到当前目录 xtrabackup_backupfiles
--backup 备份到 --target-dir（必选），不选则只打印过程
--prepare 恢复前准备，生成 InnoDB log file（应用 redo，把备份变成一致状态）
--apply-log-only 只应用 redo 不回滚未提交事务，增量备份合并时除最后一次外都必须加
--copy-back 从备份目录以拷贝形式恢复到数据目录
--move-back 从备份目录以移动形式恢复到数据目录（省空间，但备份被消耗）
--print-param 打印备份或恢复时需要的参数
--suspend-at-end 在备份目录生成 xtrabackup_suspended 文件，挂起进程持续同步变化，直到手动删除该文件
--tables 备份指定表（支持正则）
--tables-exclude 排除指定表
--databases 备份指定库
--datadir MySQL 数据目录路径，一般不用指定（从配置文件读）
--incremental 创建增量备份
--incremental-basedir 增量备份时指定上一次备份目录，自动确定备份起点（读取其 LSN）
--incremental-lsn 增量备份时指定 to_lsn 位置（手工指定起点）
--incremental-dir 将增量备份与全量备份合并后放入新目录
--compress 备份时启用压缩（qpress 格式，需装 qpress）
--compress-threads=N 压缩线程数
--decompress 解压压缩备份
--parallel=N 并行拷贝文件的线程数，大库必调（一般 4~8）
--throttle=N 限速，每秒最多 N 对 IO 操作，避免打满磁盘影响业务
--no-timestamp 备份时不自动创建以日期时间命名的目录（脚本里常用，目录名可控）
--stream=xbstream/tar 备份时启用打包/流式输出，可直接管道到远端或压缩
--no-lock 备份时关闭自动锁表功能（仅当全库都是 InnoDB 且不需要 binlog 位点时才可用）
--safe-slave-backup 备份时先停止 slave 的 SQL 线程，保证一致
--slave-info 备份从库时把主库位点写入 xtrabackup_slave_info
--galera-info 备份 Galera 集群节点时记录状态
--remote-host 使用 ssh 将备份文件拷贝至远程服务器（老版本 innobackupex 参数，新版用 --stream + ssh）
--apply-log （innobackupex）恢复时通过 xtrabackup_logfile 在备份目录中创建备份（恢复时必选），等价于新版 --prepare
--redo-only （innobackupex）等价于 --apply-log-only
--uncompress （innobackupex）解压以压缩方式备份的文件

5.3.4 xtrabackup 全量备份与恢复

全量备份：

xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/myback -uroot --port=3306 -S /var/lib/mysql/mysql.sock -p

备份目录内容：

a/                        ← 各数据库目录（含 .ibd/.frm）
backup-my.cnf             ← 备份时的关键配置（恢复时要用）
ibdata1                   ← 系统表空间
mysql/
performance_schema/
test/
xtrabackup_binlog_info    ← 备份时刻的 binlog 文件名与 position（PITR 起点！）
xtrabackup_checkpoints    ← LSN 检查点信息（增量备份的依据）
xtrabackup_info           ← 备份的元信息（工具版本、命令、时间、是否压缩）
xtrabackup_logfile        ← 备份期间拷贝的 redo log，prepare 时用它把数据补齐到一致状态
# 备份关键产物解读
cat /myback/xtrabackup_binlog_info
# mysql-bin.000006    1245              ← 从这个位点之后应用 binlog 即可做时间点恢复
cat /myback/xtrabackup_checkpoints
cat /myback/xtrabackup_info

全量恢复：

模拟数据丢失，停止数据库并删除数据目录：

systemctl stop mariadb
rm /var/lib/mysql/*

注意：如果是非 yum 版 MySQL，删除数据目录后将无法启动，需要重新初始化。生产中请用 mv 保留现场而不是 rm。

恢复前准备（初始化备份目录，应用 redo 达到一致状态）：

xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/myback

将备份目录全部文件拷贝到数据目录：

\cp -rf /myback/* /var/lib/mysql
# 或使用官方推荐的方式（会自动排除 xtrabackup_* 元文件）
# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/myback

重新授权并启动：

chown -R mysql.mysql /var/lib/mysql
systemctl start mariadb
# 验证
mysql -uroot -p -e "SHOW DATABASES;"
mysql -uroot -p -e "use a;show tables;"

恢复后如需继续做时间点恢复：取 xtrabackup_binlog_info 里的位点，用 mysqlbinlog --start-position=1245 mysql-bin.000006 | mysql -uroot -p 追加增量。

5.3.5 xtrabackup 增量备份与恢复

增量备份的原理：InnoDB 每个数据页头部都有 LSN（Log Sequence Number），xtrabackup 只拷贝「页 LSN > 上次备份 to_lsn」的页，因此增量备份体积极小。

全量备份（作为增量的基准）：

xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/myback -uroot --port=3306 -S /var/lib/mysql/mysql.sock -p

查看检查点：

cat /myback/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0            本次备份开始点
to_lsn = 1632546        本次备份结束点
last_lsn = 1632546      下次备份开始点
compact = 0
recover_binlog_info = 0

第一次增量备份（备份至 /inc/inc1）：

mysql -uroot -p -e "create table a.a2 (id int);show tables;"
xtrabackup --backup --target-dir=/inc/inc1 --incremental-basedir=/myback -uroot -S /var/lib/mysql/mysql.sock -P 3306
cat /inc/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1632546
to_lsn = 1634384
last_lsn = 1634384
compact = 0
recover_binlog_info = 0

第二次增量备份（备份至 /inc/inc2）：

mysql -uroot -p -e "create table a.a3 (id int);show tables;"
xtrabackup --backup --target-dir=/inc/inc2 --incremental-basedir=/inc/inc1 -uroot -S /var/lib/mysql/mysql.sock -P 3306
cat /inc/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1634384
to_lsn = 1636206
last_lsn = 1636206
compact = 0
recover_binlog_info = 0

增量备份参数说明：

参数 说明
--target-dir 指定增备目录（备份到哪儿）
--incremental-basedir 指定上一次备份目录。第一次增量备份时为全备目录，第二次增量备份时为第一次增备目录

LSN 链条必须首尾相接：全备 to_lsn=1632546 → inc1 from_lsn=1632546；inc1 to_lsn=1634384 → inc2 from_lsn=1634384。检查备份链是否完整，只需核对相邻两次的 to_lsn 与 from_lsn 是否相等，不相等说明中间少了一次备份，链条已断，无法恢复。 若把 --incremental-basedir 始终指向全备目录，得到的就是差异备份（体积更大但恢复只需两步）。

全量恢复（全备 + 增备）：

systemctl stop mariadb
rm /var/lib/mysql/*

初始化全量备份：

xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/myback

初始化第一次增量备份：

xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/myback --incremental-basedir=/inc/inc1

初始化第二次增量备份：

xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/myback --incremental-basedir=/inc/inc2

拷贝合并后的数据到数据目录：

cp /inc/inc2/* /var/lib/mysql/ -r
chown -R mysql.mysql /var/lib/mysql
systemctl start mariadb

验证：

mysql -uroot -p -e "use a;show tables;"
+-------------+
| Tables_in_a |
+-------------+
| a1          |
| a2          |
| a3          |
+-------------+

关于 --apply-log-only 的规范用法（官方推荐顺序，比上面的写法更严谨，生产按此执行）：

# ① 全备 prepare，只应用 redo，不回滚未提交事务（因为后面还要合并增量）
xtrabackup --prepare --apply-log-only --target-dir=/myback
# ② 合并第一个增量（同样加 --apply-log-only）
xtrabackup --prepare --apply-log-only --target-dir=/myback --incremental-dir=/inc/inc1
# ③ 合并最后一个增量（最后一次【不加】--apply-log-only，此时回滚未提交事务）
xtrabackup --prepare --target-dir=/myback --incremental-dir=/inc/inc2
# ④ 从合并后的全备目录恢复
xtrabackup --copy-back --target-dir=/myback
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

记忆要点：除最后一次外，所有 prepare 都要 --apply-log-only；最后一次绝不能加。加错的后果是数据处于不一致状态或无法再合并后续增量。

压缩与并行备份：

# 并行 + 限速（大库生产写法）
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/myback \
           --parallel=4 --throttle=200 -ubkpuser -p'BkpPass'

# 压缩备份（需先 yum install qpress）
xtrabackup --backup --compress --compress-threads=4 --target-dir=/myback_c -ubkpuser -p'BkpPass'
# 解压后才能 prepare
xtrabackup --decompress --parallel=4 --target-dir=/myback_c
xtrabackup --prepare --target-dir=/myback_c

# 流式备份直接传到远端（不落地本地磁盘）
xtrabackup --backup --stream=xbstream --target-dir=/tmp -ubkpuser -p'BkpPass' \
  | ssh root@192.168.2.20 "xbstream -x -C /myback/remote_$(date +%F)"

# 流式 + 压缩到单个文件
xtrabackup --backup --stream=xbstream --target-dir=/tmp | gzip > /myback/full_$(date +%F).xbstream.gz

备份日志解读（备份结束时的关键输出，必须确认）：

xtrabackup: Transaction log of lsn (1632546) to (1632546) was copied.
...
xtrabackup: Streaming complete.
completed OK!                      ← 【最关键】只有出现这行才算备份成功
# 脚本中判断备份是否成功的正确方式
xtrabackup --backup --target-dir=/myback 2>/myback/xtrabackup.log
if grep -q "completed OK!" /myback/xtrabackup.log; then
    echo "备份成功"
else
    echo "备份失败，请检查日志"
fi
日志关键行 含义
Copying ./ibdata1 to ... 正在拷贝系统表空间
>> log scanned up to (LSN) 后台线程正在持续跟踪 redo log，这是热备不丢数据的关键
Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 准备锁 MyISAM 表
Executing FLUSH TABLES WITH READ LOCK... 开始加全局读锁（此刻业务只读，通常仅 1 秒内）
All tables unlocked 锁已释放
MySQL binlog position: filename 'mysql-bin.000006', position '1245' 备份对应的 binlog 位点
completed OK! 备份成功。没有这行即备份失败，不管目录里有多少文件

5.4 innobackupex 备份与恢复

5.4.1 innobackupex 常用参数

参数 说明
--apply-log 恢复时通过 xtrabackup_logfile 在备份目录中创建备份（恢复时必选）
--copy-back 从备份目录以拷贝形式恢复到数据目录
--move-back 从备份目录以移动形式恢复到数据目录
--defaults-file 指定 MySQL 主配置文件路径
--remote-host 使用 ssh 将备份文件拷贝至远程服务器
--stream 备份时启用打包
--compress 备份时启用压缩
--tables 指定要备份的表
--databases 指定要备份的库
--uncompress 解压以压缩方式备份的文件
--incremental 创建增量备份
--incremental-basedir 增量备份时指定全量备份路径，以确定全量备份终止位置
--incremental-dir 将增量备份与全量备份合并后放入新目录
--incremental-lsn 增量备份时指定起点
--redo-only 只应用 redo 不回滚，合并增量时除最后一次外都要加
--no-lock 备份时关闭自动锁表功能
--safe-slave-backup 备份时先停止 slave 进程
--no-timestamp 备份时不自动创建以日期时间命名的目录

5.4.2 innobackupex 全量备份与恢复

全量备份：

innobackupex --defaults-file=/etc/my.cnf /myback -uroot -P 3306 -S /var/lib/mysql/mysql.sock
# 或
innobackupex --defaults-file=/etc/my.cnf -u root -p123 -h 127.0.0.1 /myback

备份文件会存放在 /myback 下以当前日期命名的目录中，如 /myback/2018-09-24_22-07-00/。加 --no-timestamp 可让目录名由自己指定。

模拟误操作：

mysql -uroot -p -e "drop database a;"
mysql -uroot -p -e "show databases;"

恢复前停止数据库并清空数据目录：

systemctl stop mariadb
rm -rf /var/lib/mysql/*

恢复前准备（初始化数据）：

innobackupex --defaults-file=/etc/my.cnf --apply-log /myback/2018-09-24_22-07-00/

恢复数据到数据目录：

innobackupex --defaults-file=/etc/my.cnf --copy-back --datadir=/var/lib/mysql /myback/2018-09-24_22-07-00/

--datadir 指定数据目录位置，如果配置文件中已有指定则不需要再指定。

重新授权并启动：

chown -R mysql.mysql /var/lib/mysql
systemctl start mariadb

验证数据：

mysql -uroot -p -e "show databases;"
mysql -uroot -p -e "select * from a.a1;"

5.4.3 innobackupex 增量备份与恢复

查看全量备份检查点：

cat /myback/`ls /myback/`/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1623090        全量结束点，即增量备份开始点
last_lsn = 1623090
compact = 0
recover_binlog_info = 0

第一次增量备份：

innobackupex --defaults-file=/etc/my.cnf --incremental-basedir=/myback/`ls /myback/` --incremental /inc/ -uroot -S /var/lib/mysql/mysql.sock -P 3306

查看增备检查点：

cat /inc/2018-09-24_22-18-17/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1623090      本次增量起点
to_lsn = 1630106        本次增量终点
last_lsn = 1630106      下次增量起点
compact = 0
recover_binlog_info = 0

第二次增量备份：

innobackupex --defaults-file=/etc/my.cnf --incremental-basedir=/inc/2018-09-24_22-18-17 --incremental /inc -uroot -S /var/lib/mysql/mysql.sock -P 3306

备份目录说明：

目录 用途
/myback/2018-09-24_22-32-18 全备目录（--incremental-basedir 使用）
/inc/2018-09-24_22-34-47 第一次增量备份目录（--incremental 使用）
/inc/2018-09-24_22-38-10 第二次增量备份目录（--incremental 使用）

增量恢复：

systemctl stop mariadb
rm /var/lib/mysql/*

初始化全备目录：

innobackupex --apply-log --redo-only /myback/2018-09-24_22-32-18

初始化第一次增备目录：

innobackupex --apply-log --redo-only /myback/2018-09-24_22-32-18 --incremental-dir=/inc/2018-09-24_22-34-47

初始化第二次增备目录（最后一次不需要加 --redo-only）：

innobackupex --apply-log /myback/2018-09-24_22-32-18 --incremental-dir=/inc/2018-09-24_22-38-10

完整恢复：

innobackupex --copy-back /myback/2018-09-24_22-32-18

过程：先将两次增量备份的数据整合到全备目录中，再一次性恢复。注意 --incremental-dir 指向增备目录，而 --apply-log 后面的路径始终是全备目录（合并的目标）。

重新授权并启动：

chown -R mysql.mysql /var/lib/mysql
systemctl start mariadb

验证：

mysql -uroot -p -e "show databases;"
mysql -uroot -p -e "use a;show tables;"
+-------------+
| Tables_in_a |
+-------------+
| a1          |
| a2          |
| a3          |
| a4          |
+-------------+

至此，innobackupex 增量备份与恢复完成。

5.5 表级操作：表空间传输与单表快速恢复

InnoDB 在 innodb_file_per_table=ON（5.6+ 默认）时每表一个 .ibd 文件，可以做可传输表空间（Transportable Tablespace），实现单表的物理级导出导入——比逻辑备份快几十倍，是「单表误删」的最快恢复手段。

5.5.1 导出表空间

-- 前提确认
SHOW VARIABLES LIKE 'innodb_file_per_table';    -- 必须为 ON

-- ① 在源库上锁表并把脏页刷到 .ibd
USE m2;
FLUSH TABLES a FOR EXPORT;
-- 此时会在数据目录生成 m2/a.cfg（表结构元数据）
-- 【本会话不要退出】，退出即释放锁，.cfg 文件会被删除
# ② 另开终端，拷走 .ibd 与 .cfg
cp /var/lib/mysql/m2/a.ibd /var/lib/mysql/m2/a.cfg /myback/
-- ③ 回到第一个会话，解锁
UNLOCK TABLES;

5.5.2 导入表空间

-- ① 在目标库建一张【结构完全相同】的空表（列、类型、索引、行格式都必须一致）
CREATE TABLE m2.a (id INT(2), name CHAR(10), sex CHAR(1)) ENGINE=InnoDB;

-- ② 丢弃它自带的空表空间（此后 a.ibd 被删除）
ALTER TABLE m2.a DISCARD TABLESPACE;
# ③ 把备份的 .ibd/.cfg 放进目标库目录并改好属主
cp /myback/a.ibd /myback/a.cfg /var/lib/mysql/m2/
chown mysql:mysql /var/lib/mysql/m2/a.ibd /var/lib/mysql/m2/a.cfg
-- ④ 导入表空间
ALTER TABLE m2.a IMPORT TABLESPACE;

-- ⑤ 验证
CHECK TABLE m2.a;
SELECT COUNT(*) FROM m2.a;

常见报错与原因：

报错 原因
Schema mismatch (Table has ROW_TYPE_DYNAMIC ...) 表结构/行格式不一致，需保证 ROW_FORMAT、字符集、列定义完全相同
Tablespace is missing for table .ibd 没放进目录或属主不对
Cannot find .cfg file 缺 .cfg（可用 IMPORT TABLESPACE 强制导入但风险高）
表空间 ID 冲突 跨实例导入时正常，IMPORT 会自动改写

5.5.3 单表误删的快速恢复决策

误删了一张表的数据，怎么恢复最快？

① 有延迟从库（MASTER_DELAY）且延迟未追平
     → 直接从延迟库 SELECT ... INTO OUTFILE 或 mysqldump 单表，回搬。分钟级，最优。
② 有全备（逻辑）+ binlog
     → 从全备中 sed 提取该表 → 恢复到临时库 → 应用 binlog 到误操作前 → INSERT ... SELECT 回搬。
③ 有全备（物理 xtrabackup）
     → xtrabackup --prepare --export 后用表空间传输把单表搬回临时实例，再回搬数据。
④ 只有 binlog（ROW 格式）
     → 闪回：解析 binlog 反向生成补偿 SQL（DELETE→INSERT、UPDATE 前后镜像互换）
        工具：binlog2sql、MyFlash、my2sql
        python binlog2sql.py -h127.0.0.1 -uroot -p'pwd' -dm2 -ta \
          --start-file='mysql-bin.000002' --start-position=851 --stop-position=1111 -B > rollback.sql
        （-B 即 flashback 模式，生成回滚 SQL）
⑤ 什么都没有 → 只能找数据恢复公司扫盘，成功率低、代价极高。
# xtrabackup 单表恢复：备份时导出单表表空间
xtrabackup --prepare --export --target-dir=/myback
# prepare --export 后会为每个表生成 .exp/.cfg 文件，之后按 5.5.2 导入

六、备份自动化与运维

6.1 完整的 Shell 备份脚本

生产可用的 mysqldump 全备脚本，涵盖变量定义、按日期命名、压缩、删除过期备份、成功/失败判断、告警、写日志：

#!/bin/bash
#===============================================================
# 文件名: /opt/scripts/mysql_backup.sh
# 功能  : MySQL 全库逻辑备份（压缩 + 过期清理 + 校验 + 告警 + 日志）
# 用法  : bash /opt/scripts/mysql_backup.sh
# 作者  : DBA
#===============================================================

#--------- ① 变量定义 ---------
DB_USER="backup"
DB_PASS="BackupPass123"
DB_HOST="127.0.0.1"
DB_PORT="3306"

BACK_DIR="/myback"                       # 备份根目录
LOG_FILE="/var/log/mysql_backup.log"     # 脚本日志
KEEP_DAYS=7                              # 备份保留天数
DATE=$(date +%F_%H-%M)                   # 2026-08-22_02-00
DAY=$(date +%F)
BACK_FILE="${BACK_DIR}/all_${DATE}.sql.gz"
MAIL_TO="dba@example.com"
MIN_SIZE=1024                            # 备份文件最小合理体积（KB），小于此判为失败

#--------- ② 日志函数 ---------
log() {
    echo "[$(date '+%F %T')] $*" >> "$LOG_FILE"
}

alert() {
    local msg="$1"
    log "ALERT: $msg"
    # 邮件告警（需已配置 mailx / postfix）
    echo "$msg" | mail -s "[严重] MySQL备份失败 $(hostname) ${DAY}" "$MAIL_TO"
    # 企业微信/钉钉机器人告警（按需启用）
    # curl -s "https://oapi.dingtalk.com/robot/send?access_token=XXX" \
    #      -H 'Content-Type: application/json' \
    #      -d "{\"msgtype\":\"text\",\"text\":{\"content\":\"MySQL备份失败: $msg\"}}"
}

#--------- ③ 前置检查 ---------
log "========== 备份开始 =========="
[ -d "$BACK_DIR" ] || mkdir -p "$BACK_DIR"

# 检查磁盘剩余空间（低于 10GB 直接告警退出）
AVAIL=$(df -k "$BACK_DIR" | awk 'NR==2{print $4}')
if [ "$AVAIL" -lt $((10*1024*1024)) ]; then
    alert "备份目录 $BACK_DIR 剩余空间不足 10GB（当前 $((AVAIL/1024/1024))GB），备份中止"
    exit 1
fi

# 检查数据库是否可连接
if ! mysqladmin -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" -P"$DB_PORT" ping &>/dev/null; then
    alert "数据库无法连接，备份中止"
    exit 1
fi

#--------- ④ 执行备份 ---------
log "开始导出到 $BACK_FILE"
START=$(date +%s)

mysqldump -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" -P"$DB_PORT" \
          --all-databases \
          --single-transaction \
          --master-data=2 \
          --flush-logs \
          --routines --triggers --events \
          --hex-blob \
          --default-character-set=utf8 \
          2>>"$LOG_FILE" | gzip > "$BACK_FILE"

# 关键：管道中 mysqldump 的退出码要用 PIPESTATUS 取，不能用 $?
DUMP_RC=${PIPESTATUS[0]}
GZIP_RC=${PIPESTATUS[1]}
END=$(date +%s)
COST=$((END-START))

#--------- ⑤ 成功/失败判断（三重校验） ---------
# 校验 1：退出码
if [ "$DUMP_RC" -ne 0 ] || [ "$GZIP_RC" -ne 0 ]; then
    alert "mysqldump 返回码 $DUMP_RC / gzip 返回码 $GZIP_RC，备份失败：$BACK_FILE"
    rm -f "$BACK_FILE"
    exit 1
fi

# 校验 2：文件存在且体积合理
SIZE=$(du -k "$BACK_FILE" 2>/dev/null | awk '{print $1}')
if [ ! -s "$BACK_FILE" ] || [ "${SIZE:-0}" -lt "$MIN_SIZE" ]; then
    alert "备份文件体积异常（${SIZE}KB），疑似截断：$BACK_FILE"
    exit 1
fi

# 校验 3：压缩包完整性 + 结尾标记
if ! gzip -t "$BACK_FILE" 2>>"$LOG_FILE"; then
    alert "备份文件压缩校验失败（gzip -t）：$BACK_FILE"
    exit 1
fi
if ! zcat "$BACK_FILE" | tail -1 | grep -q "Dump completed"; then
    alert "备份文件缺少 'Dump completed' 结尾标记，内容不完整：$BACK_FILE"
    exit 1
fi

# 记录 MD5，便于异地传输后核对
md5sum "$BACK_FILE" > "${BACK_FILE}.md5"

log "备份成功：$BACK_FILE 大小 $((SIZE/1024))MB 耗时 ${COST}s"

#--------- ⑥ 归档 binlog（增量部分） ---------
BINLOG_DIR=$(mysql -u"$DB_USER" -p"$DB_PASS" -N -e "SHOW VARIABLES LIKE 'log_bin_basename';" | awk '{print $2}')
if [ -n "$BINLOG_DIR" ]; then
    mkdir -p "${BACK_DIR}/binlog/${DAY}"
    # --flush-logs 已切换日志，此时把除【当前正在写】以外的日志全部归档
    CUR_LOG=$(mysql -u"$DB_USER" -p"$DB_PASS" -N -e "SHOW MASTER STATUS;" | awk '{print $1}')
    for f in ${BINLOG_DIR}.[0-9]*; do
        [ "$(basename $f)" = "$CUR_LOG" ] && continue
        cp -a "$f" "${BACK_DIR}/binlog/${DAY}/" && log "已归档 binlog: $(basename $f)"
    done
fi

#--------- ⑦ 删除过期备份 ---------
log "清理 ${KEEP_DAYS} 天前的备份"
find "$BACK_DIR" -maxdepth 1 -name "all_*.sql.gz"     -mtime +${KEEP_DAYS} -print -delete >> "$LOG_FILE" 2>&1
find "$BACK_DIR" -maxdepth 1 -name "all_*.sql.gz.md5" -mtime +${KEEP_DAYS} -print -delete >> "$LOG_FILE" 2>&1
find "${BACK_DIR}/binlog" -maxdepth 1 -mindepth 1 -type d -mtime +${KEEP_DAYS} -exec rm -rf {} \; 2>/dev/null

# 安全兜底：清理后至少要还剩 1 份备份，否则告警
LEFT=$(ls -1 ${BACK_DIR}/all_*.sql.gz 2>/dev/null | wc -l)
if [ "$LEFT" -lt 1 ]; then
    alert "清理后备份目录内无任何备份文件，请立即检查！"
fi
log "当前保留备份数：$LEFT"

#--------- ⑧ 异地同步 ---------
rsync -avz --bwlimit=10240 -e "ssh -p 22" "$BACK_FILE" "${BACK_FILE}.md5" \
      backup@192.168.2.200:/remote_backup/mysql/ >> "$LOG_FILE" 2>&1
if [ $? -eq 0 ]; then
    log "异地同步成功"
else
    alert "异地同步失败：$BACK_FILE（本地备份仍然有效）"
fi

log "========== 备份结束 =========="
exit 0

脚本使用与权限：

mkdir -p /opt/scripts
chmod 700 /opt/scripts/mysql_backup.sh        # 内含密码，必须 700
chown root:root /opt/scripts/mysql_backup.sh
mkdir -p /myback && chmod 700 /myback
touch /var/log/mysql_backup.log && chmod 600 /var/log/mysql_backup.log

# 先手工跑一次，确认无误再进 crontab
bash -x /opt/scripts/mysql_backup.sh          # -x 打开调试，逐行看执行
tail -f /var/log/mysql_backup.log

密码更安全的做法：脚本中不写密码，改用 /root/.my.cnf（chmod 600），mysqldump 自动读取；或使用 MySQL 5.6+ 的 mysql_config_editor set --login-path=bk --host=... --user=... --password，脚本里写 mysqldump --login-path=bk。 Shell 语法（变量、条件判断、循环、函数、PIPESTATUS、退出码、重定向）详见 06-01-Shell脚本编程.md。

XtraBackup 版本的备份脚本（物理备份，大库用）：

#!/bin/bash
# /opt/scripts/xtrabackup_full.sh —— 每周日全备，周一至周六增备
BACK_ROOT=/xtraback
FULL_DIR=${BACK_ROOT}/full
INC_DIR=${BACK_ROOT}/inc
LOG=/var/log/xtrabackup.log
USER=bkpuser
PASS=BkpPass
DAY_OF_WEEK=$(date +%u)      # 1=周一 ... 7=周日
TODAY=$(date +%F)

mkdir -p $FULL_DIR $INC_DIR

if [ "$DAY_OF_WEEK" -eq 7 ]; then
    # 周日：全量备份
    TARGET=${FULL_DIR}/${TODAY}
    xtrabackup --defaults-file=/etc/my.cnf --backup --no-timestamp \
               --target-dir=$TARGET --parallel=4 --throttle=200 \
               -u$USER -p$PASS -S /var/lib/mysql/mysql.sock 2>>$LOG
else
    # 其余：基于上一次备份的增量
    LAST=$(ls -1dt ${INC_DIR}/* ${FULL_DIR}/* 2>/dev/null | head -1)
    TARGET=${INC_DIR}/${TODAY}
    xtrabackup --defaults-file=/etc/my.cnf --backup --no-timestamp \
               --target-dir=$TARGET --incremental-basedir=$LAST --parallel=4 \
               -u$USER -p$PASS -S /var/lib/mysql/mysql.sock 2>>$LOG
fi

# 判断成功（xtrabackup 必须看 completed OK!）
if tail -20 $LOG | grep -q "completed OK!"; then
    echo "[$(date '+%F %T')] 备份成功 $TARGET" >> $LOG
    tar -zcf ${TARGET}.tar.gz -C $(dirname $TARGET) $(basename $TARGET) && rm -rf $TARGET
else
    echo "[$(date '+%F %T')] 备份失败 $TARGET" >> $LOG
    echo "xtrabackup 失败" | mail -s "[严重] $(hostname) XtraBackup 失败" dba@example.com
    exit 1
fi

# 清理 30 天前的备份
find $BACK_ROOT -name "*.tar.gz" -mtime +30 -delete

6.2 crontab 定时执行

# 编辑 root 的定时任务
crontab -e
# ┌── 分 (0-59)
# │ ┌── 时 (0-23)
# │ │ ┌── 日 (1-31)
# │ │ │ ┌── 月 (1-12)
# │ │ │ │ ┌── 周 (0-7，0和7都是周日)
# │ │ │ │ │
# * * * * *  command

# 每天凌晨 2:00 全备（业务低峰）
0 2 * * * /bin/bash /opt/scripts/mysql_backup.sh >/dev/null 2>&1

# 每周日 3:00 做一次 XtraBackup 全备，周一到周六 3:00 增备（脚本内部判断星期）
0 3 * * * /bin/bash /opt/scripts/xtrabackup_full.sh >/dev/null 2>&1

# 每 10 分钟归档一次 binlog 到异地（把 RPO 降到 10 分钟）
*/10 * * * * /usr/bin/rsync -az /var/lib/mysql/mysql-bin.0* backup@192.168.2.200:/remote_backup/binlog/ >/dev/null 2>&1

# 每周一 5:00 清理 7 天前的 binlog（脚本内含 PURGE 与从库检查）
0 5 * * 1 /bin/bash /opt/scripts/purge_binlog.sh >/dev/null 2>&1

# 每月 1 号 4:00 做一次恢复演练（在测试机上自动恢复并对账，结果发邮件）
0 4 1 * * /bin/bash /opt/scripts/restore_drill.sh >/dev/null 2>&1
# 常用管理命令
crontab -l                       # 查看当前用户的定时任务
crontab -l -u mysql              # 查看指定用户
crontab -r                       # 删除全部任务（危险，慎用）
systemctl status crond           # CentOS 7 确认服务在跑
systemctl enable crond
service crond status             # CentOS 6
tail -f /var/log/cron            # 查看 cron 执行记录（只记录是否调起，不记录脚本输出）

crontab 的常见坑：

坑 说明与解决
PATH 极简 cron 环境的 PATH 只有 /usr/bin:/bin，脚本里的 mysqldump、xtrabackup 若在 /usr/local/bin 会「command not found」。解决：脚本内用绝对路径，或开头 export PATH=/usr/local/bin:/usr/bin:/bin:$PATH
% 被转义 cron 中 % 是特殊字符，date +%F 直接写在 crontab 里会被截断，必须写成 \%F 或把命令放进脚本（推荐）
无输出丢失日志 不写 >/dev/null 2>&1 会给 root 发本地邮件塞满 /var/spool/mail；但全丢弃又看不到错误——所以脚本内部必须自己写日志
任务重叠 备份没跑完下一次又启动，会双份消耗 IO。用 flock 加锁：0 2 * * * /usr/bin/flock -n /tmp/bk.lock /bin/bash /opt/scripts/mysql_backup.sh
时区/时间不同步 服务器时间漂移导致备份时间点不准，PITR 找错位置。装 ntpd/chronyd
脚本无执行权限/换行符 Windows 上传的脚本含 \r，报 bad interpreter。用 dos2unix script.sh 修复

crontab 语法与 at、anacron 的完整说明见 04-进程与任务管理.md（本仓库对应 04- 系列）。

6.3 备份文件的校验与恢复演练流程

每次备份后的自动校验（已集成在脚本 ⑤ 中）：

# ① 文件存在且非空
[ -s /myback/all_2026-08-22.sql.gz ] && echo "存在"

# ② 压缩完整性
gzip -t /myback/all_2026-08-22.sql.gz && echo "压缩包完整"
# tar 包
tar -tzf /myback/full.tar.gz >/dev/null && echo "tar 完整"

# ③ 逻辑备份的结尾标记
zcat /myback/all_2026-08-22.sql.gz | tail -1 | grep "Dump completed"

# ④ 物理备份的成功标记
grep "completed OK!" /var/log/xtrabackup.log

# ⑤ MD5 一致性（异地传输后核对）
md5sum -c /myback/all_2026-08-22.sql.gz.md5

# ⑥ 体积同比（突然缩小 50% 说明出问题了）
ls -l --time-style=+%F /myback/all_*.sql.gz | awk '{print $6, $5/1024/1024"MB"}'

季度恢复演练标准流程（必须形成书面记录）：

□ 1. 准备独立演练环境（不得在生产库上演练！）
      版本、字符集、参数与生产一致的测试机或临时容器
□ 2. 从异地备份仓库拉取备份文件（顺便验证异地备份可用、网络带宽够用）
      记录：下载耗时 ______
□ 3. 校验文件（md5 + gzip -t + 结尾标记）
□ 4. 执行恢复，全程计时
      逻辑：zcat all.sql.gz | mysql -uroot -p
      物理：xtrabackup --prepare → --copy-back → chown → start
      记录：恢复耗时 ______ （这就是真实 RTO）
□ 5. 应用 binlog 到指定时间点
      记录：能恢复到的最新时间点 ______ （这就是真实 RPO）
□ 6. 数据对账（关键！）
      □ 库数量、表数量一致：SELECT COUNT(*) FROM information_schema.tables WHERE ...
      □ 核心表行数一致：SELECT COUNT(*) FROM orders;
      □ 关键业务值一致：SELECT SUM(amount) FROM orders WHERE create_time<'...';
      □ CHECKSUM 对比：CHECKSUM TABLE orders;   （或 pt-table-checksum）
      □ 存储过程/函数/触发器/事件/用户权限是否齐全
      □ 字符集与排序规则无变化，中文无乱码
□ 7. 启动应用连上恢复后的库，跑一遍核心业务流程
□ 8. 输出演练报告：耗时、RTO/RPO 实测值、发现的问题、改进项、责任人、下次演练时间
□ 9. 清理演练环境
-- 快速对账用的表清单与行数
SELECT table_schema, COUNT(*) AS tables, SUM(table_rows) AS approx_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql','sys')
GROUP BY table_schema;

-- 精确校验单表（数据量大时较慢）
CHECKSUM TABLE m2.a;
CHECKSUM TABLE m2.a EXTENDED;      -- 逐行计算，更准更慢
# 主从/两库之间的一致性校验（生产标准工具）
pt-table-checksum --host=127.0.0.1 --user=root --ask-pass --databases=m2
pt-table-sync --print --host=127.0.0.1 --user=root --ask-pass --sync-to-master h=slave_ip

6.4 异地同步：rsync 与 scp

# ① scp：简单一次性传输
scp /myback/all_2026-08-22.sql.gz backup@192.168.2.200:/remote_backup/mysql/
scp -P 2222 -l 8000 /myback/all.sql.gz backup@remote:/backup/   # -P 端口  -l 限速(Kbit/s)
scp -r /myback/2026-08-22/ backup@remote:/remote_backup/        # -r 递归目录

# ② rsync：增量同步，支持断点续传与限速，生产首选
rsync -avz --progress /myback/ backup@192.168.2.200:/remote_backup/mysql/
#   -a 归档模式(保留权限属主时间)  -v 详细  -z 传输压缩  --progress 进度
rsync -avz --bwlimit=10240 --partial --append-verify \
      -e "ssh -p 22 -i /root/.ssh/id_rsa" \
      /myback/ backup@192.168.2.200:/remote_backup/mysql/
#   --bwlimit 限速 KB/s（避免打满出口带宽）
#   --partial --append-verify 断点续传
rsync -avz --delete /myback/ backup@remote:/remote_backup/mysql/   # 目标端删除源端已不存在的文件（镜像，慎用）

# ③ 免密配置（定时任务必须免密）
ssh-keygen -t rsa -N '' -f /root/.ssh/id_rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub backup@192.168.2.200
ssh backup@192.168.2.200 "echo ok"          # 验证免密成功

# ④ 传输后校验
md5sum /myback/all_2026-08-22.sql.gz
ssh backup@192.168.2.200 "md5sum /remote_backup/mysql/all_2026-08-22.sql.gz"

# ⑤ 上传到对象存储（异地容灾的现代做法）
# 阿里云 OSS
ossutil cp /myback/all_2026-08-22.sql.gz oss://mybucket/mysql/ -f
# AWS S3
aws s3 cp /myback/all_2026-08-22.sql.gz s3://mybucket/mysql/ --storage-class STANDARD_IA

rsync 的守护进程模式、--exclude、inotify 实时同步、tar/dump/restore 等系统级备份工具详见 03-03-备份与恢复.md。

6.5 排错清单

6.5.1 备份阶段

现象 / 报错 原因 解决
备份期间业务卡死，大量 Waiting for table metadata lock 用了 --lock-all-tables / FTWRL，或 --lock-tables 默认锁表；且有长查询把 FTWRL 顶住，后续请求全部排队 改用 --single-transaction（InnoDB）；备份前 kill 长查询；备份放到从库上做
备份中断，Lost connection to MySQL server during query net_write_timeout/wait_timeout 太小，或网络抖动，或大表单行超包 --max-allowed-packet=512M；服务端调 net_read_timeout=3600、net_write_timeout=3600、wait_timeout=28800；加 --quick（默认已开）
Got error: 2013: Lost connection to MySQL server during query when dumping table 同上，常见于含大 BLOB 的表 见上；单独用 --where 分批导那张大表
Got error: 1044/1045 Access denied 备份账号缺权限 GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, RELOAD, PROCESS, REPLICATION CLIENT, EVENT ON *.* TO 'backup'@'localhost';
Couldn't execute 'SHOW FUNCTION STATUS': Access denied 加了 --routines 但账号无 SELECT ON mysql.proc（5.7 之前） GRANT SELECT ON mysql.proc TO backup@localhost;
--single-transaction 备份出的数据不一致 库中有 MyISAM 表；或备份期间执行了 DDL MyISAM 转 InnoDB；备份窗口禁止 DDL；或改用 --lock-all-tables
备份文件比预期小很多 / 结尾无 Dump completed 磁盘写满导致截断（最常见的「假备份」） 备份前检查 df；脚本必须做体积与结尾标记校验
mysqldump: Error 1412: Table definition has changed --single-transaction 期间该表被 DDL 重做备份，避开 DDL 窗口
xtrabackup 报 Can't connect to local MySQL server through socket socket 路径不对 显式 -S /var/lib/mysql/mysql.sock，或 --defaults-file 指对配置
xtrabackup 日志无 completed OK! 但目录有文件 备份实际失败 脚本必须用 grep "completed OK!" 判断，不能只看返回码
xtrabackup 报 xtrabackup: error: The transaction log ... too small redo log 太小，备份期间日志被覆盖（写入量大 + innodb_log_file_size 小） 调大 innodb_log_file_size（如 1G）；或用 --parallel 加快备份速度
备份把磁盘 IO 打满，业务变慢 顺序大 IO 抢带宽，并挤占 Buffer Pool xtrabackup --throttle=200、--parallel 减小；ionice -c2 -n7、nice -n 19；改到从库备份
mysqldump 备份 GTID 实例后无法恢复 dump 中写入了 SET @@GLOBAL.GTID_PURGED 备份时加 --set-gtid-purged=OFF

6.5.2 恢复阶段

现象 / 报错 原因 解决
ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci' 备份来自 MySQL 8.0，恢复到 5.7/MariaDB（8.0 新增排序规则低版本不识别） ① 在源库用低版本兼容字符集重新导出；② sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g; s/CHARSET=utf8mb4/CHARSET=utf8/g' all.sql；③ 升级目标库版本。根本原则：只能低版本 → 高版本恢复，不能反向
Unknown collation: 'utf8mb4_unicode_520_ci' 5.6+ 导出恢复到 5.5 同上，sed 替换为 utf8mb4_unicode_ci 或 utf8_general_ci
ERROR 1064 You have an error in your SQL syntax 高版本语法（如 8.0 的 DEFAULT (expr)、ROW_FORMAT、窗口函数、CHECK 约束）在低版本无法解析 手工改 SQL；或用源版本客户端加 --compatible=mysql40/mysql5 导出
中文乱码（显示 ??? 或 测试） 导出与导入字符集不一致；dump 文件缺 SET NAMES 导出加 --default-character-set=utf8；导入前 SET NAMES utf8; 或 mysql --default-character-set=utf8 < all.sql；确认 SHOW VARIABLES LIKE 'character_set%' 三端一致；确认 dump 文件实际编码 file all.sql
ERROR 1005/1215: Cannot add foreign key constraint 子表先于父表恢复；或引擎/类型/字符集不一致 恢复前 SET FOREIGN_KEY_CHECKS=0;（mysqldump 默认已加，用 --compact 导出的文件会缺这行）
ERROR 1062: Duplicate entry for key 'PRIMARY' 目标表已有数据，恢复的备份不含 DROP TABLE（用了 --skip-add-drop-table 或 -t） 先清空目标表/库；或用 --insert-ignore/--replace 重新导出
ERROR 2006: MySQL server has gone away 单条 SQL 超过 max_allowed_packet（--extended-insert 合并出的超长 INSERT） 服务端 SET GLOBAL max_allowed_packet=512M; 并写进配置重启；客户端 mysql --max-allowed-packet=512M；导出时减小 --net-buffer-length
恢复极慢（几十小时） 逐条 INSERT + 逐行维护索引 + 每条写 binlog + 每次提交 fsync 见 §2.4.3：SET SQL_LOG_BIN=0、FOREIGN_KEY_CHECKS=0、UNIQUE_CHECKS=0、AUTOCOMMIT=0、innodb_flush_log_at_trx_commit=2、sync_binlog=0、调大 innodb_buffer_pool_size；大库改用物理备份
binlog 找不到对应 position ① 位置取到了事件中间；② 该 binlog 已被 PURGE/expire_logs_days 删除；③ 找错了日志文件 ① 用 SHOW BINLOG EVENTS 或 mysqlbinlog 确认事件边界，--stop-position 取 BEGIN 的 Pos、--start-position 取 COMMIT 的 End_log_pos；② 从异地归档目录找；③ 用 --start-datetime 先粗定位文件，再用 position 精确定位
mysqlbinlog: Error in Log_event::read_log_event(): 'Sanity check failed' position 不在事件边界，或日志文件损坏 校正 position；--verify-binlog-checksum 检查损坏；5.6+ 解析 5.5 日志需加 --binlog-checksum 兼容处理
mysqlbinlog \| mysql 执行后数据没变 GTID 模式下事务被判定为「已执行」而跳过 加 --skip-gtids
ERROR 1236: Could not find first log file name in binary log index file 从库要的 binlog 已被主库删除 重建从库（用全备 + 位点）；今后 expire_logs_days 设大、PURGE 前检查从库位点
磁盘空间不足（恢复中途失败） 未评估恢复所需空间：数据 + 索引 + redo + 临时文件 恢复前确保剩余空间 ≥ 备份解压后体积 × 2~3；把 tmpdir 指到大盘；分库分表逐个恢复
物理恢复后启动失败，Permission denied / Can't open and lock privilege tables 属主/权限/SELinux 未恢复 chown -R mysql:mysql /var/lib/mysql、chmod 750、restorecon -Rv /var/lib/mysql；看 /var/log/mysqld.log
物理恢复后启动失败，InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT 忘了 --prepare，或增量合并顺序/--apply-log-only 用错 严格按 §5.3.5 顺序重做 prepare
克隆实例后主从复制报 UUID 冲突 物理拷贝带走了 auto.cnf 删除新实例的 /var/lib/mysql/auto.cnf 后重启，自动生成新 UUID
恢复后存储过程/触发器/事件都不见了 备份时没加 --routines --triggers --events 从源库补导；今后模板固定加这三个参数
恢复后用户和权限丢失 只恢复了业务库，没恢复 mysql 库 用 -A 全备；或单独 mysqldump -B mysql，恢复后 FLUSH PRIVILEGES;；也可用 pt-show-grants 单独备份权限

6.5.3 关键参数速查（排错时常用）

-- 连接与包大小
SHOW VARIABLES LIKE 'max_allowed_packet';      -- 建议 64M~512M
SHOW VARIABLES LIKE 'net_read_timeout';        -- 默认 30，备份/恢复建议 3600
SHOW VARIABLES LIKE 'net_write_timeout';       -- 默认 60，建议 3600
SHOW VARIABLES LIKE 'wait_timeout';            -- 默认 28800
SHOW VARIABLES LIKE 'interactive_timeout';
-- 一致性与性能
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';   -- 生产 1
SHOW VARIABLES LIKE 'sync_binlog';                       -- 生产 1
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
-- 字符集
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
-- 空间占用（找出大表，评估备份耗时与空间）
SELECT table_schema, table_name,
       ROUND(data_length/1024/1024,2) AS data_mb,
       ROUND(index_length/1024/1024,2) AS index_mb,
       ROUND((data_length+index_length)/1024/1024,2) AS total_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql','sys')
ORDER BY (data_length+index_length) DESC LIMIT 10;
# /etc/my.cnf 中与备份恢复相关的推荐配置
[mysqld]
max_allowed_packet = 128M
net_read_timeout = 3600
net_write_timeout = 3600
innodb_file_per_table = 1        # 必须开，否则无法单表恢复
log_bin = mysql-bin
server_id = 1
binlog_format = ROW
sync_binlog = 1
expire_logs_days = 15
innodb_flush_log_at_trx_commit = 1

[mysqldump]
quick
max_allowed_packet = 512M
single-transaction
default-character-set = utf8

[mysql]
default-character-set = utf8
safe-updates                     # 无 WHERE 的 UPDATE/DELETE 直接报错，防误删

6.6 备份恢复命令速查

目标 命令
全库热备（InnoDB） mysqldump -uroot -p --single-transaction --master-data=2 -R -E --triggers -A > all.sql
全库温备（含 MyISAM） mysqldump -uroot -p --lock-all-tables --master-data=2 -A > all.sql
单库备份 mysqldump -uroot -p -B m2 > m2.sql
单表备份 mysqldump -uroot -p m2 a > a.sql
只备结构 / 只备数据 mysqldump -uroot -p -A -d / mysqldump -uroot -p -B m2 -t
压缩备份 mysqldump -uroot -p -A \| gzip > all.sql.gz
恢复全库 mysql -uroot -p < all.sql
恢复到指定库 mysql -uroot -p m2 < a.sql
交互恢复 mysql> SOURCE /myback/all.sql;
查看 binlog 列表 / 当前位点 SHOW BINARY LOGS; / SHOW MASTER STATUS;
查看 binlog 事件 SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 245 LIMIT 20;
切换 / 清理 binlog FLUSH LOGS; / PURGE BINARY LOGS TO 'mysql-bin.000005';
解析 ROW 格式 binlog mysqlbinlog --base64-output=decode-rows -v mysql-bin.000002
按位置恢复 mysqlbinlog --start-position=245 --stop-position=851 mysql-bin.000002 \| mysql -uroot -p
按时间恢复 mysqlbinlog --start-datetime='...' --stop-datetime='...' mysql-bin.000002 \| mysql -uroot -p
binlog 实时远程归档 mysqlbinlog -R --raw --stop-never -h主库 -urepl -p mysql-bin.000001
多线程逻辑备份 / 恢复 mydumper -u root -p -B m2 -t 8 -c -o /myback / myloader -u root -p -d /myback -o -t 8
物理全备 xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/myback --parallel=4 -ubkp -p
物理增备 xtrabackup --backup --target-dir=/inc/inc1 --incremental-basedir=/myback -ubkp -p
物理 prepare（非最后一次） xtrabackup --prepare --apply-log-only --target-dir=/myback --incremental-dir=/inc/inc1
物理恢复 xtrabackup --copy-back --target-dir=/myback; chown -R mysql:mysql /var/lib/mysql
innobackupex 全备 / 恢复 innobackupex --defaults-file=/etc/my.cnf /myback / --apply-log → --copy-back
单表表空间导出 / 导入 FLUSH TABLES a FOR EXPORT; → 拷 .ibd/.cfg → ALTER TABLE a DISCARD/IMPORT TABLESPACE;
冷备 systemctl stop mysqld; tar -zcvpf mysql.tar.gz -C /var/lib mysql; systemctl start mysqld
校验备份 gzip -t f.gz、zcat f.gz \| tail -1 \| grep "Dump completed"、grep "completed OK!" xtrabackup.log

6.7 生产红线

1. 严禁在生产库上直接执行 mysql < all_backup.sql（会 DROP 掉现有数据）。
2. 严禁用 rm 删除 binlog，必须用 PURGE BINARY LOGS。
3. 严禁在主库上执行 RESET MASTER。
4. 严禁只有一份备份、只在本机保存备份。
5. 严禁跳过恢复演练——没演练过的备份视为不存在。
6. 严禁在没有 --single-transaction 的情况下对生产 InnoDB 库做 mysqldump（会锁表）。
7. 严禁高版本备份恢复到低版本（collation/语法不兼容）。
8. 严禁在备份窗口内执行 DDL。
9. 物理恢复后必须 chown -R mysql:mysql 并 restorecon。
10. 恢复期间临时调低的 innodb_flush_log_at_trx_commit / sync_binlog 必须改回 1。
11. 误操作发生后第一动作是「止损 + 保护现场（备份当前 binlog 与 datadir）」，不是马上恢复。
12. 备份脚本中的密码文件权限必须 600，备份目录必须 700。

七、练习

7.1 基础题

  1. 说明物理备份与逻辑备份的区别，并各举两个工具。
  2. 全量备份、增量备份、差异备份的区别是什么？各自的恢复链有多长？
  3. 冷备、温备、热备分别对应数据库的什么状态？MyISAM 能做热备吗？为什么？
  4. RPO 与 RTO 分别由什么决定？只做每日全备、不保留 binlog 的 RPO 是多少？
  5. mysqldump 加 -B 与不加 -B 导出的文件有什么区别？恢复方式有何不同？
  6. --single-transaction 与 --lock-all-tables 有什么区别？对 MyISAM 表分别是什么效果？
  7. binlog 的三种格式是什么？生产为什么推荐 ROW？
  8. 为什么不能用 rm 删除 binlog 文件？正确做法是什么？
  9. 判断 mysqldump 备份文件完整的标志是什么？判断 xtrabackup 备份成功的标志是什么？
  10. XtraBackup 增量备份合并时，--apply-log-only（或 --redo-only）什么时候要加、什么时候不能加？

7.2 实操题

  1. 在测试库中完成：建库 m2、建表 a、插入 2 行数据 → 全备（切换 binlog）→ 再插入 1 行 → 备份 binlog → drop database m2 → 完整恢复到 3 行数据。写出全部命令。
  2. 编写一个 shell 脚本，实现：每天全备、按日期命名、gzip 压缩、保留 7 天、失败发邮件、写日志。
  3. 写出对应的 crontab 配置：每天 2:00 执行上述脚本，每 10 分钟同步 binlog 到 192.168.2.200。
  4. 用 mysqldump 只导出 m2 库中 orders 表 2026 年 8 月的数据。
  5. 模拟误删：DELETE FROM m2.a; 后，用「全备 + binlog 跳过误操作」恢复，且保留误操作之后的正常数据。写出定位 position 与两段式重放的完整命令。
  6. 用 XtraBackup 完成「一次全备 + 两次增备 → 合并 → 恢复 → 验证」的完整链条。
  7. 用表空间传输把 m2.a 表从 A 实例搬到 B 实例。

7.3 排错题

  1. 恢复时报 ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'，原因与三种解决方案。
  2. 备份中途报 Got error: 2013: Lost connection to MySQL server during query，可能的两个原因及对应参数。
  3. 执行 mysqlbinlog --start-position=1000 ... | mysql 后数据没有任何变化，可能是什么原因？
  4. 物理恢复后 MySQL 启动失败，日志显示 Can't open and lock privilege tables: Permission denied，如何处理？
  5. 备份脚本每天都「成功」，但某天需要恢复时发现文件只有 3KB，如何在脚本层面提前发现这种问题？

八、练习参考答案

1. 物理备份直接拷贝数据文件（二进制），备份/恢复快、体积大、不可读、跨版本差；逻辑备份导出为 SQL 文本，慢、体积小、可读可编辑、跨版本跨引擎好。物理工具：cp/tar 冷备、LVM 快照、XtraBackup/innobackupex、mariabackup。逻辑工具：mysqldump、mysqlpump、mydumper、SELECT ... INTO OUTFILE。

2. 全量备份全部数据；增量备份「自上一次任意备份以来」的变化，体积最小但恢复需全备 + 所有增备（链条长，断一环即失败）；差异备份「自最近一次全备以来」的全部变化，体积居中，恢复只需全备 + 最后一次差备（2 步）。MySQL 中增量的载体通常是 binlog（逻辑）或 LSN 增量（XtraBackup）。

3. 冷备 = 数据库完全停止；温备 = 运行中但加全局读锁，只读不可写（FTWRL / --lock-all-tables）；热备 = 运行中读写均正常（--single-transaction 或 XtraBackup）。MyISAM 不能热备，因为它不支持事务、没有 MVCC 和 redo log，无法在不锁表的情况下取得一致性快照。

4. RPO（能丢多少数据）由备份/日志频率决定，binlog 实时归档可使 RPO≈0；RTO（多久恢复完）由备份方式与数据量决定，物理备份和热备从库能显著降低 RTO。只做每日全备且无 binlog，RPO 最坏为 24 小时。

5. 加 -B：dump 中含 CREATE DATABASE IF NOT EXISTS 与 USE，可直接 mysql < file 恢复，会恢复到原库名。不加 -B：只有建表与 INSERT，必须 mysql -uroot -p 目标库 < file 指定库，且库要先存在——好处是可以恢复到另一个库名，便于克隆和「恢复到临时库再回搬」。

6. --single-transaction 通过 START TRANSACTION WITH CONSISTENT SNAPSHOT 在 MVCC 快照中导出，全程不锁表，对 InnoDB 有效、对 MyISAM 无效（数据可能不一致）；--lock-all-tables 对全库加全局读锁（FTWRL）直到导出结束，对两种引擎都有效但全库只读。两者互斥。

7. STATEMENT（记 SQL 语句，体积小但含 NOW()/UUID()/RAND() 等不确定函数时不安全）、ROW（记每行前后镜像，绝对安全、可闪回、体积大）、MIXED（默认 SBR，不安全时自动切 RBR）。生产推荐 ROW，因为：数据一致性优先；改用 RC 隔离级别的前提；只有 ROW 能做闪回恢复；Canal 等订阅工具只支持 ROW。

8. rm 删除后 mysql-bin.index 中仍保留记录，导致 SHOW BINARY LOGS 与实际不符、PURGE 失败、从库找不到日志而复制中断（ERROR 1236）。正确做法：PURGE BINARY LOGS TO 'mysql-bin.000005'; 或 PURGE BINARY LOGS BEFORE '2026-08-15 00:00:00';，并在清理前检查所有从库已消费到的位点。

9. mysqldump 文件末尾必须有 -- Dump completed on ...（tail -1 all.sql | grep "Dump completed"）；xtrabackup 日志末尾必须有 completed OK!（grep "completed OK!" xtrabackup.log）。只看目录里有文件或只看返回码都不可靠。

10. 合并增量时，除最后一次之外的所有 prepare 都必须加 --apply-log-only / --redo-only（只应用 redo、不回滚未提交事务，以便继续合并后续增量）；最后一次绝不能加（此时才回滚未提交事务，使数据达到最终一致状态）。加错会导致数据不一致或无法再合并后续增量。

11.

mkdir /myback
mysql -uroot -p -e "create database m2; create table m2.a (id int(2),name char(10),sex char(1)); \
insert into m2.a values (1,'zhangsan','f'),(2,'lisi','m');"
mysqldump -uroot -p -B m2 -F > /myback/all.sql          # 全备并切换 binlog
mysql -uroot -p -e "insert into m2.a values (3,'wangwu','f');"
mysql -uroot -p -e "FLUSH LOGS;"                        # 让含新数据的日志封口
cp /var/lib/mysql/mysql-bin.000002 /myback              # 备份增量
mysql -uroot -p -e "drop database m2;"                  # 模拟误删
mysql -uroot -p < /myback/all.sql                       # 恢复全备（回到 2 行）
mysqlbinlog /myback/mysql-bin.000002 | mysql -uroot -p  # 应用增量（回到 3 行）
mysql -uroot -p -e "select * from m2.a;"                # 验证

12. 见 §6.1 的 /opt/scripts/mysql_backup.sh。要点：变量集中定义、log()/alert() 函数、前置检查磁盘与连通性、--single-transaction --master-data=2 -F -R -E --triggers、管道中用 ${PIPESTATUS[0]} 取 mysqldump 退出码、三重校验（返回码 + 体积 + gzip -t 与 Dump completed）、find -mtime +7 -delete 清理并做「至少剩 1 份」兜底、rsync 异地同步、脚本 chmod 700。

13.

0 2 * * * /bin/bash /opt/scripts/mysql_backup.sh >/dev/null 2>&1
*/10 * * * * /usr/bin/rsync -az /var/lib/mysql/mysql-bin.0* backup@192.168.2.200:/remote_backup/binlog/ >/dev/null 2>&1

注意：脚本内用绝对路径或 export PATH；crontab 里的 % 需转义（所以 date +%F 应放在脚本中）；防重叠可用 flock -n /tmp/bk.lock。

14.

mysqldump -uroot -p m2 orders \
  --where="create_time >= '2026-08-01' AND create_time < '2026-09-01'" \
  --single-transaction > /myback/orders_2026-08.sql

15.

# ① 止损与保护现场
mysql -uroot -p -e "SET GLOBAL read_only=ON; FLUSH LOGS;"
cp -a /var/lib/mysql/mysql-bin.* /safe/
# ② 定位误操作事务的起止位置
mysql -uroot -p -e "SHOW BINLOG EVENTS IN 'mysql-bin.000002';"
mysqlbinlog --base64-output=decode-rows -v /safe/mysql-bin.000002 | grep -n -B5 -i "DELETE FROM"
#    假设：误操作 BEGIN 的 Pos = 851，其 Xid 的 End_log_pos = 1111
# ③ 恢复全备
mysql -uroot -p -e "DROP DATABASE IF EXISTS m2;"
mysql -uroot -p < /myback/all.sql
# ④ 两段式重放，跳过 851~1111 这个误操作事务
mysqlbinlog --start-position=245  --stop-position=851 /safe/mysql-bin.000002 > /myback/inc1.sql
mysqlbinlog --start-position=1111                     /safe/mysql-bin.000002 > /myback/inc2.sql
grep -i -E "delete from|drop |truncate" /myback/inc1.sql /myback/inc2.sql   # 人工确认
mysql -uroot -p < /myback/inc1.sql
mysql -uroot -p < /myback/inc2.sql
# ⑤ 验证后放开写入
mysql -uroot -p -e "select count(*) from m2.a; SET GLOBAL read_only=OFF;"

关键：--stop-position 取误操作事务 BEGIN 的 Pos，--start-position 取其 COMMIT(Xid) 的 End_log_pos；位置必须落在事件边界。GTID 模式下还需加 --skip-gtids。

16.

# 全备
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/myback -uroot -p -S /var/lib/mysql/mysql.sock
cat /myback/xtrabackup_checkpoints                     # to_lsn 记下
# 第一次增备
mysql -uroot -p -e "create table a.a2 (id int);"
xtrabackup --backup --target-dir=/inc/inc1 --incremental-basedir=/myback -uroot -p
# 第二次增备
mysql -uroot -p -e "create table a.a3 (id int);"
xtrabackup --backup --target-dir=/inc/inc2 --incremental-basedir=/inc/inc1 -uroot -p
# 合并（除最后一次外都加 --apply-log-only）
systemctl stop mysqld ; mv /var/lib/mysql /var/lib/mysql.bak
xtrabackup --prepare --apply-log-only --target-dir=/myback
xtrabackup --prepare --apply-log-only --target-dir=/myback --incremental-dir=/inc/inc1
xtrabackup --prepare                  --target-dir=/myback --incremental-dir=/inc/inc2
# 恢复
mkdir -p /var/lib/mysql
xtrabackup --copy-back --target-dir=/myback
chown -R mysql:mysql /var/lib/mysql ; restorecon -Rv /var/lib/mysql
systemctl start mysqld
mysql -uroot -p -e "use a; show tables;"               # 应看到 a1 a2 a3

检查链条完整性：核对相邻备份的 to_lsn 与 from_lsn 是否首尾相接。

17.

-- A 实例
FLUSH TABLES m2.a FOR EXPORT;      -- 本会话保持不退出
cp /var/lib/mysql/m2/a.ibd /var/lib/mysql/m2/a.cfg /myback/
scp /myback/a.ibd /myback/a.cfg root@B:/tmp/
-- A 实例
UNLOCK TABLES;
-- B 实例：建结构完全一致的空表
CREATE TABLE m2.a (id INT(2), name CHAR(10), sex CHAR(1)) ENGINE=InnoDB;
ALTER TABLE m2.a DISCARD TABLESPACE;
cp /tmp/a.ibd /tmp/a.cfg /var/lib/mysql/m2/ ; chown mysql:mysql /var/lib/mysql/m2/a.*
ALTER TABLE m2.a IMPORT TABLESPACE;
CHECK TABLE m2.a; SELECT COUNT(*) FROM m2.a;

前提：innodb_file_per_table=ON，两端表结构、行格式、字符集完全一致。

18. 原因：utf8mb4_0900_ai_ci 是 MySQL 8.0 新增的排序规则，5.7/MariaDB 不认识——备份从高版本恢复到了低版本。三种解决：① 在源库用 --default-character-set=utf8 或 --compatible 重新导出（推荐从源头解决）；② sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' all.sql 替换后再恢复；③ 把目标库升级到 8.0。根本原则：只能低版本 → 高版本恢复。

19. ① 单条 SQL 或单行数据超过 max_allowed_packet（常见于 BLOB 大字段）→ 加 --max-allowed-packet=512M，服务端也调大；② 读写超时或网络中断 → 服务端调大 net_read_timeout、net_write_timeout（如 3600）、wait_timeout，并确认 --quick 生效、避开网络抖动；大表可用 --where 分批导出。

20. 最常见原因是 GTID 模式下这些事务的 GTID 已存在于 gtid_executed 中，MySQL 认为已执行而直接跳过 → 加 --skip-gtids。其他可能：--start-position 之后的区间本身没有该库的变更；用了 --database 过滤而语句是跨库执行的（STATEMENT 格式下会被漏掉）；sql_log_bin=0 的会话产生的变更本就不在 binlog 中。

21. 属主/权限/SELinux 未恢复。处理：

chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql
restorecon -Rv /var/lib/mysql          # 数据目录非默认位置时先 semanage fcontext -a -t mysqld_db_t
tail -50 /var/log/mysqld.log           # 复查错误日志
systemctl start mysqld

22. 在脚本中做三重校验并告警（见 §6.1 ⑤）：① 用 ${PIPESTATUS[0]} 判断 mysqldump 真实退出码（管道下 $? 只反映 gzip）；② 判断文件体积是否小于阈值 MIN_SIZE，并可与前一天体积做同比（缩小超过 50% 告警）；③ gzip -t 校验压缩完整性 + zcat | tail -1 | grep "Dump completed" 校验结尾标记。另外：备份前检查磁盘剩余空间、记录 MD5、把「最新备份年龄/体积/耗时」接入监控告警，并坚持季度恢复演练。

本文档小结：备份的核心公式是「全量备份 + binlog = 可恢复到任意时间点」。小库用 mysqldump --single-transaction --master-data=2，大库用 xtrabackup 全量 + 增量，binlog 必须实时归档到异地。所有方案都必须经过恢复演练验证——没有验证过恢复的备份等于没有备份。

最后修改：2026 年 09 月 20 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏