测试环境
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
[root@lineqi 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语句恢复