mysqlbinlog flashback 5.6使用案例

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 转自:https://www.cnblogs.com/youge-OneSQL/p/5249736.html 简介 DBA或者开发人员,有时会误删除或者误更新数据。传统的数据库恢复方法是利用之前的备份再加上误操作之前的binlog,来恢复数据。

转自:https://www.cnblogs.com/youge-OneSQL/p/5249736.html

简介

DBA或者开发人员,有时会误删除或者误更新数据。传统的数据库恢复方法是利用之前的备份再加上误操作之前的binlog,来恢复数据。该方法需要耗费较长时间来恢复备份,甚至需要停机维护,严重降低系统的可用性。

MySQL的flashback功能是由淘宝的彭立勋,在MySQL-5.5.18的基础上开发的。随着binlog格式的变动和类型的增加,基于5.5.18的版本,无法用于目前主流的5.6版本的。因此平民软件将该功能移植到5.6版本上,让相关人员对数据库有更强的掌控能力。

 

binlog格式选择

MySQL的binlog格式分为三类:statement、mixed、row。其中,

    statement格式,记录了所有客户端的执行语句。比如 update tb1 set name='aaa' where id=1;

    row格式,记录了数据库的变更前和变更后数据。比如 update tb1 set name='aaa' ,id=1 where name='bbb' , id=1;

    mixed格式,是二者的混合。在使用statement格式不会造成主从不一致时,使用statement格式,否则使用row模式。

因此想要把数据回退到上一个时间点,必须保证binlog_format=row

闪回原理

单个event闪回

举例说明

  • 在数据库中创建如下表

