Mysql误操作后使用binlog2sql快速回滚

拼搏现实的明天。 2022-05-20 00:40 198阅读 0赞

Mysql误操作后使用binlog2sql快速回滚  
一、binlog2sql安装  
从mysql binlog解析出你要的sql。根据不同选项，你可以得到原始sql、回滚sql、去除主键的insert sql等。  
1.1、用途  
数据快速回滚(闪回)  
主从切换后数据不一致的修复  
从binlog生成标准SQL，带来的衍生功能  
1.2、安装  
\[root@foidndb ~\]\# cd /usr/local/  
\[root@foidndb local\]\# git clone git://github.com/danfengcao/binlog2sql.git  
  
\[root@foidndb binlog2sql\]\# pip install -r requirements.txt  
  
MySQL的配置要开启以下选项：  
  
\[mysqld\]  
server\_id = 1  
log\_bin = /var/log/mysql/mysql-bin.log  
max\_binlog\_size = 1G  
binlog\_format = row  
binlog\_row\_image = full  
  
  
  
2.3 user需要的最小权限集合  
select, super/replication client, replication slave权限  
建议授权：  
mysql > grant select,replication slave,replication client on \*.\* to flashback@'localhost' identified by 'Shiyu!520';  
mysql > grant select,replication slave,replication client on \*.\* to flashback@'127.0.0.1' identified by 'Shiyu!520';  
  
2.4基本用法  
解析出标准SQL  
  
shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -ddatabase -t table1 table2 --start-file='mysql-bin.000040' --start-datetime='2018-07-09 18:30:00' --stop-datetime='2018-07-09 18:40:00' --start-pos=1240  
  
解析出回滚SQL  
  
shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000040' --start-position=763 --stop-position=1147  
  
三、测试  
3.1 新建表test  
mysql> create table yuhuashi(id int,name varchar(10));  
Query OK, 0 rows affected (0.26 sec)  
  
mysql> insert into yuhuashi values(1,'s');  
Query OK, 1 row affected (0.06 sec)  
  
mysql> insert into yuhuashi values(2,'h');  
Query OK, 1 row affected (0.06 sec)  
  
mysql> insert into yuhuashi values(3,'i');  
Query OK, 1 row affected (0.05 sec)  
  
mysql> insert into yuhuashi values(4,'y');  
Query OK, 1 row affected (0.07 sec)  
  
mysql> insert into yuhuashi values(5,'u');  
Query OK, 1 row affected (0.06 sec)  
  
mysql> select \* from yuhuashi;  
\+------+------+  
| id   | name |  
\+------+------+  
|    1 | s    |  
|    2 | h    |  
|    3 | i    |  
|    4 | y    |  
|    5 | u    |  
\+------+------+  
5 rows in set (0.00 sec)  
  
停个5分钟以后执行一下命令：  
  
mysql> delete from yuhuashi where id>=3;  
Query OK, 3 rows affected (0.05 sec)  
  
  
3.2 解析标准sql  
\[root@foidndb ~\]\# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashback -pShiyu'!'520 -dmonitor -tyuhuashi --start-file='mysql-bin.000040' --start-datetime='2018-07-09 17:16:00' --stop-datetime='2018-07-09 17:19:00' > yuhuashi.sql  
\[root@foidndb ~\]\# cat yuhuashi.sql  
DELETE FROM \`monitor\`.\`yuhuashi\` WHERE \`id\`=3 AND \`name\`='i' LIMIT 1; \#start 25735066 end 25735319 time 2018-07-09 17:18:14  
DELETE FROM \`monitor\`.\`yuhuashi\` WHERE \`id\`=4 AND \`name\`='y' LIMIT 1; \#start 25735066 end 25735319 time 2018-07-09 17:18:14  
DELETE FROM \`monitor\`.\`yuhuashi\` WHERE \`id\`=5 AND \`name\`='u' LIMIT 1; \#start 25735066 end 25735319 time 2018-07-09 17:18:14  
  
3.3 解析出回滚SQL  
\[root@foidndb ~\]\# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashback -pShiyu'!'520 -dmonitor -tyuhuashi --start-file='mysql-bin.000040' --start-datetime='2018-07-09 17:16:00' --stop-datetime='2018-07-09 17:19:00' -B >rollback.sql  
\[root@foidndb ~\]\# cat rollback.sql  
INSERT INTO \`monitor\`.\`yuhuashi\`(\`id\`, \`name\`) VALUES (5, 'u'); \#start 25735066 end 25735319 time 2018-07-09 17:18:14  
INSERT INTO \`monitor\`.\`yuhuashi\`(\`id\`, \`name\`) VALUES (4, 'y'); \#start 25735066 end 25735319 time 2018-07-09 17:18:14  
INSERT INTO \`monitor\`.\`yuhuashi\`(\`id\`, \`name\`) VALUES (3, 'i'); \#start 25735066 end 25735319 time 2018-07-09 17:18:14  
  
mysql连接配置  
   -h host; -P port; -u user; -p password  
解析模式  
  --realtime 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。  
  --popPk 对INSERT语句去除主键。可选。  
  -B, --flashback 生成回滚语句。可选。与realtime或popPk不能同时添加。  
解析范围控制  
  --start-file 起始解析文件。必须。  
  --start-pos start-file的起始解析位置。可选。默认为start-file的起始位置；  
  --end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为realtime，此选项失效。  
  --end-pos end-file的末尾解析位置。可选。默认为end-file的最末位置；若解析模式为realtime，此选项失效。  
对象过滤  
   -d, --databases 只输出目标db的sql。可选。默认为空。  
   -t, --tables 只输出目标tables的sql。可选。默认为空。  
     
     
3.4、开始回滚  
\[root@foidndb ~\]\# mysql -u root -p < rollback.sql  
Enter password:  
3.5登录数据库验证  
 mysql> select \* from yuhuashi;  
\+------+------+  
| id   | name |  
\+------+------+  
|    1 | s    |  
|    2 | h    |  
|    5 | u    |  
|    4 | y    |  
|    3 | i    |  
\+------+------+  
5 rows in set (0.00 sec)  
四 、注意事项  
  
4.1、在配置文件中设置了以下参数：  
server\_id = 1  
log\_bin = /data/mysql/mysql-bin.log  
max\_binlog\_size = 1G  
binlog\_format = row  
binlog\_row\_image = full \# 默认  
  
4.2、在闪回的时候必须启动 MySQL 服务  
  
因为它是通过 BINLOG\_DUMP 协议来获取 binlog 内容，需要读取server端 information\_schema.COLUMNS 表，来获取表结构的元信息，才能拼接成 SQL 语句。因此需要给用户提供的最小权限如下：  
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON \*.\* TO 'user'@'%';  
源码中，主要是使用 python-mysql-replication 作为实时解析 MySQL binlog 来获取各个 EVENT。  
python-mysql-replication 实现了 MySQL 复制协议，客户端伪装成 slave 来获取主的 binlog 和 EVENT。  
  
4.3、insert、update、delete大部分时候可以解析出来标准sql和回滚sql  
  
一种情况例外:insert、updete、delete操作之后，drop/truncate table。 此时虽然在binlog中记录了所有的event，但是使用binlog2sql生成标准sql、回滚sql的时候已经找不到了dml操作的相应的表  
  
4.4、DDL无法使用binlog2sql闪回数据。