安装
这里介绍 binlog2sql 的离线安装方法。
如果电脑服务器可以联网，则可以直接参考源码地址的安装方法。
源码地址： https://github.com/danfengcao/binlog2sql
离线所需相关包如下：
argparse-1.4.0-py2.py3-none-any.whl
binlog2sql-master.zip
linecache2-1.0.0-py2.py3-none-any.whl
mysql-replication-0.9.tar.gz
PyMySQL-0.7.8-py2-none-any.whl
Python-2.7.18.tar.xz
six-1.10.0-py2.py3-none-any.whl
traceback2-1.4.0-py2.py3-none-any.whl
unittest2-1.1.0-py2.py3-none-any.whl
wheel-0.24.0-py2.py3-none-any.whl
python 下载地址：https://www.python.org/downloads/release/python-2718/
包下载地址：https://pypi.org/search/?q=python&page=2
PS：python 要用 2.6 以上的安装准备环境
yum install -y zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gcc libffi-devel xz unzip解压 python 安装包并安装
tar xvf Python-2.7.18.tar.xz
cd Python-2.7.18
./configure --prefix=/usr --with-ensurepip --with-system-ffi
make && make install检查是否安装成功
[root@test2 Python-2.7.18]# python --version
Python 2.7.18
[root@test2 Python-2.7.18]# pip --version
pip 19.2.3 from /usr/lib/python2.7/site-packages/pip (python 2.7)
[root@test2 Python-2.7.18]#安装其他包
cd /soft
pip install *.whl mysql-replication-0.9.tar.gz
添加别名
alias binlog2sql='python /soft/binlog2sql-master/binlog2sql/binlog2sql.py'检查安装是否成功
binlog2sql --help，有帮助出现则成功
usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]]
[-P PORT] [--start-file START_FILE]
[--start-position START_POS] [--stop-file END_FILE]
[--stop-position END_POS] [--start-datetime START_TIME]
[--stop-datetime STOP_TIME] [--stop-never] [--help]
[-d [DATABASES [DATABASES ...]]]
[-t [TABLES [TABLES ...]]] [--only-dml]
[--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B]
[--back-interval BACK_INTERVAL]
Parse MySQL binlog to SQL you want
optional arguments:
--stop-never Continuously parse binlog. default: stop at the latest
event when you start.
--help help information
-K, --no-primary-key Generate insert sql without primary key if exists
-B, --flashback Flashback data to start_position of start_file
--back-interval BACK_INTERVAL
Sleep time between chunks of 1000 rollback sql. set it
to 0 if do not need sleep
connect setting:
-h HOST, --host HOST Host the MySQL database server located
-u USER, --user USER MySQL Username to log in as
-p [PASSWORD [PASSWORD ...]], --password [PASSWORD [PASSWORD ...]]
MySQL Password to use
-P PORT, --port PORT MySQL port to use
interval filter:
--start-file START_FILE
Start binlog file to be parsed
--start-position START_POS, --start-pos START_POS
Start position of the --start-file
--stop-file END_FILE, --end-file END_FILE
Stop binlog file to be parsed. default: '--start-file'
--stop-position END_POS, --end-pos END_POS
Stop position. default: latest position of '--stop-
file'
--start-datetime START_TIME
Start time. format %Y-%m-%d %H:%M:%S
--stop-datetime STOP_TIME
Stop Time. format %Y-%m-%d %H:%M:%S;
schema filter:
-d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]]
dbs you want to process
-t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]]
tables you want to process
type filter:
--only-dml only print dml, ignore ddl
--sql-type [SQL_TYPE [SQL_TYPE ...]]
Sql type you want to process, support INSERT, UPDATE,
DELETE.至此安装完毕
但是这时可能 yum 有问题，会出现如下提示：
[root@test2 soft]# yum install openssh
There was a problem importing one of the Python modules
required to run yum. The error leading to this problem was:
No module named yum
Please install a package which provides this module, or
verify that the module is installed correctly.
It's possible that the above module doesn't match the
current version of Python, which is:
2.7.18 (default, Apr 23 2020, 01:24:55)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-18)]
If you cannot solve this problem yourself, please go to
the yum faq at:
http://yum.baseurl.org/wiki/Faq如果有此提示，则可能是因为有 2 个 python 版本的导致，处理如下：
1.查看 yum 的文件
[root@test2 soft]# whereis yum
yum: /usr/bin/yum /etc/yum /etc/yum.conf /usr/share/man/man8/yum.8.gz
2.编辑 /usr/bin/yum
将第一行的 #!/usr/bin/python 修改为 #!/usr/bin/python2.6，然后保存退出即可如果用的 python 2.6 ，则在执行 binlog2sql 名的时候就会出现如下错误。
# binlog2sql参数
[root@dba_test_002 binlog2sql]# /usr/bin/python /soft/binlog2sql/binlog2sql/binlog2sql.py --help
File "/opt/binlog2sql/binlog2sql/binlog2sql.py", line 73
with temp_open(tmp_file, "w") as f_tmp, self.connection as cursor:
^
SyntaxError: invalid syntax
[root@dba_test_002 binlog2sql]#
# 出现上述提示，请更换python版本即可，不要使用python2.6版本
使用
使用前必须设置的参数及权限
MySQL server必须设置以下参数:
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
user需要的最小权限集合：
select, super/replication client, replication slave
建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
权限说明
select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
限制（对比mysqlbinlog）
mysql server必须开启，离线模式下不能解析
参数 binlog_row_image 必须为FULL，暂不支持MINIMAL
解析速度不如mysqlbinlog
优点（对比mysqlbinlog）
纯Python开发，安装与使用都很简单
自带flashback、no-primary-key解析模式，无需再装补丁
flashback模式下，更适合闪回实战
解析为标准SQL，方便理解、筛选
代码容易改造，可以支持更多个性化解析
更详细的介绍可参考源码地址建立表，如下：
mysql> show create table t;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------+
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)模拟误删除操作
mysql> delete from t;
Query OK, 4 rows affected (0.01 sec)查看当前日志并解析
mysql> show master status;
+------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------------------------------+
| bin.000006 | 1379 | | | ea27a55f-84b9-11ea-8c48-000c29dbfcd1:1-13 |
+------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> mysql> show binlog events in 'bin.000006';
+------------+------+----------------+------------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------+------+----------------+------------+-------------+--------------------------------------------------------------------+
| bin.000006 | 4 | Format_desc | 1921686526 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| bin.000006 | 123 | Previous_gtids | 1921686526 | 194 | ea27a55f-84b9-11ea-8c48-000c29dbfcd1:1-9 |
| bin.000006 | 194 | Gtid | 1921686526 | 259 | SET @@SESSION.GTID_NEXT= 'ea27a55f-84b9-11ea-8c48-000c29dbfcd1:10' |
| bin.000006 | 259 | Query | 1921686526 | 329 | BEGIN |
| bin.000006 | 329 | Rows_query | 1921686526 | 376 | # insert into t values(2) |
| bin.000006 | 376 | Table_map | 1921686526 | 418 | table_id: 108 (ty.t) |
| bin.000006 | 418 | Write_rows | 1921686526 | 458 | table_id: 108 flags: STMT_END_F |
| bin.000006 | 458 | Xid | 1921686526 | 489 | COMMIT /* xid=11 */ |
| bin.000006 | 489 | Gtid | 1921686526 | 554 | SET @@SESSION.GTID_NEXT= 'ea27a55f-84b9-11ea-8c48-000c29dbfcd1:11' |
| bin.000006 | 554 | Query | 1921686526 | 624 | BEGIN |
| bin.000006 | 624 | Rows_query | 1921686526 | 671 | # insert into t values(3) |
| bin.000006 | 671 | Table_map | 1921686526 | 713 | table_id: 108 (ty.t) |
| bin.000006 | 713 | Write_rows | 1921686526 | 753 | table_id: 108 flags: STMT_END_F |
| bin.000006 | 753 | Xid | 1921686526 | 784 | COMMIT /* xid=15 */ |
| bin.000006 | 784 | Gtid | 1921686526 | 849 | SET @@SESSION.GTID_NEXT= 'ea27a55f-84b9-11ea-8c48-000c29dbfcd1:12' |
| bin.000006 | 849 | Query | 1921686526 | 919 | BEGIN |
| bin.000006 | 919 | Rows_query | 1921686526 | 966 | # insert into t values(4) |
| bin.000006 | 966 | Table_map | 1921686526 | 1008 | table_id: 108 (ty.t) |
| bin.000006 | 1008 | Write_rows | 1921686526 | 1048 | table_id: 108 flags: STMT_END_F |
| bin.000006 | 1048 | Xid | 1921686526 | 1079 | COMMIT /* xid=16 */ |
| bin.000006 | 1079 | Gtid | 1921686526 | 1144 | SET @@SESSION.GTID_NEXT= 'ea27a55f-84b9-11ea-8c48-000c29dbfcd1:13' |
| bin.000006 | 1144 | Query | 1921686526 | 1214 | BEGIN |
| bin.000006 | 1214 | Rows_query | 1921686526 | 1251 | # delete from t |
| bin.000006 | 1251 | Table_map | 1921686526 | 1293 | table_id: 108 (ty.t) |
| bin.000006 | 1293 | Delete_rows | 1921686526 | 1348 | table_id: 108 flags: STMT_END_F |
| bin.000006 | 1348 | Xid | 1921686526 | 1379 | COMMIT /* xid=20 */ |
+------------+------+----------------+------------+-------------+--------------------------------------------------------------------+
直接用 mysqlbinlog 查看
mysqlbinlog --base64-output=DECODE-ROWS -vv bin.000006
# at 1144
#200423 3:58:52 server id 1921686526 end_log_pos 1214 CRC32 0x2864bad1 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1587585532/*!*/;
BEGIN
/*!*/;
# at 1214
#200423 3:58:52 server id 1921686526 end_log_pos 1251 CRC32 0x70480eb2 Rows_query
# delete from t
# at 1251
#200423 3:58:52 server id 1921686526 end_log_pos 1293 CRC32 0xf7c3b49a Table_map: `ty`.`t` mapped to number 108
# at 1293
#200423 3:58:52 server id 1921686526 end_log_pos 1348 CRC32 0x33eeabf3 Delete_rows: table id 108 flags: STMT_END_F
### DELETE FROM `ty`.`t`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `ty`.`t`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `ty`.`t`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `ty`.`t`
### WHERE
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 1348
#200423 3:58:52 server id 1921686526 end_log_pos 1379 CRC32 0xcfaf7edc Xid = 20
COMMIT/*!*/;
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*/;
可以看到删除语句的点位是在 1144 ~ 1379利用 binlog2sql 解析出误操作 sql
[root@test2 ~]# binlog2sql -h192.168.65.2 -P3306 -uroot -p123 -dty -tt --start-file='bin.000006' --start-position=1144 --stop-position=1379
DELETE FROM `ty`.`t` WHERE `id`=1 LIMIT 1; #start 1144 end 1348 time 2020-04-23 03:58:52
DELETE FROM `ty`.`t` WHERE `id`=2 LIMIT 1; #start 1144 end 1348 time 2020-04-23 03:58:52
DELETE FROM `ty`.`t` WHERE `id`=3 LIMIT 1; #start 1144 end 1348 time 2020-04-23 03:58:52
DELETE FROM `ty`.`t` WHERE `id`=4 LIMIT 1; #start 1144 end 1348 time 2020-04-23 03:58:52
这里得到的就是删除数据的语句解析出恢复语句（-B 参数）
[root@test2 ~]# binlog2sql -h192.168.65.2 -P3306 -uroot -p123 -dty -tt --start-file='bin.000006' --start-position=1144 --stop-position=1379 -B
INSERT INTO `ty`.`t`(`id`) VALUES (4); #start 1144 end 1348 time 2020-04-23 03:58:52
INSERT INTO `ty`.`t`(`id`) VALUES (3); #start 1144 end 1348 time 2020-04-23 03:58:52
INSERT INTO `ty`.`t`(`id`) VALUES (2); #start 1144 end 1348 time 2020-04-23 03:58:52
INSERT INTO `ty`.`t`(`id`) VALUES (1); #start 1144 end 1348 time 2020-04-23 03:58:52
可直接输出道文件，然后在 mysql 执行即可。也可以根据时间点来，例句：
binlog2sql -h127.0.0.1 -P3306 -uroot -p123 -dty -tt --start-file='bin.000006' --start-datetime='2020-05-23 15:35:59' --stop-datetime='2020-05-23 16:09:59' -B
其他参数可参考帮助文档
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
