数据库备份和普通文件备份不一样：直接 cp 数据文件是错的做法（InnoDB 文件在写入时拷贝会损坏），mysqldump 也有讲究。

这篇讲 MySQL 备份的完整方案：逻辑备份（dump）、物理备份（xtrabackup）、binlog 增量恢复，以及怎么验证备份可用、怎么按时间点恢复。

需要云服务器可以看看财富云计算：支持快照，NAT 型无独立公网 IP，价格比独立 IP 机型省一半以上，一键开服面板，主流游戏快速部署。

一、备份方式对比：先选对方案

个人站/小企业（90% 场景）：mysqldump + 每日快照，够了。
中大型：XtraBackup 全量 + binlog 增量，恢复灵活。

二、逻辑备份：mysqldump 完全指南

基础命令

# 备份单个库
mysqldump -u root -p blogdb > blogdb-$(date +%F).sql

# 备份所有库
mysqldump -u root -p --all-databases > all-$(date +%F).sql

# 备份单表
mysqldump -u root -p blogdb wp_posts > wp_posts-$(date +%F).sql

关键参数（别用裸命令）

mysqldump -u backup_user -p \
  --single-transaction \        # InnoDB 热备关键:一个事务内一致快照,不锁表
  --quick \                     # 逐行读,不全加载进内存(大表必须)
  --routines \                  # 包含存储过程和函数
  --triggers \                  # 包含触发器(默认开,显式写清楚)
  --events \                    # 包含定时事件
  --hex-blob \                  # 二进制数据用十六进制导出(防乱码)
  --default-character-set=utf8mb4 \
  --all-databases \
  | gzip > /backup/mysql-$(date +%F).sql.gz

--single-transaction 原理：启动一个一致性读取事务，导出期间其他会话的写入不阻塞、导出的数据也是某个时间点的一致快照。只对 InnoDB 有效，MyISAM 表还是会锁。

备份账号（不要用 root）

-- 创建专用备份账号,只要读权限
CREATE USER 'backup'@'localhost' IDENTIFIED BY '强密码';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

把密码放配置文件，避免命令行暴露（ps 能看到的）：

# ~/.my.cnf(权限设 600)
[client]
user=backup
password=你的密码

chmod 600 ~/.my.cnf

之后 mysqldump --all-databases 就不用带账号密码了。

排除大表（可选的优化）

单表几个 G 时，dump 会很慢。可以单独处理：

# 备份时排除某些大表
mysqldump -u root -p --all-databases \
  --ignore-table=bigdb.logs \
  --ignore-table=bigdb.sessions \
  > all-except-big.sql

# 大表用 SELECT INTO OUTFILE 备份(更快)
mysql -u root -p -e "
SELECT * FROM bigdb.logs 
INTO OUTFILE '/backup/logs.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n';"

三、自动化备份脚本

生产级脚本，含日志、告警、清理、验证：

#!/bin/bash
#==================================================
# MySQL 自动备份脚本
#==================================================

BACKUP_DIR="/backup/mysql"
KEEP_DAYS=14
LOGFILE="/var/log/mysql-backup.log"
DATE=$(date +%Y%m%d-%H%M)

log() { echo "[$(date '+%F %T')] $1" | tee -a "$LOGFILE"; }

mkdir -p "$BACKUP_DIR"

log "=== 开始备份 ==="

# 1. 备份
if ! mysqldump --single-transaction --quick --routines --triggers \
     --events --hex-blob --default-character-set=utf8mb4 \
     --all-databases | gzip > "$BACKUP_DIR/full-$DATE.sql.gz"; then
    log "错误: 备份失败!"
    # 告警
    exit 1
fi

SIZE=$(du -h "$BACKUP_DIR/full-$DATE.sql.gz" | cut -f1)
log "备份完成: full-$DATE.sql.gz ($SIZE)"

