当前位置： 首页 > article >正文

【binlog2sql实践】MySQL数据库binlog日志ROW格式转换标准SQL

article 2025/3/18 17:58:51

在MySQL数据库中，二进制日志（binlog）是记录所有更改数据库数据的操作日志。binlog的格式主要有三种：STATEMENT、ROW和MIXED。其中，ROW格式的binlog记录了每一行数据的变更情况，虽然这种格式的日志文件较大，但它能够提供更详细的数据变更信息，特别适合用于数据恢复和审计。

但是ROW格式的binlog并不便于直接用于数据恢复。为了将ROW格式的binlog转化为可读的标准SQL语句，我们可以使用binlog2sql工具。本文将介绍如何使用binlog2sql工具来恢复ROW格式的binlog并转化为标准SQL。

1 下载源码包和依赖

1.1 下载binlog2sql

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

1.2 安装pyhont环境

# yum安装python
yum install python -y

# 安装pip
[root@node1 tool]# curl https://bootstrap.pypa.io/pip/2.7/get-pip.py -o get-pip.py
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
 29 1863k   29  558k    0     0   9642      0  0:03:17  0:00:59  0:02:18  6736
 53 1863k   53  990k    0     0   8426      0  0:03:46  0:02:00  0:01:46  6540
 80 1863k   80 1502k    0     0   8492      0  0:03:44  0:03:01  0:00:43  6818
100 1863k  100 1863k    0     0   7858      0  0:04:02  0:04:02 --:--:-- 10194
[root@node1 tool]# python get-pip.py
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Collecting pip<21.0
  Downloading pip-20.3.4-py2.py3-none-any.whl (1.5 MB)
     |██████                          | 286 kB 9.0 kB/s eta 0:02:18
     |████████████████▍               | 778 kB 8.5 kB/s eta 0:01:28
     |███████████████████████████████▋| 1.5 MB 8.8 kB/s eta 0:00:02
     |████████████████████████████████| 1.5 MB 12 kB/s 
Collecting wheel
  Downloading wheel-0.37.1-py2.py3-none-any.whl (35 kB)
Installing collected packages: pip, wheel
Successfully installed pip-20.3.4 wheel-0.37.1
[root@node1 tool]# 

# 验证python 版本
[root@node1 ~]# python --version
Python 2.7.5
[root@node1 ~]# 

# 验证pip版本
[root@node1 ~]# pip --version
pip 20.3.4 from /usr/lib/python2.7/site-packages/pip (python 2.7)
[root@node1 ~]#

1.3 安装依赖包

[root@node1 tool]# pip install mysql-replication==0.21
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Collecting mysql-replication==0.21
  Downloading mysql-replication-0.21.tar.gz (38 kB)
Collecting pymysql
  Downloading PyMySQL-0.10.1-py2.py3-none-any.whl (47 kB)
     |████████████████████████████████| 47 kB 10 kB/s 
Building wheels for collected packages: mysql-replication
  Building wheel for mysql-replication (setup.py) ... done
  Created wheel for mysql-replication: filename=mysql_replication-0.21-py2-none-any.whl size=42064 sha256=2b895385488863729b55290e6f00cce24c1ac47f3fb7a4639d477fade0b8fafb
  Stored in directory: /root/.cache/pip/wheels/c2/d2/9b/d875336da30aa3d725c9acd8649f71d13099aa2cba48600271
Successfully built mysql-replication
Installing collected packages: pymysql, mysql-replication
Successfully installed mysql-replication-0.21 pymysql-0.10.1
[root@node1 tool]#

2 binlog2sql解析ROW格式binlog

2.1 基本语法

