MySQL备份与恢复详解：mysqldump、XtraBackup、binlog和PITR

发表于2020-02-10|数据库

|浏览量:

备份的目标是可恢复，不是「有个 dump 文件」。逻辑备份、物理备份与 binlog 各管一层。

三类手段

mysqldump：逻辑备份，便携、慢，适合小库或表级迁移。
XtraBackup：热物理备份，适合大库；配合 binlog 做 PITR。
binlog：增量变更日志，按时间点/位点恢复误删数据。

PITR 基本流程

恢复最近全量备份到临时实例。
mysqlbinlog --start-datetime=... --stop-datetime=... 重放增量。
验证后切换或回灌生产。

1 2	mysqldump -uroot -p --single-transaction --master-data=2 app > full.sql mysqlbinlog --start-position=154 mysql-bin.000012 \| mysql -uroot -p app

实践建议

定期做恢复演练；备份文件加密与异地存储；监控 binlog 保留天数与磁盘。

权限与脱敏

备份文件含全量数据，存储加密、访问审计、定期销毁策略必备。恢复环境应与生产网络隔离。

跨版本恢复

大版本升级前先在副本验证 backup 可恢复性。

实践复习清单

全量+binlog PITR 流程演练；备份加密存储；恢复 RTO/RPO 目标；xtrabackup 热备窗口；mysqlbinlog 位点管理；误删应急：只读、留 binlog、影子恢复。

常见坑

只有全量无 binlog，无法恢复到误操作前一刻。
备份从不恢复测试，真故障才发现文件损坏。
大库用 mysqldump 峰值拖垮生产 IO。

总结与自测

逻辑备份与物理备份适用场景？PITR 需要哪两类文件？误删表第一个动作是什么？备份文件如何保管？半年内是否做过恢复演练？无演练的备份等于没备份。

原理延伸

InnoDB 的核心竞争力在于把 B+ 树索引、缓冲池、redo/undo 与 MVCC 组合成可预测的 OLTP 引擎。读路径尽量走缓冲池与覆盖索引，写路径则要在行锁、日志刷盘与复制延迟之间取平衡。排查问题时不要只盯单条 SQL，还要看事务边界、锁等待、buffer pool 命中率与磁盘 fsync 延迟。性能优化永远遵循「先正确、再可测、后优化」：用慢日志和 EXPLAIN 定位瓶颈，用规范设计减少回表与锁竞争，用架构手段（读写分离、缓存、分片）承接流量，而不是在未测量前堆参数。

文章作者: Dai Wei

文章链接: https://www.daiwei.biz/knowledge/database/mysql/mysql-backup-and-restore/

版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Dai Wei！

MySQL 备份 binlog

扫码打赏

微信
支付宝

相关推荐

MySQL三大日志(binlog、redo log和undo log)详解

三大日志回答三个问题：崩溃如何恢复、事务如何回滚与 MVCC、主从与 PITR 如何做到。各 log 职责日志层级作用 redo InnoDB 崩溃恢复，保证已提交事务持久 undo InnoDB 回滚、MVCC 旧版本 binlog Server 复制、审计、时间点恢复两阶段提交事务提交时 redo prepare → 写 binlog → redo commit，保证引擎与 server 层一致，避免主从数据分叉。配置关注点123innodb_flush_log_at_trx_commit = 1 # 每次提交刷盘，最安全sync_binlog = 1 # binlog 同步刷盘binlog_format = ROW # 主从一致性与闪回友好实践建议误删恢复：先停写入，保留 binlog，全量+增量回放。监控 redo 日志等待与 checkpoint。 flashback 思路ROW binlog 可解析为反向 SQL，需工具（binlog2sql）...

MySQL事务隔离级别详解

隔离级别是并发正确性与性能的旋钮。InnoDB 默认 RR，但「可重复读」不等于完全无幻读。四种级别级别脏读不可重复读幻读 READ UNCOMMITTED 可能可能可能 READ COMMITTED 否可能可能 REPEATABLE READ 否否 InnoDB 当前读仍防 SERIALIZABLE 否否否 InnoDB 实现要点RC 每次 SELECT 新快照；RR 首次一致性读建 Read View。幻读在 RR 下通过 next-key lock 在当前读场景限制。 1234SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;START TRANSACTION;SELECT * FROM account WHERE id=1 FOR UPDATE;COMMIT; 实践建议大多数 OLTP 用默认 RR；若需要最新已提交读（如报表）可会话级 RC；避免长事务。死锁与隔离RR 下间隙锁增加死锁概率；应用捕获 1213 重试。锁等待 timeout 调优 inno...

