《数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog》
添加图片注释，不超过 140 字（可选）
数据意外删除是数据库管理中常见的问题之一。MySQL作为广泛使用的数据库管理系统，当数据意外删除时，有几种方法可以尝试恢复数据。以下是binlog方式的数据恢复方法和步骤：
一、Binlog恢复数据
MySQL的二进制日志（Binary Log）可以用于数据的恢复，特别是在数据库发生灾难性故障或误操作时。二进制日志记录了数据库中所有对数据的更改操作，包括插入、更新和删除。以下是使用MySQL的二进制日志进行数据恢复的一般步骤：
1、启用二进制日志
确保MySQL服务器已启用二进制日志。你可以在MySQL配置文件（通常是 my.cnf）中添加或修改以下行来启用二进制日志：
[mysqld] log-bin=/var/log/mysql/mysql-bin.log
然后重新启动MySQL服务。
2、查看二进制日志文件
使用以下命令查看当前二进制日志文件和文件位置：
#进入mysql控制台 mysql -uroot -p #执行以下命令 mysql> show variables like 'log_%';
添加图片注释，不超过 140 字（可选）
#当前binlog mysql> SHOW MASTER STATUS;
添加图片注释，不超过 140 字（可选）
记下输出中的 File 和 Position，这将用于数据恢复。
#所有binlog show master logs;
添加图片注释，不超过 140 字（可选）
3、创建数据备份
在进行数据恢复之前，确保已经创建了数据库的备份，以防意外情况。 或者新建一个备份数据库用于测试恢复数据，恢复后再用于生产库。
4、导出二进制日志为sql语句
使用 mysqlbinlog 工具导出二进制日志。假设你的二进制日志文件是 mysql-bin.000001，则可以使用以下命令：
#全部导出 mysqlbinlog /var/log/mysql/mysql-bin.000001 > binlog_dump.sql #指定一个时间区间来导出 mysqlbinlog --base64-output=decode-rows -v --database=数据库名 --start-datetime="2023-12-04 16:50:00" --stop-datetime="2023-12-04 17:10:10" /var/log/mysql/mysql-bin.000001 > binlog_dump.sql
这将把二进制日志的内容导出到一个文本文件中。 mysqlbinlog 是 MySQL 提供的用于解析二进制日志文件的工具，它可以将二进制日志导出成可读的 SQL 语句。
5、通过时间或position恢复
- 通过时间恢复
如果你记得大概数据删除或修改的时间就可以根据时间来恢复数据
#根据SHOW MASTER STATUS定位到日志文件，然后复制出来 cp /var/log/mysql/mysql-bin.000003 /data/ #按时间恢复 mysqlbinlog --start-datetime="2023-12-08 16:10:00" --stop-datetime="2023-12-08 16:20:00" --database=数据库名 /data/mysql-bin.000003 | mysql -uroot -p123456
执行完成后看是否报错，以下只是密码不安全警告，说明已经恢复完成，这里再检查一下自己的数据是否恢复了。
添加图片注释，不超过 140 字（可选）
当然如果自己不知道大概的时间可以通过上面第4步导出的binlog_dump.sql进行查看
添加图片注释，不超过 140 字（可选）
- 通过position恢复
也可以根据position来恢复数据
#根据SHOW MASTER STATUS定位到日志文件，然后复制出来 cp /var/log/mysql/mysql-bin.000003 /data/ #查看事件 mysql> show binlog events in 'mysql-bin.000003';
添加图片注释，不超过 140 字（可选）
通过分析事件找到相应的start-position与stop-position。
#通过position恢复 mysqlbinlog --start-position=2016 --stop-position=2336 --database=test /data/mysql-bin.000003 | mysql -uroot -p123456;
当然在binlog_dump.sql文件里也是有的。
添加图片注释，不超过 140 字（可选）
使用二进制日志进行数据恢复是一项敏感操作，确保在执行之前已经做好充分的备份，并仔细阅读和理解导出的二进制日志文件。此外，要确保二进制日志文件没有被过期或清理，以保证日志的完整性。
二、解读binlog_dump.sql
MySQL binlog，也称为二进制日志，是一个文件，包含了对 MySQL 服务器所做的所有数据修改的按时间顺序记录。它是以下几个关键功能的重要组成部分：
- 复制: binlog 允许您将数据从一个 MySQL 服务器（主服务器）复制到一个或多个服务器（从服务器）。这确保了多个服务器之间数据的一致性，增强了容错性和可扩展性。
- 时间点恢复 (PITR): 如果由于意外删除或硬件故障导致数据丢失，您可以使用 binlog 将数据库恢复到特定的时间点。这可以最大限度地减少数据丢失和停机时间。
- 审计和跟踪更改: binlog 提供了数据库所做的所有更改的详细记录，使您可以跟踪用户活动、识别潜在问题并审计数据库修改。
binlog 的结构:
binlog 是一个二进制文件，包含一系列事件，每个事件描述特定的数据库修改。这些事件可以分为以下几类：
- DML (数据操作语言): 如 INSERT、UPDATE 和 DELETE 语句，用于修改表中的数据。
- DDL (数据定义语言): 如 CREATE TABLE、ALTER TABLE 和 DROP TABLE 语句，用于更改数据库的结构。
- 管理事件: 如 XID (事务标识符) 和 BEGIN/COMMIT 语句，用于管理事务。
binlog 中的每个事件包含以下信息：
- 时间戳: 事件发生的时间。
- 服务器 ID: 生成事件的服务器的唯一标识符。
- 结束日志位置: 事件在 binlog 文件中的位置。
- CRC32: 用于确保数据完整性的校验和。
- 事件类型: 事件的特定类型（例如，INSERT、UPDATE）。
- 表 ID: 事件影响的表的标识符。
- 标志: 有关事件的附加信息（例如，STMT_END_F）。
- 数据: 对于 DML 事件，包括实际被修改的数据。
用于处理 binlog 的工具:
有几种工具可用于处理 binlog，包括：
- mysqlbinlog: 此工具将 binlog 从二进制格式转换为人类可读格式，显示与每个事件对应的 SQL 语句。
- binlog2sql: 与 mysqlbinlog 类似，但还生成可用于复制事件的 SQL 语句。
- pt-archiver: 此工具可用于归档和管理 binlog 文件，方便数据保留和恢复。
以下是对MySQL二进制日志的注释和语法结构的说明：
- binlog_dump.sql 示例
添加图片注释，不超过 140 字（可选）
# 事务开始 BEGIN /*!*/; # 事件头 # at 923 # 时间戳: 231208 16:03:57，服务器ID: 1，binlog事件起始位置: 923 # binlog事件结束位置: 981，CRC32哈希值: 0x7e80b732 # 事件类型: Table_map，表`test`.`user`映射到编号83 # 是否生成不可见主键: 否 # 下一个事件的起始位置: 981 # 事件标志: STMT_END_F # # 更新行事件 # 表ID: 83，标志: STMT_END_F # CRC32哈希值: 0xabb7709d # binlog事件结束位置: 981 # 表`test`.`user`映射到编号83 # 更新行的数据 BINLOG ' 7c1yZRMBAAAAOgAAANUDAAAAAFMAAAAAAAEABHRlc3QABHVzZXIAAgMPApABAgEBAAID/P8AMreA fg== 7c1yZR8BAAAAPgAAABMEAAAAAFMAAAAAAAEAAgAC//8AAwAAAAYAbGlzaGkyAAMAAAAGAGxpc2hp M51wt6s= ' /*!*/; # 事件头 # 时间戳: 231208 16:03:57，服务器ID: 1，binlog事件结束位置: 1074 # 事务ID: 38 # 事务提交 COMMIT /*!*/;
以上示例中包含了一个事务，其中有一个表映射事件（Table_map）和更新行事件（Update_rows）。二进制日志的语法结构包括事务开始、事件头、具体事件内容（如表映射、更新行等），以及事务提交。每个事件都有特定的注释信息，描述了事件类型、时间戳、服务器ID、binlog事件的起始位置等。在这个例子中，更新行事件包含了更新表test.user的行的数据。
mysqlbinlog 工具将 binlog 导出为 SQL 语句的结构如下：
BEGIN; # 表映射 ... # 事件 # 事件 1 ... # 事件 2 ... # 事件 n COMMIT;
表映射
表映射部分将 binlog 中引用的表映射到内部标识符。这有助于 mysqlbinlog 更有效地解析 binlog。
事件
事件部分包含 binlog 中记录的每个操作。事件类型包括：
- INSERT：插入新行
- UPDATE：更新现有行
- DELETE：删除现有行
- DDL：数据定义语言（如创建表或更改表结构）
- XID：事务结束
每个事件都包含以下信息：
- 事件类型
- 表 ID
- 标志
- 数据
BEGIN 和 COMMIT 语句用于标记事务的开始和结束。
如果一个事务包含多个事件，则这些事件将按顺序排列在 BEGIN 和 COMMIT 语句之间。
以下是一些示例：
# 表映射 Table_map: `test`.`user` mapped to number 123 # 事件 1 Update_rows: table id 123 flags: STMT_END_F # 更新行 BINLOG ' 7c1yZRMBAAAAOgAAANUDAAAAAFMAAAAAAAEABHRlc3QABHVzZXIAAgMPApABAgEBAAID/P8AMreA fg== '; # 事件 2 Xid = 38
这段 binlog 包含两个事件：
- 第一个事件是 UPDATE 事件，它将 test.user 表中的行更新为 name 为 John Doe。
- 第二个事件是 XID 事件，它标记事务的结束。
