MySQL-数据库服务日志管理与数据闪回

一、数据库中存在什么日志

1. 错误日志

默认存在，位置在数据目录中，文件名为"主机名称.err"：

[root@db02 data]# ll /data/3306/data/db02.err -rw-r----- 1 mysql mysql 2694183 10月 21 10:51 db02.err

设置生成错误日志文件（vim /etc/my.cnf）：

log_error=/tmp/mysql3306.err -- 永久设置

日志作用：记录数据库服务运行情况，通过错误信息可以分析数据库服务故障原理。

2. 通用日志

默认不存在，需要手动开启。

设置生成通用日志文件（vim /etc/my.cnf）：

general_log=ON -- 是否开启通用日志记录功能 general_log_file=/data/3306/data/general.log -- 设置日志存储路径和名称

验证日志文件生成：

ll /data/3306/data/general.log -rw-r----- 1 mysql mysql 867 6月 26 18:07 /data/3306/data/general.log

日志作用：审计日志/开发人员做调式使用的日志。具体包括：

1. 可以记录登录数据库的用户信息，是否登录成功可以做记录；
2. 可以记录登录成功后，用户在数据库中操作行为（DDL、DCL、DML、DQL）;
3. 数据备份mysqldump 做的数据备份流程和原理也体现在日志中。

3.慢查询日志

作用：记录慢查询数据语句信息，利用及早发现的慢查询语句做优化，可以提高数据库服务性能。

设置：默认在数据库中不会创建慢查询日志，需手动配置开启。

慢查询日志相关参数说明：

# 配置慢查询日志 [root@db01 ~]# vim /etc/my.cnf slow_query_log=on slow_query_log_file=/tmp/slow.log long_query_time=0.001 log_queries_not_using_indexes=on 配置项说明： 1.slow_query_log=on：开启慢查询日志功能（对应"确认是否创建慢查询日志"）； 2.slow_query_log_file=/tmp/slow.log：设置慢查询日志的存储路径和日志名称； 3.long_query_time=0.001：设置语句执行多长时间算慢查询（此处设置为 0.001 秒）； 4.log_queries_not_using_indexes=on：开启记录功能，在查询数据信息时，没有利用索引的查询也会被记录。

4.二进制日志

二进制日志的作用：

1. 可以用于数据恢复（历史数据、实时增量数据）；
2. 可以用于主从同步：主数据库insert→binlog→ 从数据库insert。

配置开启binlog日志：