一千行 MySQL 学习笔记

长篇笔记的价值在于「可检索的操作清单」。本文按连接、DDL/DML、权限与状态查看重组，方便日常查阅。连接与基础操作12345mysql -h127.0.0.1 -P3306 -uroot -pSHOW DATABASES;USE app;SHOW TABLES;DESC users; DDL 与 DML 要点建表时明确字符集、主键策略与必要索引；更新删除务必带 WHERE 并先 SELECT 验证影响行数。批量变更大表用 pt-online-schema-change 或 gh-ost，避免长时间锁表。用户与权限最小权限原则：应用账号仅授予所需库的 SELECT/INSERT/UPDATE/DELETE；DDL 与 SUPER 权限隔离给运维账号。状态诊断入口123SHOW PROCESSLIST;SHOW ENGINE INNODB STATUS\GSHOW VARIABLES LIKE 'innodb%'; 常用信息_schema 查询查表大小、索引Cardinality、未使用索引等可从 infor...

InnoDB存储引擎对MVCC的实现

MVCC 让读写少冲突：快照读不加锁，当前读才走锁。理解 Read View 是读懂 repeatable read 的关键。三要素隐藏列：DB_TRX_ID（最后修改事务 ID）、DB_ROLL_PTR（回滚指针）。 undo log：保存旧版本链，支持回滚与一致性读。 Read View：事务启动时（或首次一致性读）生成，判断行版本可见性。可见性规则（简化）比较行的 trx_id 与 Read View 的 min/max 及活跃事务列表：若版本对当前事务不可见，沿 undo 链找更早版本。快照读 vs 当前读1234-- 快照读（普通 SELECT）SELECT * FROM account WHERE id = 1;-- 当前读（加锁读）SELECT * FROM account WHERE id = 1 FOR UPDATE; RR 下快照读可避免不可重复读；幻读靠间隙锁/next-key lock 在当前读场景防护。实践建议短事务、避免长查询占旧版本导致 undo 堆积；监控 History list length。版本链长度频...

MySQL 专题：索引、事务、日志、备份恢复、MVCC 与性能优化

MySQL 是后端核心考点。把索引、执行计划、事务隔离、三大日志连成一条链，慢 SQL 与并发问题才看得懂。推荐学习顺序索引结构 → EXPLAIN 执行计划 → SQL 执行链路 → 隔离级别与 MVCC → redo/undo/binlog → 备份恢复与规范优化。核心主题地图索引：B+ 树、最左前缀、覆盖索引、回表、索引下推。事务：ACID、四种隔离级别、幻读与间隙锁。日志：redo 保证崩溃恢复，undo 支撑回滚与 MVCC，binlog 服务复制与 PITR。性能：慢查询、连接池、规范建表、避免隐式转换。实践方法每学一章做一个小实验：故意写错索引看 type 变化；开两个事务验证 repeatable read；用 binlog 做一次时间点恢复演练。常见误区只背八股不做 EXPLAIN；忽略 buffer pool 与磁盘 IO；把分库分表当性能万能药而不先优化 SQL。工具链配套慢日志、slow_query_log、performance_schema、sys schema、PMM/Grafana 形成闭环。S...

MySQL索引详解

索引是 InnoDB 性能的第一杠杆。理解 B+ 树与聚簇/二级索引差异，设计才不会拍脑袋。 B+ 树为何适合磁盘多路平衡、叶子链表便于范围扫；非叶只存键减少 IO。InnoDB 聚簇索引叶子存整行，二级索引叶子存主键值需回表。最左前缀与覆盖索引联合索引 (a,b,c) 可支持 a、a+b、a+b+c 前缀查询；覆盖索引指 SELECT 列全在索引内，Extra 出现 Using index。 123CREATE INDEX idx_user_status ON orders(user_id, status, created_at);-- 覆盖列尽量放进索引SELECT user_id, status, created_at FROM orders WHERE user_id=1 AND status=1; 索引下推 ICP5.6+ 在存储引擎层先过滤二级索引条件，减少回表次数。设计原则高选择性列在前；控制宽度；避免重复索引；监控 duplicate index。前缀索引与区分度INDEX(name(10)) 要算 distinct prefix 选择性。过...

评论

数据加载中