通过myflash闪回mysql5.6,mysql5.7部分数据
测试环境
centos7.7+mysql5.6.48
centos7.5+mysql5.7.29
测试场景
对一张进行多次修改后,通过mysqlflash闪回某一时间段的update操作
测试表与测试语句
CREATE TABLE testFlashback2
(id
int(11) NOT NULL AUTO_INCREMENT,nameShort
varchar(20) DEFAULT NULL,nameLong
varchar(260) DEFAULT NULL,amount
decimal(19,9) DEFAULT NULL,amountFloat
float DEFAULT NULL,amountDouble
double DEFAULT NULL,createDatetime6
datetime(6) DEFAULT NULL,createDatetime
datetime DEFAULT NULL,createTimestamp
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,nameText
text,nameBlob
blob,nameMedium
mediumtext,
PRIMARY KEY (id
)
) ENGINE=innodb
插入一条记录
insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values(‘aaa’,‘bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb’,10.5,10.6,10.7,‘2017-10-26 10:00:00’,‘2017-10-26 10:00:00’,‘2017-10-26 10:00:00’,‘cccc’,‘dddd’,‘eee’);
将刚才记录的nameshort字段值由bbb修改ccc
update testFlashback2 set nameshort=‘ccc’ where nameshort=‘bbb’
验证过程
./flashback --databaseNames=qjqx --tableNames=testflashback2 --start-datetime=‘2020-06-06 19:50:00’ --stop-datetime=‘2020-06-06 20:02:00’ --sqlTypes=update --binlogFileNames=/data/mysql_13306/logs/mysql-bin.000011 --outBinlogFileNameBase=test
[[email protected] binary]# mysqlbinlog test.flashback |mysql -uroot -p -S /data/mysql_13306/mysql.sock
确认
mysql> select nameshort from testflashback2;
±----------+
| nameshort |
±----------+
| aaa |
| bbb |
±----------+
记录为aaa并没有被覆盖,bbb的记录已经成功闪回
总结
1、myflash可以基于库,表,时间范围,位置范围,sql操作类型生成相应的闪回语句
2、myflash不支持ddl和truncate语句恢复