前言
使用数据库过程中难免有出错的时候,假如不小心删除了一张表,或者错误执行了update语句,那么这个时候想要恢复数据就需要用到数据闪回工具,如:binlog2sql和myflash等。但因为binlog2sql工具要求数据库在线,即需要连接数据库进行binlog解析才能转换为sql,无法直接对binlog文件进行解析,使用条件上相对苛刻,所以没有考虑。
介绍
myflash是美团研发的一款开源的数据库闪回工具,他可以用来回滚DML操作。
使用条件
- MySQL数据库必须开启binlog
- binlog格式必须为row,且binlog_row_image=full
- 仅支持5.6与5.7
- 只能回滚DML(增、删、改)
下载与安装
下载地址为:https://github.com/Meituan-Dianping/MyFlash
按照下图方式下载,可以不用安装git。
首先要安装编译所必须的依赖包
[root@hadoop101 /]# yum install -y gcc pkg-config glib2 libgnomeui-devel
将压缩包上传到服务器后使用unzip命令解压。
[root@hadoop101 package]# pwd
/opt/package
[root@hadoop101 package]# ls
myflash-master.zip mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
[root@hadoop101 package]# unzip -d /opt/ myflash-master.zip
...解压文件列表过长不展示
[root@hadoop101 MyFlash-master]# cd /opt/MyFlash-master/
[root@hadoop101 MyFlash-master]# ls
binary binlog_output_base.flashback build.sh doc License.md README.md source testbinlog
如果下载的是zip包,他会有一个编译好的程序为解压路径/binary/flashback
,也可以再次编译,进入解压后的目录,编译并安装
[root@hadoop101 MyFlash-master]# gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
安装完之后是没有环境变量的,只能使用绝对路径调用工具,使用--help
可以查看命令的使用方式
[root@hadoop101 binary]# /opt/MyFlash-master/binary/flashback --help
Usage:
flashback [OPTION?]
Help Options:
-h, --help Show help options
Application Options:
--databaseNames databaseName to apply. if multiple, seperate by comma(,)
--tableNames tableName to apply. if multiple, seperate by comma(,)
--start-position start position
--stop-position stop position
--start-datetime start time (format %Y-%m-%d %H:%M:%S)
--stop-datetime stop time (format %Y-%m-%d %H:%M:%S)
--sqlTypes sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
--maxSplitSize max file size after split, the uint is M
--binlogFileNames binlog files to process. if multiple, seperate by comma(,)
--outBinlogFileNameBase output binlog file name base
--logLevel log level, available option is debug,warning,error
--include-gtids gtids to process
--exclude-gtids gtids to skip
参数解释,这里我直接借用了别人的,有些东西我也没用过,所以不确保是否可用。
参数 | 释义 |
---|---|
--databaseNames | 需要闪回的数据库名称,如果有多个数据库,用逗号”,”隔开。 |
--tableNames | 要闪回的表名称,如果有多个表,用逗号”,”隔开。 |
--start-position | 闪回的起始位置,如不指定,从文件开始处回滚。 |
--stop-position | 闪回的终止位置,如不指定,回滚到文件结尾。 |
--start-datetime | 闪回的开始时间。 |
--stop-datetime | 闪回的终止时间。 |
--sqlTypes | 指定需要回滚的sql类型,支持INSERT、UPDATE、DELETE,多个类型使用逗号”,”分开。 |
--maxSplitSize | 对文件进行固定尺寸的切割,以防止单次应用binlog尺寸较大,对线上造成压力。 |
--binlogFileNames | 指定需要回滚的binlog文件,美团文档说目前只支持单个binlog文件,经测试已经支持多个binlog文件同时闪回。 |
--outBinlogFileNameBase | 指定输出的binlog文件前缀,如不指定,则默认为binlog_output_base.flashback。 |
logLevel | 仅供开发者使用,默认级别为error级别。在生产环境中不要修改这个级别,否则输出过多。 |
include-gtids | 指定需要回滚的gtid,支持gtid的单个和范围两种形式。 |
exclude-gtids | 指定不需要回滚的gtid,用法同include-gtids。 |
事先准备
首先我准备了一个单节点的mysql,然后刷新了binlog,之后会建表写入演示数据,然后模拟误操作再演示回滚数据。
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status ;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000005 | 194 | | | 2ec63aca-cd00-11ec-ba97-000c29648fd7:1-144 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> create database demo;
Query OK, 1 row affected (0.00 sec)
mysql> create table `time`(
-> `id` int primary key auto_increment,
-> `string` varchar(50),
-> `create_time` datetime
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into demo.`time` values(null,'!@#',now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into demo.`time` values(null,'*&^',now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into demo.`time` values(null,'^%$',now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from demo.`time`;
+----+--------+---------------------+
| id | string | create_time |
+----+--------+---------------------+
| 1 | !@# | 2022-05-06 17:21:48 |
| 2 | *&^ | 2022-05-06 17:21:53 |
| 3 | ^%$ | 2022-05-06 17:21:58 |
+----+--------+---------------------+
3 rows in set (0.00 sec)
删除数据
此时删除整张表的数据,但保留表结构,一般误操作都是在图形化界面误操作的,所以我这里使用navicat模拟删除。
这里千万不要点删除表或者截断表,删除表的命令的drop
,截断表的命令是truncate
,这两个都是DDL语句,binlog是不会记录详细的删除信息的,一旦执行操作将无法回滚,是myflash无法拯救的。
此时可以查看一下mysql的binlog文件,看看binlog的情况。
[root@hadoop101 binlog]# pwd
/data/mysql_data/binlog
[root@hadoop101 binlog]# ls
mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.index
[root@hadoop101 binlog]# /opt/mysql_6033/bin/mysqlbinlog -vv --base64-output=DECODE-ROWS mysql-bin.000005
......
create database demo
/*!*/;
# at 353
......
create table `time`( `id` int primary key auto_increment, `string` varchar(50), `create_time` datetime )
/*!*/;
# at 596
......
# at 865
#220506 17:21:48 server id 1000 end_log_pos 914 CRC32 0x305f5f15 Write_rows: table id 112 flags: STMT_END_F
### INSERT INTO `demo`.`time`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='!@#' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='2022-05-06 17:21:48' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 914
......
# at 1214
#220506 17:21:53 server id 1000 end_log_pos 1263 CRC32 0x834574e8 Write_rows: table id 112 flags: STMT_END_F
### INSERT INTO `demo`.`time`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='*&^' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='2022-05-06 17:21:53' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
......
# at 1563
#220506 17:21:58 server id 1000 end_log_pos 1612 CRC32 0xa58ebd4d Write_rows: table id 112 flags: STMT_END_F
### INSERT INTO `demo`.`time`
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='^%$' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='2022-05-06 17:21:58' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
......
#220506 17:22:24 server id 1000 end_log_pos 1951 CRC32 0xc374eb11 Delete_rows: table id 112 flags: STMT_END_F
### DELETE FROM `demo`.`time`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='!@#' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='2022-05-06 17:21:48' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `demo`.`time`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='*&^' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='2022-05-06 17:21:53' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `demo`.`time`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='^%$' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='2022-05-06 17:21:58' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 1951
......
多余的内容我用省略号代替了,主要是想展示在binlog中包含了所有有价值的内容,比如建表,insert和delete,而且对整张表执行delete
命令时,他会一条一条数据删除,删除的每条数据都完好的保存在binlog中,这也为回滚提供了便捷。
回滚
使用myfalsh工具对binlog进行回滚,他会生成一个新的以.flashback
为后缀的文件,这是一个已经回滚完的binlog文件,上面binlog中的delete语句会被转换为insert语句,然后使用mysqlbinlog命令就可以导入此文件到mysql实例中,如此便完成回滚。
执行命令:
[root@hadoop101 binlog]# /opt/MyFlash-master/binary/flashback --databaseNames="demo" --tableNames="time" --start-datetime="2022-05-06 17:00:00" --stop-datetime="2022-05-06 18:00:00" --sqlTypes="UPDATE,DELETE" --binlogFileNames=/data/mysql_data/binlog/mysql-bin.000005 --outBinlogFileNameBase=/tmp/rollbak.sql
命令中要回滚的数据库为demo,要回滚的表是time,误操作的开始时间,即回滚开始时间为2022-05-06 17:00:00,回滚结束时间为2022-05-06 18:00:00,要回滚的语句指定为UPDATE和DELETE,要解析的binlog文件是mysql-bin.000005,回滚后的文件保存在/tmp/下,文件名前缀为rollbak.sql。如果有多个数据库和多张表记得用逗号隔开。
然后会在/tmp/
路径下生成一个叫rollbak.sql.flashback
的文件,该文件使用mysqlbinlog命令解析后如下图
最后将此文件导入即可:
[root@hadoop101 binlog]# /opt/mysql_6033/bin/mysqlbinlog --skip-gtids /tmp/rollbak.sql.flashback | /opt/mysql_6033/bin/mysql -h 127.0.0.1 -P 6033 -u root -p
这里注意,如果mysql数据库使用了gtid,则导入回滚数据时要添加--skip-gtids
的参数,否则会报错。
最后登录数据库查看
额外的内容
insert和delete的回滚很容易,两者的操作是相悖的,只需要直接替换关键词即可,而update是特殊的,在某些场景下不建议在--sqlTypes
参数中添加UPDATE。
比如原字段A的值是1,现将其改为99,那么在binlog中会记录下“update table set A=99 where A=1”,如果将其回滚,这条语句就会变成“update table set A=1 where A=99”,而如果在误操作delete之间有正常的update,那么回滚之后数据就会异常,正常应该执行的update语句被回滚回去了。
所以一定要清楚自己的误操作到底操作了什么,针对误操作的类型进行单独的回滚是比较好的。