系統(tǒng)版本表是SQL:2011標準中***引入的功能，它存儲所有更改的歷史數(shù)據(jù)，而不僅僅是當前時刻有效的數(shù)據(jù)。

舉個例子，同一行數(shù)據(jù)一秒內(nèi)被更改了10次，那么系統(tǒng)版本表就會保存10份不同時間的版本數(shù)據(jù)。就像電影《源代碼》里的平行世界理論一樣，你可以退回任意時間里，從而有效保障你的數(shù)據(jù)是安全的。也就是說，DBA手抖或是程序BUG引起的數(shù)據(jù)丟失，在MariaDB 10.3里已然成為過去。

一、創(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; 

注意看紅色字體，這就是新增加的語法，字段ts和te是數(shù)據(jù)變化的起止時間和結束時間。

另外用ALTER TABLE更改表結構，語法如下： 

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ù)

這里我們做一個實驗，首先要插入一條數(shù)據(jù)，如下圖所示：

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

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

語法一：查詢一小時內(nèi)的歷史數(shù)據(jù)。 

SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 HOUR) AND NOW(); 

HOUR：小時

MINUTE：分鐘

DAY：天

MONTH：月

YEAR：年

語法二：查詢一段時間內(nèi)的歷史數(shù)據(jù)。 

SELECT * FROM t1 FOR SYSTEM_TIME FROM '2018-05-15 00:00:00' TO '2018-05-15 14:00:00'; 

語法三：查詢所有歷史數(shù)據(jù)。 

SELECT * FROM t1 FOR SYSTEM_TIME ALL; 

三、恢復歷史數(shù)據(jù)

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

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 '"' —— 字符串帶雙引號 

導入恢復。 

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

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

四、單獨存儲歷史數(shù)據(jù)

當歷史數(shù)據(jù)與當前數(shù)據(jù)一起存儲時，勢必會增加表的大小，且當前的數(shù)據(jù)查詢：表掃描和索引搜索，將會花費更多時間，因為需要跳過歷史數(shù)據(jù)。那么我們可以通過表分區(qū)將其分開、單獨存儲，以減少版本控制的開銷。 

接上面的例子，執(zhí)行下面的語句： 

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ù)，今天至一個月后（2018年6月15日）的歷史數(shù)據(jù)放入p0分區(qū)，次月的歷史數(shù)據(jù)放入p1分區(qū)，依次類推至（2018年12月15日）存p6分區(qū)。當前數(shù)據(jù)存儲在pcur分區(qū)里。

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

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

五、刪除舊的歷史數(shù)據(jù)

系統(tǒng)版本表存儲了所有的歷史數(shù)據(jù)，隨著時間的推移，歷史版本數(shù)據(jù)會變得越來越大，那么我們就可以將其最老的歷史數(shù)據(jù)刪除。

例：將p0分區(qū)刪除 

ALTER TABLE t1 DROP PARTITION p0; 

六、正確使用姿勢

通過上述介紹，我們了解了系統(tǒng)版本表的原理。但在高并發(fā)寫入場景下，勢必會帶來性能上的損失，所以要用正確的姿勢開啟該功能。

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

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

七、注意事項

1、參數(shù)system_versioning_alter_history要設置為KEEP（在my.cnf配置文件里寫死），否則默認不能執(zhí)行DDL修改表結構操作。 

set global system_versioning_alter_history = 'KEEP'; 

注：增加字段時，要加上after關鍵字，否則會在te字段后面，造成同步失敗。例： 

alter table t1 add column address varchar(500) after name; 

2、mysqldump工具不會導出歷史數(shù)據(jù)，所以在做備份時，可以通過Percona XtraBackup熱備份工具來備份物理文件。

3、搭建從庫時，如果你用mysqldump工具，要先導出表結構文件，再導出數(shù)據(jù)。

1）只導出表結構： 

# mysqldump -S /tmp/mysql3306.sock -uroot -p123456 --single-transaction --compact -c -d -q -B test > ./test_schema.sql 

導入完表結構后，批量執(zhí)行DDL轉(zhuǎn)換系統(tǒng)版本表，腳本如下（點擊文末【閱讀原文】可下載腳本）： 

# 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 表正在進行轉(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 '更改表結構成功.'.PHP_EOL;  
        echo ''.PHP_EOL;  
    }  
    else{  
            echo '更改表結構失敗.'.PHP_EOL;  
        echo ''.PHP_EOL;  
    }  
}  
 
mysqli_close($conn);  
 
?> 

注：先安裝php-mysql驅(qū)動 

# yum install php php-mysql -y  
# php convert.php  

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

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

4、對于DROP DATABASE和DROP TABLE，以及TRUNCATE TABLE等操作是無法通過上述方法閃回恢復數(shù)據(jù)的，切記！

請務必在生產(chǎn)環(huán)境，搭建延遲復制從庫，命令如下： 

shell > perl /usr/local/bin/pt-slave-delay -S /tmp/mysql.sock --user root --password 123456  \  
--delay 43200 --log /root/delay.log --daemonize 

注：單位秒，43200秒等于12小時。

參考文檔：

https://mariadb.com/kb/en/library/system-versioned-tables/