当前位置: 首页 > 科技观察

MariaDB10.3率先推出系统版表,误删数据不用跑路!

时间:2023-03-12 22:08:10 科技观察

系统版本表是SQL:2011标准中第一个引入的功能,它存储的是所有变化的历史数据,而不仅仅是当前时刻有效的数据。比如同一行数据在一秒内变化了10次,那么系统版本表会保存10份不同时间的版本数据。就像电影《源代码》里的平行世界理论一样,你可以随时回到过去,让你的数据安全得到有效保障。也就是说,由于DBA手抖或者程序bug导致的数据丢失在MariaDB10.3中已经成为历史。1、创建系统版本表示例:CREATETABLE`t1`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(100)DEFAULTNULL,`ts`timestamp(6)GENERATEDALWAYSASROWSTART,`te`timestamp(6)GENERATEDALWAYSASROWEND,PRIMARYKEY(`id`,`te`),PERIODFORSYSTEM_TIME(`ts`,`te`))ENGINE=InnoDBDEFAULTCHARSET=utf8WITHSYSTEMVERSIONING;注意红色字体,这是新增加的语法,字段ts和te分别是数据变化的起止时间和EndTime。此外,使用ALTERTABLE更改表结构。语法如下:ALTERTABLEt1ADDCOLUMNtsTIMESTAMP(6)GENERATEDALWAYSASROWSTART,ADDCOLUMNteTIMESTAMP(6)GENERATEDALWAYSASROWEND,ADDPERIODFORSYSTEM_TIME(ts,te),ADDSYSTEMVERSIONING;如图:然后把名字从“张三”改成“李四”(误改数据):现在数据改成功了,那要查看历史数据怎么办呢?很简单,一条命令搞定。语法一:查询一小时内的历史数据。SELECT*FROMtFORSYSTEM_TIMEBETWEEN(NOW()-INTERVAL1HOUR)ANDNOW();HOUR:小时MINUTE:分钟DAY:日MONTH:月YEAR:年语法二:查询一段时间内的历史数据。SELECT*FROMt1FORSYSTEM_TIMEFROM'2018-05-1500:00:00'TO'2018-05-1514:00:00';语法3:查询所有历史数据。选择*FROMt1FORSYSTEM_TIMEALL;3、恢复历史数据现在我们已经找到了“张三”这个历史数据,我们只需要将其导出进行恢复即可。SELECTid,nameFROMt1FORSYSTEM_TIMEALLwhereid=1ANDname='张三'intooutfile'/tmp/t1.sql'\FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"';FIELDSTERMINATEDBY','——字段分隔符OPTIONALLYENCLOSEDBY'"'——字符串导入用双引号恢复。loaddatainfile'/tmp/t1.sql'replaceintotablet1\FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'\(id,name);恢复数据非常简单,这种方法比使用mysqlbinlog等工具闪回或者自检更高效以前开发的脚本多得多四、历史数据分开存储历史数据和当前数据存储在一起时,表的大小势必会增大,当前数据查询:表扫描和索引查找会耗费更多的时间,因为历史需要被跳过的数据,那么我们可以通过分表的方式将它们分开单独存储,减少版本控制的开销。接上面的例子,执行下面的语句:altertablet1PARTITIONBYSYSTEM_TIMEINTERVAL1MONTH(PARTITIONp0HISTORY,PARTITIONp1HISTORY,PARTITIONp2HISTORY,PARTITIONp3HISTORY,PARTITIONp4HISTORY,PARTITIONp5HISTORY,PARTITIONp6HISTORY,PARTITIONpcurCURRENT);意思是:按照月份分割历史数据,今天至一个月后(2018年6月15日)intothep0partition,thenextmonth’shistoricaldataintothep1partition,andsoonuntil(December15,2018)intothep6partition.Thecurrentdataisstoredinthepcurpartition.Youcanviewthedatapollingtimestatusinformationofeachpartitiontablethroughthedatadictionarytable.SELECTPARTITION_DESCRIPTION,TABLE_ROWSFROM`information_schema`.`PARTITIONS`WHEREtable_schema='hcy'ANDtable_name='t1';5.DeleteoldhistoricaldataThesystemversiontablestoresallhistoricaldata,andastimegoesby,thehistoricalversiondatawillbecomeBiggerandbigger,thenwecandeleteitsoldesthistoricaldata.Example:deletethep0partitionALTERTABLEt1DROPPARTITIONp0;6.CorrectusepostureThroughtheaboveintroduction,weunderstandtheprincipleofthesystemversiontable.However,inahigh-concurrencywritingscenario,itwillinevitablyleadtoalossinperformance,soyoumustusethecorrectposturetoenablethisfunction.Example:ThemainlibraryisMySQL5.6orMariaDB10.0/1/2version,buildanewslavelibraryMariaDB10.3,andconvertittothesystemversioncontroltableontheslavelibrary.Inthisway,accidentallydeletedortampereddataonthemasterlibrarycanberetrievedthroughversioncontrolontheslavelibrary.Note:Themainlibraryisalowerversion,andtheslavelibraryisahigherversion,whichisforwardcompatiblewiththebinlogformat.7.Precautions1.Theparametersystem_versioning_alter_historyshouldbesettoKEEP(hardcodedinthemy.cnfconfigurationfile),otherwisetheDDLoperationtomodifythetablestructurecannotbeperformedbydefault.setglobalsystem_versioning_alter_history='KEEP';Note:Whenaddingafield,addtheafterkeyword,otherwiseitwillbebehindthetefield,causingsynchronizationfailure.示例:altertablet1addcolumnaddressvarchar(500)aftername;2、mysqldump工具不会导出历史数据,所以备份时可以使用PerconaXtraBackup热备份工具备份物理文件。3、建从库时,如果使用mysqldump工具,必须先导出表结构文件,再导出数据。1)只导出表结构:#mysqldump-S/tmp/mysql3306.sock-uroot-p123456--single-transaction--compact-c-d-q-Btest>./test_schema.sql导入表结构后,执行DDL转换系统批量版本表,脚本如下(点击文末【阅读原文】下载脚本):#catconvert.php注意:安装php-mysql驱动优先#yuminstallphpphp-mysql-y#phpconvert.php2)仅导出数据:#mysqldump-S/tmp/mysql3306.sock-uroot-p123456--single-transaction--master-data=2--compact-c-q-t-btest>test_data.sql4,对于DROPDATABASE、DROPTABLE、TRUNCATETABLE等操作,是不可能通过上述方法进行闪回和恢复数据的,切记!请务必在生产环境搭建延迟复制从库,命令如下:shell>perl/usr/local/bin/pt-slave-delay-S/tmp/mysql.sock--userroot--password123456\--delay43200--log/root/delay.log--daemonize注意:单位是秒,43200秒等于12小时。参考文档:https://mariadb.com/kb/en/library/system-versioned-tables/