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>