Mysql误操作后利用binlog2sql快速回滚的方法详解

前言

在日常工作或者学习中，操作数据库时候难免会因为“大意”而误操作，需要快速恢复的话通过备份来恢复是不太可能的，下面这篇文章主要给大家介绍关于mysql误操作后利用binlog2sql快速回滚的方法，话不多说，来一起看看详细的介绍：

一、总体解释：

dml（data manipulation language）：

       它们是select、update、insert、delete，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

ddl（data definition language）：

       ddl比dml要多，主要的命令有create、alter、drop等，ddl主要是用在定义或改变表（table）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

dcl（data control language）：

       是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行dcl

二、binlog2sql安装

从mysql binlog解析出你要的sql。根据不同选项，你可以得到原始sql、回滚sql、去除主键的insert sql等。

2.1、用途

• 数据快速回滚(闪回)
• 主从切换后数据不一致的修复
• 从binlog生成标准sql，带来的衍生功能

2.2、安装

# cd /usr/local
# git clone https://github.com/danfengcao/binlog2sql.git
# ls 
binlog2sql games java lib64 mariadb sbin src 
# cd binlog2sql
# pip install -r requirements.txt
-bash: pip: command not found
 -------------安装pip工具-------------
# wget https://bootstrap.pypa.io/get-pip.py 
# python get-pip.py
# pip -v　　#查看pip版本
pip 9.0.1 from /usr/lib/python2.7/site-packages (python 2.7)
# pip install -r requirements.txt
requirement already satisfied: pymysql==0.7.8 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 1))
requirement already satisfied: wheel==0.24.0 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 2))
requirement already satisfied: mysql-replication==0.9 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 3))

2.3、user需要的最小权限集合：

select, super/replication client, replication slave权限建议授权

mysql > grant select,replication slave,replication client on *.* to flashback@'localhost' identified by 'flashback';
mysql > grant select,replication slave,replication client on *.* to flashback@'127.0.0.1' identified by 'flashback';

2.4、基本用法

解析出标准sql

shell> python binlog2sql.py -h127.0.0.1 -p3306 -uadmin -p'admin' -ddatabase -t table1 table2 --start-file='mysql-bin.000002' --start-datetime='2017-01-12 18:00:00' --stop-datetime='2017-01-12 18:30:00' --start-pos=1240

解析出回滚sql

shell> python binlog2sql.py --flashback -h127.0.0.1 -p3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147

三、测试：

3.1、新建表users

create table cope_users like info_users; # 新建表
insert into cope_users select * from info_users limit 500; # 插入500行数据
delete from cope_users where id<20;  # 删除20行数据

3.2、解析标准sql

# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-datetime='2017-07-11 15:10:00' --stop-datetime='2017-07-11 15:12:00'
delete from `ttt`.`users` where `uid`='0e8e2609c748bbb052d7' and `ip`='172.16.208.32' and `sex`=0 and `app_ver`='5.2.3' and `device_type`=2 and `guides`='' and `last_login_time`=1481602129 and `id`=1 and `latitude`='' and `add_time`=1481602080 and `recharge_time`=0 and `token_change_time`=1481602129 and `expire_time`=0 and `nickname`='阿超' and `device_id`='cc0e154d9b5dd703eccc7d8a0dbc0f67d64b79e8' and `push_key`='' and `level`=0 and `mobile`='18810895535' and `settings`='' and `longitude`='' and `signature`='' and `os_ver`='' limit 1; #start 79078 end 83053 time 2017-07-11 15:11:50
delete from `ttt`.`users` where `uid`='b5cfbdb4205b56703a97' and `ip`='172.16.208.48' and `sex`=0 and `app_ver`='5.2.2' and `device_type`=2 and `guides`='' and `last_login_time`=1481602096 and `id`=2 and `latitude`='' and `add_time`=1481602096 and `recharge_time`=0 and `token_change_time`=1481602096 and `expire_time`=0 and `nickname`='家长091410' and `device_id`='fedea666076a7906be53523acc7a8b32811354fe' and `push_key`='7759d6772c9851a2bfc13835a3d7e7da' and `level`=0 and `mobile`='13629470521' and `settings`='' and `longitude`='' and `signature`='' and `os_ver`='' limit 1; #start 79078 end 83053 time 2017-07-11 15:11:50

3.3、解析出回滚sql

# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -h127.0.0.1 -p3306 -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-position=79078 --stop-position=83053

查看解析出的sql，如无误，可打印到sql文件中/data/backup/rollback.sql

# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-position=79078 --stop-position=83053> /data/backup/rollback.sql
# cat /data/backup/rollback.sql 
`id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) values ('24667530f4b16a446b3e', '172.16.218.75', 0, '5.2.93', 3, '{\"2103\":1,\"2100\":1,\"2101\":1,\"2102\":1,\"2104\":1,\"2105\":1}', 1490239125, 19, '', 1481610680, 0, 1490239125, 0, 'zf', 'da75b093-bd22-48f6-bbb1-d3296e29e9b5', 'be05183f80a96e788e0b0a99d1275392', 0, '15101538925', '', '', '', ''); #start 79078 end 83053 time 2017-07-11 15:11:50
insert into `ttt`.`users`(`uid`, `ip`, `sex`, `app_ver`, `device_type`, `guides`, `last_login_time`, `id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) values ('77e50b4910a9389057ed', '172.16.218.37', 0, '5.2.1.14', 3, '', 1488787835, 18, '39.978212', 1481610517, 0, 1488787835, 0, '陈俊宇', 'ed0a273d-74de-4173-92c6-55d92597bc79', '', 0, '18612482272', '', '116.306826', '', ''); #start 79078 end 83053 time 2017-07-11 15:11:50

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、开始回滚

# mysql -uroot -p000000 < /data/backup/rollback.sql

3.5、登陆数据库验证

四、注意事项

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闪回数据。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。