1. 先创建binlog存储目录 [root@db01 ~]# mkdir -p /data/3306/log 2. 给目录授予mysql用户权限 [root@db01 ~]# chown -R mysql.mysql /data/3306/log 3. 配置文件增加binlog日志 [root@db01 ~]# vim /etc/my.cnf log_bin=/data/3306/log/xiaoQ-binlog mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------------+ | log_bin | ON | | log_bin_basename | /data/3306/log/mysql-binlog | | log_bin_index | /data/3306/log/mysql-binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------------+ 相关参数： 参数 作用 log_bin_basename 指定日志存储路径和名称 log_bin_index 指定日志索引信息路径和名称 查询当前 MySQL 实例中与二进制日志（Binary Log，简称 Binlog）相关的系统变量配置。 1. log_bin = ON 含义：表示当前 MySQL 实例的二进制日志功能是否开启。 说明：ON 代表已开启，OFF 代表未开启。这是 MySQL 非常重要的一个全局开关，开启后，数据库所有的数据修改操作（如 INSERT、UPDATE、DELETE 等）都会按顺序记录到二进制日志文件中，常用于主从复制和数据恢复。 2. log_bin_basename = /data/3306/log/mysql-binlog 含义：二进制日志文件的基础路径和名称前缀。 说明：当 MySQL 生成新的二进制日志文件时，会以这个值为基础。例如，它会在该路径下生成 mysql-binlog.000001、mysql-binlog.000002 等文件。日志文件正是存放在 /data/3306/log/ 目录下。 3. log_bin_index = /data/3306/log/mysql-binlog.index 含义：二进制日志索引文件的存放路径。 说明：这个 .index 文件是一个普通的文本文件。里面记录了当前 MySQL 所有正在使用的二进制日志文件（如 000001, 000002 等）的完整路径清单，MySQL 通过它来追踪有哪些日志文件。 4. log_bin_trust_function_creators = OFF 含义：是否信任创建存储函数/触发器的用户。 说明：当开启了 Binlog 后，为了防止主从复制时由于函数执行结果不一致导致主从数据错乱（比如用到 NOW() 或 RAND() 函数），MySQL 默认（OFF）会限制普通用户创建存储函数或触发器。只有拥有 SUPER 权限的用户才能创建。如果设为 ON，则允许普通用户创建。 5. log_bin_use_v1_row_events = OFF 含义：是否使用 V1 版本的 Row 格式事件。 说明：这是 MySQL 历史遗留的一个版本控制参数。在 MySQL 5.1 版本以后，为了支持更丰富的数据类型和功能，Row 格式的日志事件升级到了 V2 版本。通常情况下，这个值都是 OFF（即默认使用 V2 版本），不需要修改，修改它可能会导致主从版本不兼容。 6. sql_log_bin = ON 含义：当前会话（Session）是否将 SQL 操作记录到 Binlog 中。 说明：这既是一个全局变量，也是一个会话变量。默认是 ON。如果你当前登录的会话不想把某条 SQL 写入 Binlog（比如你正在做临时的测试、批量导数据，且不想让从库同步），你可以执行 SET sql_log_bin = 0; 临时关闭当前会话的日志记录，执行完操作后再 SET sql_log_bin = 1; 打开。

查看日志：

方式一：在数据库中查看 binlog 日志信息

show binary logs; -- 确认可以查看binlog日志名称 show master status; -- 确认正在记录内容binlog日志名称 show binlog events in '日志名称'; -- 查看binlog日志内容 # 日志切割（全量备份时先执行一次） mysql> flush logs; Query OK, 0 rows affected (0.01 sec) 强制 MySQL 立即关闭当前正在使用的日志文件，并生成（切换）一个新的日志文件。 具体来说，这个命令会执行以下三个主要动作： 重新生成一个新的二进制日志（Binlog）： 比如你之前在看 mysql-binlog.000001，执行完这个命令后，MySQL 会停止向 000001 写入，并创建一个全新的 mysql-binlog.000002，后续的所有数据修改都会记录到新的 000002 文件里。 重新生成一个新的错误日志（Error Log）（如果开启了该功能，取决于系统的日志配置）。 重新生成一个慢查询日志（Slow Query Log）（如果开启了慢查询日志）。 （常见使用场景） 方便备份与数据恢复： 当你准备对数据库进行全量备份时，通常会先执行一次 flush logs;。这样可以把备份时刻之前的 Binlog 封存（比如 000001），备份完成后的新操作记录到新文件（000002）中。后续如果要用 Binlog 做“基于时间点恢复（PITR）”，边界会非常清晰，不会混淆。 主从复制切换： 在主从架构中，有时候为了让从库重新同步（比如重置复制状态或重新指定起点），会在主库执行这个命令，方便从库定位新的文件位置。 清理磁盘空间： 当你的 Binlog 文件占用磁盘过大时，如果 000001 不再被使用，你可以执行 flush logs; 生成 000002，然后用 PURGE BINARY LOGS TO 'mysql-binlog.000002'; 删掉 000001，从而释放磁盘空间。 排查问题： 当前的日志文件可能非常大了，你想通过一个全新的文件来单独观察接下来发生的操作，方便排查问题。 # 执行了两次日志切割 mysql> show binary logs; +---------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------------+-----------+-----------+ | mysql-binlog.000001 | 873 | No | | mysql-binlog.000002 | 247 | No | | mysql-binlog.000003 | 197 | No | +---------------------+-----------+-----------+ 3 rows in set (0.00 sec) mysql> show binlog events in 'mysql-binlog.000001'; +---------------------+-----+----------------+-----------+-------------+-----------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------------+-----+----------------+-----------+-------------+-----------------------------------+ | mysql-binlog.000001 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.36, Binlog ver: 4 | | mysql-binlog.000001 | 126 | Previous_gtids | 1 | 157 | | +---------------------+-----+----------------+-----------+-------------+-----------------------------------+ 2 rows in set (0.00 sec) 位置点概念： Pos：日志信息开始位置点 End_log_pos：日志信息结束位置点

