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

栏目: 数据库 · Mysql · 发布时间: 6年前

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

系统版本表是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条数据,如下图所示:

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

接着把姓名为“张三”,改成“李四”(误更改数据)

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

现在数据已经成功变更,那么我想查看历史数据怎么办呢?非常简单,一条命令搞定。

语法一:查询一小时内的历史数据。

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

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

语法三:查询所有历史数据

SELECT * FROM t1 FOR SYSTEM_TIME ALL;

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

三、恢复历史数据

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

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

MariaDB10.3 系统版本表 有效防止数据丢失 非常简单的恢复完数据,此方法比之前用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分区里。

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

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

可以通过数据字典表,来查看每个分区表的数据轮询时间状态信息。

SELECT PARTITION_DESCRIPTION,TABLE_ROWS FROM

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

table_name='t1';

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

五、删除旧的历史数据

系统版本表存储了所有的历史数据,随着时间的推移,历史版本数据会变得越来越大,那么我们就可以将其最老的历史数据删除。

例:将p0分区删除

ALTER TABLE t1 DROP PARTITION p0;
MariaDB10.3 系统版本表 有效防止数据丢失

六、正确使用姿势

通过上述介绍,我们了解了系统版本表的原理。在高并发写入场景下,势必会带来性能上的损失,所以要用正确的姿势开启该功能。

例:主库是 MySQL 5.6或者MariaDB 10.0/1/2版本,搭建一个新从库MariaDB 10.3,在该从库上转换为系统版本控制表。这样主库上误删或误篡改数据,可以在从库上通过版本控制找回。

MariaDB10.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

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

2)只导出数据:

# mysqldump -S /tmp/mysql3306.sock -uroot -p123456 --single-transaction

--master-data=2 --compact -c -q -t -B test > test_data.sql

参考文档:

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


以上所述就是小编给大家介绍的《MariaDB10.3 系统版本表 有效防止数据丢失》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Rationality for Mortals

Rationality for Mortals

Gerd Gigerenzer / Oxford University Press, USA / 2008-05-02 / USD 65.00

Gerd Gigerenzer's influential work examines the rationality of individuals not from the perspective of logic or probability, but from the point of view of adaptation to the real world of human behavio......一起来看看 《Rationality for Mortals》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具