logback 持久化mysql_mysql单表回复工具binlogback_mysql

mysql单表恢复工具binlogback

在维护mysql的经验中,很多时候我们需要进行单表恢复,以应对误操作 或者sql注入引起的故障,本人也在实际的工作中遇到若干次开发误操作需要恢复数据的情况。

为了方便的恢复数据,所以写了一个逆向解析binlog的工具,能够方便的进行单表恢复操作。目前仅支持5.5,binlog格式必须为row格式

工具下载地址:https://github.com/visician/binlogback,具体的限制以及使用方法请查看README

使用演示:

创建表unit.t1

create table t1(c1 int not null primary key,c2 varchar(50),c3 datetime)

插入部分数据:

点击(此处)折叠或打开

unit> insert into t1 values (1,"xxxx",now());

Query OK, 1 row affected (0.00 sec)

unit> insert into t1 values (2,"a",now());

Query OK, 1 row affected (0.00 sec)

unit> insert into t1 values (3,"b",now());

Query OK, 1 row affected (0.00 sec)

unit> select * from t1;

+----+------+---------------------+

| c1 | c2 | c3 |

+----+------+---------------------+

| 1 | xxxx | 2015-06-10 20:33:34 |

| 2 | a | 2015-06-10 20:33:40 |

| 3 | b | 2015-06-10 20:33:46 |

+----+------+---------------------+

3 rows in set (0.00 sec)

这时候我们模拟一个误操作,删除所有的数据:

点击(此处)折叠或打开

unit> delete from t1;

Query OK, 3 rows affected (0.00 sec)

查看binlog:

# at 4573

#150610 20:33:34 server id 5295721 end_log_pos 4649 Query thread_id=909740 exec_time=0 error_code=0

SET TIMESTAMP=1433939614/*!*/;

SET @@session.time_zone='SYSTEM'/*!*/;

BEGIN

/*!*/;

# at 4649

# at 4694

#150610 20:33:34 server id 5295721 end_log_pos 4694 Table_map: `unit`.`t1` mapped to number 83

#150610 20:33:34 server id 5295721 end_log_pos 4741 Write_rows: table id 83 flags: STMT_END_F

BINLOG '

ni54VRNpzlAALQAAAFYSAAAAAFMAAAAAAAEABHVuaXQAAnQxAAMDDwwClgAG

ni54VRdpzlAALwAAAIUSAAAAAFMAAAAAAAEAA//4AQAAAAR4eHh4xpL2rVMSAAA=

'/*!*/;

### INSERT INTO unit.t1

### SET

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2='xxxx' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */

### @3=2015-06-10 20:33:34 /* DATETIME meta=0 nullable=1 is_null=0 */

# at 4741

#150610 20:33:34 server id 5295721 end_log_pos 4768 Xid = 3484054

COMMIT/*!*/;

# at 4768

#150610 20:33:40 server id 5295721 end_log_pos 4844 Query thread_id=909740 exec_time=0 error_code=0

SET TIMESTAMP=1433939620/*!*/;

BEGIN

/*!*/;

# at 4844

# at 4889

#150610 20:33:40 server id 5295721 end_log_pos 4889 Table_map: `unit`.`t1` mapped to number 83

#150610 20:33:40 server id 5295721 end_log_pos 4933 Write_rows: table id 83 flags: STMT_END_F

BINLOG '

pC54VRNpzlAALQAAABkTAAAAAFMAAAAAAAEABHVuaXQAAnQxAAMDDwwClgAG

pC54VRdpzlAALAAAAEUTAAAAAFMAAAAAAAEAA//4AgAAAAFhzJL2rVMSAAA=

'/*!*/;

### INSERT INTO unit.t1

### SET

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2='a' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */

### @3=2015-06-10 20:33:40 /* DATETIME meta=0 nullable=1 is_null=0 */

# at 4933

#150610 20:33:40 server id 5295721 end_log_pos 4960 Xid = 3484055

COMMIT/*!*/;

# at 4960

#150610 20:33:46 server id 5295721 end_log_pos 5036 Query thread_id=909740 exec_time=0 error_code=0

SET TIMESTAMP=1433939626/*!*/;

BEGIN

/*!*/;

# at 5036

# at 5081

#150610 20:33:46 server id 5295721 end_log_pos 5081 Table_map: `unit`.`t1` mapped to number 83

#150610 20:33:46 server id 5295721 end_log_pos 5125 Write_rows: table id 83 flags: STMT_END_F

BINLOG '

qi54VRNpzlAALQAAANkTAAAAAFMAAAAAAAEABHVuaXQAAnQxAAMDDwwClgAG

qi54VRdpzlAALAAAAAUUAAAAAFMAAAAAAAEAA//4AwAAAAFi0pL2rVMSAAA=

'/*!*/;

### INSERT INTO unit.t1

### SET

### @1=3 /* INT meta=0 nullable=0 is_null=0 */

### @2='b' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */

### @3=2015-06-10 20:33:46 /* DATETIME meta=0 nullable=1 is_null=0 */