1. 各列含义解释 Log_name: 当前事件所在的 Binlog 文件名。因为你在查询时指定了 in 'mysql-binlog.000001'，所以这里全是一样的。 Pos (Start Position): 该事件在 Binlog 文件中的起始字节位置（偏移量）。 Event_type: 事件的类型。比如 Query（执行的 SQL 语句）、Table_map（映射表 ID 和表名）、Write_rows（插入数据）、Xid（事务提交）等。 Server_id: 产生该事件的 MySQL 服务器的唯一 ID。 End_log_pos: 该事件在 Binlog 文件中结束的字节位置。 Info: 具体的事件信息。通常包含了 SQL 语句、GTID 编号或者表 ID 等信息。 2. 重点解释：Pos 和 End_log_pos 的关系 这两个字段是理解 Binlog 结构的关键： Pos 是当前事件的起点。 End_log_pos 是当前事件的终点（同时也是下一个事件的起点）。 3.Binlog 是一个连续的二进制文件，所有事件像串珠一样紧挨着写入。你可以观察表格中的数据： 第 1 行：从 4 开始，到 126 结束（记录了文件头格式）。 第 2 行：从 126 开始，到 157 结束（即上一行的 End_log_pos 等于这一行的 Pos）。 以此类推。 当你看到 Xid (COMMIT) 时，比如 Pos 为 459，End_log_pos 为 490，说明这个提交事件占了 31 个字节。紧接着下一个 Gtid 事件就从 490 开始了。 结论：End_log_pos - Pos = 该事件占用的字节大小。它们首尾相连，构成了整个日志文件。 4. 深入解析：这份日志记录了什么？ 从这份日志可以看出，你（或者某个程序）向 testing.student 表中插入（Insert）了两次数据，且开启了 GTID 模式。我们来拆解其中一次完整的事务（比如 Pos 157 到 490 这一段）： Gtid (Pos 157): 分配了全局事务 ID（...f97d83:2），表示这是一个新事务。 Query (Pos 236): 记录了 BEGIN，表示事务开始。 Table_map (Pos 314): 记录了要操作的表是 table_id: 89 (testing.student)。这里只记录表 ID 和表名，不记录具体数据。 Write_rows (Pos 388): 记录了实际的插入数据。注意，因为这是 Row 格式的 Binlog，具体的列名和数据（比如张三、18岁等）在底层被转成了二进制，这里简略显示为 Write_rows。 Xid (Pos 459): 记录了 COMMIT，表示事务提交成功。 结合起来看： 在这个表里插入了两次数据，形成了两个事务（GTID:2 和 GTID:3）。 第一个事务记录从 Pos 157 开始，到 Pos 490 结束（提交）。 第二个事务紧挨着从 Pos 490 开始，到 Pos 823 结束（提交）。

方式二：在命令行中查看 binlog 日志信息

