MariaDB10.3 系統(tǒng)版本表有效預(yù)防數(shù)據(jù)丟失

2024-09-07 22:13:01

字體：大中小

供稿：網(wǎng)友

　　系統(tǒng)版本表是SQL:2011標(biāo)準(zhǔn)中首次引入的功能。系統(tǒng)版本表存儲(chǔ)所有更改的歷史數(shù)據(jù)，而不僅僅是當(dāng)前時(shí)刻有效的數(shù)據(jù)。舉個(gè)例子，同一行數(shù)據(jù)一秒內(nèi)被更改了10次，那么就會(huì)保存10份不同時(shí)間的版本數(shù)據(jù)。就像《源代碼》電影里的平行世界理論一樣，你可以退回任意時(shí)間里。從而有效保障你的數(shù)據(jù)是安全的，DBA手抖或程序BUG引起的數(shù)據(jù)丟失，在MariaDB10.3里已成為過(guò)去。

　　一、創(chuàng)建系統(tǒng)版本表
　　例子：

　　CREATE TABLE `t1` (
　　 `id` int(11) NOT NULL AUTO_INCREMENT,
　　 `name` varchar(100) DEFAULT NULL,
　　 `ts` timestamp(6) GENERATED ALWAYS AS ROW START,
　　 `te` timestamp(6) GENERATED ALWAYS AS ROW END,
　　 PRIMARY KEY (`id`,`te`),
　　 PERIOD FOR SYSTEM_TIME (`ts`, `te`)
　　) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING;
　　注意看紅色字體，這就是新增加的語(yǔ)法，字段ts和te是數(shù)據(jù)變化的起止時(shí)間和結(jié)束時(shí)間。

　　另外用ALTER TABLE更改表結(jié)構(gòu)，語(yǔ)法如下：

　　ALTER TABLE t1 ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
　　              ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
　　              ADD PERIOD FOR SYSTEM_TIME(ts, te),
　　              ADD SYSTEM VERSIONING;
　　二、查詢歷史數(shù)據(jù)
　　這里我們做一個(gè)實(shí)驗(yàn)，首先要插入1條數(shù)據(jù)，

　　接著把姓名為“張三”，改成“李四”（誤更改數(shù)據(jù)）

　　現(xiàn)在數(shù)據(jù)已經(jīng)成功變更，那么我想查看歷史數(shù)據(jù)怎么辦呢？非常簡(jiǎn)單，一條命令搞定。

　　語(yǔ)法一：查詢一小時(shí)內(nèi)的歷史數(shù)據(jù)。
　　SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 HOUR) AND NOW();
　　HOUR:小時(shí)
　　MINUTE:分鐘
　　DAY:天
　　MONTH:月
　　YEAR:年

　　語(yǔ)法二：查詢一段時(shí)間內(nèi)的歷史數(shù)據(jù)
　　SELECT * FROM t1 FOR SYSTEM_TIME FROM '2018-05-15 00:00:00' TO '2018-05-15 14:00:00';

　　語(yǔ)法三：查詢所有歷史數(shù)據(jù)
　　SELECT * FROM t1 FOR SYSTEM_TIME ALL;

　　三、恢復(fù)歷史數(shù)據(jù)
　　現(xiàn)在我們已經(jīng)找到了歷史數(shù)據(jù)“張三”，只需把它導(dǎo)出來(lái)做恢復(fù)即可。

　　SELECT id,name FROM t1 FOR SYSTEM_TIME ALL where id = 1 AND name =
　　'張三' into outfile '/tmp/t1.sql' /
　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
　　FIELDS TERMINATED BY ',' --- 字段的分隔符
　　OPTIONALLY ENCLOSED BY '"' --- 字符串帶雙引號(hào)

　　導(dǎo)入恢復(fù)

　　load data infile '/tmp/t1.sql' replace into table t1 /
　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' /
　　(id,name);

　　非常簡(jiǎn)單的恢復(fù)完數(shù)據(jù)，此方法比之前用mysqlbinlog或自研腳本等工具做閃回效率高得多。

　　四、單獨(dú)存儲(chǔ)歷史數(shù)據(jù)
　　當(dāng)歷史數(shù)據(jù)與當(dāng)前數(shù)據(jù)一起存儲(chǔ)時(shí)，勢(shì)必會(huì)增加表的大小，且當(dāng)前的數(shù)據(jù)查詢：表掃描和索引搜索，將會(huì)花費(fèi)更多的時(shí)間，因?yàn)樾枰^(guò)歷史數(shù)據(jù)。那么我們可以將通過(guò)表分區(qū)將其分開(kāi)、單獨(dú)存儲(chǔ)，以減少版本控制的開(kāi)銷。
　　接上面的例子，執(zhí)行下面的語(yǔ)句：

　　alter table t1
　　 PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH (
　　    PARTITION p0 HISTORY,
　　    PARTITION p1 HISTORY,
　　    PARTITION p2 HISTORY,
　　    PARTITION p3 HISTORY,
　　    PARTITION p4 HISTORY,
　　    PARTITION p5 HISTORY,
　　    PARTITION p6 HISTORY,
　　    PARTITION pcur CURRENT
　　 );
　　意思為：按照月份分割歷史數(shù)據(jù)，今天至一個(gè)月后（2018年6月15日）的歷史數(shù)據(jù)放入p0分區(qū)，次月的歷史數(shù)據(jù)放入p1分區(qū)，依次類推至（2018年12月15日）存p6分區(qū)。當(dāng)前數(shù)據(jù)存儲(chǔ)在pcur分區(qū)里。

