MariaDB10.3 系统版本表 有效预防数据丢失
发布时间:2022-07-08 12:13:54 所属栏目:MySql教程 来源:互联网
导读:系统版本表是SQL:2011标准中首次引入的功能。系统版本表存储所有更改的历史数据,而不仅仅是当前时刻有效的数据。举个例子,同一行数据一秒内被更改了10次,那么就会保存10份不同时间的版本数据。就像《源代码》电影里的平行世界理论一样,你可以退回任意
系统版本表是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 * 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 (编辑:大连站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
站长推荐