# 进入binlog存储路径查看，这里使用的是相对路径 [root@db01 log]# mysqlbinlog mysql-binlog.000003 # The proper term is pseudo_replica_mode, but we use this compatibility alias # to make the statement usable on server versions 8.0.24 and older. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #261022 16:17:54 server id 1 end_log_pos 126 CRC32 0x77161edb Start: binlog v 4, server v 8.0.36 created 261022 16:17:54 # Warning: this binlog is either in use or was not closed properly. BINLOG ' ssbZag8BAAAAegAAAH4AAAABAAQAOC4wLjM2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA CigAAdseFnc= '/*!*/; # at 126 #261022 16:17:54 server id 1 end_log_pos 197 CRC32 0x185e08c1 Previous-GTIDs # 630b76d3-cc61-11f1-8061-000c29f97d83:2-3 # at 197 #261022 16:26:14 server id 1 end_log_pos 274 CRC32 0xde185fef GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1792657574462605 immediate_commit_timestamp=1792657574462605 transaction_length=188 # original_commit_timestamp=1792657574462605 (2026-10-22 16:26:14.462605 CST) # immediate_commit_timestamp=1792657574462605 (2026-10-22 16:26:14.462605 CST) /*!80001 SET @@session.original_commit_timestamp=1792657574462605*//*!*/; /*!80014 SET @@session.original_server_version=80036*//*!*/; /*!80014 SET @@session.immediate_server_version=80036*//*!*/; SET @@SESSION.GTID_NEXT= '630b76d3-cc61-11f1-8061-000c29f97d83:4'/*!*/; # at 274 #261022 16:26:14 server id 1 end_log_pos 385 CRC32 0x0380691e Query thread_id=1307 exec_time=0 error_code=0 Xid = 29 SET TIMESTAMP=1792657574/*!*/; SET @@session.pseudo_thread_id=1307/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; /*!80016 SET @@session.default_table_encryption=0*//*!*/; create database testq /*!*/; # at 385 #261022 16:27:59 server id 1 end_log_pos 462 CRC32 0x6a66a6e3 GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1792657679159908 immediate_commit_timestamp=1792657679159908 transaction_length=213 # original_commit_timestamp=1792657679159908 (2026-10-22 16:27:59.159908 CST) # immediate_commit_timestamp=1792657679159908 (2026-10-22 16:27:59.159908 CST) /*!80001 SET @@session.original_commit_timestamp=1792657679159908*//*!*/; /*!80014 SET @@session.original_server_version=80036*//*!*/; /*!80014 SET @@session.immediate_server_version=80036*//*!*/; SET @@SESSION.GTID_NEXT= '630b76d3-cc61-11f1-8061-000c29f97d83:5'/*!*/; # at 462 #261022 16:27:59 server id 1 end_log_pos 598 CRC32 0x661c1e69 Query thread_id=1307 exec_time=0 error_code=0 Xid = 31 use `testing`/*!*/; SET TIMESTAMP=1792657679/*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; CREATE TABLE testq.q1 (id int,name char(10)) /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

1）扩展知识：DML 语句信息查看方法/记录方法

1. 行格式记录（binlog_format=ROW）

命令行日志查看：

• DML 语句以行格式记录，不能直接查看；
• binlog_format=ROW：记录行的数据信息；
• 设置作用：保证数据的一致性。

# 解码操作 [root@db01 log]# mysqlbinlog --base64-output=decode-rows -vvv mysql-binlog.000003 @1=1 表示在第一列插入数据 @1=2 表示在第二列插入数据 @1=3 表示在第三列插入数据 ### INSERT INTO `testq`.`q3` SET @1=1 @2=xx @3=xx

2. 语句方式记录（binlog_format=statement）

# 记录语句信息 mysql> select @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | STATEMENT | +-----------------+ 1 row in set, 1 warning (0.00 sec) # 将 MySQL 二进制日志（Binlog）的记录格式全局修改为 STATEMENT（基于 SQL 语句）模式。 mysql> set global binlog_format='statement'; Query OK, 0 rows affected, 1 warning (0.00 sec) 三种主要格式的对比 你可以把 Binlog 记录数据修改想象成“记录做菜”： 1.STATEMENT（语句模式，你刚设置的）： 记录方式：直接记录菜谱（即原封不动的 SQL 语句）。 优点：日志文件非常小，节省磁盘 I/O 和网络带宽（主从同步时传的快）。 缺点：如果 SQL 里有不确定的函数（比如 NOW()、RAND()、UUID()），主库和从库执行同一句 SQL 得到的时间或结果可能不一样，会导致主从数据不一致。 2.ROW（行模式，MySQL 5.7 之后的默认模式）： 记录方式：记录食材具体变成了什么样（即记录每一行数据修改前后的具体内容）。 优点：绝对安全，保证主从数据 100% 一致，不会因为函数或环境不同而错乱。 缺点：日志文件非常大（比如一条 UPDATE 改了 10 万行，就会记录 10 万行的变化），占用磁盘空间大。 3.MIXED（混合模式）： 记录方式：MySQL 自己判断，普通的 SQL 用 STATEMENT 记录，遇到不安全的函数（如 RAND()）自动切换成 ROW 记录。

