【MySQL】根据binlog日志获取回滚sql的一个开发思路

根据binlog日志获取回滚sql的一个开发思路

需要获取的信息

• thread_id

• 打开 mysql 客户端 开始时间

• 关闭 mysql 客户端 结束时间

binlog 匹配流程

• 指定 mysql 客户端 开始时间和结束时间

• 先匹配 thread_id 相同的

• 然后匹配 ^BEGIN$行和 ^COMMIT/*!*/;$行之间的数据

• 当匹配到 ^COMMIT/*!*/;$行，需要再次寻找 thread_id 相同的行，匹配到后执行上一个流程

• 直到匹配到文件结尾

具体流程

1. 开启一个mysql连接
2. 查看当前开始时间
3. 查看当前线程 id
4. 执行 sql
5. 失败处理逻辑
6. 查看当前结束时间
7. 关闭mysql连接
8. mysqlbinlog 命令 截取 指定开始时间和结束时间的 binlog文件
9. 用代码匹配binlog信息中的 thread_id
10. 匹配到 binlog 中的 thread_id 后 匹配 ^BEGIN$行
11. 匹配到 ^BEGIN$行后将行内容追加至文件
12. 匹配到 ^COMMIT/*!*/;$行 结束追加
13. 在匹配 thread_id 然后执行循环执行 👆三个步骤
14. 直到文件结尾
15. 用 **MyFlash**工具生成回滚 sql 保存至文件待用
16. 需要回滚时 先和业务人员确认 导入文件即可

注意：

• 不同客户端之间会交替追加在 binlog 中，需要通过 👆的 binlog 匹配流程来控制匹配

• 一个线程执行多个 sql 回滚到同一个文件可能带来的问题

  • 需要回滚的可能只有一个 sql
  • 如何处理这里的逻辑？
  • 是否需要每个 sql 开启一个线程？

获取线程 id

sql 获取当前连接的线程 id

对应 binlog 日志中的 thread_id=

select ID from information_schema.processlist where info like 'select id from information_schema.processlist where info like%'; 

在 binlog 中的线程 ID 记录为 thread_id

# 这是一个完整的事务
# at 1433
#240719 15:19:37 server id 33068094  end_log_pos 1498 CRC32 0xd91f110c  GTID    last_committed=5        sequence_number=6       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'c0acf1fe-23b9-11ef-8afa-000c29fa06b9:96490'/*!*/;
# at 1498
#240719 15:19:37 server id 33068094  end_log_pos 1570 CRC32 0x16965799  Query thread_id=1837  exec_time=0 error_code=0 # thread_id=1837 这里
SET TIMESTAMP=1721373577/*!*/;
BEGIN
/*!*/;
# at 1570
#240719 15:19:37 server id 33068094  end_log_pos 1615 CRC32 0x5a08aba9  Table_map: `test`.`t1` mapped to number 9257
# at 1615
#240719 15:19:37 server id 33068094  end_log_pos 1665 CRC32 0x6d1db298  Write_rows: table id 9257 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 1665
#240719 15:19:37 server id 33068094  end_log_pos 1696 CRC32 0xd51ac516  Xid = 394675
COMMIT/*!*/;

实时查看 binlog 日志

mysqlbinlog -S /mydata/3306/socket/mysql.sock  --stop-never -R  mysql-bin.000044