mysqlbinlog的flashback工具binlog2sql

my.cnf中关于binlog的设置 binlog_format = row binlog格式必须是row模式 binlog_row_image = full 必须为full 默认就是full 准备工作 #yum intall git #yum install python-setuptools pip需要这个 #wget "https://pypi.python.org/packages/source/p/pip/pip-1.5.4.tar.gz#md5=834b2904f92d46aaa333267fb1c922bb" --no-check-certificate # tar -xzvf pip-1.5.4.tar.gz # cd pip-1.5.4 # python setup.py install 安装 # git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql # pip install -r requirements.txt 用户授权（用于binglog2sql使用） GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO xiaomage@'localhost' identified by 'xiaomage';

select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表 replication slave：通过BINLOG_DUMP协议获取binlog内容的权限

+------+-------+----------+ | id | name | time | +------+-------+----------+ | 1 | lalal | 16:32:21 | | 2 | ababa | 16:32:34 | | 3 | jgjgj | 16:32:43 | | 4 | opopo | 16:32:52 | | 5 | jljlj | 16:33:06 | +------+-------+----------+ 5 rows in set (0.00 sec) mysql> delete from test; --清空表-- Query OK, 5 rows affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000013 | 2016 | | | f90ca153-b46d-11e6-ab68-0800279ecefa:1-35 | +------------------+----------+--------------+------------------+-------------------------------------------+ 一般情况下都是知道个大概的时间，先从大概的时间找起 [root@master binlog2sql]# python /opt/binlog2sql/binlog2sql/binlog2sql.py -uxiaomage -pxiaomage -dtest -ttest --start-file=mysql-bin.000013 --start-datetime='2017-05-05 16:30:00' --stop-datetime='2017-05-05 16:40:00' USE test; create table test (id int, name varchar(20),time time); INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:21', 1, 'lalal'); #start 478 end 659 time 2017-05-05 16:32:21 INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:34', 2, 'ababa'); #start 690 end 871 time 2017-05-05 16:32:34 INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:43', 3, 'jgjgj'); #start 902 end 1083 time 2017-05-05 16:32:43 INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:52', 4, 'opopo'); #start 1114 end 1295 time 2017-05-05 16:32:52 INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:33:06', 5, 'jljlj'); #start 1326 end 1507 time 2017-05-05 16:33:06 USE test; alter table test modify time datetime; USE test; alter table test modify time time; DELETE FROM `test`.`test` WHERE `time`='16:32:21' AND `id`=1 AND `name`='lalal' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27 ----这就是delete的数据--- DELETE FROM `test`.`test` WHERE `time`='16:32:34' AND `id`=2 AND `name`='ababa' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27 DELETE FROM `test`.`test` WHERE `time`='16:32:43' AND `id`=3 AND `name`='jgjgj' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27 DELETE FROM `test`.`test` WHERE `time`='16:32:52' AND `id`=4 AND `name`='opopo' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27 DELETE FROM `test`.`test` WHERE `time`='16:33:06' AND `id`=5 AND `name`='jljlj' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27 生成回滚语句 -B，上面看到的具体的位置start 1756 end 1985（这里一定要过滤好了，一定要细心，配合grep什么的进行匹配） [root@master binlog2sql]# python /opt/binlog2sql/binlog2sql/binlog2sql.py -uxiaomage -pxiaomage -dtest -ttest --start-file=mysql-bin.000013 --start-position=1756 --stop-position=1985 -B INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:33:06', 5, 'jljlj'); #start 1756 end 1985 time 2017-05-05 16:37:27 INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:52', 4, 'opopo'); #start 1756 end 1985 time 2017-05-05 16:37:27 INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:43', 3, 'jgjgj'); #start 1756 end 1985 time 2017-05-05 16:37:27 INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:34', 2, 'ababa'); #start 1756 end 1985 time 2017-05-05 16:37:27 INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:21', 1, 'lalal'); #start 1756 end 1985 time 2017-05-05 16:37:27 重定向到/opt/rollback.sql文件中恢复： mysql> select count(*) from test; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.05 sec) mysql> source /opt/rollback.sql Query OK, 1 row affected (0.03 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.07 sec) Query OK, 1 row affected (0.04 sec) Query OK, 1 row affected (0.00 sec) mysql> select * from test; +------+-------+----------+ | id | name | time | +------+-------+----------+ | 5 | jljlj | 16:33:06 | | 4 | opopo | 16:32:52 | | 3 | jgjgj | 16:32:43 | | 2 | ababa | 16:32:34 | | 1 | lalal | 16:32:21 | +------+-------+----------+ 5 rows in set (0.00 sec) ok恢复会来了

解析模式 --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。可选。默认为空

参考：https://github.com/danfengcao/binlog2sql

相关阅读