• DML 语句以语句方式记录，可以直接查看内容；
• binlog_format=statement：记录语句信息；
• 设置作用：方便查看 DML 语句信息，但无法保证数据一致性。

日志记录方式已经发生改变：

命令查看日志：

5.日志切割

方式一：手动进行日志切割

flush logs;

方式二：自动进行日志切割

执行链路： flush logs --> shell脚本 --> crontab 通过设置单个 binlog 文件大小上限，达到阈值自动切割： set global max_binlog_size=104857600; （104857600 字节 = 100M）

4）清理日志

PS：什么 binlog 日志可以清理？

• 已经备份的 binlog 日志可以清理；

• binlog 日志中的 DML、DDL、DCL 信息已经保存备份到备份文件，此日志可以清理。

例如：create database、create user、insert等操作产生的日志，已经通过mysqldump / xbk备份保存后，对应 binlog 即可清理。

方式一：手动清理 binlog 日志

按日志文件清理（清理到指定文件之前）：

# 按日志文件清理（清理到指定文件之前）： purge binary logs to 'xiaoQ-binlog.000004'; # 按时间点清理（清理指定时间之前的日志）： PURGE BINARY LOGS BEFORE '2026-06-28 19:30:26';

方式二：自动清理 binlog 日志

设置示例（1 小时）：

binlog_expire_logs_seconds=3600 -- 1小时

PS：千万不要直接 rm 清理 binlog 日志文件

直接rm删除 binlog 日志文件会导致问题（关闭数据库服务），必须使用上述 purge 或自动过期参数的方式清理。

6.备份日志与日志信息截取

1）备份日志（备份服务:10.0.0.52）

步骤一：在备份服务器，创建日志备份服务主机/并设置日志存储目录 mkdir /backup/binlog/10.0.0.53/ -p cd /backup/binlog/10.0.0.53/ 步骤二：执行 mysqlbinlog 命令实现日志备份,备份53服务上的日志 mysqlbinlog -R --host=10.0.0.53 --port=3306 --user=root --password=root123 --raw --stop-never mysql-binlog.000001 &

参数说明：

--raw实时同步示意（10.0.0.53 → 10.0.0.52）：

日志切割：

日志自动实时通步：

即主库 binlog 中记录的每条操作（insert/update ...），都会实时同步到远程日志备份服务器的同名日志文件中。

2）日志信息截取

方式一：根据位置点截取信息

参数说明：

• --start-position：截取日志信息从哪个位置点开始截取
• --stop-position：截取日志信息截取到哪个位置点

#在binlog日志存储路径执行 [root@db01 log]# mysqlbinlog --start-position=276 --stop-position=476 mysql-binlog.000014 >/backup/backup.sql

恢复数据：

方式二：根据时间截取信息

场景：恢复数据库，数据库大概在2026-10-10 18:05:00 创建；2026-10-10 22:35:00 误操作（数据库删除）。

确定截取时间范围：

• 起始时间：2026-10-26 16:30:15
• 结束时间：2026-10-26 16:31:05

（范围取在创建之后、误操作之前，避开误删除操作。）

