MariaDB10.3 系统版本表 有效防止数据丢失

绝代码农

　　系统版本表是SQL:2011标准中首次引入的功能。系统版本表存储所有更改的历史数据，而不仅仅是当前时刻有效的数据。举个例子，同一行数据一秒内被更改了10次，那么就会保存10份不同时间的版本数据。就像《源代码》电影里的平行世界理论一样，你可以退回任意时间里。从而有效保障你的数据是安全的，DBA手抖或程序BUG引起的数据丢失，在MariaDB10.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;

二、查询历史数据

　　这里我们做一个实验，首先要插入1条数据，如下图所示：


　　现在数据已经成功变更，那么我想查看历史数据怎么办呢？非常简单，一条命令搞定。
　　语法一：查询一小时内的历史数据。
SELECT <em> FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 HOUR) AND NOW();　　HOUR:小时
　　MINUTE:分钟
　　DAY:天
　　MONTH:月
　　YEAR:年
　　语法二：查询一段时间内的历史数据
SELECT </em> FROM t1 FOR SYSTEM_TIME FROM '2018-05-15 00:00:00' TO '2018-05-15 14:00:00';

三、恢复历史数据

　　现在我们已经找到了历史数据“张三”，只需把它导出来做恢复即可。

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

　　

五、删除旧的历史数据

　　系统版本表存储了所有的历史数据，随着时间的推移，历史版本数据会变得越来越大，那么我们就可以将其最老的历史数据删除。
　　例：将p0分区删除
ALTER TABLE t1 DROP PARTITION p0;

　　注：主库是低版本，从库是高版本，是可以向前兼容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