mysql MyFlash使用
发布时间：2022-04-04 10:24:54 所属栏目：MySql教程 来源：互联网
导读：mysql MyFlash使用： 一.安装MyFlash 1.安装条件 binlog_format=ROW binlog_row_image=FULL 仅支持5.6与5.7，并且只能回滚DML 2.安装 unzip MyFlash.zip yum -y install glib2* cd /data/MyFlash/MyFlash-master gcc -w `pkg-config --cflags --libs glib-
|
mysql MyFlash使用： 一.安装MyFlash 1.安装条件 binlog_format=ROW binlog_row_image=FULL 仅支持5.6与5.7，并且只能回滚DML 2.安装 unzip MyFlash.zip yum -y install glib2* cd /data/MyFlash/MyFlash-master gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback *没有报错，证明安装成功 二.恢复场景 1.回滚整个文件 ./flashback --binlogFileNames=haha.000041 mysqlbinlog binlog_output_base.flashback | mysql -h -u -p 2.回滚某个表的DML语句 -删除数据 mysql> show tables; +---------------+ | Tables_in_wwj | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> select * from t1; +----+------+ | id | name | +----+------+ | 5 | haha | | 6 | mxt3 | | 7 | mxt4 | +----+------+ 3 rows in set (0.00 sec) mysql> delete from t1; Query OK, 3 rows affected (0.02 sec) mysql> select * from t1; Empty set (0.00 sec) -生成恢复文件(回滚t1表的delete操作) cd /data/MyFlash/MyFlash-master/binary # ./flashback --databaseNames='wwj' --tableNames='t1' --sqlTypes='DELETE' --binlogFileNames=/home/mysql3307/mysql3307/mysql-bin.000001 -查看文件 # /usr/local/mysql/bin/mysqlbinlog -vv binlog_output_base.flashback -恢复 # /usr/local/mysql/bin/mysqlbinlog binlog_output_base.flashback|/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -p 恢复完成 mysql> select * from t1; +----+------+ | id | name | +----+------+ | 5 | haha | | 6 | mxt3 | | 7 | mxt4 | +----+------+ 3 rows in set (0.00 sec) 3.恢复一段时间的binlog -生成数据 [root@mysql5 mysql3307]# date Thu Mar 29 05:41:00 CST 2018 insert into wwj.t3 values(4,'mxt2'); insert into wwj.t3 values(5,'mxt3'); insert into wwj.t3 values(6,'mxt4'); [root@mysql5 mysql3307]# date Thu Mar 29 05:41:37 CST 2018 delete from wwj.t3; [root@mysql5 mysql3307]# date Thu Mar 29 05:42:57 CST 2018 - 回滚2018-03-29 05:41:37~2018-03-29 05:42:57 之间的操作 - 查看binlog # /usr/local/mysql/bin/mysqlbinlog --start-datetime='2018-03-29 05:41:37' --stop-datetime='2018-03-29 05:42:57' mysql-bin.000001 --base64-output=decode-rows -v - 生成恢复文件 ./flashback --databaseNames='wwj' --start-datetime='2018-03-29 05:41:37' --stop-datetime='2018-03-29 05:42:57' --binlogFileNames=/home/mysql3307/mysql3307/mysql-bin.000001 - 查看回滚文件 # /usr/local/mysql/bin/mysqlbinlog -vv binlog_output_base.flashback - 执行回滚 /usr/local/mysql/bin/mysqlbinlog binlog_output_base.flashback|/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -p - 查看恢复结果 mysql> select * from wwj.t3; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 8 Current database: *** NONE *** +----+------+ | id | name | +----+------+ | 4 | mxt2 | | 5 | mxt3 | | 6 | mxt4 | +----+------+ 3.恢复一段position的binlog文件 - 生成数据 insert into wwj.t3 values(4,'mxt2'); insert into wwj.t3 values(5,'mxt3'); insert into wwj.t3 values(6,'mxt4'); insert into wwj.t2 values(4,'mxt2'); insert into wwj.t2 values(5,'mxt3'); insert into wwj.t2 values(6,'mxt4'); insert into wwj.t1 values(4,'mxt2'); insert into wwj.t1 values(5,'mxt3'); insert into wwj.t1 values(6,'mxt4'); mysql> delete from wwj.t3; Query OK, 3 rows affected (0.01 sec) mysql> delete from wwj.t2; Query OK, 3 rows affected (0.01 sec) mysql> delete from wwj.t1; Query OK, 3 rows affected (0.00 sec) -查看binlog /usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 --base64-output=decode-rows -v -确认恢复 mysql-bin.000001 position 1823~2487，生成恢复文件 # ./flashback --databaseNames='wwj' --start-position=1823 --stop-position=2487 --binlogFileNames=/home/mysql3307/mysql3307/mysql-bin.000001 - 查看回滚文件 # /usr/local/mysql/bin/mysqlbinlog -vv binlog_output_base.flashback - 执行回滚 /usr/local/mysql/bin/mysqlbinlog binlog_output_base.flashback|/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -p -查看数据 mysql> select * from wwj.t1; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 17 Current database: *** NONE *** +----+------+ | id | name | +----+------+ | 4 | mxt2 | | 7 | mxt2 | | 5 | mxt3 | | 8 | mxt3 | | 6 | mxt4 | | 9 | mxt4 | +----+------+ 6 rows in set (0.00 sec) mysql> select * from wwj.t2; +----+------+ | id | name | +----+------+ | 4 | mxt2 | | 5 | mxt3 | | 6 | mxt4 | +----+------+ 3 rows in set (0.00 sec) mysql> select * from wwj.t3; +----+------+ | id | name | +----+------+ | 4 | mxt2 | | 5 | mxt3 | | 6 | mxt4 | +----+------+ 3 rows in set (0.00 sec) 三.相关参数 点击(此处)折叠或打开 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 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 --exclude-gtids gtids to skip （编辑：丽水站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