# 2. 校验压缩包完整性
if ! gzip -t "$BACKUP_DIR/full-$DATE.sql.gz"; then
    log "错误: 备份文件损坏!"
    exit 1
fi
log "完整性校验通过"

# 3. 记录 binlog 位置(用于恢复到精确时间点)
mysql -e "SHOW MASTER STATUS;" > "$BACKUP_DIR/binlog-$DATE.txt"
log "binlog 位置已记录"

# 4. 清理旧备份
find "$BACKUP_DIR" -name "full-*.sql.gz" -mtime +$KEEP_DAYS -delete
log "已清理 $KEEP_DAYS 天前备份"

# 5. 外传(可选)
# rclone copy "$BACKUP_DIR/full-$DATE.sql.gz" remote:mysql-backup/

log "=== 备份结束 ==="

# crontab: 每天凌晨 2 点
0 2 * * * /opt/scripts/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

四、恢复操作：三种场景

场景 1：误删数据，恢复整个库

# 1. 停应用(防止新写入覆盖)
systemctl stop nginx

# 2. 删旧库,建新库
mysql -u root -p -e "DROP DATABASE blogdb; CREATE DATABASE blogdb CHARACTER SET utf8mb4;"

# 3. 恢复(不需要先解压,直接管道)
gunzip < /backup/mysql/full-20261012-0200.sql.gz | mysql -u root -p

# 4. 验证
mysql -u root -p -e "SELECT COUNT(*) FROM blogdb.wp_posts;"

# 5. 起应用
systemctl start nginx

场景 2：只恢复一张表

# 1. 从全量备份中提取单表结构+数据
gunzip < /backup/mysql/full-20261012-0200.sql.gz > /tmp/full.sql

# 2. 提取目标表(从建表语句到下一个表之前)
sed -n '/-- Table structure for table `wp_posts`/,/-- Table structure for table/p' /tmp/full.sql > /tmp/wp_posts.sql

# 3. 导入
mysql -u root -p blogdb < /tmp/wp_posts.sql

# 4. 清理大文件
rm /tmp/full.sql /tmp/wp_posts.sql

场景 3：恢复到某个时间点（binlog 恢复）

这是最强大的能力：比如今天下午 3 点误操作，想恢复到 2 点 59 分。

# 1. 先恢复最近的备份(比如今天凌晨 2 点的)
gunzip < /backup/mysql/full-20261013-0200.sql.gz | mysql -u root -p

# 2. 看备份时的 binlog 位置
cat /backup/mysql/binlog-20261013-0200.txt
# 输出: mysql-bin.000003  位置: 154

# 3. 重放 binlog 到指定时间(误操作之前)
mysqlbinlog --stop-datetime="2026-10-13 14:59:00" \
  --start-position=154 \
  /var/log/mysql/mysql-bin.000003 | mysql -u root -p

# 4. 如果涉及多个 binlog 文件,一起处理
mysqlbinlog --stop-datetime="2026-10-13 14:59:00" \
  /var/log/mysql/mysql-bin.000003 \
  /var/log/mysql/mysql-bin.000004 | mysql -u root -p

前提：log_bin 已开启，且 binlog 文件还在（配了 binlog_expire_logs_seconds，别设太短）。

场景 4：整机恢复（快照）

控制台回滚快照，机器回到快照时刻。注意：这会丢失快照之后的所有数据（包括其他服务的），重要数据先手动拉下来。

五、物理备份：XtraBackup（大库必学）

数据量 50G 以上，mysqldump 慢到无法忍受（几小时），用 Percona XtraBackup：

# 安装(Debian/Ubuntu)
apt install -y percona-xtrabackup-80

# 全量备份
xtrabackup --backup --user=root --password=密码 \
  --target-dir=/backup/mysql/xtra-$(date +%F)/

# 准备阶段(应用日志,让数据一致,必须做!)
xtrabackup --prepare --target-dir=/backup/mysql/xtra-2026-10-13/

