mysql原生自带mysqlbinlog，但使用binlog恢复的原理是“重做”而不是“回滚”，所以很多应急场景mysqlbinlog已经不再合适，那就研究下binlog2sql怎么使用。
安装依赖特别多，推荐大神博客下载安装就可以，https://www.cnblogs.com/ivictor/p/6418409.html。
使用该工具的前提
1. binlog_format为ROW，且binlog_row_image为full或noblog，默认为full。
2. 必须开启MySQL Server，理由有如下两点：
1> 它是基于BINLOG_DUMP协议来获取binlog内容
2> 需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
该工具所需权限如下：
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
因为是伪装成slave来获取主的二进制事件，故无需对binlog有可读权限。
安装后查看查看binglog2sql怎么使用:
python binlog2sql.py --help
usage: binlog2sql.py [-h HOST] [-u USER] [-p PASSWORD] [-P PORT]
[--start-file STARTFILE] [--start-position STARTPOS]
[--stop-file ENDFILE] [--stop-position ENDPOS]
[--start-datetime STARTTIME] [--stop-datetime STOPTIME]
[--stop-never] [--help] [-d [DATABASES [DATABASES ...]]]
[-t [TABLES [TABLES ...]]] [-K] [-B]
Parse MySQL binlog to SQL you want
optional arguments:
--stop-never Wait for more data from the server. default: stop
replicate at the last binlog when you start binlog2sql
--help help infomation
-K, --no-primary-key Generate insert sql without primary key if exists
-B, --flashback Flashback data to start_postition of start_file
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
MySQL Password to use
-P PORT, --port PORT MySQL port to use
range filter:
--start-file STARTFILE
Start binlog file to be parsed
--start-position STARTPOS, --start-pos STARTPOS
Start position of the --start-file
--stop-file ENDFILE, --end-file ENDFILE
Stop binlog file to be parsed. default: ‘--start-file‘
--stop-position ENDPOS, --end-pos ENDPOS
Stop position of --stop-file. default: latest position
of ‘--stop-file‘
--start-datetime STARTTIME
Start reading the binlog at first event having a
datetime equal or posterior to the argument; the
argument must be a date and time in the local time
zone, in any format accepted by the MySQL server for
DATETIME and TIMESTAMP types, for example: 2004-12-25
11:25:56 (you should probably use quotes for your
shell to set it properly).
--stop-datetime STOPTIME
Stop reading the binlog at first event having a
datetime equal or posterior to the argument; the
argument must be a date and time in the local time
zone, in any format accepted by the MySQL server for
DATETIME and TIMESTAMP types, for example: 2004-12-25
11:25:56 (you should probably use quotes for your
shell to set it properly).
schema filter:
-d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]]
dbs you want to process
-t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]]
tables you want to process
首先查看线上的数据：
现在人为更新一条数据：
查看binlog events:
执行binlog2sql查看先关的SQL：
-B参数生成相反的回滚SQL，或者>到文件中，
执行反向SQL或者source 生成的文件，即可恢复数据.
binlog2sql使用方法验证完毕。