背景:
研发: 糟糕,我刚想删一条数据,没加where,删了全表,DBA老哥,帮忙找回?
DBA: 额,这个问题嘛,骚等,让我想想。。。
---------------------------
MyFlash介绍:
Myflash 是美团开发的一个闪回功能,我们平时从BINLOG中取数据,需要结合sed和awk等工具一起使用,如果字段含有一些特殊字符,则需要转义,加大了恢复的难度和时长;
https://zhuanlan.zhihu.com/p/31217864
优点:
- a. 无需把binlog解析成文本,再进行转换。
b. 提供原生的基于库、表、SQL类型、位置、时间等多种过滤方式。
c. 支持MySQL多个版本 5.6、5.7。
d. 操作简单
限制
- binlog格式必须为row,且binlog_row_image=full
- 仅支持5.6与5.7
- 只能回滚DML(增、删、改),不支持ALTER
安装:
安装:https://github.com/Meituan-Dianping/MyFlash
1, 下载解压:MyFlash-master
drwxr-xr-x 2 root root 4096 4月 28 15:29 binary
-rw-r--r-- 1 root root 490 8月 15 2019 binlog_output_base.flashback
-rw-r--r-- 1 root root 122 8月 15 2019 build.sh
drwxr-xr-x 2 root root 4096 8月 15 2019 doc
-rw-r--r-- 1 root root 1103 8月 15 2019 License.md
-rw-r--r-- 1 root root 1273 8月 15 2019 README.md
drwxr-xr-x 4 root root 4096 8月 15 2019 source
drwxr-xr-x 2 root root 4096 8月 15 2019 testbinlog
2, 动态编译链接
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
如果报错:ackage glib-2.0 was not found
则执行安装: yum install libgnomeui-devel
功能测试:
帮助
./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(,)
#指定SQL类型,只支持DML,不支持DDL
--maxSplitSize max file size after split, the uint is M
#如果较大,支持拆分,单位MB
--binlogFileNames binlog files to process. if multiple, seperate by comma(,)
#指定要恢复的BINLOG文件名,如果不是本目录,需要给出绝对路径
--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
找个测试表,查看修改前数据:
mysql> select * from t1 limit 10;
+----+---------+-----------+----------+----------+---------------------+
| id | ip | type | hw | configid | create_time |
+----+---------+-----------+----------+----------+---------------------+
| 1 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
| 2 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
| 3 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
| 4 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
| 5 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
现在id = 1的这个地址,硬件配置扩了,我想更新下这个hw字段; update t1 set hw = '32c 64G 1000G' where id=1;
由于粗心,忘记写where条件,最后执行了: update t1 set hw = '32c 64G 1000G' 导致全表UPDATE;
查看此时数据:
mysql> update t1 set hw = '32c 64G 1000G' ;
Query OK, 521 rows affected (0.07 sec)
Rows matched: 521 Changed: 521 Warnings: 0
mysql> select * from t1 limit 5;
+----+---------+-----------+---------------+----------+---------------------+
| id | ip | type | hw | configid | create_time |
+----+---------+-----------+---------------+----------+---------------------+
| 1 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
| 2 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
| 3 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
| 4 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
| 5 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
+----+---------+-----------+---------------+----------+---------------------+
5 rows in set (0.00 sec)
然后我没发现,想删除id=2的数据,由于昨晚LOL太晚,精神不佳,又忘记where条件了,执行了delete from t1;
现在表空了,啥东西都没有了;
mysql> delete from t1;
Query OK, 521 rows affected (0.06 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql>
然后我们flush logs, show master logs; 查看BINLOG;mysql-bin.000011
我们利用myflash 来恢复;
我们知道的信息:
库名:wang
表名:t1,
BINLOG: mysql-bin.000011
时间范围: 2020-04-28 12:00:00 -- 2020-04-28 17:20:00
SQL类型,DELETE和UPDATE,为了便于观察,我们2步恢复;
执行SQL:
[root@host-10-185-161-202 dbbak]# flashback --databaseNames=wang --tableNames=t1 --sqlTypes=DELETE --binlogFileNames=mysql-bin.000011 --start-datetime='2020-04-28 12:00:00' --stop-datetime='2020-04-28 17:20:00' --outBinlogFileNameBase=DELETE
[root@host-10-185-161-202 dbbak]# flashback --databaseNames=wang --tableNames=t1 --sqlTypes=UPDATE --binlogFileNames=mysql-bin.000011 --start-datetime='2020-04-28 12:00:00' --stop-datetime='2020-04-28 17:20:00' --outBinlogFileNameBase=UPDATE
[root@host-10-185-161-202 dbbak]# ll
总用量 136
-rw-r--r-- 1 root root 23954 4月 28 17:21 DELETE.flashback
-rw-r----- 1 root root 68787 4月 28 17:13 mysql-bin.000011
-rw-r--r-- 1 root root 44984 4月 28 17:22 UPDATE.flashback
这2个文件是二进制文件,无法查看;想要看内容,得用MYSQL
如果想查看文件内容,使用命令: mysqlbinlog DELETE.flashback | less
[root@host-10-185-161-202 dbbak]# mysqlbinlog --no-defaults DELETE.flashback | less
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200428 17:01:06 server id 161202 end_log_pos 120 CRC32 0x84b8a716 Start: binlog v 4, server v 5.6.36-log created 200428 17:01:06
BINLOG '
0vCnXg+ydQIAdAAAAHgAAAAAAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAARan
uIQ=
'/*!*/;
# at 120
#200428 17:08:03 server id 161202 end_log_pos 177 CRC32 0x45c061f8 Table_map: `wang`.`t1` mapped to number 80
# at 177
#200428 17:08:03 server id 161202 end_log_pos 7367 CRC32 0x1e23a071 Write_rows: table id 80 flags: STMT_END_F
BINLOG '
c/KnXhOydQIAOQAAALEAAAAAAFAAAAAAAAEABHdhbmcAAnQxAAYDDw8PAxEHLQA8ADwAAB74YcBF
c/KnXh6ydQIAFhwAAMccAAAAAFAAAAAAAAEAAgAG/8BrAQAAB2lwIGFkZHIJ5a6e5L2T5py6DTMy
YyA2NEcgMTAwMEcDAAAAXYDUE8BsAQAAB2lwIGFkZHIJ5a6e5L2T5py6DTMyYyA2NEcgMTAwMEcD
AAAAXYDUE8BtAQAAB2lwIGFkZHIJ5a6e5L2T5py6DTMyYyA2NEcgMTAwMEcDAAAAXYDUE8BuAQAA
执行恢复命令:mysqlbinlog --no-defaults DELETE.flashback | mysql -uuser -p'password'
mysql> select * from t1 limit 5;
+----+---------+-----------+---------------+----------+---------------------+
| id | ip | type | hw | configid | create_time |
+----+---------+-----------+---------------+----------+---------------------+
| 1 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
| 2 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
| 3 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
| 4 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
| 5 | ip addr | 实体机 | 32c 64G 1000G | 4 | 2019-09-17 20:39:47 |
+----+---------+-----------+---------------+----------+---------------------+
5 rows in set (0.00 sec)
可以看到此时数据回来了,只是hw 内容还是错误的;
咱们再来恢复UPDATE 操作:mysqlbinlog --no-defaults UPDATE.flashback | mysql -uuser -p'password'
mysql> select * from t1 limit 5;
+----+---------+-----------+----------+----------+---------------------+
| id | ip | type | hw | configid | create_time |
+----+---------+-----------+----------+----------+---------------------+
| 1 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
| 2 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
| 3 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
| 4 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
| 5 | ip addr | 实体机 | 32G 600G | 4 | 2019-09-17 20:39:47 |
+----+---------+-----------+----------+----------+---------------------+
5 rows in set (0.00 sec)
mysql>
恢复完毕;
------------------------
flashback参考:https://zhuanlan.zhihu.com/p/31217864