　　可以通過(guò)數(shù)據(jù)字典表，來(lái)查看每個(gè)分區(qū)表的數(shù)據(jù)輪詢時(shí)間狀態(tài)信息。

　　SELECT PARTITION_DESCRIPTION,TABLE_ROWS FROM
　　`information_schema`.`PARTITIONS` WHERE table_schema='hcy' AND
　　table_name='t1';

　　五、刪除舊的歷史數(shù)據(jù)
　　系統(tǒng)版本表存儲(chǔ)了所有的歷史數(shù)據(jù)，隨著時(shí)間的推移，歷史版本數(shù)據(jù)會(huì)變得越來(lái)越大，那么我們就可以將其最老的歷史數(shù)據(jù)刪除。
　　例：將p0分區(qū)刪除
　　ALTER TABLE t1 DROP PARTITION p0;

　　六、正確使用姿勢(shì)
　　通過(guò)上述介紹，我們了解了系統(tǒng)版本表的原理。在高并發(fā)寫入場(chǎng)景下，勢(shì)必會(huì)帶來(lái)性能上的損失，所以要用正確的姿勢(shì)開(kāi)啟該功能。

　　例：主庫(kù)是MySQL 5.6或者M(jìn)ariaDB 10.0/1/2版本，搭建一個(gè)新從庫(kù)MariaDB 10.3，在該從庫(kù)上轉(zhuǎn)換為系統(tǒng)版本控制表。這樣主庫(kù)上誤刪或誤篡改數(shù)據(jù)，可以在從庫(kù)上通過(guò)版本控制找回。

　　注：主庫(kù)是低版本，從庫(kù)是高版本，是可以向前兼容binlog格式的。

　　七、注意事項(xiàng)
　　1、參數(shù)system_versioning_alter_history要設(shè)置為KEEP（在my.cnf配置文件里寫死），否則默認(rèn)不能執(zhí)行DDL修改表結(jié)構(gòu)操作。
　　set global system_versioning_alter_history = 'KEEP';

　　注：增加字段時(shí)，要加上after關(guān)鍵字，否則會(huì)在te字段后面，造成同步失敗。例：
　　alter table t1 add column address varchar(500) after name;

　　2、mysqldump工具不會(huì)導(dǎo)出歷史數(shù)據(jù)，所以在做備份時(shí)，可以通過(guò)Percona XtraBackup熱備份工具來(lái)備份物理文件。

　　3、搭建從庫(kù)時(shí)，如果你用mysqldump工具，要先導(dǎo)出表結(jié)構(gòu)文件，再導(dǎo)出數(shù)據(jù)。
　　1）只導(dǎo)出表結(jié)構(gòu)：
　　# mysqldump -S /tmp/mysql3306.sock -uroot -p123456 --single-transaction --compact -c -d -q -B test > ./test_schema.sql

　　導(dǎo)入完表結(jié)構(gòu)后，批量執(zhí)行DDL轉(zhuǎn)換系統(tǒng)版本表，腳本如下（附件里點(diǎn)擊下載）：

　　# cat convert.php
　　<?php

　　$conn=mysqli_connect("10.10.100.11","admin","123456","test","3306") or die("error connecting");
　　mysqli_query($conn,"SET NAMES utf8");

　　$table = "show tables";
　　$result1 = mysqli_query($conn,$table);
　　while($row = mysqli_fetch_array($result1)){
　　    $table_name=$row[0];
　　    echo "$table_name 表正在進(jìn)行轉(zhuǎn)換系統(tǒng)版本表。。。".PHP_EOL;
　　    $convert_table="
　　ALTER TABLE {$table_name} ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
　　              ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
　　              ADD PERIOD FOR SYSTEM_TIME(ts, te),
　　              ADD SYSTEM VERSIONING";
　　    $result2=mysqli_query($conn,$convert_table);
　　    if($result2){
　　            echo '更改表結(jié)構(gòu)成功.'.PHP_EOL;
　　           echo ''.PHP_EOL;
　　    }
　　    else{
　　            echo '更改表結(jié)構(gòu)失敗.'.PHP_EOL;
　　           echo ''.PHP_EOL;
　　    }
　　}

　　mysqli_close($conn);

　　?>
　　注：先安裝php-mysql驅(qū)動(dòng)
　　#yum install php php-mysql -y
　　#php convert.php

　　2)只導(dǎo)出數(shù)據(jù)：

　　# mysqldump -S /tmp/mysql3306.sock -uroot -p123456 --single-transaction
　　--master-data=2 --compact -c -q -t -B test > test_data.sql

（編輯：武林網(wǎng)）

上一篇：php優(yōu)化數(shù)據(jù)庫(kù)的方法有哪些

下一篇：Amoeba達(dá)成數(shù)據(jù)庫(kù)讀寫分離