[root@db01 log]# mysqlbinlog --start-datetime="2026-10-26 16:30:00" --stop-datetime="2026-10-26 16:32:00" --skip-gtids --base64-output=DECODE-ROWS -v mysql-binlog.000016 > /backup/backup01.sql ## mysqlbinlog 参数说明 --start-datetime 只解析指定时间点之后的 binlog 事件，如 `"2026-10-26 16:30:00"`。需与 MySQL 服务器时区一致。 --stop-datetime 只解析指定时间点之前的 binlog 事件，如 `"2026-10-26 16:32:00"`。与 start 搭配可精确圈定一个时间窗口。 --skip-gtids 输出结果中不生成 `SET @@GLOBAL.GTID_PURGED` 等 GTID 语句，防止恢复数据时污染目标实例的 GTID 集合，影响主从复制。 --base64-output=DECODE-ROWS 解码 ROW 格式的行变更事件，不输出原始 Base64 编码串（`BINLOG '...'`）。需配合 `-v` 才能还原为可读内容。 -v（--verbose） 把 ROW 事件重建为可读的伪 SQL（含 WHERE / SET 的列值，`@1`、`@2` 为列序号）。加 `-vv` 可额外输出列名等元信息。必须与 `--base64-output=DECODE-ROWS` 搭配使用。

方式三：截取指定数据库的数据信息

# 切割日志--查看新的binlog日志--创建数据库、表以及插入数据--备份数据--删除数据库--恢复数据 flush logs； show master status; CREATE DATABASE IF NOT EXISTS prod_db DEFAULT CHARSET utf8mb4; USE prod_db; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT ); INSERT INTO users (name, age) VALUES ('张三', 20), ('李四', 25); [root@db01 log]# mysqlbinlog --database=prod_db --skip-gtids mysql-binlog.000019 > /backup/prod.sql --database 指定只截取某个数据库的数据信息 mysql> DROP DATABASE prod_db; mysql> source /backup/prod.sql;

7.数据恢复

1）数据库异常数据丢失后，修复数据方式

方式一：借助备份文件+binlog恢复数据

模拟企业场景：

