binlog2sql 离线安装与使用

安装

这里介绍 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

其他参数可参考帮助文档