python binlog2sql.py \
-h<host> \
-P<port> \
-u<user> \
-p<password> \
-d<database> \
-t<table> \
--start-file=<binlog_file> \
[--start-position=<start_pos>] \
[--stop-position=<end_pos>] \
[--start-datetime=<start_time>] \
[--stop-datetime=<end_time>]
参数说明：
  • -h：MySQL主机地址（默认：127.0.0.1）
  • -P：MySQL端口号（默认：3306）
  • -u：MySQL用户名
  • -p：MySQL密码
  • -d：数据库名称（可选）
  • -t：表名称（可选）
  • --start-file：binlog 文件名（如 mysql-bin.000001）
  • --start-position：binlog的起始位置（可选）
  • --stop-position：binlog的结束位置（可选）
  • --start-datetime：起始时间（格式：YYYY-MM-DD HH:MM:SS，可选）
  • --stop-datetime：结束时间（格式：YYYY-MM-DD HH:MM:SS，可选）

2.2 基于时间范围解析binlog

python /root/tool/binlog2sql-master/binlog2sql/binlog2sql.py \
-h127.0.0.1 \
-P3306 \
-uroot \
-p'password' \
-dmydb \
-ttmp_user \
--start-file='mysql-bin.000010' \
--start-datetime='2025-03-17 00:00:00' \
--stop-datetime='2025-03-17 00:00:00' >/data/mysql/backup/backup.sql

 2.3 基于位置范围解析binlog

python /root/tool/binlog2sql-master/binlog2sql/binlog2sql.py \
-h127.0.0.1 \
-P3306 \
-uroot \
-p'password' \
-dmydb \
-ttmp_user \
--start-file='mysql-bin.000005' \
--start-position=1234 \
--stop-position=5678 >/data/mysql/backup/backup.sql

3 恢复数据

# 生成的SQL文件后，就可以使用MySQL客户端工具（如mysql命令行工具）来执行这些SQL语句，从而恢复数据
mysql -h127.0.0.1 -uroot -p'passwd' testdb < data/mysql/backup/backup.sql

4 binlog2sql解析ROW格式binlog实战

4.1 准备工作

# 在mydb数据库中创建一张临时表，并在临时表插入10条数据
mysql> use mydb;
Database changed
mysql> CREATE TABLE tmp_user (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(50) NOT NULL,
    ->     age INT
    -> ) ENGINE=InnoDB;
INSERT INTO tmp_user (name, age) VALUES
('A', 25),
('B', 30),
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO tmp_user (name, age) VALUES
    -> ('A', 25),
    -> ('B', 30),
    -> ('C', 22),
    -> ('D', 28),
    -> ('E', 26),
    -> ('F', 35),
    -> ('G', 29),
    -> ('H', 31),
    -> ('I', 27),
    -> ('J', 24);
