Mysql闪回

备注：

在实际操作中如果不慎操作了错误的DML语句，如何快速的恢复，利用flashback可以对BINLOG的DML进行反向的解析，下面介绍如何安装及使用

1.安装git，因为要从github上面下载

yum install git

2.下载并安装

git clone https://github.com/Meituan-Dianping/MyFlash.git

yum install libgnomeui-devel -y #安装glib

cd MyFlash/

cat build.sh

sh build.sh

3.查看帮助

[root@db192168084125 MyFlash]# cd binary/

[root@db192168084125 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(,)

--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 ｉｎｓｅｒｔ, ｕｐｄａｔｅ ,ｄｅｌｅｔｅ. 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

--exclude-gtids gtids to skip

4.测试

插入数据

[root@localhost][test]> flush logs;

Query OK, 0 rows affected (0.04 sec)

[root@localhost][test]> show master status;

+---------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------+

| mysql-binlog.000004 | 194 | | | 99d89b20-c969-11e9-a27c-000c29924f35:1-8,

af7883dc-c43b-11e9-b42f-000c29924f35:1-5358804 |

+---------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

[root@localhost][test]> use test;

Database changed

[root@localhost][test]> ｉｎｓｅｒｔ into b ｓｅｌｅｃｔ 1,‘a‘;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

将binlog导出

[root@db192168084125 binary]# ./flashback --binlogFileNames=/usr/local/mysql/data/mysql-binlog.000004 --outBinlogFileNameBase=ｉｎｓｅｒｔ

[root@db192168084125 binary]# ll

总用量 7348

-rwxr-xr-x. 1 root root 82192 8月 29 00:12 flashback

-rw-r--r--. 1 root root 243 8月 29 01:15 ｉｎｓｅｒｔ.flashback

-rwxr-xr-x. 1 root root 7463125 8月 29 00:08 mysqlbinlog20160408

用mysqlbinlog查看，可以查看刚才的ｉｎｓｅｒｔ语句变成的ｄｅｌｅｔｅ语句

[root@db192168084125 bin]# ./mysqlbinlog -vv /software/MyFlash/binary/ｉｎｓｅｒｔ.flashback

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

ｄｅｌｉｍｉｔｅｒ /*!*/;

# at 4

#190829 1:13:48 server id 1 end_log_pos 123 CRC32 0xdc5e3b2e Start: binlog v 4, server v 5.7.26-log created 190829 1:13:48

# Warning: this binlog is either in use or was not closed properly.

BINLOG ‘

TLZmXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AS47Xtw=

‘/*!*/;

# at 123

#190829 1:14:13 server id 1 end_log_pos 170 CRC32 0xae1a43b0 Table_map: `test`.`b` mapped to number 229

# at 170

#190829 1:14:13 server id 1 end_log_pos 212 CRC32 0xd2057157 Delete_rows: table id 229 flags: STMT_END_F

BINLOG ‘

ZbZmXRMBAAAALwAAAKoAAAAAAOUAAAAAAAEABHRlc3QAAWIAAgMPAsgAA7BDGq4=

ZbZmXSABAAAAKgAAANQAAAAAAOUAAAAAAAEAAgAC//wBAAAAAWFXcQXS

‘/*!*/;

### ｄｅｌｅｔｅ FROM `test`.`b`

### WHERE

### @1=1 /* INT meta=0 nullable=1 is_null=0 */

### @2=‘a‘ /* VARSTRING(200) meta=200 nullable=1 is_null=0 */

# at 212

#190829 1:14:13 server id 1 end_log_pos 243 CRC32 0x32a640bb Xid = 223

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/;

ｄｅｌｉｍｉｔｅｒ ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

回滚

./mysqlbinlog --skip-gtids -vv /software/MyFlash/binary/ｉｎｓｅｒｔ.flashback |mysql -uroot -p

[root@db192168084125 bin]# ./mysqlbinlog --skip-gtids -vv /software/MyFlash/binary/ｉｎｓｅｒｔ.flashback |mysql -uroot -p

Enter password: