Shulou(Shulou.com)05/31 Report--

This article is about how to install and use MyFlash in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Installation and use of Myflash

1. Environmental description

1) centos 7.x

2) mysql5.7.21 restricts the binlog format to be row and binlog_row_image=full

Only DML rollback is supported (insert, delete, updte)

Operands test01 library, users table

3) Myflash

2. Install Myflash

1) install dependency packages

Yum install gcc* pkg-config glib2 libgnomeui-devel-y

2) installation

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

Display the help documentation to indicate that the installation was successful

3) set environment variables

Vim / etc/profile

Add the last line

Alias flashback= "/ opt/MyFlash/binary/flashback"

Source / etc/profile

3. Use:? The following parameters can be combined arbitrarily.

1.databaseNames specifies the database name that needs to be rolled back. Multiple databases can be separated by ",". If you do not specify this parameter, it is equivalent to specifying all databases. 2.tableNames? Specify the table name that needs to be rolled back. Multiple tables can be separated by ",". If you do not specify this parameter, it is equivalent to specifying all tables.

3.start-position? Specify the location where the rollback begins. If not specified, rollback from the beginning of the file. Please specify the correct and valid location, otherwise you cannot roll back

4.stop-position? Specify the location where the rollback ends. If not specified, roll back to the end of the file. Please specify the correct and valid location, otherwise you cannot roll back

5.start-datetime? Specifies the start time of the rollback. Note that the format must be Y-%m-%d H:%M:%S. If not specified, there is no time limit

6.stop-datetime? Specifies the end time of the rollback. Note that the format must be Y-%m-%d H:%M:%S. If not specified, there is no time limit

7.sqlTypes? Specifies the sql type that needs to be rolled back. Currently, the supported filter types are INSERT, UPDATE and DELETE. Multiple types can be separated by ",".

8.maxSplitSize? Once this parameter is specified, the file is segmented in a fixed size (in M), and the filter condition is valid, but no rollback operation is performed. This parameter is mainly used to cut large binlog files to prevent the binlog size of a single application from being too large and causing pressure on the line.

9.binlogFileNames? Specify the binlog file that needs to be rolled back. Currently, only a single file is supported. Multiple file support will be added later.

10.outBinlogFileNameBase? Specifies the output binlog file prefix, if not specified, defaults to binlog_output_base.flashback

11.logLevel? For developers only. The default level is error. Do not modify this level in the production environment, otherwise there will be too much output.

12.include-gtids? Specify the gtid that needs to be rolled back, supporting both single and scope forms of gtid.

13.exclude-gtids? Specify the gtid that does not need to be rolled back, using the same as include-gtids

4. Case 1

1) create libraries and tables

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 'Real name'

-> age int not null default '20' comment' age'

-> primary key (id)

->) engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci comment 'Test user Table'

Query OK, 0 rows affected (0.60 sec)

2) create data

(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.05sec)

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.05sec)

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 data

Delete from user where idselect

-> * 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) check binlog to confirm 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

# 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

# @ 1room1

# @ 2 thanks kitten.'

# @ 3x25

# DELETE FROM `test01`.`users`

# WHERE

# @ 1room2

# @ 2 thanks kitten.'

# @ 3x25

# DELETE FROM `test01`.`users`

# WHERE

# @ 1y3

# @ 2 thanks kitten.'

# @ 3x25

# DELETE FROM `test01`.`users`

# WHERE

# @ 1mm 4

# @ 2 thanks kitten.'

# @ 3x25

# DELETE FROM `test01`.`users`

# WHERE

# @ 1: 6

# @ 2 thanks kitten.'

# @ 3x25

# DELETE FROM `test01`.`users`

# WHERE

# @ 12007

# @ 2 thanks kitten.'

# @ 3x25

# DELETE FROM `test01`.`users`

# WHERE

# @ 1x8

# @ 2 thanks kitten.'

# @ 3x25

# DELETE FROM `test01`.`users`

# WHERE

# @ 1y9

# @ 2 thanks kitten.'

# @ 3x25

# at 29604

# 180308 13:35:46 server id 223 end_log_pos 29635 CRC32 0x443a1025 Xid = 4813

Com _ MIT _ blank /

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) generate binlog log

Flashback-binlogFileNames=/data/mysqldata/mysql-bin.00005-start-position= 29216-stop-position=29635

Generate a binlog_output_base.flashback file by default

7) restore

Mysqlbinlog-skip-gtids / opt/MyFlash/binary/binlog_output_base.flashbak | mysql-uroot-p

8) View data

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

+-+

Confirm that the deleted data has been restored

5. Case 2-Test updte, delete recovery

1) modify data

(root@localhost:mysql.sock) [test01] > update users set realname='panxiao',age=28 where id in (1pm 2pm 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) View current data

(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) generate recovery 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) View the generated 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.

ROLLBACKUPUBERBACKUBUBUBUR

# at 123

# 180308 14:19:18 server id 223 end_log_pos 177CRC32 0x5fd8b365 Table_map: `test01`.`users` mapped to number

# 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

# @ 1room8 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2percent meta=80 nullable=0 is_null=0 / * VARSTRING (80) Kittens /

# @ 320th 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

# 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

# @ 1room1 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2 VARSTRING panxiao / * VARSTRING (80) panxiao * /

# @ 334 / 28 / * INT meta=0 nullable=0 is_null=0 * /

# SET

# @ 1room1 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2percent meta=80 nullable=0 is_null=0 / * VARSTRING (80) Kittens /

# @ 320th 25 / * INT meta=0 nullable=0 is_null=0 * /

# UPDATE `test01`.`users`

# WHERE

# @ 1room2 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2 VARSTRING panxiao / * VARSTRING (80) panxiao * /

# @ 334 / 28 / * INT meta=0 nullable=0 is_null=0 * /

# SET

# @ 1room2 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2percent meta=80 nullable=0 is_null=0 / * VARSTRING (80) Kittens /

# @ 320th 25 / * INT meta=0 nullable=0 is_null=0 * /

# UPDATE `test01`.`users`

# WHERE

# @ 1room3 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2 VARSTRING panxiao / * VARSTRING (80) panxiao * /

# @ 334 / 28 / * INT meta=0 nullable=0 is_null=0 * /

# SET

# @ 1room3 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 2percent meta=80 nullable=0 is_null=0 / * VARSTRING (80) Kittens /

# @ 320th 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) restore sql

Mysqlbinlog-- no-defaults test01_users.flashback | mysql-uroot-p

5) View data-restored

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

Thank you for reading! This is the end of the article on "how to install and use MyFlash in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.