MariaDB 10.3首推系统版本表，误删数据不用跑路了！

系统版本表是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_DESCRIPTION,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  
 
<?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/