使用binlog2mysql回滚mysql的binlog数据

原创已于 2025-01-07 19:13:15 修改 · 1.1k 阅读

17 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql

于 2025-01-07 18:01:40 首次发布

Python3.8

Python 是一种高级、解释型、通用的编程语言，以其简洁易读的语法而闻名，适用于广泛的应用，包括Web开发、数据分析、人工智能和自动化脚本

文章目录

背景

提示：本文是在windows系统中安装的 binlog2mysql 工具，在linux中安装是一样的

由于用户不小心删除或者修改了mysql数据，然后需要回滚数据。使用binlogmysql工具去还原sql语句。

解决方案

解决方案就是通过mysql的binlog日志还原数据。对于binlog文件来说，实际上保存的是对于数据库的正向操作。比如说，插入数据insert，binlog中保存的也是insert语句；删除数据delete，binlog中保存的也是delete语句。

操作binlog

1.查看开启状态

首先保证mysql的binlog是开启的，否则是无法恢复数据的。

这个查询将返回一个结果集，其中包含名为log_bin的系统变量的值。如果log_bin的值为ON，则表示binlog已经开启；如果值为OFF，则表示binlog没有开启。

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

2.开起binlog

如果binlog没有开起，可以修改mysql配置文件（通常是my.cnf或my.ini）中的[mysqld]部分开起binlog

[mysqld]
# 开启binlog日志
log-bin=mysql-bin
server-id=123456
# binlog格式
# 1. STATEMENT：基于SQL语句的模式，binlog 数据量小，但是某些语句和函数在复制过程可能导致数据不一致甚至出错；
# 2. MIXED：混合模式，根据语句来选用是 STATEMENT 还是 ROW 模式；
# 3. ROW：基于行的模式，记录的是行的完整变化。安全，但 binlog 会比其他两种模式大很多；
binlog_format=ROW
expire_logs_days=30

修改成功后重启mysql才会生效。

2.1 binlog模式

查询binlog模式sql命令

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

这将返回一个结果集，其中包含当前的binlog格式。可能的值有：

ROW：表示使用行模式（row-based replication），这是推荐的设置，因为它提供了更好的数据一致性。

STATEMENT：表示使用语句模式（statement-based replication），在这种模式下，可能会丢失一些数据，因为它仅记录执行的SQL语句。

MIXED：表示混合模式（mixed-based replication），在这种模式下，MySQL会根据需要自动切换行模式和语句模式。

2.2 查询所有的binlog日志

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     75211 |
+------------------+-----------+
1 row in set (0.00 sec)

2.3 查询binlog日志位置

mysql> SHOW VARIABLES LIKE 'log_bin_basename';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| log_bin_basename | /var/lib/mysql/mysql-bin |
+------------------+--------------------------+
1 row in set (0.00 sec)

2.4 解析binlog文件（非重点）

通过mysql自带的 mysqlbinlog 的工具解析binlog日志文件。

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2025-01-01 15:00:00" --stop-datetime="2025-01-07 17:00:00" mysql-bin.000001 > binlog.sql

–base64-output=decode-rows：将二进制日志中的行事件解码为 SQL 语句。
-v 或 --verbose：输出详细的事件信息。
–start-datetime：从指定的日期和时间开始读取二进制日志。通过指定时间范围，可以减小解析出来的sql文件，避免太多无用信息使得查询位置比较困难。
–stop-datetime：在指定的日期和时间停止读取二进制日志。
mysql-bin.000001 ：要解析的二进制日志文件的路径和名称。
binlog.sql：保存解码后的 SQL 语句的文件名。

操作binlog2mysql

1.描述

binlog2sql由美团点评DBA团队(上海)出品，python脚本实现。主要原理是伪装成slave，向master获取binlog，并且根据binlog生成逆向操作。

2.安装python（如果已安装可以跳过）

官网下载链接：https://www.python.org/downloads/

在这里插入图片描述
下载完成双击安装python

2.1 安装页面

进入到可选功能【Optional Features】界面后，点击下一步【Next】按钮即可
① 去勾选【Create shortcuts for installed applications】，一般不需要创建快捷方式
勾选【Associate files with Python (requires the py launcher)】，默认python运行.py文件
勾选【Add Python to environment variables】，添加环境变量
② 修改安装的文件路径，尽量安装在磁盘根目录。例如：D:\python3.10
③ 点击【install】按钮执行安装
安装选项
安装成功

3.安装binlog2mysql

下载地址：https://github.com/danfengcao/binlog2sql.git

3.1 安装

# 下载git文件
PS D:\> git clone https://github.com/danfengcao/binlog2sql.git
# 进入binlog2sql目录
PS D:\> cd binlog2sql
# 安装插件
PS D:\> pip install -r requirements.txt

3.2 获取正向操作

# 再进入下一层binlog2sql文件夹
PS D:\> cd binlog2sql
PS D:\> python binlog2sql.py -h127.0.0.1 -P13306 -uroot -p --start-file=mysql-bin.000001 > binlog.sql

就能获取binlog日志中sql文件
在这里插入图片描述

3.3 获取逆向向操作

# 再进入下一层binlog2sql文件夹
PS D:\> cd binlog2sql
PS D:\> python binlog2sql.py -h127.0.0.1 -P13306 -uroot -p --start-file=mysql-bin.000001 --flashback > binlog.sql