flashback实现数据快速复原

MyFlash 限制

1. 仅支持 5.6 与 5.7 版本
2. binlog 格式必须为 row，且 binlog_row_image=full
3. 只能回滚DML（增、删、改）

第零步：确定日志

mysql> show variables like 'log_bin%';
 --------------------------------- ------------------------------------------------ 
| Variable_name                   | Value                                          |
 --------------------------------- ------------------------------------------------ 
| log_bin                         | ON                                             |
| log_bin_basename                | /application/mysql-5.6.40/data/mysql-bin       |
| log_bin_index                   | /application/mysql-5.6.40/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                            |
| log_bin_use_v1_row_events       | OFF                                            |
 --------------------------------- ------------------------------------------------ 
5 rows in set (0.01 sec)

mysql>  SHOW VARIABLES LIKE '%binlog_row_image%';
 ------------------ ------- 
| Variable_name    | Value |
 ------------------ ------- 
| binlog_row_image | FULL  |
 ------------------ ------- 
1 row in set (0.00 sec)

vi /etc/my.cnf

[mysqld]
log-bin=mysql-bin
binlog_format=row
server_id=1
[root@localhost home]#

第一步：下载myflash

官网：Meituan-Dianping/MyFlash: flashback mysql data to any point

yum -y install git
git clone https://github.com/Meituan-Dianping/MyFlash.git
#装依赖
yum install -y gcc pkg-config glib2 libgnomeui-devel
#编译
gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback

提示

#编译在这个目录下
[root@localhost ~]# cd /root/MyFlash/
[root@localhost MyFlash]# ll
总用量 16
drwxr-xr-x. 2 root root   50 10月 31 19:38 binary
-rw-r--r--. 1 root root  490 10月 31 19:38 binlog_output_base.flashback
-rw-r--r--. 1 root root  122 10月 31 19:38 build.sh
drwxr-xr-x. 2 root root   97 10月 31 19:38 doc
-rw-r--r--. 1 root root 1103 10月 31 19:38 License.md
-rw-r--r--. 1 root root 1273 10月 31 19:38 README.md
drwxr-xr-x. 4 root root   65 10月 31 19:38 source
drwxr-xr-x. 2 root root  101 10月 31 19:38 testbinlog


[root@localhost MyFlash]# gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback

成功示范

[root@localhost binary]# pwd
/root/MyFlash/binary
[root@localhost 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(,)
  --tableNames-file           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. if multiple, seperate by comma(,)
  --include-gtids-file        gtids to process. if multiple, seperate by comma(,)
  --exclude-gtids             gtids to skip. if multiple, seperate by comma(,)
  --exclude-gtids-file        gtids to skip. if multiple, seperate by comma(,)

[root@localhost binary]#

STEP1：构造测试数据

CREATE TABLE test01 (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(255) NOT NULL,  
    birthday DATE NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test01 (id, name, birthday) VALUES  
(1, '小明', '1993-01-02'),  
(2, '小华', '1994-08-15'),  
(3, '小丽', '1995-07-12');

STEP2：执行插入、更新、删除操作

mysql> insert into test01 values(4,'小红','2000-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> delete from test01 where id = 1;
Query OK, 1 row affected (0.03 sec)

mysql> update test01 set birthday = '1994-09-15';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test01;
 ---- -------- ------------ 
| id | name   | birthday   |
 ---- -------- ------------ 
|  2 | 小华   | 1994-09-15 |
|  3 | 小丽   | 1994-09-15 |
|  4 | 小红   | 1994-09-15 |
 ---- -------- ------------ 
3 rows in set (0.00 sec)

STEP3：确认上面的DML操作二进制日志

mysql>  show master status;
 ------------------ ---------- -------------- ------------------ ------------------- 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 ------------------ ---------- -------------- ------------------ ------------------- 
| mysql-bin.000001 |     1716 |              |                  |                   |
 ------------------ ---------- -------------- ------------------ ------------------- 
1 row in set (0.00 sec)

STEP4：发现误删除(delete)数据，要求恢复（需要2步）

执行闪回操作，将闪回结果存放到binlog_output_base.flashback中

[root@localhost binary]# [root@localhost binary]# ./flashback --databaseNames=test --tableNames=test01 --sqlTypes='DELETE' --binlogFileNames=/application/mysql-5.6.40/data/mysql-bin.000001
[root@localhost binary]# ll
总用量 7324
-rw-r--r--. 1 root root     390 10月 31 20:34 binlog_output_base.flashback
-rwxr-xr-x. 1 root root   58768 10月 31 19:51 flashback
-rwxr-xr-x. 1 root root 7463125 10月 31 19:38 mysqlbinlog20160408

出现这个报错原因是bin

应用闪回的日志：

[root@masterdb binary]# mysqlbinlog binlog_output_base.flashback | mysql -uroot -p123456

STEP5：确认结果，已经将“DELETE”删除的数据找了回来

mysql> select * from test01;
ERROR 1046 (3D000): No database selected
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test01;
 ---- -------- ------------ 
| id | name   | birthday   |
 ---- -------- ------------ 
|  1 | 小明   | 1993-01-02 |
|  2 | 小华   | 1994-09-15 |
|  3 | 小丽   | 1994-09-15 |
|  4 | 小红   | 1994-09-15 |
 ---- -------- ------------ 
4 rows in set (0.00 sec)

mysql>