一、二进制日志基础

查看二进制日志配置

 查看二进制日志状态

SHOW VARIABLES LIKE 'log_bin%';

 

 查看当前二进制日志文件

SHOW MASTER STATUS;

 

 查看所有二进制日志文件

SHOW BINARY LOGS;

 

 查看二进制日志格式

SHOW VARIABLES LIKE 'binlog_format';

 

输出示例:

 Variable_name               Value                         

 log_bin                     ON                            

 log_bin_basename            /var/lib/mysql/mysql-bin      

 log_bin_index               /var/lib/mysql/mysql-bin.index

 File              Position  Binlog_Do_DB  Binlog_Ignore_DB  Executed_Gtid_Set

 mysql-bin.000003      785                                                   

 

二、二进制日志恢复的基本方法

1. 全量备份 + 二进制日志恢复流程

这是最常用的恢复策略:

1. 恢复最近的全量备份

2. 应用全量备份之后的二进制日志

 

 1. 恢复全量备份

mysql -u root -p < full_backup.sql

 

 2. 应用二进制日志(从备份时间点开始)

mysqlbinlog --start-datetime="2023-10-27 10:00:00" mysql-bin.000003 | mysql -u root -p

mysqlbinlog mysql-bin.000004 | mysql -u root -p

mysqlbinlog mysql-bin.000005 | mysql -u root -p

 

2. 恢复到指定时间点(Point-in-Time Recovery)

 恢复到特定时间点(不包括该时间点之后的操作)

mysqlbinlog --stop-datetime="2023-10-27 14:30:00" mysql-bin.000003 | mysql -u root -p

mysqlbinlog --stop-datetime="2023-10-27 14:30:00" mysql-bin.000004 | mysql -u root -p

 

 恢复特定时间范围内的操作

mysqlbinlog --start-datetime="2023-10-27 10:00:00" \

            --stop-datetime="2023-10-27 11:00:00" \

            mysql-bin.000003 | mysql -u root -p

 

3. 恢复到指定位置点

 恢复到特定二进制日志位置

mysqlbinlog --stop-position=107 mysql-bin.000003 | mysql -u root -p

mysqlbinlog --start-position=107 --stop-position=185 mysql-bin.000004 | mysql -u root -p

 

三、高级恢复技巧

1. 查看二进制日志内容

在恢复前,先查看日志内容确认要执行的操作:

 查看二进制日志内容(可读格式)

mysqlbinlog mysql-bin.000003

 

 查看特定数据库的操作

mysqlbinlog --database=test_db mysql-bin.000003

 

 输出到文件查看

mysqlbinlog mysql-bin.000003 > binlog_content.sql

 

2. 跳过某些操作

 跳过某些GTID或特定位置的操作

mysqlbinlog --exclude-gtids='UUID:1-100' mysql-bin.000003 | mysql -u root -p

 

3. 仅恢复特定表

 先提取相关表的操作到SQL文件

mysqlbinlog --database=test_db mysql-bin.000003 | grep -A 5 -B 5 "mytable" > table_recovery.sql

 

 然后审核并执行

mysql -u root -p test_db < table_recovery.sql

 

四、二进制日志管理

1. 清理二进制日志

 删除指定日志文件之前的所有日志

PURGE BINARY LOGS TO 'mysql-bin.000010';

 

 删除3天前的所有日志

PURGE BINARY LOGS BEFORE '2023-10-24 00:00:00';

 

 设置自动过期时间(单位:天)

SET GLOBAL binlog_expire_logs_seconds = 604800; -- 7

 

2. 手动切换日志文件

 强制创建新的二进制日志文件

FLUSH BINARY LOGS;

 

五、完整的数据恢复示例

场景:误删除数据后的恢复

 1. 发现误操作时间:2023-10-27 14:25:00

 2. 立即停止应用写入,防止新日志覆盖恢复信息

 3. 查看当前二进制日志状态

mysql -u root -p -e "SHOW MASTER STATUS"

 4. 恢复全量备份(假设备份时间是今天凌晨2点)

mysql -u root -p < /backups/full_backup_20231027_020000.sql

 5. 应用凌晨2点到误操作前的二进制日志

mysqlbinlog --start-datetime="2023-10-27 02:00:00" \

            --stop-datetime="2023-10-27 14:24:59" \

            mysql-bin.000003 mysql-bin.000004 | mysql -u root -p

 6. 验证数据完整性

 7. 重新开启应用写入

 

六、最佳实践与注意事项

1. 定期备份:始终保留多个时间点的全量备份

2. 监控日志大小:二进制日志可能快速增长,需要监控磁盘空间

    设置二进制日志大小限制

   SET GLOBAL max_binlog_size = 1073741824; -- 1GB

3. 安全存储:将二进制日志存储在与数据文件不同的磁盘上

4. 测试恢复流程:定期测试备份恢复流程,确保可用性

5. 记录重要位置点:在执行重要操作前手动记录二进制日志位置

    重要操作前

   FLUSH BINARY LOGS;

   SHOW MASTER STATUS; -- 记录File和Position

6. 使用GTID(推荐):在MySQL 5.6+中启用GTID可以简化复制和恢复

   [mysqld]

   gtid_mode = ON

   enforce_gtid_consistency = ON

 

恢复前的安全检查清单:

1. [ ] 确认备份文件完整性

2. [ ] 确认要使用的二进制日志文件存在且完整

3. [ ] 在测试环境先演练恢复过程

4. [ ] 记录当前生产环境状态(`SHOW MASTER STATUS`)

5. [ ] 通知相关方维护窗口时间

 

七、常见问题处理

问题1:二进制日志损坏

 尝试修复或跳过损坏部分

mysqlbinlog --force-if-open mysql-bin.000003 | mysql -u root -p

 

问题2:找不到需要的日志文件

如果所需的二进制日志已被清理,只能恢复到最近可用的备份点。

 

问题3:恢复后数据不一致

检查二进制日志格式,确保使用`ROW`格式以获得最可靠的恢复:

 设置为ROW格式(推荐)

SET GLOBAL binlog_format = 'ROW';

点赞(0)

C语言网提供由在职研发工程师或ACM蓝桥杯竞赛优秀选手录制的视频教程,并配有习题和答疑,点击了解:

一点编程也不会写的:零基础C语言学练课程

解决困扰你多年的C语言疑难杂症特性的C语言进阶课程

从零到写出一个爬虫的Python编程课程

只会语法写不出代码?手把手带你写100个编程真题的编程百练课程

信息学奥赛或C++选手的 必学C++课程

蓝桥杯ACM、信息学奥赛的必学课程:算法竞赛课入门课程

手把手讲解近五年真题的蓝桥杯辅导课程

Dotcpp在线编译      (登录可减少运行等待时间)