MySQL中MyFlash如何安装使用

发布时间：2021-11-01 15:54:40 作者：小新
来源：亿速云阅读：146

这篇文章给大家分享的是有关MySQL中MyFlash如何安装使用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

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<10;
(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 |

感谢各位的阅读！关于“MySQL中MyFlash如何安装使用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

MySQL中MyFlash如何安装使用

相关阅读