commit;Query OK, 10 rows affected (0.07 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select *from tmp_user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | A    |   25 |
|  2 | B    |   30 |
|  3 | C    |   22 |
|  4 | D    |   28 |
|  5 | E    |   26 |
|  6 | F    |   35 |
|  7 | G    |   29 |
|  8 | H    |   31 |
|  9 | I    |   27 |
| 10 | J    |   24 |
+----+------+------+
10 rows in set (0.00 sec)

mysql>

4.2 模拟删除表数据

# 模拟误操作将tmp_user表数据删除
mysql> delete from tmp_user;
Query OK, 10 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>

4.3 基于binlog实现数据恢复

4.3.1 查询误操作时间节点

# 这里我们是通过general_log进行查找误操作时间点，使用general_log的前提是开启了general_log
1. 查询是否开启general_log，如果是On则开启
mysql> SHOW VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> 

2. 查看slow_query_log文件路径
mysql> SHOW VARIABLES LIKE 'general_log_file';
+------------------+-----------------------------+
| Variable_name    | Value                       |
+------------------+-----------------------------+
| general_log_file | /data/mysql/log/general.log |
+------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> 

3. 分析general_log的文件/data/mysql/log/general.log
[root@node3 ~]# grep -i "insert into" /data/mysql/log/general.log
2025-03-17T14:10:02.804862+08:00           35 Query     INSERT INTO tmp_user (name, age) VALUES
[root@node3 ~]#

4.3.2 binlog2sql解析导出binlog

python /root/tool/binlog2sql-master/binlog2sql/binlog2sql.py \
-h127.0.0.1 \
-P3306 \
-uroot \
-p'lahmy1c@' \
-dmydb \
-ttmp_user \
--start-file='mysql-bin.000005' \
--start-datetime='2025-03-17 14:10:02' \
--stop-datetime='2025-03-17 14:11:02' >/data/mysql/backup/backup.sql

[root@node3 ~]# python /root/tool/binlog2sql-master/binlog2sql/binlog2sql.py \
> -h127.0.0.1 \
> -P3306 \
> -uroot \
> -p'lahmy1c@' \
> -dmydb \
> -ttmp_user \
> --start-file='mysql-bin.000005' \
> --start-datetime='2025-03-17 14:10:02' \
> --stop-datetime='2025-03-17 14:11:02' >/data/mysql/backup/backup.sql
[root@node3 ~]#

4.3.3 恢复数据

mysql -h127.0.0.1 -uroot -p'lahmy1c@' mydb < /data/mysql/backup/backup.sql

[root@node3 ~]# mysql -h127.0.0.1 -uroot -p'lahmy1c@' mydb < /data/mysql/backup/backup.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@node3 ~]#

4.3.4 验证

mysql> select * from tmp_user;
+----+------+------+
| id | name | age  |
+----+------+------+
| 11 | A    |   25 |
| 12 | B    |   30 |
| 13 | C    |   22 |
| 14 | D    |   28 |
| 15 | E    |   26 |
| 16 | F    |   35 |
| 17 | G    |   29 |
| 18 | H    |   31 |
| 19 | I    |   27 |
| 20 | J    |   24 |
+----+------+------+
10 rows in set (0.00 sec)

mysql>

5 错误问题解决办法

5.1 mysql-replication依赖安装错误解决

[root@node1 tool]# pip install mysql-replication
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Collecting mysql-replication
  Using cached mysql_replication-1.0.9.tar.gz (71 kB)
  Installing build dependencies ... done
  Getting requirements to build wheel ... error
  ERROR: Command errored out with exit status 1:
   command: /usr/bin/python /usr/lib/python2.7/site-packages/pip/_vendor/pep517/_in_process.py get_requires_for_build_wheel /tmp/tmpYwoAbg
       cwd: /tmp/pip-install-vRXNI3/mysql-replication
  Complete output (18 lines):
  Traceback (most recent call last):
    File "/usr/lib/python2.7/site-packages/pip/_vendor/pep517/_in_process.py", line 280, in <module>
      main()
    File "/usr/lib/python2.7/site-packages/pip/_vendor/pep517/_in_process.py", line 263, in main
      json_out['return_val'] = hook(**hook_input['kwargs'])
    File "/usr/lib/python2.7/site-packages/pip/_vendor/pep517/_in_process.py", line 114, in get_requires_for_build_wheel
      return hook(config_settings)
    File "/tmp/pip-build-env-TUvCpr/overlay/lib/python2.7/site-packages/setuptools/build_meta.py", line 146, in get_requires_for_build_wheel
      return self._get_build_requires(config_settings, requirements=['wheel'])
    File "/tmp/pip-build-env-TUvCpr/overlay/lib/python2.7/site-packages/setuptools/build_meta.py", line 127, in _get_build_requires
      self.run_setup()
    File "/tmp/pip-build-env-TUvCpr/overlay/lib/python2.7/site-packages/setuptools/build_meta.py", line 243, in run_setup
      self).run_setup(setup_script=setup_script)
    File "/tmp/pip-build-env-TUvCpr/overlay/lib/python2.7/site-packages/setuptools/build_meta.py", line 142, in run_setup
      exec(compile(code, __file__, 'exec'), locals())
    File "setup.py", line 8, in <module>
      from pathlib import Path
  ImportError: No module named pathlib
  ----------------------------------------
