flashback實現資料快速復原
MyFlash 限制
- 僅支援 5.6 與 5.7 版本
- binlog 格式必須為 row，且 binlog_row_image=full
- 只能回滾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>