Shulou(Shulou.com)06/01 Report--

The system version table is the first feature introduced in the SQL:2011 standard. The system version table stores historical data for all changes, not just data that is valid at the current time. For example, if the same row of data is changed 10 times in a second, 10 versions of the data at different times will be saved. Like the parallel universe theory in the Source Code movie, you can go back at any time. In order to effectively ensure that your data is secure, data loss caused by DBA handshake or program BUG has become a thing of the past in MariaDB10.3.

First, create a system version table

Example:

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

Notice the red font, which is the newly added syntax, and the fields ts and te are the start and end time of the data change.

In addition, change the table structure with ALTER TABLE. The syntax is as follows:

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; II, query historical data

Here we do an experiment, first insert a piece of data, as shown in the following figure:

Then change the name to "Zhang San" to "Li Si" (mistakenly change the data)

Now that the data has been successfully changed, what if I want to view the historical data? It's very simple. One order.

Syntax 1: query historical data within an hour.

SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW ()-INTERVAL 1 HOUR) AND NOW ()

HOUR: hour

MINUTE: minutes

DAY: God

MONTH: month

YEAR: year

Syntax 2: query historical data over a period of time

SELECT * FROM T1 FOR SYSTEM_TIME FROM '2018-05-15 00 TO' 2018-05-15 14 FOR SYSTEM_TIME FROM'

Syntax 3: query all historical data

SELECT * FROM T1 FOR SYSTEM_TIME ALL

III. Restore historical data

Now that we have found the historical data "Zhang San", we just need to export it for recovery.

SELECT id,name FROM T1 FOR SYSTEM_TIME ALL where id = 1 AND name = 'Zhang San' into outfile'/ tmp/t1.sql'\ FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'

FIELDS TERMINATED BY','- the delimiter of the field

OPTIONALLY ENCLOSED BY'"'- string with double quotation marks

Import recovery

Load data infile'/ tmp/t1.sql' replace into table T1\ FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'\ (id,name)

It is very simple to recover data, and this method is much more efficient than using tools such as mysqlbinlog or self-developed scripts to do flashbacks.

Fourth, store historical data separately

When historical data is stored with current data, the size of the table is bound to increase, and current data queries, table scans and index searches, will take more time because historical data needs to be skipped. Then we can partition the tables separately and store them separately to reduce the overhead of versioning.

Follow the above example and execute the following statement:

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)

The historical data is divided according to the month, the historical data from today to one month later (June 15, 2018) is put into the p0 partition, the historical data of the next month is put into the p1 partition, and so on to the p6 partition (December 15, 2018). The current data is stored in the pcur partition.

You can view the data polling time status information for each partition table through the data dictionary table.

SELECT PARTITION_DESCRIPTION,TABLE_ROWS FROM `information_ schema`.`PARTITIONS` WHERE table_schema='hcy' AND table_name='t1'

Delete old historical data

The system version table stores all the historical data, and as time goes on, the historical data becomes larger and larger, so we can delete the oldest historical data.

Example: delete the p0 partition

ALTER TABLE t1 DROP PARTITION p0

6. correct use of posture

Through the above introduction, we understand the principle of the system version table. In high concurrent write scenarios, there is bound to be a loss of performance, so turn on this feature with the correct posture.

Example: the master library is MySQL 5.6or MariaDB 10.0.Universe version 2, build a new slave library MariaDB 10.3, and convert it to the system version control table on this slave library. In this way, mistakenly deleting or tampering with data on the master library can be recovered through version control on the slave library.

Note: the master library is a low version and the slave library is a high version, which is compatible with binlog format forward.

VII. matters needing attention

1. Set the parameter system_versioning_alter_history to KEEP (write it dead in the my.cnf configuration file), otherwise you cannot modify the table structure by DDL by default.

Set global system_versioning_alter_history = 'KEEP'

Note: when adding fields, add the after keyword, otherwise the synchronization will fail after the te field. Example:

Alter table T1 add column address varchar (500) after name

2. The mysqldump tool does not export historical data, so when making a backup, you can use the Percona XtraBackup hot backup tool to back up physical files.

3. When building a slave library, if you use the mysqldump tool, export the table structure file first, and then export the data.

1) only export the table structure:

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

After importing the table structure, batch execute DDL to convert the system version table. The script is as follows (click download in the attachment):

# cat convert.php

Note: install the php-mysql driver first

# yum install php php-mysql-y

# php convert.php

2) Export data only:

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

Reference documentation:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.