# 恢复(停库,清数据目录,拷贝回去)
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/mysql/xtra-2026-10-13/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

优势：物理拷贝，速度是 dump 的几倍；不锁表，业务无感知；支持增量备份（先全量，之后只备变化的）。

MariaDB 用户用 mariabackup，用法几乎一样。

六、备份验证：别等出事才发现是坏的

# 1. 完整性(压缩包没坏)
gzip -t /backup/mysql/full-20261013-0200.sql.gz

# 2. 内容抽查(有关键表、行数合理)
gunzip < /backup/mysql/full-20261013-0200.sql.gz | grep -c "CREATE TABLE"
gunzip < /backup/mysql/full-20261013-0200.sql.gz | grep "INSERT INTO \`wp_posts\`" | head -3

# 3. 试恢复(每月一次,在测试库演练)
mysql -e "CREATE DATABASE test_restore;"
gunzip < /backup/mysql/full-20261013-0200.sql.gz | mysql test_restore
mysql -e "SELECT COUNT(*) FROM test_restore.wp_posts;"
mysql -e "DROP DATABASE test_restore;"

# 4. 和线上对比行数(数据有没有漏)
mysql -e "SELECT COUNT(*) FROM blogdb.wp_posts;"    # 线上

金标准：恢复演练通过的备份才是备份，其他都是心理安慰。

七、常见问题

Q：备份文件越来越大，磁盘扛不住？

• 加压缩（gzip level 6）
• 排除归档表（历史日志单独存对象存储）
• 缩短保留天数
• 用 XtraBackup 增量，别每天全量

Q：备份时数据库卡？
--single-transaction 避免锁表，但大量读取仍可能吃 IO。用 ionice -c3 降优先级，避开业务高峰。

Q：--single-transaction 了还会锁表？
混了 MyISAM 表。ALTER TABLE 表名 ENGINE=InnoDB; 转过来。另外 DDL 操作（加字段、改索引）期间备份可能不一致，避开。

Q：恢复时报 ERROR 1071 字符集错误？
备份和恢复的字符集不一致。备份时加 --default-character-set=utf8mb4，恢复时也指定。

Q：怎么备份到对象存储？

# rclone(支持 S3 协议的各种存储)
rclone copy /backup/mysql/ cos:mysql-backup/

# 或腾讯 COS 官方工具
coscmd upload -rs /backup/mysql/ mysql-backup/

Q：binlog 增长太快把磁盘撑满？

[mysqld]
binlog_expire_logs_seconds = 604800    # 保留 7 天
max_binlog_size = 512M                 # 单文件上限

八、备份策略模板

直接抄这套配置：

一天的成本不到一度电，换来的是「任何误操作都能找回」的底气。

更多数据库教程见财富云计算知识库：MySQL 安装优化、故障排查、Redis 缓存、数据库安全加固全系列。需要云服务器可以看看财富云计算：一键开服面板，NAT 型无独立公网 IP，价格比独立 IP 机型省一半以上，支持快照。

延伸阅读

1. 网站环境怎么选：LNMP 手动 / 宝塔面板 / Docker / 云开发
2. 问卷表单系统搭建：SurveyKing / Formbricks，数据自己收
3. 图床搭建：Lsky Pro / Lychee，自托管图片托管方案
4. 邮件列表搭建：listmonk 自托管 Newsletter，把读者握在自己手里
5. 文档站点搭建：MkDocs / VuePress / Docsify，产品文档与技术手册方案
6. RSS 阅读器搭建：FreshRSS 自托管，把信息流拿回自己手里
7. 短链接服务搭建：Shlink 自托管，链接数据自己掌握
8. 网站状态监控页搭建：Uptime Kuma，服务器挂了第一时间知道

更多阅读

1. 上一篇：内核参数优化：sysctl 实战调优（游戏服与高并发两套参数）
2. 下一篇：Redis 持久化详解：RDB 与 AOF 怎么配、怎么选、怎么恢复