个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL 备份与恢复实战mysqldump、XtraBackup、binlog 增量与时间点恢复PITR一、先建立正确的备份心智模型1.1 三种备份层次1.2 冷备 / 温备 / 热备二、mysqldump逻辑备份的基本盘2.1 一条正确的全库备份命令2.2 常见的部分备份写法2.3 恢复三、Percona XtraBackup物理热备的主力3.1 它凭什么能做到热备3.2 全量备份与恢复3.3 增量备份3.4 关键元数据文件3.5 压缩、流式备份与 Clone Plugin四、binlog把 RPO 从一天压到几乎为零4.1 必须先配好这些4.2 定期刷出并异地保存 binlog4.3 时间点恢复PITR完整流程4.4 只回滚一条误操作闪回思路五、三种方案怎么选六、一套可以直接抄的备份策略七、恢复演练别跳过这一步八、常见误区小结MySQL 备份与恢复实战mysqldump、XtraBackup、binlog 增量与时间点恢复PITR备份这事儿有个残酷的特点平时看起来毫无价值出事那天决定你的去留。更残酷的是绝大多数团队有备份但从来没验证过能不能恢复。这篇把三种备份方式、增量策略、以及真正救命的 binlog 时间点恢复讲清楚顺带给出一套可以直接抄走的备份脚本和恢复演练流程。一、先建立正确的备份心智模型在选工具之前先回答两个问题它们决定一切RPO能容忍丢多少数据和RTO多久能恢复服务。RPO 是 5 分钟还是 0、RTO 是 2 小时还是 10 分钟直接决定你是每天一备还是全备 增量 实时 binlog。然后记住一条铁律没有演练过的备份 没有备份。1.1 三种备份层次类型备份对象代表工具速度恢复粒度逻辑备份SQL 语句 / 数据行mysqldump、mydumper慢单库、单表、单行物理备份数据文件.ibd、ibdata、redoXtraBackup、Clone Plugin快整实例为主binlog 备份增量变更日志mysqlbinlog、FLUSH LOGS极快任意时间点现实中的标准组合是每周一次全备 每天一次增量 实时备份 binlog出事时按全备恢复 → 增量回放 → binlog 追平到误操作前一秒的顺序还原。1.2 冷备 / 温备 / 热备方式数据库状态影响说明冷备实例关闭停服systemctl stop mysqld后直接打包数据目录温备运行中加全局读锁只读可写不可FLUSH TABLES WITH READ LOCK热备运行中正常读写几乎无影响靠 MVCC redoXtraBackup / mysqldump 一致性快照冷备最简单也最可靠但现在基本没人能接受停服。热备是主流热备的核心就是拿到一个一致性时间点 拿到对应的 binlog 位点。二、mysqldump逻辑备份的基本盘2.1 一条正确的全库备份命令mysqldump-h127.0.0.1-uadmin-p***\--single-transaction --master-data2--flush-logs\--routines--triggers--events--hex-blob\--default-character-setutf8mb4 --set-gtid-purgedOFF\--all-databases|gzip/backup/full_$(date%F).sql.gz每个参数的含义一个都不能少参数为什么必须--single-transaction在一个事务里用 RR 一致性快照导出不锁表。只对 InnoDB 有效--master-data2把备份时刻的 binlog 文件名和位置写成注释记进文件做增量恢复要用新版也写作--source-data--flush-logs备份开始时切一个新的 binlog之后的新变更都在新文件里方便定位--routines --triggers --events存储过程、触发器、事件。默认只带触发器函数和过程不带--hex-blob用十六进制导出 BINARY / BLOB避免二进制内容损坏--default-character-setutf8mb4防止中文和 emoji 变乱码--set-gtid-purgedOFF恢复到其它实例比如做从库之外的用途时避免写入 GTID 集导致冲突⚠️--single-transaction的坑只对 InnoDB 保证一致性。库里还有 MyISAM 表的话得改用--lock-all-tables或者干脆别用 MyISAM。它靠 RR 快照所以备份期间不要执行 DDL。DDL 不参与 MVCC 快照会导致导出的数据和之后拿到的 binlog 位点对不上。这也是 DDL 要放低峰的原因之一。长事务会让它卡住快照要等所有活跃事务结束才能建立。2.2 常见的部分备份写法mysqldump-uadmin-p--single-transaction--databasesshopshop.sql# 单库mysqldump-uadmin-p--single-transaction shop orders order_itemstables.sql# 指定表mysqldump-uadmin-p--no-data shopshop_schema.sql# 只备结构# 按条件导出归档常用先 EXCHANGE 出分区再 dump 独立表mysqldump-uadmin-p--single-transaction\--wherecreated_at 2024-01-01 AND created_at 2024-02-01\shop ordersorders_202401.sql2.3 恢复mysql-uadmin-p/backup/full_2024-06-01.sql# 全库gunzip/backup/full_2024-06-01.sql.gz|mysql-uadmin-p# 压缩过的# 单库备份文件里没有 CREATE DATABASE 时必须先建库再导入mysql-uadmin-p-eCREATE DATABASE shop DEFAULT CHARSET utf8mb4;mysql-uadmin-pshopshop.sqlmysqldump 的致命弱点恢复慢。恢复 逐条执行 INSERT 重建所有索引通常是备份时间的 3~10 倍。几百 GB 的库恢复到能用几个小时是常态。所以大库 200GB不要用 mysqldump 做主备份改用 XtraBackup。需要多线程逻辑备份时用mydumper / myloadermydumper-uadmin-p***-h127.0.0.1--outputdir/backup/mydump_$(date%F)\--threads8--compress--rows100000--trx-consistency-only myloader-uadmin-p***--directory/backup/mydump_2024-06-01\--threads8--overwrite-tables三、Percona XtraBackup物理热备的主力3.1 它凭什么能做到热备原理一句话拷数据文件的同时另起线程持续拷贝 redo log最后用 redo 把文件追平到一致状态。1. 启动一个后台进程实时监听 redo log 变化写入 xtrabackup_log 2. 拷贝 InnoDB 数据文件.ibd和系统表空间此时文件是不一致的没关系 3. FLUSH TABLES WITH READ LOCK短暂锁防 DDL同时取 binlog 位点 4. 拷贝 .frm / 非 InnoDB 表文件 / 记录 binlog 位点 5. UNLOCK TABLES停止 xtrabackup_log恢复时XtraBackup 内嵌一个 InnoDB 实例回放 xtrabackup_log已提交事务应用、未提交事务回滚——过程和 InnoDB 崩溃恢复一模一样。⚠️ 版本对应关系要记牢XtraBackup 版本支持的 MySQL2.4MySQL 5.6 / 5.78.0MySQL 8.0不支持 5.7而且8.0 版本的 XtraBackup 去掉了innobackupex直接用xtrabackup命令。3.2 全量备份与恢复# 备份xtrabackup--backup--useradmin--password***--host127.0.0.1\--target-dir/backup/full_$(date%F)# 准备prepare 崩溃恢复把文件追平到一致状态xtrabackup--prepare--target-dir/backup/full_2024-06-01# 恢复停库 → 清空数据目录 → 拷回 → 改权限 → 启动systemctl stop mysqldrm-rf/var/lib/mysql/* xtrabackup --copy-back --target-dir/backup/full_2024-06-01chown-Rmysql:mysql /var/lib/mysql systemctl start mysqld想保留原备份文件就用--copy-back想省一次拷贝用--move-back备份会被消耗掉。prepare 这一步必须在恢复之前做也可以提前做——很多团队在备份机上就 prepare 好这样真正故障时就只剩copy-back 启动RTO 短很多。3.3 增量备份增量备份只拷LSN 大于上次备份的页所以很快、很小。# 周日全备xtrabackup--backup--target-dir/backup/full--useradmin--password***# 周一基于全备的增量xtrabackup--backup--target-dir/backup/inc1 --incremental-basedir/backup/full\--useradmin--password***# 周二基于上一次增量的增量xtrabackup--backup--target-dir/backup/inc2 --incremental-basedir/backup/inc1\--useradmin--password***恢复时的 prepare 顺序是最容易搞错的地方# 1. 先 prepare 全备注意 --apply-log-only不回滚未提交事务xtrabackup--prepare--apply-log-only --target-dir/backup/full# 2. 依次把增量并入全备除最后一个外都要带 --apply-log-onlyxtrabackup--prepare--apply-log-only --target-dir/backup/full --incremental-dir/backup/inc1# 3. 最后一个增量不带 --apply-log-only让它做最终回滚xtrabackup--prepare--target-dir/backup/full --incremental-dir/backup/inc2# 4. 拷回systemctl stop mysqldrm-rf/var/lib/mysql/* xtrabackup --copy-back --target-dir/backup/fullchown-Rmysql:mysql /var/lib/mysql systemctl start mysqld为什么最后一个增量不能加--apply-log-only因为未提交事务可能在最后一个增量里才结束提前回滚会破坏一致性。记住口诀除最后一次外全部加--apply-log-only。3.4 关键元数据文件备份目录里有几个文件出事了要看它们文件内容用途xtrabackup_checkpoints备份类型full/incremental、from_lsn、to_lsn判断增量链是否正确xtrabackup_binlog_info备份时刻的 binlog 文件名 位点 GTID做时间点恢复的起点xtrabackup_info备份命令、时长、版本等审计和排查backup-my.cnf备份时的最小配置恢复时参考cat/backup/full/xtrabackup_binlog_info# mysql-bin.000042 197362 a1b2c3d4-...:1-982133.5 压缩、流式备份与 Clone Plugin# 压缩备份需额外安装 qpress恢复前先 --decompressxtrabackup--backup--compress--compress-threads4--target-dir/backup/full_comp# 流式备份直接传到备份机不在本地落盘xtrabackup--backup--streamxbstream --target-dir/tmp\|sshbackup10.0.0.9xbstream -x -C /backup/full_$(date%F)不想装 XtraBackup 的话8.0.17 自带Clone Plugin也能做物理备份CLONELOCALDATADIRECTORY/backup/clone_20240601;-- 本地克隆CLONE INSTANCEFROMadmin10.0.0.8:3306IDENTIFIEDBY***;-- 远程克隆限制克隆会清空目标目录 / 本地实例且版本必须完全一致。它更适合快速建从库做长期备份管理不如 XtraBackup 灵活。四、binlog把 RPO 从一天压到几乎为零全备只能回到备份那个时刻。要少丢数据必须靠 binlog 做增量。4.1 必须先配好这些[mysqld] server_id 1 log_bin /data/mysql/mysql-bin # 8.0 默认已开启 binlog_format ROW # 强烈建议 ROWSTATEMENT 有不确定性风险 sync_binlog 1 # 每次提交刷盘宕机不丢 binlog innodb_flush_log_at_trx_commit 1 binlog_row_image FULL # gh-ost 和闪回工具都依赖它 binlog_expire_logs_seconds 604800 # 保留 7 天5.7 用 expire_logs_daysSHOWVARIABLESLIKEbinlog_expire_logs_seconds;SHOWBINARYLOGS;SHOWMASTERSTATUS;-- 8.4 起为 SHOW BINARY LOG STATUS⚠️sync_binlog1和innodb_flush_log_at_trx_commit1就是常说的双一是最安全的配置代价是每次提交多一次 fsync。金融类业务必须双一普通业务可以折中成sync_binlog100innodb_flush_log_at_trx_commit2。4.2 定期刷出并异地保存 binlogFLUSH LOGS;-- 切一个 binlog老的就可以拿去备份了命令行mysqladmin flush-logs备份 binlog 的最简做法定时把已写完的 binlog 文件 rsync 到备份机。更稳妥的是用mysqlbinlog --read-from-remote-server实时拉取相当于一个轻量 binlog server。4.3 时间点恢复PITR完整流程场景周日 02:00 做全备周三 14:30 有人执行了UPDATE orders SET status0忘加 WHERE。要恢复到 14:29。# 1. 恢复全备数据回到周日 02:00systemctl stop mysqld;rm-rf/var/lib/mysql/* xtrabackup --copy-back --target-dir/backup/full;chown-Rmysql:mysql /var/lib/mysql# 2. 先隔离业务用 --skip-networking 启动或改防火墙别让应用连进来systemctl start mysqld --skip-networking# 3. 找到全备对应的 binlog 位点cat/backup/full/xtrabackup_binlog_info# mysql-bin.000042 197362# 4. 定位误操作位置ROW 格式要 decode 才看得懂mysqlbinlog --base64-outputdecode-rows-v--start-position197362\/data/mysql/mysql-bin.000042|grep-n-B5-A5UPDATE \orders\# 5. 假设误操作位点在 8934201恢复到它之前stop-position 是开区间mysqlbinlog --start-position197362--stop-position8934200\/data/mysql/mysql-bin.000042 /data/mysql/mysql-bin.000043|mysql-uadmin-p# 也可以按时间--start-datetime / --stop-datetime2024-06-05 14:29:59# 6. 校验数据确认无误后再开放业务关键注意点一定要先隔离业务再恢复--stop-position是开区间要停在误操作的前一个位置多个 binlog 文件按顺序写在一条命令里即可用 GTID 的库加--skip-gtids避免冲突。4.4 只回滚一条误操作闪回思路全库 PITR 太重时可以只把误操作反向执行。ROW 格式下UPDATE的 binlog 同时有 before image 和 after image用binlog2sql这类工具能自动生成反向 SQLpython binlog2sql.py-h127.0.0.1-uadmin-p***--start-filemysql-bin.000042\--start-datetime2024-06-05 14:30:00--stop-datetime2024-06-05 14:31:00-B# -B 生成回滚 SQL⚠️ 前提是binlog_row_imageFULL否则 before image 不全生成不了反向 SQL——这也是前文强调要设 FULL 的原因。五、三种方案怎么选场景推荐方案理由小库 50GB跨平台/跨版本迁移mysqldump逻辑备份兼容性最好中等库50GB~500GB生产主备份XtraBackup 全备 增量恢复快、锁窗口短大库 500GBXtraBackup 延迟从库靠延迟从库把 RTO 压到分钟级只想要逻辑备份但嫌慢mydumper / myloader多线程快速建从库XtraBackup 或 Clone Plugin直接拷数据 位点需要恢复到任意秒上面任意一种 binlogbinlog 才是 RPO 的保障归档历史分区mysqldump --where或SELECT INTO OUTFILE粒度最灵活额外强烈推荐延迟从库Delayed Replica。对付误删数据这类人为事故它比任何备份都快STOP REPLICA;-- 从库上设置延迟 1 小时CHANGEREPLICATIONSOURCETOSOURCE_DELAY3600;STARTREPLICA;-- 出事后把从库追到误操作前一秒STARTREPLICA UNTIL SOURCE_LOG_FILEmysql-bin.000042,SOURCE_LOG_POS8934200;5.7 用的是STOP SLAVE/CHANGE MASTER TO MASTER_DELAY/START SLAVE UNTIL。六、一套可以直接抄的备份策略#!/bin/bash# /usr/local/bin/mysql_backup.sh —— 周日全备其余日子增量set-euopipefailDATE$(date%F);DOW$(date%u);BASE/backup/mysql;USERadmin;PASS***if[$DOW-eq7];then# 全备TARGET${BASE}/full_${DATE};rm-rf${BASE}/inc_* xtrabackup--backup--user${USER}--password${PASS}--target-dir${TARGET}xtrabackup--prepare--target-dir${TARGET}# 提前 prepare缩短 RTOelse# 增量基于上一次增量没有就基于全备BASEDIR$(ls-d${BASE}/inc_*2/dev/null|tail-1)BASEDIR${BASEDIR:-$(ls -d ${BASE}/full_*|tail-1)}TARGET${BASE}/inc_${DATE}xtrabackup--backup--user${USER}--password${PASS}--target-dir${TARGET}\--incremental-basedir${BASEDIR}firsync-a--delete/data/mysql/mysql-bin.* backup10.0.0.9::mysql-binlog/# binlog 异地find${BASE}-maxdepth1-typed-mtime30-execrm-rf{}\;# 保留 30 天echo$(date)backup${TARGET}done/var/log/mysql_backup.log配套的监控告警备份是否成功退出码 日志关键字、备份文件大小是否合理突然变小通常是出了大问题、备份耗时趋势、最近一次成功备份距现在多久超过 48 小时必须告警。七、恢复演练别跳过这一步每季度至少做一次找一台空闲机器装同版本 MySQL → 用最近的备份完整恢复含增量 binlog→记录总耗时这就是你的真实 RTO→ 抽样校验数据 → 把 RTO 写进文档跟业务容忍度对齐。CHECKSUMTABLEorders;-- 校验用SELECTCOUNT(*),MAX(id),MAX(created_at)FROMorders;⚠️ 演练中最常发现的问题备份脚本里的密码早就过期了一直在假备份、备份目录磁盘满了只有空文件、增量链的--incremental-basedir指错导致链断、恢复后才想起备份机的 MySQL 版本和数据目录不兼容。八、常见误区误区 1有了主从复制就不用备份。——主库上一条DROP TABLE毫秒级同步到从库复制不是备份它只保证冗余不防误操作。误区 2备份文件生成了就等于备份成功。——没恢复过就不知道它是否可用备份了 3 年第一次恢复发现全是空文件是真实案例。误区 3binlog 随便设个过期时间就行。——binlog 保留时间必须≥ 全备周期7 天一次全备却只留 3 天 binlog中间 4 天就是恢复不了的空洞。误区 4--single-transaction万能。——库里有 MyISAM 表、或备份期间有 DDL一致性就不成立。误区 5XtraBackup 增量 prepare 时全部加--apply-log-only。——最后一次不能加否则未提交事务不回滚。误区 6恢复完直接开放业务。——先隔离校验再放流量恢复现场被二次污染是真实发生过的。误区 7备份和原始数据放同一块盘。——磁盘一坏两边一起没。遵循3-2-1 原则3 份副本、2 种介质、1 份异地。小结RPO 和 RTO 决定备份方案先跟业务对齐这两个数字再谈工具三种备份层次逻辑备份mysqldump/mydumper、物理备份XtraBackup/Clone、binlog 增量mysqldump 必备参数--single-transaction --master-data2 --flush-logs --routines --triggers --events --hex-blobmysqldump 的弱点是恢复慢要重放 SQL 建索引大库请用 XtraBackupXtraBackup 靠拷数据文件 并行追 redo实现热备恢复时 prepare 相当于一次崩溃恢复增量 prepare 口诀除最后一次外全部加--apply-log-only出事后看xtrabackup_binlog_info拿位点再用mysqlbinlog --start-position/--stop-position追平binlog 才是把 RPO 压到秒级的关键binlog_formatROWbinlog_row_imageFULL 双一 过期时间 ≥ 全备周期人为误删的克星是延迟从库SOURCE_DELAY和 binlog 闪回工具复制不是备份备份必须异地必须定期做恢复演练并测出真实 RTO下一篇聊生产关键参数调优——备份决定了你能不能活下来参数决定了你能不能跑得稳。
