Binlog2sql 恢复误删除的数据

yum -y install git python-pip

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

[mysqld]
server_id = 值
log_bin = /home/mysql/mysql_data
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

cd /home/mysql/mysql_data && mysqlbinlog mysql-bin.000007 --base64-output=decode-rows -v > binlog.sql 
more binlog.sql

#at 754      
#190613  9:44:34 server id 92  end_log_pos 819 CRC32 0x23d85f30     Anonymous_GTID  last_committed=2    sequence_number=3   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
..................
..................
### DELETE FROM `xxx`.`xxx`
4318 ### WHERE
4319 ###   @1=4160026
4320 ###   @2=1
4321 ###   @3=1
4322 ###   @4=4262999
4323 ###   @5='deal'
4324 ###   @6='2016-10-26 11:43:54'
4325 # at 14079
4326 #190613  9:44:34 server id 92  end_log_pos 14110 CRC32 0xaa751b04   Xid = 2876280
4327 COMMIT/*!*/;

授权用户REPLICATION SLAVE, REPLICATION CLIENT的权限（或者找有这两个权限的用户）
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'jmysql'@'localhost'

cd /home/mysql/mysql_data/binlog2sql/binlog2sql
python binlog2sql.py -ujmysql -hlocalhost -p123 -dxxx -ttest --start-position='754' --stop-position='14110' --start-file='mysql-bin.000007' -B > binlog2sql.sql
more binlog2sql.sql

--stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。
-B, --flashback 生成回滚语句，可解析大文件，不受内存限制，每打印一千行加一句SLEEP SELECT(1)。可选。与stop-never或no-primary-key不能同时添加。
解析范围控制
--start-file 起始解析文件。必须。
--start-position/--start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime 从哪个时间点的binlog开始解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。
--stop-datetime 到哪个时间点的binlog停止解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。
对象过滤
-d, --databases 只输出目标db的sql。可选。默认为空。
-t, --tables 只输出目标tables的sql。可选。默认为空。

mysql> source     /home/mysql/mysql_data/binlog2sql/binlog2sql/binlog2sql.sql

cat mysql-audit.json |grep 'delete from ware_close_log'
{"msg-type":"activity","date":"1560390274083","thread-id":"5167","query-id":"2876280","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"1662","_client_version":"5.7.26-29","_platform":"x86_64","program_name":"mysql"},"pid":"7844","os_user":"zabbix","appname":"mysql","rows":"531","status":"0","cmd":"delete","objects":[{"db":"t591","name":"ware_close_log","obj_type":"TABLE"}],"query":"delete from ware_close_log"}

mysqlbinlog /home/mysql/mysql_data/mysql-bin.000007 --start-position='值' --stop-position='值' | mysql -u用户 -p'密码'