作者介紹

賀春暘，《MySQL管理之道：性能調優、高可用與監控》第一、二版作者，從事數據庫管理工作多年，曾經任職於中國移動飛信、安卓機鋒網、凡普金科(愛錢進)，致力於MariaDB、MongoDB等開源技術的研究，主要負責數據庫性能調優、監控和架構設計。

系統版本表是SQL:2011標準中首次引入的功能，它存儲所有更改的歷史數據，而不僅僅是當前時刻有效的數據。

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

一、創建系統版本表

例子：

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是數據變化的起止時間和結束時間。

另外用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;

二、查詢歷史數據

這裏我們做一個實驗，首先要插入一條數據，如下圖所示：

接着把姓名爲“張三”，改成“李四”（誤更改數據）：

現在數據已經成功變更，那麼我想查看歷史數據怎麼辦呢？非常簡單，一條命令搞定。

語法一：查詢一小時內的歷史數據。

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

HOUR：小時

MINUTE：分鐘

DAY：天

MONTH：月

YEAR：年

語法二：查詢一段時間內的歷史數據。

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

語法三：查詢所有歷史數據。

SELECT * FROM t1 FOR SYSTEM_TIME ALL;

三、恢復歷史數據

現在我們已經找到了歷史數據“張三”，只需把它導出來做恢復即可。

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

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

四、單獨存儲歷史數據

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

接上面的例子，執行下面的語句：

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

);

意思是：按照月份分割歷史數據，今天至一個月後（2018年6月15日）的歷史數據放入p0分區，次月的歷史數據放入p1分區，依次類推至（2018年12月15日）存p6分區。當前數據存儲在pcur分區裏。

可以通過數據字典表，來查看每個分區表的數據輪詢時間狀態信息。

SELECT PARTITION_DEION,TABLE_ROWS FROM

`information_schema`.`PARTITIONS` WHERE table_schema='hcy' AND

table_name='t1';

五、刪除舊的歷史數據

系統版本表存儲了所有的歷史數據，隨着時間的推移，歷史版本數據會變得越來越大，那麼我們就可以將其最老的歷史數據刪除。

例：將p0分區刪除

ALTER TABLE t1 DROP PARTITION p0;

六、正確使用姿勢

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

例：主庫是MySQL 5.6或者MariaDB 10.0/1/2版本，搭建一個新從庫MariaDB 10.3，在該從庫上轉換爲系統版本控制表。這樣主庫上誤刪或誤篡改數據，可以在從庫上通過版本控制找回。

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

七、注意事項

1、參數system_versioning_alter_history要設置爲KEEP（在my.cnf配置文件裏寫死），否則默認不能執行DDL修改表結構操作。

set global system_versioning_alter_history = 'KEEP';

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

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

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

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

1）只導出表結構：

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

導入完表結構後，批量執行DDL轉換系統版本表，腳本如下（點擊文末【閱讀原文】可下載腳本）：

# cat convert.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 表正在進行轉換系統版本表。。。".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驅動

# yum install php php-mysql -y

# php convert.php

2) 只導出數據：

# 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等操作是無法通過上述方法閃回恢復數據的，切記！

請務必在生產環境，搭建延遲複製從庫，命令如下：

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/

點這裏下載相關腳本

相關文章