MySQL中MyFlash如何安裝使用

154次閱讀

共計 9633 個字符，預計需要花費 25 分鐘才能閱讀完成。

這篇文章給大家分享的是有關 MySQL 中 MyFlash 如何安裝使用的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。

Myflash 的安裝與使用
1、環境說明
1）centos 7.x
2）mysql5.7.21 限制 binlog 格式必須為 row，且 binlog_row_image=full
只支持 DML 的回滾(insert、delete、updte)
操作對象 test01 庫、users 表
3）Myflash
2、安裝 Myflash
1）安裝依賴包
yum install gcc* pkg-config glib2 libgnomeui-devel -y
2）安裝
git clone https://github.com/Meituan-Dianping/MyFlash.git
cd MyFlash
gcc -w pkg-config –cflags –libs glib-2.0 source/ binlogParseGlib.c -o binary/flashback
cd binary
./flashback –help
顯示幫助文檔即可說明安裝成功
3) 設置環境變量
vim /etc/profile
最后一行添加
alias flashback=“/opt/MyFlash/binary/flashback
source /etc/profile
3、使用：? 下面的這些參數是可以任意組合的。

1.databaseNames 指定需要回滾的數據庫名。多個數據庫可以用“,”隔開。如果不指定該參數，相當于指定了所有數據庫。 2.tableNames? 指定需要回滾的表名。多個表可以用“,”隔開。如果不指定該參數，相當于指定了所有表。

3.start-position? 指定回滾開始的位置。如不指定，從文件的開始處回滾。請指定正確的有效的位置，否則無法回滾

4.stop-position? 指定回滾結束的位置。如不指定，回滾到文件結尾。請指定正確的有效的位置，否則無法回滾

5.start-datetime? 指定回滾的開始時間。注意格式必須是 %Y-%m-%d %H:%M:%S。如不指定，則不限定時間

6.stop-datetime? 指定回滾的結束時間。注意格式必須是 %Y-%m-%d %H:%M:%S。如不指定，則不限定時間

7.sqlTypes? 指定需要回滾的 sql 類型。目前支持的過濾類型是 INSERT, UPDATE ,DELETE。多個類型可以用“,”隔開。

8.maxSplitSize? 一旦指定該參數，對文件進行固定尺寸的分割（單位為 M），過濾條件有效，但不進行回滾操作。該參數主要用來將大的 binlog 文件切割，防止單次應用的 binlog 尺寸過大，對線上造成壓力

9.binlogFileNames? 指定需要回滾的 binlog 文件，目前只支持單個文件，后續會增加多個文件支持

10.outBinlogFileNameBase? 指定輸出的 binlog 文件前綴，如不指定，則默認為 binlog_output_base.flashback

11.logLevel? 僅供開發者使用，默認級別為 error 級別。在生產環境中不要修改這個級別，否則輸出過多

12.include-gtids? 指定需要回滾的 gtid, 支持 gtid 的單個和范圍兩種形式。

13.exclude-gtids? 指定不需要回滾的 gtid，用法同 include-gtids

4、案例 1
1）創建庫和表
use test01
Database changed
(root@localhost:mysql.sock) [test01] create table users(
– id bigint unsigned NOT NULL AUTO_INCREMENT,
– realname varchar(20) not null comment 真實姓名 ,
– age int not null default 20 comment 年齡 ,
– primary key(id)
– )engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci comment 測試用戶表
Query OK, 0 rows affected (0.60 sec)
2) 造數據
(root@localhost:mysql.sock) [test01] insert into users (realname,age) values(kitten , 25
Query OK, 1 row affected (0.03 sec)

(root@localhost:mysql.sock) [test01] insert into users (realname,age) select realname,age from user;
ERROR 1146 (42S02): Table test01.user doesn t exist
(root@localhost:mysql.sock) [test01] insert into users (realname,age) select realname,age from users;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0

(root@localhost:mysql.sock) [test01] insert into users (realname,age) select realname,age from users;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

(root@localhost:mysql.sock) [test01] insert into users (realname,age) select realname,age from users;
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0

(root@localhost:mysql.sock) [test01] insert into users (realname,age) select realname,age from users;
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0

(root@localhost:mysql.sock) [test01] select count(*) from users;
+———-+
| count(*) |
+———-+
| 16 |
+———-+
1 row in set (0.00 sec)

(root@localhost:mysql.sock) [test01] select * from users;
+—-+———-+—–+
| id | realname | age |
+—-+———-+—–+
| 1 | kitten | 25 |
| 2 | kitten | 25 |
| 3 | kitten | 25 |
| 4 | kitten | 25 |
| 6 | kitten | 25 |
| 7 | kitten | 25 |
| 8 | kitten | 25 |
| 9 | kitten | 25 |
| 13 | kitten | 25 |
| 14 | kitten | 25 |
| 15 | kitten | 25 |
| 16 | kitten | 25 |
| 17 | kitten | 25 |
| 18 | kitten | 25 |
| 19 | kitten | 25 |
| 20 | kitten | 25 |
+—-+———-+—–+
16 rows in set (0.00 sec)
3)刪數據
delete from user where id
(root@localhost:mysql.sock) [test01] select
– * from users;
+—-+———-+—–+
| id | realname | age |
+—-+———-+—–+
| 13 | kitten | 25 |
| 14 | kitten | 25 |
| 15 | kitten | 25 |
| 16 | kitten | 25 |
| 17 | kitten | 25 |
| 18 | kitten | 25 |
| 19 | kitten | 25 |
| 20 | kitten | 25 |
+—-+———-+—–+
8 rows in set (0.00 sec)

