数据恢复binlog2sql--准备工作

1.确定版本信息和binlog格式

mysql版本：5.7.12 查看binlog格式的命令 mysql> show variables like 'binlog_format';

1. +---------------+-------+
2. | Variable_name | Value |
3. +---------------+-------+
4. | binlog_format | ROW |
5. +---------------+-------+

2.安装binlog2sql工具

3.在mysql的主服务器上，创建闪回操作账号的权限 mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'glon'@'%' identified by '123456';

mysql> show grants for 'glon'@'%';

1. +--------------------------------------------------------------------------+
2. | Grants for glon@% |
3. +--------------------------------------------------------------------------+
4. | GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'glon'@'%' |
5. +--------------------------------------------------------------------------+

4.初始化数据

1. CREATE TABLE `edai_binlog2sql` (
2. `id` int(11) NOT NULL AUTO_INCREMENT,
3. `name` varchar(20) NOT NULL,
4. `create_time` datetime NOT NULL,
5. PRIMARY KEY (`id`)
6. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
7. insert into edai_binlog2sql (name,create_time) values ('Glon Ho','2012-10-1'),('Eason Chan', '2016-05-02'),('Jacky Cheung', '2015-05-02');

mysql>select * from edai_binlog2sql;

1. +----+--------------+---------------------+
2. | id | name | create_time |
3. +----+--------------+---------------------+
4. | 4 | Glon Ho | 2012-10-01 00:00:00 |
5. | 5 | Eason Chan | 2016-05-02 00:00:00 |
6. | 6 | Jacky Cheung | 2015-05-02 00:00:00 |
7. +----+--------------+-------------------

• 进行 update 和 delete 操作 mysql> update edai_binlog2sql set create_time = '2017-05-12' where name = 'Glon Ho';

mysql> select * from edai_binlog2sql;

1. +----+--------------+---------------------+
2. | id | name | create_time |
3. +----+--------------+---------------------+
4. | 4 | Glon Ho | 2017-05-12 00:00:00 |
5. | 5 | Eason Chan | 2016-05-02 00:00:00 |
6. | 6 | Jacky Cheung | 2015-05-02 00:00:00 |
7. +----+--------------+---------------------+
8. rows in set (0.00 sec)

接下来使用binlog2sql工具 1）解析出标准的SQL python binlog2sql.py -h192.168.1.21 -P30136 -uglon -p'123456' -d xcrm -t edai_binlog2sql --start-file=mysql-bin.000001 > edai_binlog2sql.sql

参数解释： -h：数据库服务地址 -u：连接用户名 -p：密码 -P：端口 -d：数据库名 -t：表名 –start-file： 通俗的来讲就是，要解析sql的所在的binglog文件 –flashback： 闪回，逆向解析sql语句

cat edai_binlog2sql.sql

file

可以看到，几乎完美重现了我们上面执行过的 SQL，而且生成的每个 SQL 后面都带有该语句在 binlog 中的 position 信息和该语句的执行时间。

2）解析想要回滚的SQL 比如，我想回滚刚刚操作的，edai_binlog2sql 后面两个update和DELETE操作

找到的时间节点就是：start 6159262 end 6159823 #分析最好用pos分析，这个可以更准确的定位到想要的 python binlog2sql.py --flashback -h192.168.1.21 -P30136 -uglon -p'123456' -dxcrm -tedai_binlog2sql --start-file=mysql-bin.000001 --start-position=6159262 --stop-pos=6159823 > edai_binlog2sql-new.sql

[root@soft binlog2sql]# cat edai_binlog2sql-new.sql

INSERT INTO `xcrm`.`edai_binlog2sql`(`create_time`, `id`, `name`) VALUES ('2015-05-02 00:00:00', 6, 'Jacky Cheung'); #start 6159565 end 6159823 time 2018-11-22 15:16:30UPDATE `xcrm`.`edai_binlog2sql` SET `create_time`='2012-10-01 00:00:00', `id`=4, `name`='Glon Ho' WHERE `create_time`='2017-05-12 00:00:00' AND `id`=4 AND `name`='Glon Ho' LIMIT 1; #start 6159262 end 6159534 time 2018-11-22 15:15:46

可以看到，我们刚刚的delete语句，被反转为insert语句，update 修改为原来的时间

拿到了具体的恢复语句，那我们拿去数据库执行吧