前言

  在新近的工作中,由于自身粗(zuo)心(si)误update操作导致几百行的数据现身错误,在发急的还要(那时作者竟然不理解除了备份之后还会有binlog日志恢复生机)立马查资料学习binlog的回复,随后立即展开了苏醒。即便能够装作本身没出错(emmmmm……最终依然得肯定的!),但下班现在心理无法长时间平复,立马展开Computer举行一次执行记录技能对得起本人犯的失实。

  注:此次尝试是在Wnidows下进展的(网络Linux挺多,不过Windows的啥少,加上本身小编的计算机也是Win7就差不离做壹次实行吧!

 


 

binlog2sql贯彻MySQL误操作的回复,binlog2sqlmysql

 

对于MySQL数据库中的误操作删除数据的东山复起难题,能够使用基于MySQL中binlog做到相像于闪回只怕变化反向操作的SQL语句来兑现,是MySQL中一个不胜实用的效用。
规律轻松通晓,基于MySQL的row格式的binlog中,记录历史的增加和删除改SQL消息,基于此分析出来对应的SQL语句(回滚的话正是反向的SQL语句)。
在格式为binlog格式为row的日记格局下,binlog中的内容记录了数据库中曾经推行的增加和删除改音信,都以包蕴了反向音信的
举个例子实施delete from table where pk_id = 1;遵照主键来删除一条记下
对应的binlog中的sql语句为:delete from table where pk_id = 1 and name =
‘xxx’ and other_column =
‘xxx’;where条件不止是原始语句的Id,何况还包括中这黄金年代行全数的字段的音信的
update操作也同理,不但记录了update操作,同有的时候间记录了update记录在创新从前的每三个字段的值。那样就能够使用这几个日志来变化反向操作消息。

 

正如是应用mysqlbinlog
工具剖判出来的三个MySQL中名列第一名的binlog日志文件的生机勃勃对内容,能够领略地看看进行过的sql语句的新闻。
聊起这里,对于MySQL中基于binlog的意气风发部分运用,例如复制恐怕数据库还原,其实正是再一次推行有些数据库上的野史实行过的增加和删除改SQL语句来落到实处的。
题外话:MySQL的binlog作用记录事务语句的效果与利益上,基本上等同于SQLServer的的事务日志。
不过SQL
Server的工作日志正的二进制内容的,微软官方也未曾提供分析的办法,而MySQL中全然能够由此mysqlbinlog
来分析出来这么些日志中的内容。

如下是通过MySQL自带的mysqlbinlog工具剖析出来的binlog日志文件中的音信,能够看见里面包车型大巴SQL语句音讯。

图片 1

 

明亮了binlog中的内容,就能够依据这么些binlog来兑现种种实用的机能,规范的正是误删数据的恢复生机操作,举例苏家小萝卜同学就融洽用Python解决那一个深入分析成效
就好像作用相比知名的还也可能有大众点评网DBA自个儿写的binlog2sql工具,也是闻名遐尔,终于有空子品尝了。
binlog2sql索要语句pip安装,所以要求先安装pip
pip 安装参照他事他说加以考查:

图片 2

图片 3

binlog2sql下载以致安装:

图片 4

姣好了binlog2sql过后,就足以运用它来贯彻多少的上升操作了,如下模拟二个误操作的过来

在开启了binlog,日志格式为row的测量检验数据下,对于测量试验表test_01,分别推行以下sql语句:

insert into test_01 values (1,'aaa');
insert into test_01 values (2,'bbb');
insert into test_01 values (3,'vvv');
--以下误操作,更新了全部数据
update test_01 set name = 'xxx';

因而show master
logs;找到当前的binlog文件,对应的sql语句的实践就存款和储蓄在近些日子以此binlog中,binlog2sql的靶子就是以此文件

图片 5

参照下图,能够开掘
执行:python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p’root’ -ddb01 -t
test_01 –start-file=’binlog.000021′
(更加的多参数以至选择方法参照他事他说加以调查下文链接),通过binlog2sql来解析当前的binlog文件,
剖析出来的SQL语句正是正规SQL语句的推行(insert insert insert
update(3行记录))
执行:python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p’root’ -ddb01 -t
test_01 –start-file=’binlog.000021′ -B,通过-B参数生成反向的操作消息
加参数-B深入分析出来的SQL语句与地点的SQL语句刚好相反,包涵各个,也即以倒序的点子调换反向的操作
原有操作是insert insert insert update update
update,反向的操作正是upfate update update delete delete delete,
那样一来,能够依照现实的景况,截取生成的反向的sql语句,进行误操作的恢复生机。

以上操作注意安装的binlog2sql的门道难点,若是路线不对,找不到binlog2sql.py,上述命令也就不能履行

图片 6

更加多binlog2sql参数以至用法和限量参照他事他说加以调查官方GitHub:

 

对于MySQL数据库中的误操作删除数据的过来难题,能够动用基于MySQL中binlog做到相仿于闪回大概…

在人工手动进行一些数据库写操作的时候(举例说数据校订),极度是局地不可控的批量更新或删除,经常都提议备份后操作。不过不怕万生龙活虎,就怕少年老成万,有备无患粮草先行总是好的。在线上可能测量试验碰着误操作导致数据被删除恐怕更新后,想要恢复生机,日常常有二种办法。
方法大器晚成、利用近些日子的全量备份+增量binlog备份,复苏到误操作此前的状态,不过随着数据量的附加,binlog的增添,恢复生机起来很棘手。
办法二、假使binlog的格式为row,那么就能够将binlog深入分析出来生成反向的原始SQL

MySQL binlog日志复苏数据,mysqlbinlog

咱俩了然了MySQL 的 binlog 日志的打开药格局以至 binlog
日志的部分准绳和常用操作,大家驾驭,binlog 有两大职能,四个是接纳 binlog
复苏数据,另二个就是用来做主从复制。本篇笔记正是来记录怎样行使 binlog
日志来做数据恢复生机。当然了,使用 binlog
日志所复苏的数量只好是部分数量,并不可以预知接收 binlog
日志来做数据库的备份,假若想要做数据库备份,仍然要选拔我们古板的备份方法,而
binlog 能够作为增量备份。

 

录制链接:

 

在典型启幕以前,先来讲一说 mysql
完整备份数据库,以至复苏数据库的办法备份数据库:

 

率先大家来创设三个数据库,mytest

create database mytest;

随着我们来创设一张表

use mytest;
create table t1(id int ,name varchar(20));

下一场大家插入两条数据

insert into t1 values (1,'xiaoming');
insert into t1 values (2,'xiaohong');

上边大家对 mytest 数据库实行备份,备份到/root/bakup/

mysqldump -uroot -p -B -F -R -x --master-data=2 mytest | gzip > /root/backup/bak_$(date +%F).sql.gz

图片 7

 

参数说明:

 

-B:内定数据库

 

-F:刷新日志

 

-逍客:备份存款和储蓄进度等

 

-x:锁表

 

–master-data:在备份语句里加多 CHANGE MASTESportage 语句以致 binlog
文件及任务点音信查阅备份文件

图片 8

 

如此这般呢,大家就把数量做了贰个完整的备份。上边来删除数据库,然后经过备份数据开展回复数据库。

gzip -d bakup_xxx.gz
mysql -uroot -p < bakup_xxx.sql

如此大家就把数据导入到Curry了。

 

持续上面包车型大巴操做,大家新扩充 xiaoli 和 xiaozhao 这两条数据,并把 xiaozhao
那条记下删除掉。

 

在剔除从前,大家先来刷新 binlog
日志,生成二个新的日记,那么大家以往所要操做的源委都会被记录到新的日记文件中。(通过前面binlog 日志的详细表明大家明白,每一遍刷新和劳务重启的时候,都会生成三个binlog 日志文件。)

flush logs;
show master status;

图片 9

 

小编们注意,binlog 的文本是 0009,地方是在
154,那七个音讯很要紧上边我们来做插入和删除操作

图片 10

 

其有的时候候我们理应是来查阅一下 binlog
日志的境况,以便与大家一会来张开回复到此景况,不过,真正的景况中我们并不知道那些状态,由此这里也就不去查看那些情景了,这一个情景的值能够由在此以前边查看
binlog 日志文件来打开解析。下边大家先河误操作:

 

大家来把 xiaozhao 删除掉

图片 11

那样数据就删除掉了,上面大家再来查看 binlog 的情形

show master status;

图片 12

 

本条时候我们开采小编删除操作是个错误的操作,要进行还原,那么该怎么样回复呢?那一年大家就能够透过
binlog 的 position 来打开回复。

 

在进展此外的管理在此之前,大家提议,立刻再进行三回 flush
logs,也正是让出错的有些就聚集在此样二个 binlog 日志文件中。

 

我们来查阅 0009 的 binlog 日志。

图片 13

 

大家看来 delete_rows 甘休点是 928 那些点,发轫点是在 755
这么些点,我们得以把操作的这几个数量删除到大家上次备份的剧情,然后经过施行binlog 来张开回复,当然苏醒到 755 那一个点从前。

 

比如说笔者上次备份的是整套数据库,小编就可以把全体数据库删除,然后经过备份文件苏醒,然后再经过
binlog
做增量恢复生机。那样数据就回去了。这里就不再进行删库了,大家一向演示使用
binlog 日志复苏数据的主意

 

语法如下

mysqlbinlog mysql-bin.0000xx | mysql -u 用户名 -p 密码 数据库名

 

诸如我们要把具有的操作都复苏(不饱含大家的删减,我们了然删除是在 755
点上):

mysqlbinlog mysql-bin.000009 --start-position 154 --stop-position 755 | mysql - uroot -p mytest

 

图片 14

 

再来查看表

图片 15

 

大家开采 xiaozhao 又赶回了,当然了,这里多了二个xiali,是因为本人事先并从未删除备份前的数量。当然了,我们在平复的进程中得以选拔只回复
xiaozhao 这么一块内容上面是 binlog 日志恢复生机中有个别常用的参数

 

–start-datetime:从二进制日志中读取钦命等于时间戳只怕晚于本地计算机的年华

 

–stop-datetime:从二进制日志中读取内定小于时间戳大概等于本地Computer的时间
取值

 

和上述雷同

 

–start-position:从二进制日志中读取钦点 position
事件地点作为早前。–stop-position:从二进制日志中读取钦赐 position
事件地点作为事件截止

binlog日志苏醒数据,mysqlbinlog
大家了然了MySQL 的 binlog 日志的拉开药格局以至 binlog
日志的部分法规和常用操作,大家领略,binlog 有两大…

1、初识binlog

(1)MySQL的binlog便是我们日常所说的Binary
Log,即bin-log,是MySQL存款和储蓄对数据库改变的二进制文件,也正是记录了具有DDL与DML(select除了那些之外)语句,利用它最主要能够做两件事:

  • 数据苏醒:通过mysqlbinog工具举办复原;
  • 多少复制:MySQL
    Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来完毕master-slave数据生龙活虎致的目标。

(2)什么样查看MySQL的日记情状(是还是不是张开等),当MySQL尚未展开时候,通过命令:show
variables like ‘log_bin%’查看

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set

(3)如何在Wnidows在修改log_bin状态为ON开启呢?

  • 找到C:\ProgramData\MySQL\MySQL Server
    5.7\my.ini文件(介意:是C盘下掩瞒文件夹ProgramData,并不是Program
    Files下
    )  

  图片 16

  • 日增/订平常用的陈设属性

  图片 17

(4)常用轻松属性表达:除了上述log_bin、binlog_format三个轻巧的布署外,还足以有任何的品质配置

  • log-bin = /xxx/xxx/mysql_bin #binlog日志文件,以mysql_bin开头,六个数字结尾的文件:mysql_bin.000001,并且会将文件存储在相应的xxx/xxx路径下,如果只配置mysql_bin的话默认在C:\ProgramData\MySQL\MySQL Server 5.7\Data下;``
  • binlog_format = ROW #binlog日志格式,默认为STATEMENT:每一条SQL语句都会被记录;ROW:仅记录哪条数据被修改并且修改成什么样子,是binlog开启并且能恢复数据的关键;
  • expire_logs_days= 7 #binlog过期清理时间;
  • ``max_binlog_size = 100m #binlog每个日志文件大小;
  • binlog_cache_size = 4m #binlog缓存大小;
  • max_binlog_cache_size = 512m #最大binlog缓存大小。

以下是采用方式二写的一个python脚本binlog_rollback.py,可采纳此脚本生成反向的原始SQL。

2、复苏数据测验

(1)准备表user

mysql> select * from user;
+----+----------+----------------------------------+
| id | name     | password                         |
+----+----------+----------------------------------+
|  1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
|  2 | Lisi     | 2d73641c8e5111e8af74201a060059ce |
|  3 | Wangwu   | 2d73670c8e5111e8af74201a060059ce |
+----+----------+----------------------------------+
3 rows in set

 (2)误update恢复生机,比方本人在update user set name = ‘Lijian’ where id =
1;的时候忘写where id = 1首要原则,结果形成整个数码被更新

mysql> update user set name ='Lijian';
Query OK, 3 rows affected
Rows matched: 3  Changed: 3  Warnings: 0
mysql> select*from user;
+----+--------+----------------------------------+
| id | name   | password                         |
+----+--------+----------------------------------+
|  1 | Lijian | 2d7284808e5111e8af74201a060059ce |
|  2 | Lijian | 2d73641c8e5111e8af74201a060059ce |
|  3 | Lijian | 2d73670c8e5111e8af74201a060059ce |
+----+--------+----------------------------------+
3 rows in set

  那个时候你早晚很慌,不过先不要慌(实际上慌也没用),先看未有备份,若无再看是不是开启binlog(show
variables like
‘log_bin%’),假设双方都不曾(小编信赖我们都会定期备份+binlog)从数据库那么些范围是无法恢复生机的了,要是binlog开启的话,一切都好说。就从头推行上面几步恢复吧!

  先是步:找到当前mysql记录的binlog文件,施行show
master status;

  第二步:查看binlog,定位误操作的pos只怕时间段。施行show
binlog events in ‘mysql_bin.000001’;

mysql> show binlog events in 'mysql_bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.12-log, Binlog ver: 4 |
| mysql_bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 219 | Query          |         1 |         291 | BEGIN                                 |
| mysql_bin.000001 | 291 | Table_map      |         1 |         344 | table_id: 108 (test.user)             |
| mysql_bin.000001 | 344 | Update_rows    |         1 |         650 | table_id: 108 flags: STMT_END_F       |
| mysql_bin.000001 | 650 | Xid            |         1 |         681 | COMMIT /* xid=22 */                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set

  第三步:进入C:\ProgramData\MySQL\MySQL Server 5.7\Data执行mysqlbinlog --start-position=219 --stop-position=681 mysql-bin.000001 > e:\\update.sql将update部分单独备份出来到E盘下为update.sql  

 图片 18 

  第四步:登录mysql(mysql -uroot
-p123)

  第五步:试行source
e:update.sql复苏数据
,部分截图如下:

  图片 19

  **第六步:查看结果**

mysql> select * from user;
+----+----------+----------------------------------+
| id | name     | password                         |
+----+----------+----------------------------------+
|  1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
|  2 | Lisi     | 2d73641c8e5111e8af74201a060059ce |
|  3 | Wangwu   | 2d73670c8e5111e8af74201a060059ce |
+----+----------+----------------------------------+
3 rows in set

说明:

0、前提是binlog的格式为row
1、要过来的表操作前后表结构未有生出转移,不然脚本不能够深入分析
2、只生成DML(insert/update/delete)的rollback语句
3、最后生成的SQL是逆序的,所以新型的DML会转移在输入文件的最前边,何况带上了光阴戳和偏移点,方便寻觅指标
4、要求提供一个连连MySQL的只读客户,首假若为着获取表结构
5、假诺binlog过大,建议带上时间节制,也足以钦点只回复有些库的SQL
6、SQL生成后,请必得在测量试验情状上测验恢复生机后再使用到线上

3、总结

  (1)动用binlog只可以针对针对数据量不是贪滥无厌的情事,真正的生产条件每一个几个G的日志文件,不唯有是光靠binlog恢复生机的,还应该有越来越多的措施,在这里只是做三个轻便易行的求学记录!

  (2)剖断时间binlog日志的时刻阶段与pos地点比较重点,可是须求明白关于binlog的相当多参数!

  (3)以前日真的感觉开采人士就不需求太理解数据库相关的运转,但是前几日经历过才精通数据库的连带文化也是开采人士必需驾驭的!

 

演示

#首先创建一个只读账号
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: 0

root: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: 0

root: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_F
INSERT 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_F
UPDATE `test`.`table1`
SET
  id=3
  ,c1=3
  ,c2='c'
  ,c3=3
WHERE
  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_F
DELETE 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.sql
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query 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

剧本代码

#!/bin/env python
# -*- coding:utf-8 -*-

import os,sys,re,getopt
import MySQLdb


host = '127.0.0.1'
user = ''
password = ''
port = 3306
start_datetime = '1971-01-01 00:00:00'
stop_datetime = '2037-01-01 00:00:00'
start_position = '4'
stop_position = '18446744073709551615'
database = ''
mysqlbinlog_bin = 'mysqlbinlog -v'
binlog = ''
fileContent = ''
output='rollback.sql'
only_primary = 0


# ----------------------------------------------------------------------------------------
# 功能:获取参数,生成相应的binlog解析文件
# ----------------------------------------------------------------------------------------
def getopts_parse_binlog():
    global host
    global user
    global password
    global port
    global fileContent
    global output
    global binlog
    global start_datetime
    global stop_datetime
    global start_position
    global stop_position
    global database
    global only_primary
    try:
        options, args = getopt.getopt(sys.argv[1:], "f:o:h:u:p:P:d:", ["help","binlog=","output=","host=","user=","password=","port=","start-datetime=", \
                                                                      "stop-datetime=","start-position=","stop-position=","database=","only-primary="])
    except getopt.GetoptError:
        print "参数输入有误!!!!!"
        options = []
    if options == [] or options[0][0] in ("--help"):
        usage()
        sys.exit()
    print "正在获取参数....."
    for name, value in options:
        if name == "-f" or name == "--binlog":
            binlog = value
        if name == "-o" or name == "--output":
            output = value
        if name == "-h" or name == "--host":
            host = value
        if name == "-u" or name == "--user":
            user = value
        if name == "-p" or name == "--password":
            password = value
        if name == "-P" or name == "--port":
            port = value
        if name == "--start-datetime":
            start_datetime = value
        if name == "--stop-datetime":
            stop_datetime = value
        if name == "--start-position":
            start_position = value
        if name == "--stop-position":
            stop_position = value
        if name == "-d" or name == "--database":
            database = value
        if name == "--only-primary" :
            only_primary = value

    if binlog == '' :
        print "错误:请指定binlog文件名!"
        usage()
    if user == '' :
        print "错误:请指定用户名!"
        usage()
    if password == '' :
        print "错误:请指定密码!"
        usage()
    if database <> '' :
       condition_database = "--database=" + "'" + database + "'"
    else:
        condition_database = ''
    print "正在解析binlog....."
    fileContent=os.popen("%s %s  --base64-output=DECODE-ROWS --start-datetime='%s' --stop-datetime='%s' --start-position='%s' --stop-position='%s' %s\
                   |grep '###' -B 2|sed -e 's/### //g' -e 's/^INSERT/##INSERT/g' -e 's/^UPDATE/##UPDATE/g' -e 's/^DELETE/##DELETE/g' " \
                   %(mysqlbinlog_bin,binlog,start_datetime,stop_datetime,start_position,stop_position,condition_database)).read()
    #print fileContent



# ----------------------------------------------------------------------------------------
# 功能:初始化binlog里的所有表名和列名,用全局字典result_dict来储存每个表有哪些列
# ----------------------------------------------------------------------------------------
def init_col_name():
    global result_dict
    global pri_dict
    global fileContent
    result_dict = {}
    pri_dict = {}
    table_list = re.findall('`.*`\\.`.*`',fileContent)
    table_list = list(set(table_list))
    #table_list 为所有在这段binlog里出现过的表
    print "正在初始化列名....."
    for table in table_list:
        sname = table.split('.')[0].replace('`','')
        tname = table.split('.')[1].replace('`','')
        #连接数据库获取列和列id
        try:
            conn = MySQLdb.connect(host=host,user=user,passwd=password,port=int(port))
            cursor = conn.cursor()
            cursor.execute("select ordinal_position,column_name \
                                                       from information_schema.columns \
                                                       where table_schema='%s' and table_name='%s' " %(sname,tname))

            result=cursor.fetchall()
            if result == () :
                print 'Warning:'+sname+'.'+tname+'已删除'
                #sys.exit()
            result_dict[sname+'.'+tname]=result
            cursor.execute("select ordinal_position,column_name   \
                               from information_schema.columns \
                               where table_schema='%s' and table_name='%s' and column_key='PRI' " %(sname,tname))
            pri=cursor.fetchall()
            #print pri
            pri_dict[sname+'.'+tname]=pri
            cursor.close()
            conn.close()
        except MySQLdb.Error, e:
            try:
                print "Error %d:%s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error:%s" % str(e)

            sys.exit()
    #print result_dict
    #print pri_dict

# ----------------------------------------------------------------------------------------
# 功能:拼凑回滚sql,逆序
# ----------------------------------------------------------------------------------------
def gen_rollback_sql():
    global only_primary
    fileOutput = open(output, 'w')
    #先将文件根据'--'分块,每块代表一个sql
    area_list=fileContent.split('--\n')
    #逆序读取分块
    print "正在开始拼凑sql....."
    for area in area_list[::-1]:
        #由于一条sql可能影响多行,每个sql又可以分成多个逐条执行的sql
        sql_list = area.split('##')
        #先将pos点和timestamp传入输出文件中
        for sql_head in sql_list[0].splitlines():
            sql_head = '#'+sql_head+'\n'
            fileOutput.write(sql_head)
        #逐条sql进行替换更新,逆序
        for sql in sql_list[::-1][0:-1]:
            try:
                if sql.split()[0] == 'INSERT':
                    rollback_sql = re.sub('^INSERT INTO', 'DELETE FROM', sql, 1)
                    rollback_sql = re.sub('SET\n', 'WHERE\n', rollback_sql, 1)
                    tablename_pos = 2
                    table_name = rollback_sql.split()[tablename_pos].replace('`', '')
                    # 获取该sql中的所有列
                    col_list = sorted(list(set(re.findall('@\d+', rollback_sql))))
                    # 因为第一个列前面没有逗号或者and,所以单独替换
                    rollback_sql = rollback_sql.replace('@1=', result_dict[table_name][0][1]+'=')
                    for col in col_list[1:]:
                        i = int(col[1:]) - 1
                        rollback_sql = rollback_sql.replace(col+'=', 'AND ' + result_dict[table_name][i][1]+'=',1)
                    # 如果only_primary开启且存在主键,where条件里就只列出主键字段
                    if int(only_primary) == 1 and pri_dict[table_name] <> ():
                        sub_where = ''
                        for primary in pri_dict[table_name]:
                            primary_name = primary[1]
                            for condition in rollback_sql.split('WHERE', 1)[1].splitlines():
                                if re.compile('^\s*'+primary_name).match(condition) or re.compile('^\s*AND\s*'+primary_name).match(condition):
                                    sub_where = sub_where + condition + '\n'
                        sub_where = re.sub('^\s*AND', '', sub_where, 1)
                        rollback_sql = rollback_sql.split('WHERE', 1)[0] + 'WHERE\n' + sub_where
                if sql.split()[0] == 'UPDATE':
                    rollback_sql = re.sub('SET\n', '#SET#\n', sql, 1)
                    rollback_sql = re.sub('WHERE\n', 'SET\n', rollback_sql, 1)
                    rollback_sql = re.sub('#SET#\n', 'WHERE\n', rollback_sql, 1)
                    tablename_pos = 1
                    table_name = rollback_sql.split()[tablename_pos].replace('`', '')
                    # 获取该sql中的所有列
                    col_list = sorted(list(set(re.findall('@\d+', rollback_sql))))
                    # 因为第一个列前面没有逗号或者and,所以单独替换
                    rollback_sql = rollback_sql.replace('@1=', result_dict[table_name][0][1] + '=')
                    for col in col_list[1:]:
                        i = int(col[1:]) - 1
                        rollback_sql = rollback_sql.replace(col+'=', ',' + result_dict[table_name][i][1]+'=', 1).replace(col+'=','AND ' +result_dict[table_name][i][1]+'=')
                    # 如果only_primary开启且存在主键,where条件里就只列出主键字段
                    if int(only_primary) == 1 and pri_dict[table_name] <> ():
                        sub_where = ''
                        for primary in pri_dict[table_name]:
                            primary_name = primary[1]
                            for condition in rollback_sql.split('WHERE', 1)[1].splitlines():
                                if re.compile('^\s*' + primary_name).match(condition) or re.compile('^\s*AND\s*'+primary_name).match(condition):
                                    sub_where = sub_where + condition + '\n'
                        sub_where = re.sub('^\s*AND', '', sub_where, 1)
                        rollback_sql = rollback_sql.split('WHERE', 1)[0] + 'WHERE\n' + sub_where

                if sql.split()[0] == 'DELETE':
                    rollback_sql = re.sub('^DELETE FROM', 'INSERT INTO', sql, 1)
                    rollback_sql = re.sub('WHERE\n', 'SET\n', rollback_sql, 1)
                    tablename_pos = 2
                    table_name = rollback_sql.split()[tablename_pos].replace('`', '')
                    # 获取该sql中的所有列
                    col_list = sorted(list(set(re.findall('@\d+', rollback_sql))))
                    # 因为第一个列前面没有逗号或者and,所以单独替换
                    rollback_sql = rollback_sql.replace('@1=', result_dict[table_name][0][1] + '=')
                    for col in col_list[1:]:
                        i = int(col[1:]) - 1
                        rollback_sql = rollback_sql.replace(col+'=', ',' + result_dict[table_name][i][1]+'=',1)

                rollback_sql = re.sub('\n$',';\n',rollback_sql)
                #print rollback_sql
                fileOutput.write(rollback_sql)
            except IndexError,e:
                print "Error:%s" % str(e)
                sys.exit()
    print "done!"

def usage():
    help_info="""==========================================================================================
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
=========================================================================================="""

    print help_info
    sys.exit()



if __name__ == '__main__':
    getopts_parse_binlog()
    init_col_name()
    gen_rollback_sql()

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图