数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog

发布时间:2023年12月18日

《数据库灾难应对: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_%';

#当前binlog
mysql> SHOW MASTER STATUS;

记下输出中的 FilePosition,这将用于数据恢复。

#所有binlog
show 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/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
  • 标志
  • 数据

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

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

以下是一些示例:

# 表映射
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 表中的行更新为 nameJohn Doe
  • 第二个事件是 XID 事件,它标记事务的结束。
文章来源:https://blog.csdn.net/citywu123/article/details/135054911
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。