步骤一：在数据库中存储数据信息 mysql> create database abs; Query OK, 1 row affected (0.00 sec) mysql> create table abs.t1 (id int,name char(10)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO abs.t1 (id, name) VALUES (1, '张三'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO abs.t1 (id, name) VALUES (2, '李四'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO abs.t1 (id, name) VALUES (3, '王五'); Query OK, 1 row affected (0.00 sec) mysql> commit; 步骤二：定期对数据库做备份 [root@db01 backup]# mysqldump -uroot -proot123 -B abs >/backup/abs.sql; [root@db01 log]# mysqldump -uroot -proot123 -B test_db --source-data >/backup/test_db.sql; --source-data： 这个参数在 MySQL 8.0.26 之前被称为 --master-data。 核心作用：它会在导出的 SQL 文件中记录备份开始那一刻的二进制日志（binlog）文件名和位置（Position）。 步骤三：增加增量数据 CREATE DATABASE IF NOT EXISTS demo_binlog DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE demo_binlog; CREATE TABLE demo_binlog.t1 ( id INT, name CHAR(10) ); INSERT INTO demo_binlog.t1 (id, name) VALUES (1, '张三'),(2, '李四'),(3, '王五'); 步骤四：模拟数据库数据损坏 mysql> drop database abs; mysql> drop database demo_binlog; 步骤五：恢复数据 mysql> show master status; --查看binlog日志 mysql> show binlog events in 'mysql-binlog.000019'; --查看日志内容 # 备份目的是为了查看位置（Position），因为删除数据的时候未备份demo_binlog数据 [root@db01 log]# mysqldump -uroot -proot123 -B test_db --source-data >/backup/test_db.sql;

# 临时关闭blog日志记录，避免日志重复记录 mysql> set session sql_log_bin=0; # 根据日志记录实现增量数据恢复 [root@db01 log]# mysqlbinlog --start-position=3277 --stop-position=4098 mysql-binlog.000019 >/backup/demo_binlog.sql #恢复数据 mysql> source /backup/abs.sql; mysql> source /backup/demo_binlog.sql # 打开blog日志记录 mysql> set session sql_log_bin=1;

测试验证：数据已恢复

2）利用数据闪回工具实现数据修复

方式二：使用binlog2sql数据闪回数据恢复

在日常的数据库运维工作中，DML（数据操作语言）误操作是最高频的"事故"之一。一条手抖执行的DELETE、一次忘记加WHERE条件的UPDATE，都可能让生产数据瞬间丢失或错乱。传统手段依赖备份恢复，不仅耗时长，还会丢失误操作之后的正常数据变更。

binlog2sql是一款基于 MySQL binlog 解析的开源工具，它能够将二进制日志（binlog）解析为可读的 SQL 语句，并支持反向生成（Flashback）回滚 SQL，从而实现对INSERT、UPDATE、DELETE三类 DML 误操作的精确修复——只回滚误操作，不影响其他正常数据。

步骤一：模拟创建企业环境数据 use abs; select * from student; 步骤二：模拟进行DML语句误操作 INSERT INTO student (name, gender, age, grade, major, email, phone) VALUES ('林十一', '男', 20, '大二', '计算机科学', 'linshiyi@example.com', '13800000011'); -- 插入误操作 UPDATE student SET gender = '男' WHERE age >= 20; -- 误修改操作 DELETE FROM student WHERE id BETWEEN 6 AND 9; -- 误删除操作 mysql> select * from student; +----+-----------+--------+------+--------+-----------------+----------------------+-------------+ | id | name | gender | age | grade | major | email | phone | +----+-----------+--------+------+--------+-----------------+----------------------+-------------+ | 1 | 张三 | 男 | 20 | 大二 | 计算机科学 | zhangsan@example.com | 13800000001 | | 2 | 李四 | 女 | 19 | 大一 | 软件工程 | lisi@example.com | 13800000002 | | 3 | 王五 | 男 | 21 | 大三 | 人工智能 | wangwu@example.com | 13800000003 | | 4 | 赵六 | 男 | 22 | 大四 | 数据科学 | zhaoliu@example.com | 13800000004 | | 5 | 孙七 | 男 | 20 | 大二 | 网络安全 | sunqi@example.com | 13800000005 | | 10 | 陈二 | 男 | 22 | 大四 | 数据科学 | chener@example.com | 13800000010 | | 11 | 林十一 | 男 | 20 | 大二 | 计算机科学 | linshiyi@example.com | 13800000011 | +----+-----------+--------+------+--------+-----------------+----------------------+-------------+ 步骤三：利用数据闪回工具实现误操作数据修复(DML) yum install -y python3 python3-pip pip3 install -r requirements.txt unzip binlog2sql-master.zip -d /opt/module/ [root@db01 module]# mv requirements.txt binlog2sql-master/ cd binlog2sql-master -- 安装数据闪回工具 # 执行以下sql ALTER USER 'root'@'db01' IDENTIFIED WITH mysql_native_password BY 'root123'; FLUSH PRIVILEGES; # 解析所有 DML [root@db01 binlog2sql-master]# python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-file='mysql-binlog.000019' # 只解析 INSERT 语句 python3 binlog2sql.py -h10.0.0.51 -P3306 -uroot -p123456 -d school -t student --start-file='xiaoQ-binlog.000015' --sql-type=insert # 只解析 INSERT + 生成回滚 SQL（-B） python3 binlog2sql.py -h10.0.0.51 -P3306 -uroot -p123456 -d school -t student --start-file='xiaoQ-binlog.000015' --sql-type=insert -B -- 应用工具实现数据闪回 -- 将误插入数据信息做删除 DELETE FROM `abs`.`student` WHERE `id`=11 AND `name`='林十一' AND `gender`='男' AND `age`=20 AND `grade`='大二' AND `major`='计算机科学' AND `email`='linshiyi@example.com' AND `phone`='13800000011' LIMIT 1;

# 查看这个 binlog 里 student 表上所有 UPDATE 的原始语句 python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-file='mysql-binlog.000019' --sql-type=update # 把 UPDATE 操作反转，生成回滚 SQL python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-file='mysql-binlog.000019' --sql-type=update -B -- 将误修改数据信息做恢复 UPDATE `abs`.`student` SET `id`=4, `name`='赵六', `gender`='男', `age`=22, `grade`='大四', `major`='数据科学', `email`='zhaoliu@example.com', `phone`='13800000004' WHERE `id`=4 AND `name`='赵六' AND `gender`='女' AND `age`=22 AND `grade`='大四' AND `major`='数据科学' AND `email`='zhaoliu@example.com' AND `phone`='13800000004' LIMIT 1; UPDATE `abs`.`student` SET `id`=8, `name`='郑十', `gender`='男', `age`=21, `grade`='大三', `major`='计算机科学', `email`='zhengshi@example.com', `phone`='13800000008' WHERE `id`=8 AND `name`='郑十' AND `gender`='女' AND `age`=21 AND `grade`='大三' AND `major`='计算机科学' AND `email`='zhengshi@example.com' AND `phone`='13800000008' LIMIT 1; UPDATE `abs`.`student` SET `id`=10, `name`='陈二', `gender`='男', `age`=22, `grade`='大四', `major`='数据科学', `email`='chener@example.com', `phone`='13800000010' WHERE `id`=10 AND `name`='陈二' AND `gender`='女' AND `age`=22 AND `grade`='大四' AND `major`='数据科学' AND `email`='chener@example.com' AND `phone`='13800000010' LIMIT 1; UPDATE `abs`.`student` SET `id`=10, `name`='陈二', `gender`='女', `age`=22, `grade`='大四', `major`='数据科学', `email`='chener@example.com', `phone`='13800000010' WHERE `id`=10 AND `name`='陈二' AND `gender`='男' AND `age`=22 AND `grade`='大四' AND `major`='数据科学' AND `email`='chener@example.com' AND `phone`='13800000010' LIMIT 1; UPDATE `abs`.`student` SET `id`=8, `name`='郑十', `gender`='女', `age`=21, `grade`='大三', `major`='计算机科学', `email`='zhengshi@example.com', `phone`='13800000008' WHERE `id`=8 AND `name`='郑十' AND `gender`='男' AND `age`=21 AND `grade`='大三' AND `major`='计算机科学' AND `email`='zhengshi@example.com' AND `phone`='13800000008' LIMIT 1; UPDATE `abs`.`student` SET `id`=4, `name`='赵六', `gender`='女', `age`=22, `grade`='大四', `major`='数据科学', `email`='zhaoliu@example.com', `phone`='13800000004' WHERE `id`=4 AND `name`='赵六' AND `gender`='男' AND `age`=22 AND `grade`='大四' AND `major`='数据科学' AND `email`='zhaoliu@example.com' AND `phone`='13800000004' LIMIT 1;

# 查看这个 binlog 里 student 表上所有 DELETE 的原始记录。 python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-file='mysql-binlog.000019' --sql-type=delete # 把 DELETE 操作反转成 INSERT，生成回滚 SQL——用来把删掉的数据插回去。 python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-file='mysql-binlog.000019' --sql-type=delete -B -- 将误删除数据信息做恢复 INSERT INTO `abs`.`student`(`id`, `name`, `gender`, `age`, `grade`, `major`, `email`, `phone`) VALUES (9, '刘一', '男', 20, '大二', '人工智能', 'liuyi@example.com', '13800000009'); INSERT INTO `abs`.`student`(`id`, `name`, `gender`, `age`, `grade`, `major`, `email`, `phone`) VALUES (8, '郑十', '男', 21, '大三', '计算机科学', 'zhengshi@example.com', '13800000008'); INSERT INTO `abs`.`student`(`id`, `name`, `gender`, `age`, `grade`, `major`, `email`, `phone`) VALUES (7, '吴九', '男', 23, '大四', '软件工程', 'wujiu@example.com', '13800000007'); INSERT INTO `abs`.`student`(`id`, `name`, `gender`, `age`, `grade`, `major`, `email`, `phone`) VALUES (6, '周八', '女', 19, '大一', '物联网工程', 'zhouba@example.com', '13800000006');