DevNull 知识库

Appearance

12｜备份与恢复 ​

数据存在 MySQL 里。硬盘可能坏、有人可能误删表、程序 bug 可能写脏数据。这些发生了，怎么把数据找回来？靠备份。

备份的目标不是生成文件——是在需要的时候能把数据恢复到指定状态。备份文件存在、能恢复、恢复后数据正确，这三件事要分开验证。本篇讲怎么备份、怎么恢复、怎么恢复到某个时间点。

一、几种备份方式 ​

方式适合特点
mysqldump小库、跨版本逻辑备份（导出 SQL），几百 GB 恢复慢
XtraBackup大库、追求速度物理备份（拷文件），快
binlog 回放时间点恢复不单独当备份，配合全量备份用
存储快照快速备份克隆要处理数据库文件一致性

常规策略是全量备份 + binlog，组合起来能恢复到任意时间点：

text
昨晚 2:00 的全量备份 + 全量之后的所有 binlog
= 能恢复到今天 11:00 误删前的状态

全量备份是基础（恢复到昨晚），binlog 补上昨晚到现在的所有变更（恢复到现在或某个时间点）。

二、mysqldump 逻辑备份 ​

最常用的备份工具，导出 SQL 语句：

bash
# 单库备份
mysqldump -uroot -p \
  --single-transaction \
  --routines --triggers --events \
  app_db > app_db.sql

几个关键参数：

  • --single-transaction — InnoDB 一致性快照，不锁表（不用这个会锁表影响业务）
  • --routines --triggers --events — 导出存储过程、触发器、事件
  • 压缩：| gzip > app_db-$(date +%F).sql.gz

记录 binlog 位点（为时间点恢复准备）：

bash
mysqldump -uroot -p --single-transaction --master-data=2 app_db > app_db.sql

--master-data=2 在备份文件头部记下当前 binlog 文件和位置。恢复时从这个位点开始回放 binlog，衔接全量备份之后的数据。

恢复 ​

bash
# 先建库
mysql -uroot -p -e "CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4;"

# 导入
mysql -uroot -p app_db < app_db.sql

# 压缩文件
gunzip -c app_db.sql.gz | mysql -uroot -p app_db

恢复前确认目标库是空的或可以覆盖——导入会往现有表里插，重复主键报错。

三、XtraBackup 物理备份 ​

大库用 mysqldump 恢复太慢——导出再导入几百万行要几小时。XtraBackup 直接拷贝数据文件，快得多：

bash
# 全量备份
xtrabackup --backup --target-dir=/backup/full \
  --user=root --password=xxx

# prepare（让备份文件一致可用）
xtrabackup --prepare --target-dir=/backup/full

# 恢复（拷回数据目录）
xtrabackup --copy-back --target-dir=/backup/full \
  --datadir=/data/mysql/data

prepare 这步很关键——物理备份时数据页可能是不一致的（备份过程中数据在变），prepare 用 redo log 把它恢复到一致状态。没 prepare 的备份不能直接用。

XtraBackup 支持增量备份——只备份上次全量后变化的数据，省空间和时间：

bash
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full

四、时间点恢复（PITR） ​

最常见的事故：上午有人误删了一张表，要恢复到删之前的状态。

典型流程：

text
1. 确认误删发生时间（比如 10:30）
2. 找到最近一次全量备份（昨晚 2:00）
3. 在临时实例恢复全量备份
4. 回放 binlog 到误删前一刻（10:29）
5. 从临时实例导出误删的表
6. 导入生产

为什么在临时实例恢复，不直接在生产恢复？因为生产可能还在跑、有人在用。直接恢复会覆盖现有数据。临时实例恢复到目标时间点，导出需要的那部分，再小心导入生产。

binlog 回放：

bash
mysqlbinlog \
  --start-datetime='2026-05-21 02:00:00' \
  --stop-datetime='2026-05-21 10:29:00' \
  /data/mysql/logs/mysql-bin.000123 \
  | mysql -uroot -p

按时间范围回放 binlog。更精确可以按位点（--start-position / --stop-position），精确到某条事务。

--stop-datetime 要停在误删前一刻——停早了数据不全，停晚了把误删也回放了。

五、备份账号 ​

备份不用 root，建专门的备份账号：

sql
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'BackupPassword!';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'backup'@'localhost';

REPLICATION CLIENT 让备份工具能看到 binlog 位点。LOCK TABLES 给非 InnoDB 表用（InnoDB 用 --single-transaction 不锁表）。账号限制 localhost——备份在本机跑，不用远程连。

密码存在配置文件，权限 600：

bash
# /root/.mysql-backup.cnf
[client]
user=backup
password=BackupPassword!
bash
chmod 600 /root/.mysql-backup.cnf

不把密码写命令行——命令行密码会被 ps 看到、被 history 记录。

六、备份校验和演练 ​

备份了不等于能恢复。 备份文件可能损坏、恢复流程可能记错。定期演练：

bash
# 校验备份文件完整性
gunzip -t app_db.sql.gz      # 解压测试，不报错说明文件完整

# 定期恢复演练——在测试机恢复一份备份，确认数据正常
mysql -uroot -p test_db < app_db.sql
SELECT COUNT(*) FROM servers;   -- 行数对不对

生产环境至少每季度做一次完整的恢复演练——从备份恢复、验证数据。出事时才发现备份坏了或恢复流程不会，就晚了。

七、定时备份脚本 ​

把备份做成 cron 定期跑：

bash
#!/bin/bash
# /usr/local/sbin/mysql-backup.sh
BACKUP_DIR=/backup/mysql
KEEP_DAYS=30

mysqldump --defaults-file=/root/.mysql-backup.cnf \
  --single-transaction --master-data=2 \
  --all-databases | gzip > "$BACKUP_DIR/all-$(date +%F).sql.gz"

# 清理过期备份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete

cron 里每天跑：

bash
30 2 * * * /usr/local/sbin/mysql-backup.sh

备份之后 ​

能备份、能恢复、能恢复到时间点了。但单机 MySQL 是单点——机器挂了服务就停。下一篇讲主从复制——把数据同步到另一台，一台挂了另一台顶上。