《数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog》

数据意外删除是数据库管理中常见的问题之一。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_%';

#当前binlogmysql> SHOW MASTER STATUS;

记下输出中的 File 和 Position，这将用于数据恢复。

#所有binlogshow master logs;

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

执行完成后看是否报错，以下只是密码不安全警告，说明已经恢复完成，这里再检查一下自己的数据是否恢复了。

当然如果自己不知道大概的时间可以通过上面第4步导出的binlog_dump.sql进行查看

  • 通过position恢复

也可以根据position来恢复数据

#根据SHOW MASTER STATUS定位到日志文件，然后复制出来cp /var/log/mysql/mysql-bin.000003 /data/#查看事件mysql> show binlog events in 'mysql-bin.000003';

通过分析事件找到相应的start-position与stop-position。

#通过position恢复mysqlbinlog --start-position=2016 --stop-position=2336 --database=test/data/mysql-bin.000003 | mysql -uroot -p123456;

当然在binlog_dump.sql文件里也是有的。

使用二进制日志进行数据恢复是一项敏感操作，确保在执行之前已经做好充分的备份，并仔细阅读和理解导出的二进制日志文件。此外，要确保二进制日志文件没有被过期或清理，以保证日志的完整性。

二、解读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 示例

# 事务开始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/P8AMreAfg==7c1yZR8BAAAAPgAAABMEAAAAAFMAAAAAAAEAAgAC//8AAwAAAAYAbGlzaGkyAAMAAAAGAGxpc2hpM51wt6s=' /*!*/;# 事件头# 时间戳: 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...# 事件 nCOMMIT;

表映射

表映射部分将 binlog 中引用的表映射到内部标识符。这有助于 mysqlbinlog 更有效地解析 binlog。

事件

事件部分包含 binlog 中记录的每个操作。事件类型包括：

  • INSERT：插入新行
  • UPDATE：更新现有行
  • DELETE：删除现有行
  • DDL：数据定义语言（如创建表或更改表结构）
  • XID：事务结束

每个事件都包含以下信息：

  • 事件类型
  • 表 ID
  • 标志
  • 数据

BEGIN 和 COMMIT 语句用于标记事务的开始和结束。

如果一个事务包含多个事件，则这些事件将按顺序排列在 BEGIN 和 COMMIT 语句之间。

以下是一些示例：

# 表映射Table_map: `test`.`user` mapped to number 123# 事件 1Update_rows: table id 123 flags: STMT_END_F# 更新行BINLOG '7c1yZRMBAAAAOgAAANUDAAAAAFMAAAAAAAEABHRlc3QABHVzZXIAAgMPApABAgEBAAID/P8AMreAfg==';# 事件 2Xid = 38

这段 binlog 包含两个事件：

  • 第一个事件是 UPDATE 事件，它将 test.user 表中的行更新为 name 为 John Doe。
  • 第二个事件是 XID 事件，它标记事务的结束。