ERROR: Command errored out with exit status 1: /usr/bin/python /usr/lib/python2.7/site-packages/pip/_vendor/pep517/_in_process.py get_requires_for_build_wheel /tmp/tmpYwoAbg Check the logs for full command output.
[root@node1 tool]#

安装依赖包mysql-replication时报错：因为此时安装的是mysql_replication-1.0.9.tar.gz，版本为1.0.9，我们降低下版本，安装0.21，安装命令：

pip install mysql-replication==0.21

5.2 pymysql问题解决

[root@node1 tool]# python /root/tool/binlog2sql-master/binlog2sql/binlog2sql.py -h127.0.0.1 -uroot -p -dmysqldb -ttmp_user --start-file=mysql-bin.000005
Traceback (most recent call last):
  File "/root/tool/binlog2sql-master/binlog2sql/binlog2sql.py", line 7, in <module>
    from pymysqlreplication import BinLogStreamReader
  File "/usr/local/lib/python3.6/site-packages/mysql_replication-0.22-py3.6.egg/pymysqlreplication/__init__.py", line 23, in <module>
  File "/usr/local/lib/python3.6/site-packages/mysql_replication-0.22-py3.6.egg/pymysqlreplication/binlogstream.py", line 9, in <module>
ModuleNotFoundError: No module named 'pymysql.util'
[root@node1 tool]#

特别地，执行示例命令时报错，mysql-replication-0.21对于pymysql版本也有要求（一般是0.7到0.9），因此我们需要卸载pymysql，重新安装一个pymysql 0.9版本，操作如下： 