5)查看 binlog 確認 start position \stop position
#mysqlbinlog /data/mysqldata/mysql-bin.000005 –base64-output=decode-rows -v

#180308 13:35:46 server id 223 end_log_pos 29355 CRC32 0x1b4db5f5 Query thread_id=692 exec_time=0 error_code=0
SET TIMESTAMP=1520487346/*!*/;
BEGIN
/*!*/;
# at 29355
#180308 13:35:46 server id 223 end_log_pos 29409 CRC32 0x662b1568 Table_map: `test01`.`users` mapped to number 117
# at 29409
#180308 13:35:46 server id 223 end_log_pos 29604 CRC32 0x4e984497 Delete_rows: table id 117 flags: STMT_END_F

BINLOG
ssugWhPfAAAANgAAAOFyAAAAAHUAAAAAAAEABnRlc3QwMQAFdXNlcnMAAwgPAwJQAABoFStm
ssugWiDfAAAAwwAAAKRzAAAAAHUAAAAAAAEAAgAD//gBAAAAAAAAAAZraXR0ZW4ZAAAA+AIAAAAA
AAAABmtpdHRlbhkAAAD4AwAAAAAAAAAGa2l0dGVuGQAAAPgEAAAAAAAAAAZraXR0ZW4ZAAAA+AYA
AAAAAAAABmtpdHRlbhkAAAD4BwAAAAAAAAAGa2l0dGVuGQAAAPgIAAAAAAAAAAZraXR0ZW4ZAAAA
+AkAAAAAAAAABmtpdHRlbhkAAACXRJhO
/*!*/;
### DELETE FROM `test01`.`users`
### WHERE
### @1=1
### @2= kitten
### @3=25
### DELETE FROM `test01`.`users`
### WHERE
### @1=2
### @2= kitten
### @3=25
### DELETE FROM `test01`.`users`
### WHERE
### @1=3
### @2= kitten
### @3=25
### DELETE FROM `test01`.`users`
### WHERE
### @1=4
### @2= kitten
### @3=25
### DELETE FROM `test01`.`users`
### WHERE
### @1=6
### @2= kitten
### @3=25
### DELETE FROM `test01`.`users`
### WHERE
### @1=7
### @2= kitten
### @3=25
### DELETE FROM `test01`.`users`
### WHERE
### @1=8
### @2= kitten
### @3=25
### DELETE FROM `test01`.`users`
### WHERE
### @1=9
### @2= kitten
### @3=25
# at 29604
#180308 13:35:46 server id 223 end_log_pos 29635 CRC32 0x443a1025 Xid = 4813
COMMIT/*!*/;
mysqlbinlog: File –base64-output=decode-rows not found (Errcode: 2 – No such file or directory)
SET @@SESSION.GTID_NEXT= AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Start-positon=29216
Stop-position=29635
6)生成 binlog 日志
flashback —binlogFileNames=/data/mysqldata/mysql-bin.00005 —start-position= 29216—stop-position=29635

默認生成一個 binlog_output_base.flashback 文件
7）恢復
mysqlbinlog —skip-gtids /opt/MyFlash/binary/binlog_output_base.flashbak|mysql -uroot -p

8)查看數據

