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

数据库 其他数据库 MariaDB
同一行数据一秒内被更改了10次,那么系统版本表就会保存10份不同时间的版本数据。就像电影《源代码》里的平行世界理论一样,你可以退回任意时间里,从而有效保障你的数据是安全的。也就是说,DBA手抖或是程序BUG引起的数据丢失,在MariaDB 10.3里已然成为过去。

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

系统版本表是SQL:2011标准中***引入的功能,它存储所有更改的历史数据,而不仅仅是当前时刻有效的数据。

举个例子,同一行数据一秒内被更改了10次,那么系统版本表就会保存10份不同时间的版本数据。就像电影《源代码》里的平行世界理论一样,你可以退回任意时间里,从而有效保障你的数据是安全的。也就是说,DBA手抖或是程序BUG引起的数据丢失,在MariaDB 10.3里已然成为过去。

一、创建系统版本表

例子: 

  1. CREATE TABLE `t1` (  
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,  
  3.   `namevarchar(100) DEFAULT NULL 
  4.   `ts` timestamp(6) GENERATED ALWAYS AS ROW START,  
  5.   `te` timestamp(6) GENERATED ALWAYS AS ROW END 
  6.   PRIMARY KEY (`id`,`te`),  
  7.   PERIOD FOR SYSTEM_TIME (`ts`, `te`)  
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING; 

注意看红色字体,这就是新增加的语法,字段ts和te是数据变化的起止时间和结束时间。

另外用ALTER TABLE更改表结构,语法如下: 

  1. ALTER TABLE t1 ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,  
  2.                            ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END 
  3.                            ADD PERIOD FOR SYSTEM_TIME(ts, te),  
  4.                            ADD SYSTEM VERSIONING; 

二、查询历史数据

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

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

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

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

  1. SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 HOURAND NOW(); 

HOUR:小时

MINUTE:分钟

DAY:天

MONTH:月

YEAR:年

语法二:查询一段时间内的历史数据。 

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

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

  1. SELECT * FROM t1 FOR SYSTEM_TIME ALL

三、恢复历史数据

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

  1. SELECT id,name FROM t1 FOR SYSTEM_TIME ALL where id = 1 AND name =   
  2. '张三' into outfile '/tmp/t1.sql' \  
  3. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

FIELDS TERMINATED BY ',' —— 字段的分隔符

OPTIONALLY ENCLOSED BY '"' —— 字符串带双引号 

导入恢复。 

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

非常简单地恢复完数据,此方法比之前用mysqlbinlog或自研脚本等工具做闪回效率高得多。

四、单独存储历史数据

当历史数据与当前数据一起存储时,势必会增加表的大小,且当前的数据查询:表扫描和索引搜索,将会花费更多时间,因为需要跳过历史数据。那么我们可以通过表分区将其分开、单独存储,以减少版本控制的开销。 

接上面的例子,执行下面的语句: 

  1. alter table t1   
  2.   PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH (  
  3.     PARTITION p0 HISTORY,  
  4.     PARTITION p1 HISTORY,  
  5.     PARTITION p2 HISTORY,  
  6.     PARTITION p3 HISTORY,  
  7.     PARTITION p4 HISTORY,  
  8.     PARTITION p5 HISTORY,  
  9.     PARTITION p6 HISTORY,  
  10.     PARTITION pcur CURRENT  
  11.   ); 

意思是:按照月份分割历史数据,今天至一个月后(2018年6月15日)的历史数据放入p0分区,次月的历史数据放入p1分区,依次类推至(2018年12月15日)存p6分区。当前数据存储在pcur分区里。

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

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

五、删除旧的历史数据

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

例:将p0分区删除 

  1. 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修改表结构操作。 

  1. set global system_versioning_alter_history = 'KEEP'

注:增加字段时,要加上after关键字,否则会在te字段后面,造成同步失败。例: 

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

2、mysqldump工具不会导出历史数据,所以在做备份时,可以通过Percona XtraBackup热备份工具来备份物理文件。

3、搭建从库时,如果你用mysqldump工具,要先导出表结构文件,再导出数据。

1)只导出表结构: 

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

导入完表结构后,批量执行DDL转换系统版本表,脚本如下(点击文末【阅读原文】可下载脚本): 

  1. # cat convert.php  
  2.  
  3. <?php    
  4.  
  5. $conn=mysqli_connect("10.10.100.11","admin","123456","test","3306"or die("error connecting");  
  6. mysqli_query($conn,"SET NAMES utf8");  
  7.  
  8. $table = "show tables" 
  9. $result1 = mysqli_query($conn,$table);  
  10. while($row = mysqli_fetch_array($result1)){  
  11.     $table_name=$row[0];  
  12.     echo "$table_name 表正在进行转换系统版本表。。。".PHP_EOL;  
  13.     $convert_table="  
  14. ALTER TABLE {$table_name} ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,  
  15.               ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END 
  16.               ADD PERIOD FOR SYSTEM_TIME(ts, te),  
  17.               ADD SYSTEM VERSIONING";  
  18.     $result2=mysqli_query($conn,$convert_table);  
  19.     if($result2){  
  20.             echo '更改表结构成功.'.PHP_EOL;  
  21.         echo ''.PHP_EOL;  
  22.     }  
  23.     else 
  24.             echo '更改表结构失败.'.PHP_EOL;  
  25.         echo ''.PHP_EOL;  
  26.     }  
  27.  
  28.  
  29. mysqli_close($conn);  
  30.  
  31. ?> 

注:先安装php-mysql驱动 

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

2) 只导出数据: 

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

4、对于DROP DATABASE和DROP TABLE,以及TRUNCATE TABLE等操作是无法通过上述方法闪回恢复数据的,切记!

请务必在生产环境,搭建延迟复制从库,命令如下: 

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

注:单位秒,43200秒等于12小时。

参考文档:

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

责任编辑:庞桂玉 来源: DBAplus社群
相关推荐

2009-12-17 16:38:20

正版免费词典

2018-09-21 11:17:54

数据库

2010-10-14 16:41:45

2017-07-14 15:49:05

MongoDB误删表恢复步骤

2015-05-29 09:01:48

2011-06-17 09:20:35

MariaDB

2010-08-23 09:20:57

2023-07-26 13:17:04

数据表误删流程

2022-03-22 18:28:04

网络大学跑路

2021-12-22 10:29:23

Prometheus elasticsear运维

2022-03-08 13:14:32

数据湖大数据

2018-12-11 11:13:25

Linux系统恢复

2020-08-05 11:50:47

删库MySQL数据库

2014-03-10 17:56:33

浪潮大数据定制机

2011-09-22 09:15:49

MariaDB数据库

2019-01-02 10:32:56

Linux系统文件运维

2015-06-23 11:34:39

2020-04-13 08:33:39

高并发秒杀系统

2016-10-31 14:09:38

戴尔DIY服务器

2018-09-07 23:23:55

MYSQL开源代码
点赞
收藏

51CTO技术栈公众号