mysql> show create table tb1\G
*************************** 1. row ***************************
Table: tb1
Create Table: CREATE TABLE `tb1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

  • 执行增、删、改动作

insert into tb1 values(1,'aaa'),(2,'aaa');

delete from tb1 where id=2;

update tb1 set name='bbb' where id=1;

  • 查看binlog文件

mysqlbinlog -v 128.000129

########################################

SET TIMESTAMP=1459999601/*!*/;
BEGIN
/*!*/;
# at 301
#160407 11:26:41 server id 10 end_log_pos 346 Table_map: `test`.`tb1` mapped to number 224
# at 346
#160407 11:26:41 server id 10 end_log_pos 395 Write_rows: table id 224 flags: STMT_END_F

BINLOG '
cdMFVxMKAAAALQAAAFoBAAAAAOAAAAAAAAEABHRlc3QAA3RiMQACAw8CFAAD
cdMFVx4KAAAAMQAAAIsBAAAAAOAAAAAAAAEAAgAC//wBAAAAA2FhYfwCAAAAA2FhYQ==
'/*!*/;
### INSERT INTO `test`.`tb1`
### SET
### @1=1
### @2='aaa'
### INSERT INTO `test`.`tb1`
### SET
### @1=2
### @2='aaa'
# at 395
#160407 11:26:41 server id 10 end_log_pos 422 Xid = 271
COMMIT/*!*/;
# at 422
#160407 11:27:04 server id 10 end_log_pos 490 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1459999624/*!*/;
BEGIN
/*!*/;
# at 490
#160407 11:27:04 server id 10 end_log_pos 535 Table_map: `test`.`tb1` mapped to number 224
# at 535
#160407 11:27:04 server id 10 end_log_pos 575 Delete_rows: table id 224 flags: STMT_END_F

BINLOG '
iNMFVxMKAAAALQAAABcCAAAAAOAAAAAAAAEABHRlc3QAA3RiMQACAw8CFAAD
iNMFVyAKAAAAKAAAAD8CAAAAAOAAAAAAAAEAAgAC//wCAAAAA2FhYQ==
'/*!*/;
### DELETE FROM `test`.`tb1`
### WHERE
### @1=2
### @2='aaa'
# at 575
#160407 11:27:04 server id 10 end_log_pos 602 Xid = 272
COMMIT/*!*/;
# at 602
#160407 11:27:22 server id 10 end_log_pos 670 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1459999642/*!*/;
BEGIN
/*!*/;
# at 670
#160407 11:27:22 server id 10 end_log_pos 715 Table_map: `test`.`tb1` mapped to number 224
# at 715
#160407 11:27:22 server id 10 end_log_pos 765 Update_rows: table id 224 flags: STMT_END_F

BINLOG '
mtMFVxMKAAAALQAAAMsCAAAAAOAAAAAAAAEABHRlc3QAA3RiMQACAw8CFAAD
mtMFVx8KAAAAMgAAAP0CAAAAAOAAAAAAAAEAAgAC///8AQAAAANhYWH8AQAAAANiYmI=
'/*!*/;
### UPDATE `test`.`tb1`
### WHERE
### @1=1
### @2='aaa'
### SET
### @1=1
### @2='bbb'
# at 765
#160407 11:27:22 server id 10 end_log_pos 792 Xid = 273
COMMIT/*!*/;
# at 792
#160407 11:28:04 server id 10 end_log_pos 829 Rotate to 128.000130 pos: 4
DELIMITER ;

###################################################

  • 反转

insert:event中包含该行数据的每个字段值,删除即可
delete:event中包含了删除前,该行数据的所有字段值。将删除的数据,重新插入即可
update:只需将set和where部分调换,即可完成反转。

多个event闪回

如果误操作的事务是由多个event组成的,那么必须将整个事务倒序恢复,即从最后一个event恢复到第一个event。

 

使用方法

举例说明

  • insert into tb1 values(1,'aaa'),(2,'aaa');
  • delete from tb1 where id=2;
  • update tb1 set name='bbb' where id=1;
  • select * from tb1;

+------+------+
| id     | name |
+------+------+
| 1      | bbb |
+------+------+

  • mysqlbinlog -v --start-pos=557 128.000132

#####################

# at 557
#160407 12:04:15 server id 10 end_log_pos 602 Table_map: `test`.`tb1` mapped to number 224
# at 602
#160407 12:04:15 server id 10 end_log_pos 652 Update_rows: table id 224 flags: STMT_END_F

BINLOG '
P9wFVxMKAAAALQAAAFoCAAAAAOAAAAAAAAEABHRlc3QAA3RiMQACAw8CFAAD
P9wFVx8KAAAAMgAAAIwCAAAAAOAAAAAAAAEAAgAC///8AQAAAANhYWH8AQAAAANiYmI=
'/*!*/;
### UPDATE `test`.`tb1`
### WHERE
### @1=1
### @2='aaa'
### SET
### @1=1
### @2='bbb'
# at 652
#160407 12:04:15 server id 10 end_log_pos 679 Xid = 540
COMMIT/*!*/;

#####################

  • mysqlbinlog -v -B --start-pos=557 128.000132

############################

BINLOG '
P9wFVxMKAAAALQAAAFoCAAAAAOAAAAAAAAEABHRlc3QAA3RiMQACAw8CFAAD
P9wFVx8KAAAAMgAAAIwCAAAAAOAAAAAAAAEAAgAC///8AQAAAANiYmL8AQAAAANhYWE=
'/*!*/;
### UPDATE `test`.`tb1`
### WHERE
### @1=1
### @2='bbb'
### SET
### @1=1
### @2='aaa'

############################

可以看出update已经被反转

执行反转

  • mysqlbinlog -B -v --start-pos=557 128.000132 | mysql -utest -ptest test

mysql> select * from tb1;
+------+------+
| id     | name |
+------+------+
| 1      | aaa |
+------+------+

有兴趣的,可以继续往下恢复。

       注意:恢复之前先在不使用的从机上做测试,测试成功后,再在主库上做。

限制

       该软件利用binlog中记录了操作前的数据镜像和操作后的数据镜像。有如下限制

1)        binlog_format=row

2)        只支持insert、update、delete

3)        不支持drop 、truncate、alter等ddl语句

结论

       flashback功能,可以在紧急情况下,避免用户漫长的数据库恢复过程。让高可用提升一个级别。

 

下载地址

  http://pan.baidu.com/s/1nutwWg5

 

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8月前
|
SQL 存储 关系型数据库
mysqldump+binlog+gtid 实现数据库的增量备份(下)
mysqldump+binlog+gtid 实现数据库的增量备份(下)
119 0
|
10月前
|
SQL 存储 关系型数据库
MySQL-binlog+dump备份还原
MySQL-binlog+dump备份还原
152 0
|
SQL 关系型数据库 MySQL
【MySQL】mysqlbinlog_flashback工具使用
mysqlbinlog_back.py 是在线读取row格式的mysqld的binlog,然后生成反向的sql语句的工具。 一般用于数据恢复的目的。 所谓反向的sql语句就是如果是insert,则反向的sql为delete。 如果delete,反向的sql是insert,如果是update, 反向的sql还是update,但是update的值是原来的值。
167 0
【MySQL】mysqlbinlog_flashback工具使用
|
监控 关系型数据库 MySQL
使用mysqlbinlog报错
不知道怎么了,今天使用mysqlbinlog这个命令查看数据库的日志文件,出现了如下报错: [root@Darren1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.
1364 0
|
SQL MySQL 关系型数据库
MariaDB的闪回flashback尝试
测试背景 MariaDB是MySQL的分支,使用也是越来越广泛。闪回是对误操作最大的福音,本文是用来测试MariaDB支持的闪回功能 测试环境 MariaDB 10.2.22 CentOS 7.
4597 0
|
存储 Oracle 关系型数据库
|
Oracle 关系型数据库
Oracle闪回(flashback)功能详解 闪回数据归档(Flashback Data Archive)
Oracle闪回(flashback)功能详解  闪回数据归档(Flashback Data Archive)                 >>>>>>>>          >>>                              ...
917 0
|
关系型数据库 数据库 Oracle
关于Flashback的小测试
    对于Oracle的Flashback来说,在11g里面有了一个很细微的变化,可以说是一个很不错的福利,那就是开启闪回不需要重启数据库至mount状态下,归档模式下open状态就可以开启,关闭。
1131 0