(root@localhost:mysql.sock) [test01] select * from users;
+—-+———-+—–+
| id | realname | age |
+—-+———-+—–+
| 1 | kitten | 25 |
| 2 | kitten | 25 |
| 3 | kitten | 25 |
| 4 | kitten | 25 |
| 6 | kitten | 25 |
| 7 | kitten | 25 |
| 8 | kitten | 25 |
| 9 | kitten | 25 |
| 13 | kitten | 25 |
| 14 | kitten | 25 |
| 15 | kitten | 25 |
| 16 | kitten | 25 |
| 17 | kitten | 25 |
| 18 | kitten | 25 |
| 19 | kitten | 25 |
| 20 | kitten | 25 |
+——+———-+—–+
確認已經刪除的數據已經恢復

5、案例 2 —測試 updte、delete 恢復

1）修改數據
(root@localhost:mysql.sock) [test01] update users set realname= panxiao ,age=28 where id in (1,2,3);
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3 Changed: 3 Warnings: 0

(root@localhost:mysql.sock) [test01] delete from users where id =8
2）查看當前的數據
(root@localhost:mysql.sock) [test01] select * from users;
+—-+———-+—–+
| id | realname | age |
+—-+———-+—–+
| 1 | panxiao | 28 |
| 2 | panxiao | 28 |
| 3 | panxiao | 28 |
| 4 | kitten | 25 |
| 6 | kitten | 25 |
| 7 | kitten | 25 |
| 9 | kitten | 25 |
| 13 | kitten | 25 |
| 14 | kitten | 25 |
| 15 | kitten | 25 |
| 16 | kitten | 25 |
| 17 | kitten | 25 |
| 18 | kitten | 25 |
| 19 | kitten | 25 |
| 20 | kitten | 25 |
+——+———-+—–+
3?3）生成恢復 sql
flashback –binlogFileNames=/data/mysqldata/mysql-bin.000005 –start-datetime= 2018-03-08 14:13:00 –stop-datetime= 2018-03-08 14:23:00 –databaseNames=test01 –tableNames=users –sqlTypes= UPDATE , DELETE —outBinlogFileNameBase=test01_users
4）查看生成的 binlog
#mysqlbinlog –no-defaults –base64-output=decode-row -vv test01_users.flashback
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLE TION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180306 20:14:47 server id 223 end_log_pos 123 CRC32 0xb78347d8 Start: binlog v 4, server v 5.7.21-log created 180306 20:14:47 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#180308 14:19:18 server id 223 end_log_pos 177 CRC32 0x5fd8b365 Table_map: `test01`.`users` mapped to number 117
# at 177
#180308 14:19:18 server id 223 end_log_pos 232 CRC32 0x608a735a Write_rows: table id 117 flags: STMT_END_F
### INSERT INTO `test01`.`users`
### SET
### @1=8 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2= kitten /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### @3=25 /* INT meta=0 nullable=0 is_null=0 */
# at 232
#180308 14:17:56 server id 223 end_log_pos 286 CRC32 0xf48cb3b5 Table_map: `test01`.`users` mapped to number 117
# at 286
#180308 14:17:56 server id 223 end_log_pos 445 CRC32 0xcd45ef63 Update_rows: table id 117 flags: STMT_END_F
### UPDATE `test01`.`users`
### WHERE
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2= panxiao /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### @3=28 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2= kitten /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### @3=25 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test01`.`users`
### WHERE
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2= panxiao /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### @3=28 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2= kitten /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### @3=25 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test01`.`users`
### WHERE
### @1=3 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2= panxiao /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### @3=28 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=3 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2= kitten /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### @3=25 /* INT meta=0 nullable=0 is_null=0 */
SET @@SESSION.GTID_NEXT= AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
4）恢復 sql
mysqlbinlog –no-defaults test01_users.flashback |mysql -uroot -p
5）查看數據 —已經恢復
(root@localhost:mysql.sock) [test01] select * from users;
+—-+———-+—–+
| id | realname | age |
+—-+———-+—–+
| 1 | kitten | 25 |
| 2 | kitten | 25 |
| 3 | kitten | 25 |
| 4 | kitten | 25 |
| 6 | kitten | 25 |
| 7 | kitten | 25 |
| 8 | kitten | 25 |
| 9 | kitten | 25 |
| 13 | kitten | 25 |
| 14 | kitten | 25 |
| 15 | kitten | 25 |
| 16 | kitten | 25 |
| 17 | kitten | 25 |
| 18 | kitten | 25 |
| 19 | kitten | 25 |
| 20 | kitten | 25 |