一、为什么要做数据库备份：先想清楚三个问题
任何"跑得稳"的 MySQL 服务，背后都有一整套备份恢复预案。备份不是把数据拷出来那么简单，动手之前先回答三个问题：
- RPO（可容忍的数据丢失量）：最多能丢多少数据？一小时、一分钟还是零丢失？这决定了全量备份的周期和是否要开 binlog 增量。
- RTO（可容忍的恢复时间）：故障后多久必须恢复？这决定了用逻辑备份（mysqldump，恢复慢、体积大）还是物理备份（xtrabackup，恢复快）。
- 备份验证方式：备份文件不经过恢复演练，等于没有备份。定期在测试实例上还原，是检验备份是否可用的唯一标准。
备份的类型划分：
逻辑备份：mysqldump / mysqlpump —— 导出 SQL 语句，跨版本、跨架构通用，适合中小库 物理备份：Percona XtraBackup —— 直接拷贝数据文件，速度快、恢复快，适合大库 全量备份：备份当前完整数据 增量备份：基于 binlog 记录上次备份之后的所有变更，配合全量实现"恢复到任意时刻"
二、环境准备与备份基线检查
先确认数据库版本、数据目录占用和磁盘剩余空间，避免备份中途把磁盘写满。
mysql --version mysql -uroot -p -e "SHOW VARIABLES LIKE 'datadir'; SHOW VARIABLES LIKE 'server_id'; SHOW VARIABLES LIKE 'log_bin';" du -sh /var/lib/mysql df -h /var/lib/mysql /backup
建议为备份单独规划挂载点（独立磁盘或对象存储挂载），不要把备份文件与数据目录放在同一块盘上，否则数据盘故障时备份同样全军覆没。
mkdir -p /backup/mysql/{full,binlog}
chown mysql:mysql /backup/mysql
三、mysqldump 逻辑备份实战
mysqldump 是最常用的逻辑备份工具。InnoDB 表使用 --single-transaction 可以在不锁表的前提下获得一致性快照（依赖 MVCC，备份期间 DDL 仍可能造成数据不一致，业务高峰期应避免执行 DDL）。
3.1 单库备份
mysqldump -uroot -p \ --single-transaction \ --routines --triggers --events \ --set-gtid-purged=OFF \ --databases myblog > /backup/mysql/full/myblog_$(date +%F).sql
3.2 全库备份并压缩
mysqldump -uroot -p \ --single-transaction \ --routines --triggers --events \ --master-data=2 --flush-logs \ --all-databases | gzip > /backup/mysql/full/all_$(date +%F_%H%M).sql.gz # 查看备份文件中记录的主库 binlog 位点（做增量恢复的起点） zcat /backup/mysql/full/all_2026-10-04.sql.gz | grep -m1 "CHANGE MASTER TO"
参数说明：
--master-data=2：在备份文件中注释记录 binlog 文件名和位点，是做增量恢复的关键锚点。--flush-logs：备份前滚动一个新的 binlog，方便增量文件分段管理。--set-gtid-purged=OFF：GTID 环境恢复到非 GTID 实例时避免报错。--routines --triggers --events：默认不带存储过程、触发器、事件，必须显式加上，否则恢复后应用会缺函数。
3.3 只备份表结构
mysqldump -uroot -p --no-data --routines --triggers --events --all-databases > schema_only.sql
四、定时自动备份脚本与保留策略
手动备份靠不住，写一个带保留策略的脚本交给 cron。脚本要点：备份失败即发告警、只保留最近 N 份、删除前校验文件完整性。
#!/bin/bash
# /usr/local/bin/mysql_backup.sh
set -euo pipefail
DB_USER="root"
DB_PASS="你的密码" # 生产环境建议用 my.cnf 中 [client] 段配置，避免密码进命令行
BACKUP_DIR="/backup/mysql/full"
KEEP_DAYS=7
STAMP=$(date +%F_%H%M)
LOG="/var/log/mysql_backup.log"
log() { echo "$(date '+%F %T') $*" >> "$LOG"; }
log "备份开始"
mysqldump -u"$DB_USER" -p"$DB_PASS" \
--single-transaction --routines --triggers --events \
--master-data=2 --all-databases | gzip > "$BACKUP_DIR/all_$STAMP.sql.gz" \
&& log "备份完成: all_$STAMP.sql.gz" \
|| { log "备份失败!"; exit 1; }
# 校验 gzip 完整性
gzip -t "$BACKUP_DIR/all_$STAMP.sql.gz" || { log "备份文件损坏"; exit 1; }
# 清理 7 天前的备份
find "$BACKUP_DIR" -name "all_*.sql.gz" -mtime +"$KEEP_DAYS" -delete
log "清理完成，当前备份: $(ls "$BACKUP_DIR" | wc -l) 份"
# crontab -e 每天凌晨 2 点执行 0 2 * * * /usr/local/bin/mysql_backup.sh # 每 5 分钟滚动一次 binlog，并定期把增量传走（见第五节）
五、binlog 增量备份：把数据恢复到任意时刻
逻辑备份只能恢复到备份时刻，两次全量之间的数据要靠 binlog 补上。先确认已开启 binlog：
mysql -uroot -p -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format';"
若未开启，编辑 /etc/my.cnf 的 [mysqld] 段并重启：
[mysqld] server-id = 1 log-bin = /var/log/mysql/mysql-bin binlog_format = ROW expire_logs_days = 7 max_binlog_size = 256M gtid_mode = ON enforce_gtid_consistency = ON
增量备份脚本（配合全量备份，全量后先 flush logs 让增量从新文件开始）：
#!/bin/bash # /usr/local/bin/mysql_binlog_backup.sh —— 建议每小时执行一次 mysqladmin -uroot -p"$DB_PASS" flush-logs BINLOG_DIR="/var/log/mysql" BACKUP_DIR="/backup/mysql/binlog" cd "$BINLOG_DIR" for f in mysql-bin.*; do [ "$f" = "mysql-bin.index" ] && continue [ -f "$BACKUP_DIR/$f" ] && continue cp "$f" "$BACKUP_DIR/" && echo "$(date +%F_%T) 已备份 $f" >> /var/log/mysql_backup.log done
六、xtrabackup 物理备份：大库的正确打开方式
数据量上了 100G，mysqldump 导出再导入耗时以小时计，此时用 Percona XtraBackup 做物理备份更合适，它利用 InnoDB 崩溃恢复机制做到在线热备，备份与恢复都快一个量级。
6.1 安装（Ubuntu 22.04 示例）
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb dpkg -i percona-release_latest.generic_all.deb percona-release setup ps80 apt update && apt install -y percona-xtrabackup-80 xtrabackup --version
6.2 全量备份与 prepare
xtrabackup --backup \ --target-dir=/backup/mysql/xtra_full \ --user=root --password=你的密码 # prepare：对备份应用重做日志使其一致，恢复前必须执行 xtrabackup --prepare --target-dir=/backup/mysql/xtra_full
6.3 增量备份（基于上次全量或增量）
xtrabackup --backup \ --target-dir=/backup/mysql/xtra_inc1 \ --incremental-basedir=/backup/mysql/xtra_full \ --user=root --password=你的密码 # 合并增量：依次把增量应用到全量上 xtrabackup --prepare \ --target-dir=/backup/mysql/xtra_full \ --incremental-dir=/backup/mysql/xtra_inc1
6.4 恢复
# 停止 MySQL 并清空数据目录 systemctl stop mysql mv /var/lib/mysql /var/lib/mysql.bak mkdir -p /var/lib/mysql # 把备份拷回数据目录并修复权限 xtrabackup --copy-back --target-dir=/backup/mysql/xtra_full chown -R mysql:mysql /var/lib/mysql systemctl start mysql mysql -uroot -p -e "SELECT 1;"
七、恢复演练：误删数据后的完整救援流程
以一个真实场景为例：上午 10:00 误执行了 DELETE FROM orders WHERE status='pending'，凌晨 2:00 有全量备份，binlog 增量备份到 9:00。目标：恢复到 9:59:59。
7.1 恢复全量备份
zcat /backup/mysql/full/all_2026-10-04.sql.gz | mysql -uroot -p
7.2 从全量位点应用 binlog 到误操作前一刻
# 第一步：找出全量备份记录的位点 grep -m1 "CHANGE MASTER TO" /backup/mysql/full/all_2026-10-04.sql # 示例输出: CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=154; # 第二步：应用 mysql-bin.000018 从位点 154 开始 mysqlbinlog --start-position=154 /backup/mysql/binlog/mysql-bin.000018 | mysql -uroot -p # 第三步：应用后续 binlog 文件，并止于误操作之前 mysqlbinlog \ --start-position=4 \ --stop-datetime="2026-10-04 09:59:59" \ /backup/mysql/binlog/mysql-bin.000019 /backup/mysql/binlog/mysql-bin.000020 \ | mysql -uroot -p # 更精确：先用 --stop-position 或 --stop-datetime 试出 DELETE 语句的准确位置 mysqlbinlog --start-position=154 /backup/mysql/binlog/mysql-bin.000019 | grep -n "DELETE FROM orders" | head -5
7.3 恢复后校验
mysql -uroot -p -e "SELECT COUNT(*) FROM orders WHERE status='pending';" mysql -uroot -p -e "CHECK TABLE orders;"
注意：恢复演练一定要在独立测试实例上做，恢复完成后对比业务表行数与业务侧对账，确认无误再谈正式恢复。
八、常见报错与排错方法
| 报错 / 现象 | 原因 | 解决办法 |
|---|---|---|
| mysqldump: Got error: 1045: Access denied | 账号缺少 LOCK TABLES、RELOAD 等权限 | GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, PROCESS ON *.* TO 'backup'@'localhost'; 建议用专用备份账号而非 root |
| mysqldump: Error 1412: Table definition changed | 备份期间执行了 DDL，快照一致性被破坏 | 备份期间避免 DDL；或用 --single-transaction 且把 DDL 窗口错开 |
| zcat: invalid compressed data | 备份文件未完整写入（磁盘满 / 中断） | 检查 df -h；脚本中加入 gzip -t 完整性校验；保留多天版本以便回退 |
| ERROR 1781: GTID_NEXT cannot be set to UUID:... | GTID 环境备份/恢复时 GTID 集不一致 | 备份加 --set-gtid-purged=OFF；或恢复前 SET GLOBAL gtid_purged=... 同步 GTID 集 |
| xtrabackup: failed to read redo log | 未执行 prepare 或 prepare 不完整 | 恢复前必须 xtrabackup --prepare；增量备份先合并再 prepare |
| mysqlbinlog: unknown variable 'default-character-set=utf8mb4' | my.cnf 中的字符集参数被 mysqlbinlog 误读 | 改用 mysqlbinlog --no-defaults --set-charset=utf8mb4 ... |
| 恢复后外键/自增列错乱 | 只导出了表数据未按依赖顺序导入 | 全库备份默认处理依赖；单表恢复用 --foreign-key-checks=0 并随后修复 |
九、把备份体系固化：Checklist
□ 已开启 binlog，ROW 格式，保留 7 天 □ 每天凌晨全量备份（mysqldump 或 xtrabackup） □ 每小时增量备份 binlog □ 备份文件异地存放（rclone 推对象存储 / 另一台机器） □ 脚本带完整性校验和失败告警 □ 每月至少一次在测试实例上完整恢复演练 □ 备份文件加密（敏感数据用 gpg 或 openssl 加密后落盘）
数据库备份不是"做了就行"，而是"能恢复才算数"。把备份、增量、异地、演练做成一条完整的链路，才能在故障真正来临时从容应对。