# at 5125

#150610 20:33:46 server id 5295721 end_log_pos 5152 Xid = 3484056

COMMIT/*!*/;

# at 5152

#150610 20:35:52 server id 5295721 end_log_pos 5220 Query thread_id=909740 exec_time=0 error_code=0

SET TIMESTAMP=1433939752/*!*/;

BEGIN

/*!*/;

# at 5220

# at 5265

#150610 20:35:52 server id 5295721 end_log_pos 5265 Table_map: `unit`.`t1` mapped to number 83

#150610 20:35:52 server id 5295721 end_log_pos 5342 Delete_rows: table id 83 flags: STMT_END_F

BINLOG '

KC94VRNpzlAALQAAAJEUAAAAAFMAAAAAAAEABHVuaXQAAnQxAAMDDwwClgAG

KC94VRlpzlAATQAAAN4UAAAAAFMAAAAAAAEAA//4AQAAAAR4eHh4xpL2rVMSAAD4AgAAAAFhzJL2

rVMSAAD4AwAAAAFi0pL2rVMSAAA=

'/*!*/;

### DELETE FROM unit.t1

### WHERE

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2='xxxx' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */

### @3=2015-06-10 20:33:34 /* DATETIME meta=0 nullable=1 is_null=0 */

### DELETE FROM unit.t1

### WHERE

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2='a' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */

### @3=2015-06-10 20:33:40 /* DATETIME meta=0 nullable=1 is_null=0 */

### DELETE FROM unit.t1

### WHERE

### @1=3 /* INT meta=0 nullable=0 is_null=0 */

### @2='b' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */

### @3=2015-06-10 20:33:46 /* DATETIME meta=0 nullable=1 is_null=0 */

# at 5342

#150610 20:35:52 server id 5295721 end_log_pos 5369 Xid = 3484242

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

可以看到我们的insert操作和delete操作,在时间2015-06-10 20:35:52 我们做了delete误操作。现在我们要恢复到这个时间点

[root@centos mysql_6302_binlog]# python binlogback.py -f ./mysql-bin.000022 -B unit -m desc -t t1 -c "c1,c2,c3" --begin-datetime="2015-06-10 20:35:52"

#pos:5342

BEGIN;

insert into `unit`.`t1`(`c1`,`c2`,`c3`)values ( 3, 'b', '2015-06-10 20:33:46');

insert into `unit`.`t1`(`c1`,`c2`,`c3`)values ( 2, 'a', '2015-06-10 20:33:40');

insert into `unit`.`t1`(`c1`,`c2`,`c3`)values ( 1, 'xxxx', '2015-06-10 20:33:34');

COMMIT

可以看到工具把delete语句按照反序转换成了insert语句,把生成的语句执行:

unit> BEGIN;

Query OK, 0 rows affected (0.00 sec)

unit> insert into `unit`.`t1`(`c1`,`c2`,`c3`)values ( 3, 'b', '2015-06-10 20:33:46');

Query OK, 1 row affected (0.00 sec)

unit> insert into `unit`.`t1`(`c1`,`c2`,`c3`)values ( 2, 'a', '2015-06-10 20:33:40');

Query OK, 1 row affected (0.00 sec)

unit> insert into `unit`.`t1`(`c1`,`c2`,`c3`)values ( 1, 'xxxx', '2015-06-10 20:33:34');

Query OK, 1 row affected (0.00 sec)

unit> COMMIT;

Query OK, 0 rows affected (0.00 sec)

unit> select * from t1;

+----+------+---------------------+

| c1 | c2 | c3 |

+----+------+---------------------+

| 1 | xxxx | 2015-06-10 20:33:34 |

| 2 | a | 2015-06-10 20:33:40 |

| 3 | b | 2015-06-10 20:33:46 |

+----+------+---------------------+

3 rows in set (0.00 sec)

可以看到表t1已经恢复到了我们删除前的状态。

工具限制:

1、要恢复的表必须有主键,不然恢复可能会引起严重的数据错误

2、目前仅支持mysql5.5,以及5.5的衍生版本,只要binlog协议没有发生改变,原理上都适用

3、只支持row格式的binlog

4、time类型解析有问题,如果time类型的值小于“00:00:00”那么会解析出错误的值

5、因为工作时间问题,暂时没有支持设置binlog position来恢复数据,后续会支持

优点:

1、方便快捷,单表恢复

2、修复了5.5的mysqlbinlog工具的一些BUG

3、使用python,没有依赖,下载即可使用

该工具是博主最近研究binlog协议生产出来的一个小工具,欢迎大家试用,多提BUG,多多交流。代码没有经过严格的构思,请勿吐槽代码水平。

欢迎大家阅读《mysql单表回复工具binlogback_mysql》,跪求各位点评,by 搞代码

e7ce419cf2d6ad34d01da2ceb8829eed.png

微信 赏一包辣条吧~

023a57327877fb4402bcc76911ec18ea.png

支付宝 赏一听可乐吧~

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页