去年接管一套老系统时赶上一次典型事故运营误操作把订单表 truncate 了结果发现这台 MySQL 上一次全量备份是十几天前binlog 也没有异地归档。最后花了一整天从磁盘碎片和 binlog 残留里手动拼数据业务停机超过 8 小时。那会儿我就下定决定不管项目大小MySQL 数据库备份与恢复方案必须第一条写进运维规范。这篇文章就把我这些年在 MySQL 备份恢复上踩过的坑、用过的工具、写过的脚本以及恢复现场的操作细节全部整理出来。会从备份方案设计、工具选型、自动脚本落地到误删数据后的完整恢复流程逐步展开也会把常见的报错和排查思路做成速查清单。无论你是刚接手公司数据库的新人 DBA还是自己搭站点的后端开发只要 MySQL 里存着不能丢的数据这篇文章就能直接给你一套可落地的备份恢复参考方案。1. 备份方案设计先别急着写脚本把恢复目标想清楚很多人一上来就写个 mysqldump 脚本然后 crontab 一挂就觉得万事大吉。等你真到了要恢复的时候才发现要么备份文件不可用要么缺少关键参数导致数据不一致要么增量 binlog 没保留找不到时间点。所以第一步不是选工具而是想清楚几个核心问题。1.1 RPO 和 RTO你最多能丢多少数据允许停机多久备份方案的设计核心是两个指标RPORecovery Point Objective和 RTORecovery Time Objective。说得直白点RPO 是“你能容忍丢多少数据”RTO 是“出事后你多久必须恢复业务”。比如一个电商网站用户下单支付这种场景 RPO 最好趋近于零也就是任何一秒钟的数据都不能丢。那你的方案必须是“实时 binlog 归档 定期全量备份”否则一旦主库磁盘损坏你丢的就是从上次备份到现在所有交易记录。而一个纯展示型官网数据一天更新一次那 RPO 可以放宽到 24 小时每天凌晨备份一次就够。RTO 同样关键。如果是核心交易库业务方可能要求 30 分钟内恢复那你必须提前演练物理备份恢复流程因为用 mysqldump 导几十个 G 的数据再 source 回去可能几个小时都搞不定。反过来如果业务允许停机半天逻辑备份的压力就会小很多。这个阶段要输出的是一张“备份需求确认表”至少包含数据量、增长量、允许丢失时间、允许停机时间、恢复对象粒度整库/单表、备份保存周期。这张表直接决定你后面选择什么工具、备份频率多高、存几份、放哪里。1.2 三种备份类型全量、增量、差异怎么组合最合理MySQL 备份从类型上分最常用的是全量备份和增量备份。差异备份在 MySQL 场景里用得不多这里不展开。全量备份就是一个完整的数据快照可以用 mysqldump 导出逻辑数据也可以用 xtrabackup 直接拷贝物理文件。增量备份则是基于 binlog把全量备份之后产生的所有变更记录下来。常见的组合方式有两种全量备份 binlog比如每天凌晨 2 点做一次全量备份binlog 持续保留并同步到异地。恢复时先恢复最近一次全量备份再重放 binlog 到指定时间点。全量备份 增量备份 binlog比如周日做全量周一到周六每天做 xtrabackup 增量备份binlog 实时归档。这种方案适合数据量较大、全量备份耗时长、但恢复要求又比较高的场景。重点说下“全量 binlog”这种组合它其实已经能覆盖绝大多数中小型业务。全量备份保证基础数据binlog 保证全量之后的每一次变更理论上你可以恢复到任意时间点。前提是 binlog 完整且连续。很多人备份了全量却忽略了 binlog 的连续性binlog 在服务器上被自动清理了或者只保留了最近三天结果恢复时找不到更早的 binlog一样白搭。所以我给客户的统一建议是binlog 保留周期至少是全量备份间隔的两倍比如每天全量备份binlog 至少留 7 天同时把 binlog 文件同步到备份服务器归档不能只依赖本地。1.3 物理备份和逻辑备份同样是全量差别很大全量备份还能继续拆成物理备份和逻辑备份这两个必须拎清楚。逻辑备份用 mysqldump、mydumper 这类工具把数据通过 SQL 语句导出来生成的是 .sql 文件。它的优势是跨版本、跨平台恢复方便文件可读还能只恢复某几张表。缺点是导出和恢复都慢而且数据量大时占用资源明显恢复几个小时的场景很常见。物理备份用 xtrabackup 这类工具直接拷贝 MySQL 数据目录下的物理文件。它的优势是备份和恢复速度极快特别适合大库基本就是文件拷贝进度。缺点是比较依赖同版本 MySQL跨小版本升级时可能不兼容恢复文件也不可读。选型上我的经验是数据量在 10G 以内用逻辑备份完全够10G 到 50G 看你对恢复速度的容忍度也可以继续用逻辑备份超过 50G 或者对 RTO 要求高就应该考虑 xtrabackup 物理备份。注意这个阈值不是绝对的取决于服务器性能和业务容忍度。1.4 备份验证备份文件没有经过恢复验证等于没有备份这是我最想强调的一点。见过太多团队备份脚本跑了几年日志全绿但有一天真要恢复时发现备份文件是坏的或者 mysqldump 导出来因为字符集问题导入直接报错又或者 binlog 因为服务器重启序号断开根本对不上。备份验证最有效的办法就是“周期性恢复演练”。在不影响生产的前提下在测试实例上定期从备份文件恢复一次然后做简单的数据校验比如对比行数、抽查关键表。频率不用太高一个月一次就够。如果资源紧张至少每次全量备份完成后做一次“备份文件完整性检查”用 gzip -t 验证压缩包没损坏用 mysqldump 备份的可以看看文件尾部有没有完整的 Dump completed 标记。另外备份文件一定要“异地存放”。如果全量备份和生产库在同一台服务器的同一块磁盘上磁盘坏了就是一起死。最简单的方案是备份完成后自动 rsync 到另一台机器或者对象存储。2. 核心工具选型mysqldump、mydumper、xtrabackup 到底用哪个工具选对了备份恢复这件事已经成功一半。我自己的工具箱里长期放着三套mysqldump 处理小库和单表逻辑导出mydumper 处理中等库的并行导出xtrabackup 处理大库和物理备份。下面逐个展开。2.1 mysqldump最常用的逻辑备份工具参数必须用对mysqldump 是 MySQL 自带的逻辑备份工具大多数人的入门备份工具就是它。但很多参数如果没用对备份出来的数据可能是不一致的或者缺少存储过程、触发器、事件恢复的时候才发现少了东西。生产环境我常用的核心参数是这一串mysqldump \ -h127.0.0.1 -P3306 \ -ubackup_user -p \ --single-transaction \ --routines \ --triggers \ --events \ --master-data2 \ --set-gtid-purgedOFF \ --databases db1 db2 \ backup.sql逐个解释一下关键参数--single-transaction对 InnoDB 表开启一个一致性快照备份期间不会锁表。这是 InnoDB 场景下必备参数不加它备份期间的写入会导致备份数据不一致大表可能导致长时间锁表。--routines、--triggers、--events分别导出存储过程和函数、触发器、事件调度器。这些对象默认不导出漏掉的后果是恢复后业务跑起来才发现自定义存储过程全没了。--master-data2在备份文件里记录 binlog 文件名和 position 位置以注释形式写入恢复后做增量恢复时全靠这个定位起点。1 的话会以 CHANGE MASTER TO 形式写入用于搭建 slave 也方便。--set-gtid-purgedOFF如果开启了 GTID默认导出会带 SET GLOBAL.GTID_PURGED 语句在只想导入数据而不是搭建复制环境时可能报错所以看场景加。--databases db1 db2指定导出多个库同时会在备份文件里自动加 CREATE DATABASE 和 USE 语句恢复时不用手动建库。关于字符集建议在命令行加--default-character-setutf8mb4否则如果你服务器端默认字符集不是 utf8mb4导出来的中文内容可能变成乱码。另外导出时尽量用专门的备份账号权限上只需要 SELECT、SHOW VIEW、TRIGGER、LOCK TABLES、RELOAD 这些就够不要天天用 root 跑备份。2.2 mydumper需要并行导出时的替代方案mysqldump 虽然有--parallel之类的参数但一直没做得很强。数据量大了之后单线程导出非常痛苦。这时候可以上 mydumper。mydumper 是多线程逻辑备份工具它能按表并行导出也支持把大表按行范围拆分成多个 chunk 并行导出。恢复时配合 myloader 也能并行导入。在中等数据量场景下速度能比 mysqldump 快好几倍。不过 mydumper 也有坑。它对 MySQL 版本的兼容性比较敏感比如 8.0 的某些版本需要用最新版本 mydumper 才能正确处理。另外它导出的文件是每张表一个 .sql 文件加一个 metadata 文件恢复时要用 myloader不能直接 source。如果你只想要一个完整的 .sql 文件方便直接导入mydumper 反而没那么方便。我的建议是如果你的环境是 MySQL 5.7 或 8.0且数据量在几十 G 以内mysqldump 还是优先一旦超过这个量级或备份窗口不够可以考虑 mydumper但要先在测试环境把整个备份恢复流程打通再上生产。2.3 xtrabackup大库场景下的物理备份主力xtrabackup 是 Percona 出品的物理备份工具现在 8.0 版本叫 Percona XtraBackup 8.0。它是目前 MySQL 物理备份事实标准原理是直接复制 InnoDB 数据文件同时备份过程中持续追踪 redo log最终做到一致性备份。物理备份的好处不用多说备份速度就是文件拷贝速度恢复速度也快。尤其适合数据量几十 G 甚至几个 T 的生产库。xtrabackup 有一个核心概念备份完成后数据文件本身是“不一致”的必须经过一个--prepare阶段应用 redo log才能把数据文件恢复到一致状态。这个阶段很多人会忽略拿到备份直接 cp 回去启动 MySQL结果数据文件报错或部分数据找不到。全量备份命令大概长这样xtrabackup --backup \ --target-dir/backup/mysql/20240101 \ --userbackup_user --passwordxxx \ --host127.0.0.1 --port3306恢复前先 preparextrabackup --prepare \ --target-dir/backup/mysql/20240101prepare 完成后再用xtrabackup --copy-back或者直接手动 rsync 数据目录。注意mysqld 进程需要停止数据目录需要清空文件属主要改成 mysql 用户。2.4 选型对比一张表帮你快速决策工具这么多到底选哪个我做了个表方便你直接对着选工具备份类型适合数据量备份速度恢复速度是否锁表支持增量跨版本恢复mysqldump逻辑10G 以内慢慢需配 single-transaction不支持较灵活mysqlpump逻辑10G-50G中中需配置不支持较灵活mydumper逻辑50G 以内较快较快需配置不支持一般xtrabackup物理50G 以上快快基本不锁支持对版本敏感实际生产环境里我见过很多团队的做法是全量用 xtrabackup增量用 binlog两者结合兼顾速度和恢复粒度。如果你是小团队、数据量不大从 mysqldump 开始完全没问题关键是把自动化、异地保存、恢复验证做好。3. 自动备份脚本实操Linux shell 和 Windows bat 两种写法备份方案定了工具定了接下来就是把备份这件事自动化。手动执行备份这种事干一次两次行时间久了必然出岔子。下面给两套脚本模板一套是 Linux 的 shell 脚本一套是 Windows 的 bat 脚本都是我自己用过、改过的版本。3.1 Linux shell 脚本mysqldump 全量 清理历史 日志记录我的备份脚本一般放在/usr/local/bin/mysql_backup.sh核心逻辑包括定义变量、执行备份、压缩、清理旧备份、写日志。给你一个能直接改用的版本#!/bin/bash BACKUP_DIR/backup/mysql DATE$(date %Y%m%d_%H%M%S) DB_USERbackup_user DB_PASSyour_password DB_HOST127.0.0.1 DB_PORT3306 KEEP_DAYS7 LOG_FILE/var/log/mysql_backup.log mkdir -p ${BACKUP_DIR}/${DATE} echo [$(date %F %T)] backup start ${LOG_FILE} mysqldump \ --host${DB_HOST} \ --port${DB_PORT} \ --user${DB_USER} \ --password${DB_PASS} \ --single-transaction \ --routines --triggers --events \ --master-data2 \ --all-databases \ | gzip ${BACKUP_DIR}/${DATE}/all_databases.sql.gz if [ $? -eq 0 ]; then echo [$(date %F %T)] backup success ${LOG_FILE} echo ${DATE} ${BACKUP_DIR}/latest_backup.txt else echo [$(date %F %T)] backup failed ${LOG_FILE} # 可以在这里加告警比如调用 webhook 发送到钉钉/企微 exit 1 fi # 清理超过保留天数的备份目录 find ${BACKUP_DIR} -maxdepth 1 -type d -name 20* -mtime ${KEEP_DAYS} -exec rm -rf {} \;几个细节说明一下备份目录按日期分文件夹方便后续恢复时定位lib变量和密码不要写死在脚本里我这边示例为了方便展示生产环境建议改成从/root/.my.cnf读密码--all-databases表示全库备份如果你只想备份业务库改成--databases db1 db2并去掉--all-databases。清理旧备份用的find -mtime ${KEEP_DAYS}这个逻辑是按文件修改时间判断。要注意备份目录要独立分出来别和其他文件混在一起否则可能误删。日志文件建议也做轮转或者定期清理否则时间久了日志文件能撑满磁盘。crontab 配置放在/etc/crontab或者crontab -e20 2 * * * /usr/local/bin/mysql_backup.sh /var/log/mysql_backup_cron.log 21我习惯加 ... 21这样脚本本身的输出和报错也会进日志排查时更方便。3.2 Windows bat 脚本日期格式化是大部分“写入设备错误”的元凶Windows 服务器上跑 MySQL同样可以自动备份bat 脚本就行。热搜词里有个典型报错“bat 备份mysql数据库提示 the system cannot write to the specified device”这个我见过太多次了后面常见问题里细说。先给脚本模板echo off set BACKUP_DIRD:\backup\mysql set DATE%date:~0,4%%date:~5,2%%date:~8,2% set TIME%time:~0,2%%time:~3,2% set TIMESTAMP%DATE%_%TIME% set DB_USERbackup_user set DB_PASSyour_password set LOG_FILED:\backup\mysql_backup.log if not exist %BACKUP_DIR% mkdir %BACKUP_DIR% echo %date% %time% backup start %LOG_FILE% mysqldump -h127.0.0.1 -P3306 -u%DB_USER% -p%DB_PASS% --single-transaction --routines --triggers --events --all-databases %BACKUP_DIR%\all_databases_%TIMESTAMP%.sql if %errorlevel% equ 0 ( echo %date% %time% backup success %LOG_FILE% ) else ( echo %date% %time% backup failed errorlevel %errorlevel% %LOG_FILE% ) forfiles -p %BACKUP_DIR% -m *.sql -d 7 -c cmd /c del pathbat 脚本有几个非常容易踩的坑。一是日期格式化%date%和%time%的输出格式受系统区域设置影响有的服务器输出2024/01/01有的输出01/01/2024直接拼接成文件名很容易出问题。我上面的写法是按yyyyMMdd_HHmm截取但如果你的系统日期格式不是这种顺序要先在测试环境echo %date%看看输出到底是什么再调整截取位置。二是密码包含特殊字符时bat 里转义比较麻烦建议用配置文件或环境变量而不是直接把密码怼在脚本里。三是forfiles清理旧文件系统是 Windows 7 或 Server 2008 以上一般自带老系统可能没有。3.3 binlog 增量备份脚本只备全量不管 binlog等于白备全量备份解决的是“过去某个时间点的完整数据”但备份之后新写入的数据都在 binlog 里。如果你的 binlog 不单独备份生产库的 binlog 又默认只保留几天那全量备份的作用就大打折扣恢复点只能落在全量备份的时刻。所以我每次都会额外配一个 binlog 归档任务。最简单的做法是定期把 binlog 文件复制到备份目录同时记录文件名。MySQL 8.0 和 5.7 都可以用mysqlbinlog的远程拉取功能也可以直接监听 binlog 目录做同步。shell 版 binlog 归档脚本#!/bin/bash BACKUP_BINLOG_DIR/backup/binlog BINLOG_DIR/var/lib/mysql MYSQL_BINLOG_INDEX${BINLOG_DIR}/binlog.index LAST_FILE_FLAG/backup/binlog/last_file.txt mkdir -p ${BACKUP_BINLOG_DIR} # 找到当前已归档到哪个文件 LAST_FILE if [ -f ${LAST_FILE_FLAG} ]; then LAST_FILE$(cat ${LAST_FILE_FLAG}) fi # 遍历 binlog.index 中新增的 binlog while read binlog_name; do binlog_file${BINLOG_DIR}/${binlog_name} if [ ${binlog_name} ${LAST_FILE} ]; then continue fi if [ -f ${binlog_file} ]; then cp -f ${binlog_file} ${BACKUP_BINLOG_DIR}/ echo ${binlog_name} ${LAST_FILE_FLAG} fi done ${MYSQL_BINLOG_INDEX}这段逻辑不算复杂把 binlog.index 里记录的文件逐个复制到备份目录并用一个状态文件记录上次复制到哪个文件。这样至少保证 binlog 在本地被清理后备份目录里还有归档副本。当然生产环境更推荐用 mysqlbinlog 的--read-from-remote-server或直接用专业备份工具做连续归档这里给的是最小可用的脚本方案。3.4 脚本必备的“安全护栏”经验备份脚本跑起来后有三件事必须同时做好少了任何一件都可能在关键时刻掉链子。磁盘空间监控是最容易忽视的。备份文件增长速度可能超出预期特别是 binlog 归档如果没做清理备份磁盘被写满是迟早的事。备份失败还能通过日志发现磁盘满了其他服务可能一起遭殃所以我会额外配一个磁盘空间检查超过 80% 就告警。脚本执行日志和最终备份结果告警也要加上。最简单的方案是脚本里把成功或失败写入日志同时调用企业微信/钉钉的 webhook 发一条消息到群里。这样每天定时备份完成后相关人员能在群里看到今天备份结果没收到消息就说明有问题。这个小习惯救过我很多次。最后是账号权限。备份账号不要用 root单独创建一个账号只授予必要权限密码不要写在脚本里Linux 可以用~/.my.cnf[mysqldump] userbackup_user passwordyour_passwordWindows 上可以用环境变量或临时拼接总之别把生产库 root 密码明文放在任何脚本里。4. 恢复实操从误删数据到全量恢复备份做得再好恢复不行也白搭。这一节我会把最常遇到的恢复场景过一遍包括全量恢复、binlog 增量恢复、误删库表后的定向恢复以及 xtrabackup 物理备份的恢复流程。每步都会说清楚为什么这么做。4.1 恢复前先做三件事确认时间线、找备份、锁定位点不管什么恢复场景在做任何操作之前先冷静下来确认三件事第一故障发生的时间点精确到秒这决定 binlog 恢复到哪第二现有的备份文件有哪些最近一次有效全量备份是什么时候第三全量备份里记录的 binlog position 是什么。这里说的 binlog position如果你用 mysqldump 并加了--master-data2备份文件头部会有类似这样的一行-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000014, MASTER_LOG_POS120345;这就是全量备份对应的 binlog 文件和偏移量。恢复时从全量备份恢复出数据后binlog 重放就从这个文件、这个位置开始而不是从备份时间点之前开始。如果用的是 GTID 模式那么备份文件里会有一段SET GLOBAL.GTID_PURGED...恢复时需要用--set-gtid-purged或--skip-gtid来控制否则可能跳过一些事务。这块细节很多但逻辑通了就顺着走找到全量备份位点之后重放 binlog 到目标时间或位置。4.2 从 mysqldump 全量备份恢复标准流程与提速技巧从逻辑备份恢复本质就是把 .sql 文件导入 MySQL。基础流程没什么神奇mysql -h127.0.0.1 -P3306 -uroot -p /backup/mysql/20240101/all_databases.sql但有几件事要做在前面。第一导入前确认目标实例的字符集、时区与原库一致否则中文乱码或时间错乱够你喝一壶。第二如果有--routines导出的存储过程和触发器导入时最好用 root 或具备 SUPER 权限的账号否则创建过程的权限可能不够。第三导入大文件时建议用source或者分库导入不要一条mysql 就完事中途报错不好定位。如果只需要恢复某一张表可以把备份文件里对应表的 INSERT 语句筛出来或者直接用sed截取需要的部分。更干净的做法是备份时就考虑粒度小表用--databases db_name --tables table_name单独备份恢复时直接拿来导入。大数据量导入时还有一个技巧先临时关闭唯一约束检查和外键检查导入完成后再开启能明显提速。对应 SQLSET FOREIGN_KEY_CHECKS0;和SET UNIQUE_CHECKS0;。4.3 使用 binlog 增量恢复从全量位点到指定时间或位置全量恢复只能恢复到备份时刻之后的数据就得靠 binlog 补齐。这是恢复流程里技术要求最高的一步。假设全量备份是今天凌晨 2 点做的对应 binlog pos 是mysql-bin.000014:120345而业务在上午 10:30:00 误删了一张表。我们要做的就是把mysql-bin.000014从 position 120345 开始到mysql-bin.000020截止重放到误删前的那一刻。命令大概是mysqlbinlog \ --no-defaults \ --start-position120345 \ --stop-datetime2024-01-01 10:29:59 \ mysql-bin.000014 mysql-bin.000015 mysql-bin.000016 mysql-bin.000017 mysql-bin.000018 mysql-bin.000019 mysql-bin.000020 \ --databaseyour_db \ | mysql -h127.0.0.1 -uroot -p your_db几个关键点拆开讲--start-position从全量备份文件头部记录的位点开始而不是从 0 开始否则会把全量备份之前已经包含的数据再重放一遍导致主键冲突。--stop-datetime指定截止时间这里要给出误删操作发生前的一秒特别小心时区问题。如果你不确定精确时间可以用--stop-position配合mysqlbinlog解析出来的内容找到误删语句的位置。--databaseyour_db限定只重放指定库的语句。注意 binlog 里记录的 SQL 如果使用了跨库操作这个过滤可能不生效具体要看语句里有没有显式库名。多个 binlog 文件一次性传给 mysqlbinlog它会按顺序解析不用多次执行。实际操作中误删语句往往是一条 DROP TABLE 或者 DELETE它会出现在某个 binlog 文件里。在重放之前我通常先跑一遍mysqlbinlog把相关 binlog 导出成文本文件然后 grep 出误删语句的位置再用--stop-position精确截止。这样不会因为时间差导致多放或少放事务。4.4 误删单表后的定向恢复避免全库恢复的“杀伤力”有时候只是误删了一张表但备份是全库的。如果直接把整库恢复出来再处理影响面太大而且可能要停库。更常见的做法是临时实例恢复取出需要的那张表。这样不会影响线上正常跑着的业务整个过程完全离线。我的标准操作是这样的准备一台临时实例或者本机另起一个端口用最近一次全量备份恢复到临时实例然后用 binlog 增量恢复把误删操作之前的数据补齐最后从临时实例把目标表的 .sql 导出导入到生产库。具体到 mysqldump 全量备份怎么只恢复一张表可以这样# 在临时库恢复时把备份文件中的库表筛选出来 zcat /backup/mysql/20240101/all_databases.sql.gz | grep -E CREATE TABLE.*your_table|INSERT INTO.*your_table your_table.sql # 注入到临时库 mysql -h127.0.0.1 -P3307 -uroot -p your_db your_table.sql这个方法对简单表有效但如果表结构里有触发器、外键等依赖建议还是在临时实例完整恢复再整体导出目标表。需要提醒的是如果生产库在误删之后还有大量写入直接导回旧表可能会覆盖新数据或造成主键冲突此时先确认业务是否已经用新表继续写数据再决定恢复策略。4.5 xtrabackup 物理备份的恢复流程prepare 和 copy-back 不能省xtrabackup 的恢复和 mysqldump 完全不一样喜好直接操作数据目录。恢复前准备一份同版本 MySQL步骤如下第一步停止 MySQLsystemctl stop mysqld第二步清理或备份当前数据目录。注意数据目录里的隐藏文件、日志文件都要处理干净不能残留旧数据mv /var/lib/mysql /var/lib/mysql_broken mkdir -p /var/lib/mysql第三步prepare 备份文件。如果备份是增量备份prepare 时要把多个备份目录按顺序--apply-log-only合并最后再整体 prepare。全量备份则直接xtrabackup --prepare --target-dir/backup/mysql/20240101第四步copy-back文件复制回数据目录xtrabackup --copy-back --target-dir/backup/mysql/20240101第五步修改属主并启动chown -R mysql:mysql /var/lib/mysql systemctl start mysqld这里最容易出的问题有两个一是漏了 prepare 阶段直接把备份文件 copy 回去MySQL 启动会报 redo log 不一致二是 copy-back 后忘了chown以 root 复制出来的文件属主不对mysqld 根本起不来。另外xtrabackup 还原后的实例数据目录里可能存在旧的auto.cnf这会改变 server_uuid如果这个实例要作为 slave 重新挂载要注意主从复制里的 server_uuid 冲突问题。5. 常见问题与排查技巧实录备份恢复这件事平时不出问题岁月静好一出问题全是修罗场。这一节我把自己和同行在实际操作里碰到的典型报错、排查思路全部整理出来做成速查清单照着查能少走很多弯路。5.1 备份失败的典型原因权限、磁盘、锁和语法先列一个备份失败高频原因表现象最常见原因排查方向mysqldump: Access denied备份账号权限不足检查 GRANT 是否包含 SELECT、RELOAD、LOCK TABLESmysqldump: Got error: 1017表不存在或文件损坏检查表是否有异常先修复再备份backup failed with error 28磁盘空间不足df -h 看备份目录所在分区剩余空间the system cannot write to the specified deviceWindows 下自动备份时报错多为路径包含特殊字符、系统日期格式拼接错误或盘符不可写Lock wait timeout exceeded备份时与业务事务冲突确认是否漏了 --single-transaction或等待长事务结束The process cannot access the file because it is being used by another processWindows bat 备份文件被占用检查是否有编辑器、杀毒软件锁定备份文件重点说下 Windows 下 “the system cannot write to the specified device”。这个报错字面意思是“系统无法写入指定设备”但实际操作中 90% 是和路径有关bat 脚本里%date%拼接出的目录名包含了/或-比如D:\backup\2024/01/01然后 mkdir 时路径不合法mysqldump 重定向输出时就会炸。解决方法要么先把日期格式化干净要么使用if not exist预先创建目录。还有一个常见原因是备份盘符是网络映射盘或 BitLocker 锁定的盘写入时被系统拒绝。如果确认路径没问题打开磁盘权限看当前用户是否对该目录有写权限。5.2 恢复失败的典型原因版本、字符集、位点和可见性恢复时报错比备份时报错更让人头疼因为往往到了关键时候才发现。我见过的高频问题大概这些mysqldump 文件导入时报Unknown command或语法错误。绝大多数情况是备份文件被中断、损坏或者没有完整下载。还有一种隐蔽情况用旧版本 MySQL 的 mysqldump 备份然后导入到新版本 MySQL某些 SQL 语法不兼容导致报错。反过来用新版 mysqldump 备份旧库导入新库相对安全但也不绝对。字符集导致中文乱码或导入失败。这多半是备份时没指定--default-character-setutf8mb4或者恢复时目标库的默认字符集和备份文件里的不一致。导入前先查看备份文件头部的 SET NAMES 语句确认字符集再对应设置客户端。binlog 增量恢复时提示Could not find first log file name in binary log index。通常是--start-position和 binlog 文件对不上或者 binlog 文件已经被清理了备份里记录的 binlog 文件在服务器上根本不存在。这也是为什么我一直强调 binlog 一定要归档到备份目录或异地光靠生产服务器本地保留等要恢复时文件可能早就没了。另外一个隐蔽问题binlog 重放时出现主键冲突。原因通常是全量备份位点取错或者恢复过程中手动改过数据或者 binlog 里包含了重复事务。此时不要强行跳过错误要回到全量备份和 binlog 的衔接点重新核对最好先导出一份 binlog 文本用--verbose查看内容定位冲突。5.3 恢复后的校验工作数据对得上才算恢复成功很多人恢复完看到 MySQL 能启动、表能查询就宣布“恢复完成”。但真正的校验远不止这些我一般会做以下几件事核对关键表的行数从业务侧抽取几张核心表和故障前已知的业务报表数据比对。如果业务侧有固定的统计报表恢复后直接跑一遍报表看是否跟历史趋势一致。检查 binlog 位点确认恢复后的数据库确实停在了目标时间点而不是比目标时间点晚了或者早了。可以在恢复后的实例上执行SHOW MASTER STATUS结合 binlog 内容看最后一个事务是什么。验证存储过程和触发器逻辑备份如果用--routines导出了恢复后要检查这些对象是否都在函数是否可调用触发器是否在写入时正常触发。业务冒烟测试找业务方配合在恢复实例上做只读查询、小范围写入、删除修改等操作确认关键链路没有报错。校验这步不能省。我的经验是恢复后 1 小时内发现遗漏还能快速补救等业务接手跑了两天发现少数据那就真的是事故了。5.4 备份恢复脚本状态巡检用最简单的手段盯住备份健康脚本写好了告警加上了不代表可以万事大吉。备份健康巡检应该是常态化动作我建议至少做三件事第一每天查看备份日志确认备份成功、文件大小是否正常。如果发现备份文件大小比平时小很多第一反应不是“这次数据少了”而是“备份是不是漏了表”。第二每周做一次备份文件完整性抽检比如随机挑一天的压缩包gzip -t测试或导入到测试库确认可用。第三每月做一次完整恢复演练按真实事故流程走一遍包括停库、恢复全量、重放 binlog、校验数据。我自己的习惯是把备份脚本是否执行成功、备份文件大小、binlog 归档数量、磁盘剩余空间这几个指标做成一张简单的巡检表每天花两分钟看一眼。出现异常时能比业务方更早发现问题。这个习惯救过我一次某次备份脚本因为密码过期连续失败三天要不是巡检发现得早真到了恢复的时候手里就是一堆没用的文件。回头再单独提醒一句备份脚本不要写到“能跑”就收手。Linux 上注意/tmp下面是 tmpfs 的如果你的备份临时文件放在/tmp大备份可能直接撑爆内存盘Windows 上注意杀毒软件可能锁住备份文件导致 bat 脚本删除旧文件时删不掉。这些细节只有真正跑过一段时间的人才会碰上。写在最后数据库备份恢复这行光“会执行 mysqldump”远远不够。真正值钱的是把备份当成一套完整体系来运营想清楚 RPO/RTO选对工具脚本自动化异地归档定期验证再加上一套能快速响应的恢复流程。我现在每接手一个新项目第一件事就是检查它的备份方案和恢复演练记录没有的一律先补齐。这个习惯长期看真的能规避掉绝大多数“数据没了”的灾难现场。最后分享一个我自己的小技巧每次给客户做完备份方案我都会故意做一次“实战演练”——在某台非生产实例上挑一个不忙的时间模拟误删一张核心表让负责运维的同事亲自走一遍恢复流程。演练完再拉一个复盘把步骤、报错、耗时都记录下来。这样等到真正的故障来临时团队心里是有底的。MySQL 备份恢复没有任何玄学无非是平时多做点准备关键时刻按流程执行罢了。 MySQL备份恢复全攻略：从工具选型到误删数据恢复实战
去年接管一套老系统时赶上一次典型事故运营误操作把订单表 truncate 了结果发现这台 MySQL 上一次全量备份是十几天前binlog 也没有异地归档。最后花了一整天从磁盘碎片和 binlog 残留里手动拼数据业务停机超过 8 小时。那会儿我就下定决定不管项目大小MySQL 数据库备份与恢复方案必须第一条写进运维规范。这篇文章就把我这些年在 MySQL 备份恢复上踩过的坑、用过的工具、写过的脚本以及恢复现场的操作细节全部整理出来。会从备份方案设计、工具选型、自动脚本落地到误删数据后的完整恢复流程逐步展开也会把常见的报错和排查思路做成速查清单。无论你是刚接手公司数据库的新人 DBA还是自己搭站点的后端开发只要 MySQL 里存着不能丢的数据这篇文章就能直接给你一套可落地的备份恢复参考方案。1. 备份方案设计先别急着写脚本把恢复目标想清楚很多人一上来就写个 mysqldump 脚本然后 crontab 一挂就觉得万事大吉。等你真到了要恢复的时候才发现要么备份文件不可用要么缺少关键参数导致数据不一致要么增量 binlog 没保留找不到时间点。所以第一步不是选工具而是想清楚几个核心问题。1.1 RPO 和 RTO你最多能丢多少数据允许停机多久备份方案的设计核心是两个指标RPORecovery Point Objective和 RTORecovery Time Objective。说得直白点RPO 是“你能容忍丢多少数据”RTO 是“出事后你多久必须恢复业务”。比如一个电商网站用户下单支付这种场景 RPO 最好趋近于零也就是任何一秒钟的数据都不能丢。那你的方案必须是“实时 binlog 归档 定期全量备份”否则一旦主库磁盘损坏你丢的就是从上次备份到现在所有交易记录。而一个纯展示型官网数据一天更新一次那 RPO 可以放宽到 24 小时每天凌晨备份一次就够。RTO 同样关键。如果是核心交易库业务方可能要求 30 分钟内恢复那你必须提前演练物理备份恢复流程因为用 mysqldump 导几十个 G 的数据再 source 回去可能几个小时都搞不定。反过来如果业务允许停机半天逻辑备份的压力就会小很多。这个阶段要输出的是一张“备份需求确认表”至少包含数据量、增长量、允许丢失时间、允许停机时间、恢复对象粒度整库/单表、备份保存周期。这张表直接决定你后面选择什么工具、备份频率多高、存几份、放哪里。1.2 三种备份类型全量、增量、差异怎么组合最合理MySQL 备份从类型上分最常用的是全量备份和增量备份。差异备份在 MySQL 场景里用得不多这里不展开。全量备份就是一个完整的数据快照可以用 mysqldump 导出逻辑数据也可以用 xtrabackup 直接拷贝物理文件。增量备份则是基于 binlog把全量备份之后产生的所有变更记录下来。常见的组合方式有两种全量备份 binlog比如每天凌晨 2 点做一次全量备份binlog 持续保留并同步到异地。恢复时先恢复最近一次全量备份再重放 binlog 到指定时间点。全量备份 增量备份 binlog比如周日做全量周一到周六每天做 xtrabackup 增量备份binlog 实时归档。这种方案适合数据量较大、全量备份耗时长、但恢复要求又比较高的场景。重点说下“全量 binlog”这种组合它其实已经能覆盖绝大多数中小型业务。全量备份保证基础数据binlog 保证全量之后的每一次变更理论上你可以恢复到任意时间点。前提是 binlog 完整且连续。很多人备份了全量却忽略了 binlog 的连续性binlog 在服务器上被自动清理了或者只保留了最近三天结果恢复时找不到更早的 binlog一样白搭。所以我给客户的统一建议是binlog 保留周期至少是全量备份间隔的两倍比如每天全量备份binlog 至少留 7 天同时把 binlog 文件同步到备份服务器归档不能只依赖本地。1.3 物理备份和逻辑备份同样是全量差别很大全量备份还能继续拆成物理备份和逻辑备份这两个必须拎清楚。逻辑备份用 mysqldump、mydumper 这类工具把数据通过 SQL 语句导出来生成的是 .sql 文件。它的优势是跨版本、跨平台恢复方便文件可读还能只恢复某几张表。缺点是导出和恢复都慢而且数据量大时占用资源明显恢复几个小时的场景很常见。物理备份用 xtrabackup 这类工具直接拷贝 MySQL 数据目录下的物理文件。它的优势是备份和恢复速度极快特别适合大库基本就是文件拷贝进度。缺点是比较依赖同版本 MySQL跨小版本升级时可能不兼容恢复文件也不可读。选型上我的经验是数据量在 10G 以内用逻辑备份完全够10G 到 50G 看你对恢复速度的容忍度也可以继续用逻辑备份超过 50G 或者对 RTO 要求高就应该考虑 xtrabackup 物理备份。注意这个阈值不是绝对的取决于服务器性能和业务容忍度。1.4 备份验证备份文件没有经过恢复验证等于没有备份这是我最想强调的一点。见过太多团队备份脚本跑了几年日志全绿但有一天真要恢复时发现备份文件是坏的或者 mysqldump 导出来因为字符集问题导入直接报错又或者 binlog 因为服务器重启序号断开根本对不上。备份验证最有效的办法就是“周期性恢复演练”。在不影响生产的前提下在测试实例上定期从备份文件恢复一次然后做简单的数据校验比如对比行数、抽查关键表。频率不用太高一个月一次就够。如果资源紧张至少每次全量备份完成后做一次“备份文件完整性检查”用 gzip -t 验证压缩包没损坏用 mysqldump 备份的可以看看文件尾部有没有完整的 Dump completed 标记。另外备份文件一定要“异地存放”。如果全量备份和生产库在同一台服务器的同一块磁盘上磁盘坏了就是一起死。最简单的方案是备份完成后自动 rsync 到另一台机器或者对象存储。2. 核心工具选型mysqldump、mydumper、xtrabackup 到底用哪个工具选对了备份恢复这件事已经成功一半。我自己的工具箱里长期放着三套mysqldump 处理小库和单表逻辑导出mydumper 处理中等库的并行导出xtrabackup 处理大库和物理备份。下面逐个展开。2.1 mysqldump最常用的逻辑备份工具参数必须用对mysqldump 是 MySQL 自带的逻辑备份工具大多数人的入门备份工具就是它。但很多参数如果没用对备份出来的数据可能是不一致的或者缺少存储过程、触发器、事件恢复的时候才发现少了东西。生产环境我常用的核心参数是这一串mysqldump \ -h127.0.0.1 -P3306 \ -ubackup_user -p \ --single-transaction \ --routines \ --triggers \ --events \ --master-data2 \ --set-gtid-purgedOFF \ --databases db1 db2 \ backup.sql逐个解释一下关键参数--single-transaction对 InnoDB 表开启一个一致性快照备份期间不会锁表。这是 InnoDB 场景下必备参数不加它备份期间的写入会导致备份数据不一致大表可能导致长时间锁表。--routines、--triggers、--events分别导出存储过程和函数、触发器、事件调度器。这些对象默认不导出漏掉的后果是恢复后业务跑起来才发现自定义存储过程全没了。--master-data2在备份文件里记录 binlog 文件名和 position 位置以注释形式写入恢复后做增量恢复时全靠这个定位起点。1 的话会以 CHANGE MASTER TO 形式写入用于搭建 slave 也方便。--set-gtid-purgedOFF如果开启了 GTID默认导出会带 SET GLOBAL.GTID_PURGED 语句在只想导入数据而不是搭建复制环境时可能报错所以看场景加。--databases db1 db2指定导出多个库同时会在备份文件里自动加 CREATE DATABASE 和 USE 语句恢复时不用手动建库。关于字符集建议在命令行加--default-character-setutf8mb4否则如果你服务器端默认字符集不是 utf8mb4导出来的中文内容可能变成乱码。另外导出时尽量用专门的备份账号权限上只需要 SELECT、SHOW VIEW、TRIGGER、LOCK TABLES、RELOAD 这些就够不要天天用 root 跑备份。2.2 mydumper需要并行导出时的替代方案mysqldump 虽然有--parallel之类的参数但一直没做得很强。数据量大了之后单线程导出非常痛苦。这时候可以上 mydumper。mydumper 是多线程逻辑备份工具它能按表并行导出也支持把大表按行范围拆分成多个 chunk 并行导出。恢复时配合 myloader 也能并行导入。在中等数据量场景下速度能比 mysqldump 快好几倍。不过 mydumper 也有坑。它对 MySQL 版本的兼容性比较敏感比如 8.0 的某些版本需要用最新版本 mydumper 才能正确处理。另外它导出的文件是每张表一个 .sql 文件加一个 metadata 文件恢复时要用 myloader不能直接 source。如果你只想要一个完整的 .sql 文件方便直接导入mydumper 反而没那么方便。我的建议是如果你的环境是 MySQL 5.7 或 8.0且数据量在几十 G 以内mysqldump 还是优先一旦超过这个量级或备份窗口不够可以考虑 mydumper但要先在测试环境把整个备份恢复流程打通再上生产。2.3 xtrabackup大库场景下的物理备份主力xtrabackup 是 Percona 出品的物理备份工具现在 8.0 版本叫 Percona XtraBackup 8.0。它是目前 MySQL 物理备份事实标准原理是直接复制 InnoDB 数据文件同时备份过程中持续追踪 redo log最终做到一致性备份。物理备份的好处不用多说备份速度就是文件拷贝速度恢复速度也快。尤其适合数据量几十 G 甚至几个 T 的生产库。xtrabackup 有一个核心概念备份完成后数据文件本身是“不一致”的必须经过一个--prepare阶段应用 redo log才能把数据文件恢复到一致状态。这个阶段很多人会忽略拿到备份直接 cp 回去启动 MySQL结果数据文件报错或部分数据找不到。全量备份命令大概长这样xtrabackup --backup \ --target-dir/backup/mysql/20240101 \ --userbackup_user --passwordxxx \ --host127.0.0.1 --port3306恢复前先 preparextrabackup --prepare \ --target-dir/backup/mysql/20240101prepare 完成后再用xtrabackup --copy-back或者直接手动 rsync 数据目录。注意mysqld 进程需要停止数据目录需要清空文件属主要改成 mysql 用户。2.4 选型对比一张表帮你快速决策工具这么多到底选哪个我做了个表方便你直接对着选工具备份类型适合数据量备份速度恢复速度是否锁表支持增量跨版本恢复mysqldump逻辑10G 以内慢慢需配 single-transaction不支持较灵活mysqlpump逻辑10G-50G中中需配置不支持较灵活mydumper逻辑50G 以内较快较快需配置不支持一般xtrabackup物理50G 以上快快基本不锁支持对版本敏感实际生产环境里我见过很多团队的做法是全量用 xtrabackup增量用 binlog两者结合兼顾速度和恢复粒度。如果你是小团队、数据量不大从 mysqldump 开始完全没问题关键是把自动化、异地保存、恢复验证做好。3. 自动备份脚本实操Linux shell 和 Windows bat 两种写法备份方案定了工具定了接下来就是把备份这件事自动化。手动执行备份这种事干一次两次行时间久了必然出岔子。下面给两套脚本模板一套是 Linux 的 shell 脚本一套是 Windows 的 bat 脚本都是我自己用过、改过的版本。3.1 Linux shell 脚本mysqldump 全量 清理历史 日志记录我的备份脚本一般放在/usr/local/bin/mysql_backup.sh核心逻辑包括定义变量、执行备份、压缩、清理旧备份、写日志。给你一个能直接改用的版本#!/bin/bash BACKUP_DIR/backup/mysql DATE$(date %Y%m%d_%H%M%S) DB_USERbackup_user DB_PASSyour_password DB_HOST127.0.0.1 DB_PORT3306 KEEP_DAYS7 LOG_FILE/var/log/mysql_backup.log mkdir -p ${BACKUP_DIR}/${DATE} echo [$(date %F %T)] backup start ${LOG_FILE} mysqldump \ --host${DB_HOST} \ --port${DB_PORT} \ --user${DB_USER} \ --password${DB_PASS} \ --single-transaction \ --routines --triggers --events \ --master-data2 \ --all-databases \ | gzip ${BACKUP_DIR}/${DATE}/all_databases.sql.gz if [ $? -eq 0 ]; then echo [$(date %F %T)] backup success ${LOG_FILE} echo ${DATE} ${BACKUP_DIR}/latest_backup.txt else echo [$(date %F %T)] backup failed ${LOG_FILE} # 可以在这里加告警比如调用 webhook 发送到钉钉/企微 exit 1 fi # 清理超过保留天数的备份目录 find ${BACKUP_DIR} -maxdepth 1 -type d -name 20* -mtime ${KEEP_DAYS} -exec rm -rf {} \;几个细节说明一下备份目录按日期分文件夹方便后续恢复时定位lib变量和密码不要写死在脚本里我这边示例为了方便展示生产环境建议改成从/root/.my.cnf读密码--all-databases表示全库备份如果你只想备份业务库改成--databases db1 db2并去掉--all-databases。清理旧备份用的find -mtime ${KEEP_DAYS}这个逻辑是按文件修改时间判断。要注意备份目录要独立分出来别和其他文件混在一起否则可能误删。日志文件建议也做轮转或者定期清理否则时间久了日志文件能撑满磁盘。crontab 配置放在/etc/crontab或者crontab -e20 2 * * * /usr/local/bin/mysql_backup.sh /var/log/mysql_backup_cron.log 21我习惯加 ... 21这样脚本本身的输出和报错也会进日志排查时更方便。3.2 Windows bat 脚本日期格式化是大部分“写入设备错误”的元凶Windows 服务器上跑 MySQL同样可以自动备份bat 脚本就行。热搜词里有个典型报错“bat 备份mysql数据库提示 the system cannot write to the specified device”这个我见过太多次了后面常见问题里细说。先给脚本模板echo off set BACKUP_DIRD:\backup\mysql set DATE%date:~0,4%%date:~5,2%%date:~8,2% set TIME%time:~0,2%%time:~3,2% set TIMESTAMP%DATE%_%TIME% set DB_USERbackup_user set DB_PASSyour_password set LOG_FILED:\backup\mysql_backup.log if not exist %BACKUP_DIR% mkdir %BACKUP_DIR% echo %date% %time% backup start %LOG_FILE% mysqldump -h127.0.0.1 -P3306 -u%DB_USER% -p%DB_PASS% --single-transaction --routines --triggers --events --all-databases %BACKUP_DIR%\all_databases_%TIMESTAMP%.sql if %errorlevel% equ 0 ( echo %date% %time% backup success %LOG_FILE% ) else ( echo %date% %time% backup failed errorlevel %errorlevel% %LOG_FILE% ) forfiles -p %BACKUP_DIR% -m *.sql -d 7 -c cmd /c del pathbat 脚本有几个非常容易踩的坑。一是日期格式化%date%和%time%的输出格式受系统区域设置影响有的服务器输出2024/01/01有的输出01/01/2024直接拼接成文件名很容易出问题。我上面的写法是按yyyyMMdd_HHmm截取但如果你的系统日期格式不是这种顺序要先在测试环境echo %date%看看输出到底是什么再调整截取位置。二是密码包含特殊字符时bat 里转义比较麻烦建议用配置文件或环境变量而不是直接把密码怼在脚本里。三是forfiles清理旧文件系统是 Windows 7 或 Server 2008 以上一般自带老系统可能没有。3.3 binlog 增量备份脚本只备全量不管 binlog等于白备全量备份解决的是“过去某个时间点的完整数据”但备份之后新写入的数据都在 binlog 里。如果你的 binlog 不单独备份生产库的 binlog 又默认只保留几天那全量备份的作用就大打折扣恢复点只能落在全量备份的时刻。所以我每次都会额外配一个 binlog 归档任务。最简单的做法是定期把 binlog 文件复制到备份目录同时记录文件名。MySQL 8.0 和 5.7 都可以用mysqlbinlog的远程拉取功能也可以直接监听 binlog 目录做同步。shell 版 binlog 归档脚本#!/bin/bash BACKUP_BINLOG_DIR/backup/binlog BINLOG_DIR/var/lib/mysql MYSQL_BINLOG_INDEX${BINLOG_DIR}/binlog.index LAST_FILE_FLAG/backup/binlog/last_file.txt mkdir -p ${BACKUP_BINLOG_DIR} # 找到当前已归档到哪个文件 LAST_FILE if [ -f ${LAST_FILE_FLAG} ]; then LAST_FILE$(cat ${LAST_FILE_FLAG}) fi # 遍历 binlog.index 中新增的 binlog while read binlog_name; do binlog_file${BINLOG_DIR}/${binlog_name} if [ ${binlog_name} ${LAST_FILE} ]; then continue fi if [ -f ${binlog_file} ]; then cp -f ${binlog_file} ${BACKUP_BINLOG_DIR}/ echo ${binlog_name} ${LAST_FILE_FLAG} fi done ${MYSQL_BINLOG_INDEX}这段逻辑不算复杂把 binlog.index 里记录的文件逐个复制到备份目录并用一个状态文件记录上次复制到哪个文件。这样至少保证 binlog 在本地被清理后备份目录里还有归档副本。当然生产环境更推荐用 mysqlbinlog 的--read-from-remote-server或直接用专业备份工具做连续归档这里给的是最小可用的脚本方案。3.4 脚本必备的“安全护栏”经验备份脚本跑起来后有三件事必须同时做好少了任何一件都可能在关键时刻掉链子。磁盘空间监控是最容易忽视的。备份文件增长速度可能超出预期特别是 binlog 归档如果没做清理备份磁盘被写满是迟早的事。备份失败还能通过日志发现磁盘满了其他服务可能一起遭殃所以我会额外配一个磁盘空间检查超过 80% 就告警。脚本执行日志和最终备份结果告警也要加上。最简单的方案是脚本里把成功或失败写入日志同时调用企业微信/钉钉的 webhook 发一条消息到群里。这样每天定时备份完成后相关人员能在群里看到今天备份结果没收到消息就说明有问题。这个小习惯救过我很多次。最后是账号权限。备份账号不要用 root单独创建一个账号只授予必要权限密码不要写在脚本里Linux 可以用~/.my.cnf[mysqldump] userbackup_user passwordyour_passwordWindows 上可以用环境变量或临时拼接总之别把生产库 root 密码明文放在任何脚本里。4. 恢复实操从误删数据到全量恢复备份做得再好恢复不行也白搭。这一节我会把最常遇到的恢复场景过一遍包括全量恢复、binlog 增量恢复、误删库表后的定向恢复以及 xtrabackup 物理备份的恢复流程。每步都会说清楚为什么这么做。4.1 恢复前先做三件事确认时间线、找备份、锁定位点不管什么恢复场景在做任何操作之前先冷静下来确认三件事第一故障发生的时间点精确到秒这决定 binlog 恢复到哪第二现有的备份文件有哪些最近一次有效全量备份是什么时候第三全量备份里记录的 binlog position 是什么。这里说的 binlog position如果你用 mysqldump 并加了--master-data2备份文件头部会有类似这样的一行-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000014, MASTER_LOG_POS120345;这就是全量备份对应的 binlog 文件和偏移量。恢复时从全量备份恢复出数据后binlog 重放就从这个文件、这个位置开始而不是从备份时间点之前开始。如果用的是 GTID 模式那么备份文件里会有一段SET GLOBAL.GTID_PURGED...恢复时需要用--set-gtid-purged或--skip-gtid来控制否则可能跳过一些事务。这块细节很多但逻辑通了就顺着走找到全量备份位点之后重放 binlog 到目标时间或位置。4.2 从 mysqldump 全量备份恢复标准流程与提速技巧从逻辑备份恢复本质就是把 .sql 文件导入 MySQL。基础流程没什么神奇mysql -h127.0.0.1 -P3306 -uroot -p /backup/mysql/20240101/all_databases.sql但有几件事要做在前面。第一导入前确认目标实例的字符集、时区与原库一致否则中文乱码或时间错乱够你喝一壶。第二如果有--routines导出的存储过程和触发器导入时最好用 root 或具备 SUPER 权限的账号否则创建过程的权限可能不够。第三导入大文件时建议用source或者分库导入不要一条mysql 就完事中途报错不好定位。如果只需要恢复某一张表可以把备份文件里对应表的 INSERT 语句筛出来或者直接用sed截取需要的部分。更干净的做法是备份时就考虑粒度小表用--databases db_name --tables table_name单独备份恢复时直接拿来导入。大数据量导入时还有一个技巧先临时关闭唯一约束检查和外键检查导入完成后再开启能明显提速。对应 SQLSET FOREIGN_KEY_CHECKS0;和SET UNIQUE_CHECKS0;。4.3 使用 binlog 增量恢复从全量位点到指定时间或位置全量恢复只能恢复到备份时刻之后的数据就得靠 binlog 补齐。这是恢复流程里技术要求最高的一步。假设全量备份是今天凌晨 2 点做的对应 binlog pos 是mysql-bin.000014:120345而业务在上午 10:30:00 误删了一张表。我们要做的就是把mysql-bin.000014从 position 120345 开始到mysql-bin.000020截止重放到误删前的那一刻。命令大概是mysqlbinlog \ --no-defaults \ --start-position120345 \ --stop-datetime2024-01-01 10:29:59 \ mysql-bin.000014 mysql-bin.000015 mysql-bin.000016 mysql-bin.000017 mysql-bin.000018 mysql-bin.000019 mysql-bin.000020 \ --databaseyour_db \ | mysql -h127.0.0.1 -uroot -p your_db几个关键点拆开讲--start-position从全量备份文件头部记录的位点开始而不是从 0 开始否则会把全量备份之前已经包含的数据再重放一遍导致主键冲突。--stop-datetime指定截止时间这里要给出误删操作发生前的一秒特别小心时区问题。如果你不确定精确时间可以用--stop-position配合mysqlbinlog解析出来的内容找到误删语句的位置。--databaseyour_db限定只重放指定库的语句。注意 binlog 里记录的 SQL 如果使用了跨库操作这个过滤可能不生效具体要看语句里有没有显式库名。多个 binlog 文件一次性传给 mysqlbinlog它会按顺序解析不用多次执行。实际操作中误删语句往往是一条 DROP TABLE 或者 DELETE它会出现在某个 binlog 文件里。在重放之前我通常先跑一遍mysqlbinlog把相关 binlog 导出成文本文件然后 grep 出误删语句的位置再用--stop-position精确截止。这样不会因为时间差导致多放或少放事务。4.4 误删单表后的定向恢复避免全库恢复的“杀伤力”有时候只是误删了一张表但备份是全库的。如果直接把整库恢复出来再处理影响面太大而且可能要停库。更常见的做法是临时实例恢复取出需要的那张表。这样不会影响线上正常跑着的业务整个过程完全离线。我的标准操作是这样的准备一台临时实例或者本机另起一个端口用最近一次全量备份恢复到临时实例然后用 binlog 增量恢复把误删操作之前的数据补齐最后从临时实例把目标表的 .sql 导出导入到生产库。具体到 mysqldump 全量备份怎么只恢复一张表可以这样# 在临时库恢复时把备份文件中的库表筛选出来 zcat /backup/mysql/20240101/all_databases.sql.gz | grep -E CREATE TABLE.*your_table|INSERT INTO.*your_table your_table.sql # 注入到临时库 mysql -h127.0.0.1 -P3307 -uroot -p your_db your_table.sql这个方法对简单表有效但如果表结构里有触发器、外键等依赖建议还是在临时实例完整恢复再整体导出目标表。需要提醒的是如果生产库在误删之后还有大量写入直接导回旧表可能会覆盖新数据或造成主键冲突此时先确认业务是否已经用新表继续写数据再决定恢复策略。4.5 xtrabackup 物理备份的恢复流程prepare 和 copy-back 不能省xtrabackup 的恢复和 mysqldump 完全不一样喜好直接操作数据目录。恢复前准备一份同版本 MySQL步骤如下第一步停止 MySQLsystemctl stop mysqld第二步清理或备份当前数据目录。注意数据目录里的隐藏文件、日志文件都要处理干净不能残留旧数据mv /var/lib/mysql /var/lib/mysql_broken mkdir -p /var/lib/mysql第三步prepare 备份文件。如果备份是增量备份prepare 时要把多个备份目录按顺序--apply-log-only合并最后再整体 prepare。全量备份则直接xtrabackup --prepare --target-dir/backup/mysql/20240101第四步copy-back文件复制回数据目录xtrabackup --copy-back --target-dir/backup/mysql/20240101第五步修改属主并启动chown -R mysql:mysql /var/lib/mysql systemctl start mysqld这里最容易出的问题有两个一是漏了 prepare 阶段直接把备份文件 copy 回去MySQL 启动会报 redo log 不一致二是 copy-back 后忘了chown以 root 复制出来的文件属主不对mysqld 根本起不来。另外xtrabackup 还原后的实例数据目录里可能存在旧的auto.cnf这会改变 server_uuid如果这个实例要作为 slave 重新挂载要注意主从复制里的 server_uuid 冲突问题。5. 常见问题与排查技巧实录备份恢复这件事平时不出问题岁月静好一出问题全是修罗场。这一节我把自己和同行在实际操作里碰到的典型报错、排查思路全部整理出来做成速查清单照着查能少走很多弯路。5.1 备份失败的典型原因权限、磁盘、锁和语法先列一个备份失败高频原因表现象最常见原因排查方向mysqldump: Access denied备份账号权限不足检查 GRANT 是否包含 SELECT、RELOAD、LOCK TABLESmysqldump: Got error: 1017表不存在或文件损坏检查表是否有异常先修复再备份backup failed with error 28磁盘空间不足df -h 看备份目录所在分区剩余空间the system cannot write to the specified deviceWindows 下自动备份时报错多为路径包含特殊字符、系统日期格式拼接错误或盘符不可写Lock wait timeout exceeded备份时与业务事务冲突确认是否漏了 --single-transaction或等待长事务结束The process cannot access the file because it is being used by another processWindows bat 备份文件被占用检查是否有编辑器、杀毒软件锁定备份文件重点说下 Windows 下 “the system cannot write to the specified device”。这个报错字面意思是“系统无法写入指定设备”但实际操作中 90% 是和路径有关bat 脚本里%date%拼接出的目录名包含了/或-比如D:\backup\2024/01/01然后 mkdir 时路径不合法mysqldump 重定向输出时就会炸。解决方法要么先把日期格式化干净要么使用if not exist预先创建目录。还有一个常见原因是备份盘符是网络映射盘或 BitLocker 锁定的盘写入时被系统拒绝。如果确认路径没问题打开磁盘权限看当前用户是否对该目录有写权限。5.2 恢复失败的典型原因版本、字符集、位点和可见性恢复时报错比备份时报错更让人头疼因为往往到了关键时候才发现。我见过的高频问题大概这些mysqldump 文件导入时报Unknown command或语法错误。绝大多数情况是备份文件被中断、损坏或者没有完整下载。还有一种隐蔽情况用旧版本 MySQL 的 mysqldump 备份然后导入到新版本 MySQL某些 SQL 语法不兼容导致报错。反过来用新版 mysqldump 备份旧库导入新库相对安全但也不绝对。字符集导致中文乱码或导入失败。这多半是备份时没指定--default-character-setutf8mb4或者恢复时目标库的默认字符集和备份文件里的不一致。导入前先查看备份文件头部的 SET NAMES 语句确认字符集再对应设置客户端。binlog 增量恢复时提示Could not find first log file name in binary log index。通常是--start-position和 binlog 文件对不上或者 binlog 文件已经被清理了备份里记录的 binlog 文件在服务器上根本不存在。这也是为什么我一直强调 binlog 一定要归档到备份目录或异地光靠生产服务器本地保留等要恢复时文件可能早就没了。另外一个隐蔽问题binlog 重放时出现主键冲突。原因通常是全量备份位点取错或者恢复过程中手动改过数据或者 binlog 里包含了重复事务。此时不要强行跳过错误要回到全量备份和 binlog 的衔接点重新核对最好先导出一份 binlog 文本用--verbose查看内容定位冲突。5.3 恢复后的校验工作数据对得上才算恢复成功很多人恢复完看到 MySQL 能启动、表能查询就宣布“恢复完成”。但真正的校验远不止这些我一般会做以下几件事核对关键表的行数从业务侧抽取几张核心表和故障前已知的业务报表数据比对。如果业务侧有固定的统计报表恢复后直接跑一遍报表看是否跟历史趋势一致。检查 binlog 位点确认恢复后的数据库确实停在了目标时间点而不是比目标时间点晚了或者早了。可以在恢复后的实例上执行SHOW MASTER STATUS结合 binlog 内容看最后一个事务是什么。验证存储过程和触发器逻辑备份如果用--routines导出了恢复后要检查这些对象是否都在函数是否可调用触发器是否在写入时正常触发。业务冒烟测试找业务方配合在恢复实例上做只读查询、小范围写入、删除修改等操作确认关键链路没有报错。校验这步不能省。我的经验是恢复后 1 小时内发现遗漏还能快速补救等业务接手跑了两天发现少数据那就真的是事故了。5.4 备份恢复脚本状态巡检用最简单的手段盯住备份健康脚本写好了告警加上了不代表可以万事大吉。备份健康巡检应该是常态化动作我建议至少做三件事第一每天查看备份日志确认备份成功、文件大小是否正常。如果发现备份文件大小比平时小很多第一反应不是“这次数据少了”而是“备份是不是漏了表”。第二每周做一次备份文件完整性抽检比如随机挑一天的压缩包gzip -t测试或导入到测试库确认可用。第三每月做一次完整恢复演练按真实事故流程走一遍包括停库、恢复全量、重放 binlog、校验数据。我自己的习惯是把备份脚本是否执行成功、备份文件大小、binlog 归档数量、磁盘剩余空间这几个指标做成一张简单的巡检表每天花两分钟看一眼。出现异常时能比业务方更早发现问题。这个习惯救过我一次某次备份脚本因为密码过期连续失败三天要不是巡检发现得早真到了恢复的时候手里就是一堆没用的文件。回头再单独提醒一句备份脚本不要写到“能跑”就收手。Linux 上注意/tmp下面是 tmpfs 的如果你的备份临时文件放在/tmp大备份可能直接撑爆内存盘Windows 上注意杀毒软件可能锁住备份文件导致 bat 脚本删除旧文件时删不掉。这些细节只有真正跑过一段时间的人才会碰上。写在最后数据库备份恢复这行光“会执行 mysqldump”远远不够。真正值钱的是把备份当成一套完整体系来运营想清楚 RPO/RTO选对工具脚本自动化异地归档定期验证再加上一套能快速响应的恢复流程。我现在每接手一个新项目第一件事就是检查它的备份方案和恢复演练记录没有的一律先补齐。这个习惯长期看真的能规避掉绝大多数“数据没了”的灾难现场。最后分享一个我自己的小技巧每次给客户做完备份方案我都会故意做一次“实战演练”——在某台非生产实例上挑一个不忙的时间模拟误删一张核心表让负责运维的同事亲自走一遍恢复流程。演练完再拉一个复盘把步骤、报错、耗时都记录下来。这样等到真正的故障来临时团队心里是有底的。MySQL 备份恢复没有任何玄学无非是平时多做点准备关键时刻按流程执行罢了。