系统版本表是 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
参考文档:
https://mariadb.com/kb/en/library/system-versioned-tables/
来源: http://www.linuxidc.com/Linux/2018-08/153669.htm