13 10 月 2026

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

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

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

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

方式工具适用规模停机?恢复粒度
逻辑备份mysqldump / mysqlpump< 50G否(InnoDB 可热备)库/表级
物理备份XtraBackup / MariaDB Backup50G–数 T否整库
快照云厂商快照任意否(秒级)整盘
binlog 增量mysqlbinlog配合上面用否时间点/事务级

个人站/小企业(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                 # 单文件上限

八、备份策略模板

直接抄这套配置:

项配置
全量备份每天凌晨 2 点,mysqldump + gzip
保留本地 14 天
binlog开启,保留 7 天
快照每周一次(控制台)
异地每周把最新备份传对象存储
验证每周 gzip -t,每月恢复演练
告警备份失败立即通知

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

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

延伸阅读
  1. 高防误区与避坑清单:25 个坑+下单前必问 14 问+到货验货流程
  2. DNS 攻击防护专题:劫持、隧道、投毒、放大四类攻击与 DNSSEC
  3. 电商大促防攻击预案:T-14 到当天的分级响应与降级开关
  4. 源站真实 IP 隐藏完全指南:六大暴露面排查+NAT 型终极方案
  5. 日志分析找攻击源:Nginx 日志三维度分析法与证据固定
  6. 攻击应急响应手册:六阶段响应流程,从确认到复盘的照做清单
  7. iptables 抗 DDoS 配置模板:状态过滤+SYN 防护+防扫描+游戏服版
  8. OpenResty Lua 自研 WAF:动态限流+攻击特征检测+Redis 黑名单
更多阅读
  1. 上一篇:内核参数优化:sysctl 实战调优(游戏服与高并发两套参数)
  2. 下一篇:Redis 持久化详解:RDB 与 AOF 怎么配、怎么选、怎么恢复
发表评论 15

    1. Redis 持久化详解:RDB 与 AOF 怎么配、怎么选、怎么恢复 - 			财富云计算 - 维基百科的头像
      Redis 持久化详解:RDB 与 AOF 怎么配、怎么选、怎么恢复 - 财富云计算 - 维基百科  

      [...]# 关注: rdb_last_bgsave_status:ok, aof_enabled:1, aof_last_write_status:ok八、常见问题Q:重启 Redis 数据全没了?save "" + appendonly no,持久化全关了。检查配置,至少开一个。Q:aof_last_write_status:err?磁盘满了或权限问题。df -h 看磁盘,ls -l[...]

    2. WordPress 搭建与性能优化完整指南:从安装到提速 - 			财富云计算 - 维基百科的头像
      WordPress 搭建与性能优化完整指南:从安装到提速 - 财富云计算 - 维基百科  

      [...]define('WP_POST_REVISIONS', 5);定期清理挂 crontab,见MySQL 备份与优化。4. 图片与静态资源WordPress 自动生成多个尺寸的缩略图,很容易磁盘爆炸:# 清理未使用的缩略图[...]

    3. 网站数据库迁移与换机实战:mysqldump 五步零丢数据 - 			财富云计算 - 维基百科的头像
      网站数据库迁移与换机实战:mysqldump 五步零丢数据 - 财富云计算 - 维基百科  

      [...]换服务器、换数据库版本、数据库从单机迁到 RDS——网站搬家最怕的就是数据库:迁漏了表、字符集乱了、权限丢了,网站打开全是 500。这篇给一份可直接照做的迁移流程:备份、导出、导入、校验、切换,含 WordPress 和 Typecho 实例。需要现成的游戏云服务器,可以看看财富云计算:一键开服面板,NAT 型无独立公网 IP,价格比独立 IP 机型省一半以上,建站和游戏服都能快速部署。一、迁移前[...]

    4. 磁盘空间排查与清理实战:删了文件空间不释放怎么办 - 			财富云计算 - 维基百科的头像
      磁盘空间排查与清理实战:删了文件空间不释放怎么办 - 财富云计算 - 维基百科  

      [...]long_query_time = 2binlog 超过 7 天自动清,慢查询也别无限记。数据库维护全景见MySQL 备份与恢复实战。4.3 监控告警# 磁盘 80% 告警(cron 每日)[...]

    5. MySQL 慢查询优化实战:慢日志、EXPLAIN、索引三板斧 - 			财富云计算 - 维基百科的头像
      MySQL 慢查询优化实战:慢日志、EXPLAIN、索引三板斧 - 财富云计算 - 维基百科  

      [...]DELETE FROM orders WHERE created_at < '2026-01-01';表小了,什么都快。归档数据见MySQL 备份与恢复实战。四、锁与等待查询不慢但事务卡住:-- 看当前在跑的查询[...]

    6. 小项目用什么数据库:SQLite 与 MySQL 怎么选 - 			财富云计算 - 维基百科的头像
      小项目用什么数据库:SQLite 与 MySQL 怎么选 - 财富云计算 - 维基百科  

      [...]# https://z0w.cn/wiki/database-security-hardening.htmlMySQL 的完整运维(安装、备份、主从、优化)见MySQL 安装与优化入门、MySQL 备份与恢复实战。七、常见疑问Q:SQLite 真的能扛几千 IP 的网站吗?能。Typecho + SQLite 扛日 IP 几千的博客是社区常态——读多写少的场景 SQLite 很从容。瓶颈通常在 P[...]

    7. Steam游戏开服端口速查表:AppID、端口协议、配置需求一表查完(含Windows/Linux双系统索引) - 			财富云计算 - 维基百科的头像
      Steam游戏开服端口速查表:AppID、端口协议、配置需求一表查完(含Windows/Linux双系统索引) - 财富云计算 - 维基百科  

      [...]netsh advfirewall firewall add rule name="GameRange" dir=in action=allow protocol=UDP localport=26901-26903完整的 Windows 环境准备(远程桌面连接、运行库安装、SteamCMD 用法、开机自启、8 个常见坑)见:《Windows 云服务器开服通用前置教程》→ htt[...]

    8. 小程序服务器选购:配置、带宽、地域、NAT 还是独立 IP - 			财富云计算 - 维基百科的头像
      小程序服务器选购:配置、带宽、地域、NAT 还是独立 IP - 财富云计算 - 维基百科  

      [...]restart: unless-stopped具体部署步骤(含证书、合法域名配置)见小程序后端搭建实战。两个方案的详细对比见网站环境怎么选。五、安全与备份:上线前的最后一道关小程序后端是 7×24 对着公网的,基本功课不能省:防火墙只开 443 和 SSH 端口,MySQL/Redis 的 3306/6379 绝不对外,见服务器端口安全:最小化开放SSH 用密钥登录,关密码登录,见SSH 安全加固[...]

    9. 小程序数据库对接实战:MySQL 选型、连接池与注入防护 - 			财富云计算 - 维基百科的头像
      小程序数据库对接实战:MySQL 选型、连接池与注入防护 - 财富云计算 - 维基百科  

      [...]# 3. binlog 开启,支持时间点恢复binlog 时间点恢复的完整操作见MySQL 备份与恢复实战。每周做一次恢复演练——没验证过的备份等于没有备份。七、性能 checklist[ ] 连接池已启用,池大小合理[ ] 查询字段具体化(别 SELECT *,大文本列拖慢 IO)[ ] 高频查询走索引,EXPLAIN 验证,慢查询治理见MySQL 慢查询优化实战[ ] 热点数据进 Redis,[...]

    10. 论坛网站搭建:Discourse / Flarum / NodeBB 对比与部署实战 - 			财富云计算 - 维基百科的头像
      论坛网站搭建:Discourse / Flarum / NodeBB 对比与部署实战 - 财富云计算 - 维基百科  

      [...]./discourse-setupapp.yml 里配好域名、SMTP(Discourse 强依赖邮件,注册验证、通知全走邮件,必须先配好发信,见邮件退信与 SPF/DKIM/DMARC 配置)。Discourse 的管理后台是它的精髓:信任等级自动权限、反垃圾评分、敏感词、话题合并——社区做大了,这些工具能省掉两个运营。四、社区运营的三条硬功夫技术只是上半场,社区死活在下半场:冷启动:前 100[...]

    11. WooCommerce 电商网站搭建:从开店到接支付的全流程 - 			财富云计算 - 维基百科的头像
      WooCommerce 电商网站搭建:从开店到接支付的全流程 - 财富云计算 - 维基百科  

      [...]个人中心(订单 / 地址 / 优惠券)四、接支付:最需要谨慎的一步国内自建电商的支付选项:方式资质要求费率说明微信支付企业主体 + 商户号约 0.6% 起需对接口,或用插件支付宝企业/个体户约 0.6% 起同上个人码收款无0仅限小额/熟人场景,风控严PayPal企业主体约 3.9%+做海外用硬性要求:企业主体才能申请微信/支付宝商户号,个人主体基本没戏——注册主体时想清楚全站 HTTPS 是强制的[...]

    12. 邮件列表搭建:listmonk 自托管 Newsletter,把读者握在自己手里 - 			财富云计算 - 维基百科的头像
      邮件列表搭建:listmonk 自托管 Newsletter,把读者握在自己手里 - 财富云计算 - 维基百科  

      [...]</form>2. 双重确认(Double Opt-in):设置里开启——用户提交后收到确认邮件,点链接才真正订阅。这是合规底线(GDPR/个人信息保护法都认这个),也能挡掉 90% 的垃圾订阅。3. 写与发:listmonk 支持 Markdown + HTML 模板,群发是队列调度,自动处理退信。发送节奏建议:固定周期(周更/双周更),比爆频率更能留住读者。4. 数据看板:打开率、[...]

    13. 问卷表单系统搭建:SurveyKing / Formbricks,数据自己收 - 			财富云计算 - 维基百科的头像
      问卷表单系统搭建:SurveyKing / Formbricks,数据自己收 - 财富云计算 - 维基百科  

      [...]<script src="https://survey.yourdomain.com/js/formbricks.js";></script>活动报名页、用户反馈入口、满意度评价,全都能长在自己网站上——访客不用跳出去,转化率比外链高一大截。五、数据:自己收的数据自己用自托管的核心收益在数据:导出自由:CSV/Excel 随时拉,没有"导出 50 条以上要[...]

    14. CI/CD 完全指南:从手动 FTP 到自动部署,小团队的正确姿势 - 			财富云计算 - 维基百科的头像
      CI/CD 完全指南:从手动 FTP 到自动部署,小团队的正确姿势 - 财富云计算 - 维基百科  

      [...]nginx -s reload原则:回滚必须比部署更快更简单。设计流水线时先回答"出事了两分钟内能不能回到昨天",能,再上线。3. 数据库迁移:最危险的一步代码回滚容易,数据库 schema 回滚要命。纪律:迁移脚本必须向后兼容(先加字段后删字段,分版本做)迁移在部署前跑,且先备份(见MySQL 备份与恢复)迁移失败 = 部署中止,不要让半迁移状态上线五、自建方案:Gitea + Woodpeck[...]

    15. 数据备份体系完全手册:从随手拷到 3-2-1,让数据永远丢不了 - 			财富云计算 - 维基百科的头像
      数据备份体系完全手册:从随手拷到 3-2-1,让数据永远丢不了 - 财富云计算 - 维基百科  

      [...]xtrabackup --backup --target-dir=/backup/xb-$(date +%F)完整方案见MySQL 备份与恢复实战;主从架构下的备份从库备,不拖累主库,见MySQL 主从复制。4. 云快照:用,但别只依赖云厂商快照一键整机,适合变更前打点、快速回滚。但它是"单点备份"——和原数据在同一家云,商家跑路/区域故障时一起没。快照 + 异地备份,双轨并行,见云快照实战。五、[...]