MySQL利用binlog恢复误操作数据
来源:互联网 发布:数控车削编程实例 编辑:程序博客网 时间:2024/05/18 02:55
原文:http://www.cnblogs.com/prayer21/p/6018736.html
在人工手动进行一些数据库写操作的时候(比方说数据订正),尤其是一些不可控的批量更新或删除,通常都建议备份后操作。不过不怕万一,就怕一万,有备无患总是好的。在线上或者测试环境误操作导致数据被删除或者更新后,想要恢复,一般有两种方法。
方法一、利用最近的全量备份+增量binlog备份,恢复到误操作之前的状态,但是随着数据量的增大,binlog的增多,恢复起来很费时。
方法二、如果binlog的格式为row,那么就可以将binlog解析出来生成反向的原始SQL
以下是利用方法二写的一个python脚本binlog_rollback.py,可利用此脚本生成反向的原始SQL。
说明:
0、前提是binlog的格式为row
1、要恢复的表操作前后表结构没有发生变更,否则脚本无法解析
2、只生成DML(insert/update/delete)的rollback语句
3、最终生成的SQL是逆序的,所以最新的DML会生成在输入文件的最前面,并且带上了时间戳和偏移点,方便查找目标
4、需要提供一个连接MySQL的只读用户,主要是为了获取表结构
5、如果binlog过大,建议带上时间范围,也可以指定只恢复某个库的SQL
6、SQL生成后,请务必在测试环境上测试恢复后再应用到线上
脚本代码
View Code
演示
#首先创建一个只读账号root:test> grant select on *.* to 'query'@'127.0.0.1' identified by '123456';Query OK, 0 rows affected, 1 warning (0.01 sec)#测试表结构如下root:test> CREATE TABLE `table1` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `c1` int(11) DEFAULT NULL, -> `c2` varchar(20) DEFAULT NULL, -> `c3` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`) -> );Query OK, 0 rows affected (0.09 sec)#插入三条数据root:test> insert into table1(c1,c2,c3) values (1,'a',1),(2,'b',2),(3,'c',3);Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0root:test> select * from table1;+----+------+------+------+| id | c1 | c2 | c3 |+----+------+------+------+| 1 | 1 | a | 1 || 2 | 2 | b | 2 || 3 | 3 | c | 3 |+----+------+------+------+3 rows in set (0.00 sec)#更新一条数据root:test> update table1 set c3=10 where id=3;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0root:test> select * from table1;+----+------+------+------+| id | c1 | c2 | c3 |+----+------+------+------+| 1 | 1 | a | 1 || 2 | 2 | b | 2 || 3 | 3 | c | 10 |+----+------+------+------+3 rows in set (0.00 sec)#删除一条数据root:test> delete from table1 where id=1;Query OK, 1 row affected (0.01 sec)root:test> select * from table1;+----+------+------+------+| id | c1 | c2 | c3 |+----+------+------+------+| 2 | 2 | b | 2 || 3 | 3 | c | 10 |+----+------+------+------+2 rows in set (0.00 sec)
接下来利用脚本来生成反向SQL
[root@diandi ~]# python binlog_rollback.py -f /log/mysql/bin/mysql-bin.000002 -o rollback.sql -u query -p 123456 --start-datetime='2016-10-28 00:00:00' -d test正在获取参数.....正在解析binlog.....正在初始化列名.....正在开始拼凑sql.....done!
查看反向SQL,最新的DML会生成在输入文件的最前面
[root@diandi ~]# cat rollback.sql ## at 155848##161028 17:07:10 server id 22100 end_log_pos 155898 CRC32 0x5000bca7 Delete_rows: table id 351 flags: STMT_END_FINSERT INTO `test`.`table1`SET id=1 ,c1=1 ,c2='a' ,c3=1;## at 155560##161028 17:04:56 server id 22100 end_log_pos 155626 CRC32 0x11d91e2d Update_rows: table id 351 flags: STMT_END_FUPDATE `test`.`table1`SET id=3 ,c1=3 ,c2='c' ,c3=3WHERE id=3 AND c1=3 AND c2='c' AND c3=10;## at 155258##161028 16:59:31 server id 22100 end_log_pos 155338 CRC32 0x3978c1c1 Write_rows: table id 351 flags: STMT_END_FDELETE FROM `test`.`table1`WHERE id=3 AND c1=3 AND c2='c' AND c3=3;DELETE FROM `test`.`table1`WHERE id=2 AND c1=2 AND c2='b' AND c3=2;DELETE FROM `test`.`table1`WHERE id=1 AND c1=1 AND c2='a' AND c3=1;
执行回滚操作
#直接source整个文件,table1将恢复到原来的空表状态(实际情况,在测试环境上按需索取,然后再恢复线上)root:test> source /root/rollback.sqlQuery OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.01 sec)root:test> select * from table1;Empty set (0.00 sec)
具体的参数使用方法如下:
[root@diandi ~]# python binlog_rollback.py ==========================================================================================Command line options : --help # OUT : print help info -f, --binlog # IN : binlog file. (required) -o, --outfile # OUT : output rollback sql file. (default 'rollback.sql') -h, --host # IN : host. (default '127.0.0.1') -u, --user # IN : user. (required) -p, --password # IN : password. (required) -P, --port # IN : port. (default 3306) --start-datetime # IN : start datetime. (default '1970-01-01 00:00:00') --stop-datetime # IN : stop datetime. default '2070-01-01 00:00:00' --start-position # IN : start position. (default '4') --stop-position # IN : stop position. (default '18446744073709551615') -d, --database # IN : List entries for just this database (No default value). --only-primary # IN : Only list primary key in where condition (default 0)Sample : shell> python binlog_rollback.py -f 'mysql-bin.000001' -o '/tmp/rollback.sql' -h 192.168.0.1 -u 'user' -p 'pwd' -P 3307 -d dbname
0 0
- MySQL利用binlog恢复误操作数据
- Mysql之binlog日志说明及利用binlog日志恢复数据操作记录
- Mysql之binlog日志说明及利用binlog日志恢复数据操作记录
- 利用mysql的binlog恢复数据
- 利用mysql的binlog恢复数据
- 利用mysql的binlog恢复数据
- 利用mysql的binlog恢复数据
- 利用mysql的binlog恢复数据
- MYSQL数据BINLOG恢复
- mysql binlog恢复数据
- mysql 查看binlog 操作日志及恢复数据
- MySQL--binlog日志恢复数据
- mysql通过binlog恢复数据
- 使用 mysql binlog 恢复数据
- mysql通过Binlog恢复数据
- mysql通过binlog恢复数据
- MySQL binlog日志恢复数据
- mysql通过binlog恢复数据
- 机器学习01
- poj-1125-Stockbroker Grapevine
- jzoj P2129【2017.4.21普及】礼物
- android6.0(API23)以上动态申请权限
- 为什么每个人都该学点计算机
- MySQL利用binlog恢复误操作数据
- Python学习 Mac下编辑和运行程序
- Tomcat之Directory Listing
- 商人的诀窍
- Quagga Routing Suite
- 自编码器原理以及相关算法 Basic Auto-Encoder,Regularized Auto-Encoder,Denoising Auto-Encoder
- 【架构】Top 10 Tips on Logging in Java
- 字符流与字节流练习
- Django 中的render