# 卸载pymysql
[root@node1 tool]# pip uninstall pymysql
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Found existing installation: PyMySQL 0.10.1
Uninstalling PyMySQL-0.10.1:
  Would remove:
    /usr/lib/python2.7/site-packages/PyMySQL-0.10.1.dist-info/*
    /usr/lib/python2.7/site-packages/pymysql/*
Proceed (y/n)? ^[[A^[[A
Your response ('\x1b[a\x1b[a') was not one of the expected responses: y, n
Proceed (y/n)? y
  Successfully uninstalled PyMySQL-0.10.1
[root@node1 tool]# pip install pymysql==0.9
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Collecting pymysql==0.9
  Downloading PyMySQL-0.9.0-py2.py3-none-any.whl (84 kB)
     |████████████████████████████████| 84 kB 75 kB/s 
Requirement already satisfied: cryptography in /usr/lib64/python2.7/site-packages (from pymysql==0.9) (1.7.2)
Requirement already satisfied: idna>=2.0 in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (2.4)
Requirement already satisfied: pyasn1>=0.1.8 in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (0.1.9)
Requirement already satisfied: six>=1.4.1 in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (1.9.0)
Requirement already satisfied: setuptools in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (0.9.8)
Requirement already satisfied: enum34 in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (1.0.4)
Requirement already satisfied: ipaddress in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (1.0.16)
Requirement already satisfied: cffi>=1.4.1 in /usr/lib64/python2.7/site-packages (from cryptography->pymysql==0.9) (1.6.0)
Requirement already satisfied: pycparser in /usr/lib/python2.7/site-packages (from cffi>=1.4.1->cryptography->pymysql==0.9) (2.14)
Installing collected packages: pymysql
Successfully installed pymysql-0.9.0
[root@node1 tool]# 
# 安装0.9版本的pymysql
[root@node3 ~]# pip install pymysql==0.9
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Collecting pymysql==0.9
  Downloading PyMySQL-0.9.0-py2.py3-none-any.whl (84 kB)
     |████████████████████████████████| 84 kB 604 kB/s 
Requirement already satisfied: cryptography in /usr/lib64/python2.7/site-packages (from pymysql==0.9) (1.7.2)
Requirement already satisfied: idna>=2.0 in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (2.4)
Requirement already satisfied: pyasn1>=0.1.8 in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (0.1.9)
Requirement already satisfied: six>=1.4.1 in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (1.9.0)
Requirement already satisfied: setuptools in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (0.9.8)
Requirement already satisfied: enum34 in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (1.0.4)
Requirement already satisfied: ipaddress in /usr/lib/python2.7/site-packages (from cryptography->pymysql==0.9) (1.0.16)
Requirement already satisfied: cffi>=1.4.1 in /usr/lib64/python2.7/site-packages (from cryptography->pymysql==0.9) (1.6.0)
Requirement already satisfied: pycparser in /usr/lib/python2.7/site-packages (from cffi>=1.4.1->cryptography->pymysql==0.9) (2.14)
Installing collected packages: pymysql
  Attempting uninstall: pymysql
    Found existing installation: PyMySQL 0.10.1
    Uninstalling PyMySQL-0.10.1:
      Successfully uninstalled PyMySQL-0.10.1
Successfully installed pymysql-0.9.0
[root@node3 ~]#

5.3 数据库连接问题解决

[root@node1 ~]# python /root/tool/binlog2sql-master/binlog2sql/binlog2sql.py -h192.168.10.30 -uroot -p -dmydb -ttmp_user --start-file=mysql-bin.000005
Password: 
Traceback (most recent call last):
  File "/root/tool/binlog2sql-master/binlog2sql/binlog2sql.py", line 149, in <module>
    back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
  File "/root/tool/binlog2sql-master/binlog2sql/binlog2sql.py", line 46, in __init__
    self.connection = pymysql.connect(**self.conn_setting)
  File "/usr/lib/python2.7/site-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 327, in __init__
    self.connect()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 598, in connect
    self._request_authentication()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 862, in _request_authentication
    auth_packet = self._process_auth(plugin_name, auth_packet)
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 933, in _process_auth
    pkt = self._read_packet()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/usr/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, u"Access denied for user 'root'@'192.168.10.30' (using password: YES)")
[root@node1 ~]#
mysql> SELECT user, host FROM mysql.user WHERE user = 'root';
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec)

mysql> UPDATE mysql.user SET host='%' WHERE user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql>

6 总结

通过binlog2sql工具，我们可以轻松地将ROW格式的binlog转化为标准SQL语句，从而方便地进行数据恢复和审计。虽然ROW格式的binlog文件较大，但它提供了更详细的数据变更信息，结合binlog2sql工具，我们可以更好地利用这些信息来维护数据库的完整性和一致性。


http://www.kler.cn/a/590060.html

相关文章：

  • Linux 蓝牙音频软件栈实现分析
  • 美团Leaf分布式ID生成器：使用详解与核心原理解析
  • 关于虚拟网络编辑器还原默认设置那些坑
  • Pandas DataFrame：数据分析的利器
  • 解决从deepseek接口获取的流式响应输出到前端都是undefined的问题
  • 微服务架构中10个常用的设计模式
  • 平面阵列天线波束形成的Matlab仿真
  • 一场由 ES 分片 routing 引发的问题
  • 2025年Postman的五大替代工具
  • 【软考-架构】5.3、IPv6-网络规划-网络存储-补充考点
  • SpringData Redis：RedisTemplate配置与数据操作
  • 02 windows qt配置ffmpeg开发环境搭建
  • 电脑如何录屏
  • failed to load elasticsearch nodes
  • SpringBoot + Mybatis Plus 整合 Redis
  • mariaDB中常见的DDL,DML,DQL语句
  • 协程池是调用端并发请求的缓释胶囊
  • Spring设计模式 八股速记 高层模块底层模块 依赖倒置原则 开闭原则 接口隔离原则
  • WD5202L超低成本 Buck 电源芯片的特性与应用电路解析， 将市电转换为 5V 电压
  • 缓存相关内容