How to Use MariaDB System-Versioned Tables for Easy Data Recovery

Introduction

System‑versioned tables, defined in the SQL:2011 standard, let MariaDB store a complete history of row changes. This enables point‑in‑time queries and recovery from accidental updates or deletions.

1. Creating a system‑versioned table

Define two generated timestamp columns and a PERIOD clause, then enable system versioning.

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;

To convert an existing 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;

2. Querying historical data

Three common forms are supported:

Rows changed within the last hour:

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

Rows in a specific interval:

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

All historical rows:

SELECT * FROM t1 FOR SYSTEM_TIME ALL;

3. Restoring a previous version

Export the desired rows and reload them:

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

LOAD DATA INFILE '/tmp/t1.sql' REPLACE INTO TABLE t1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(id, name);

4. Storing history separately with partitions

Partition the table by system‑time intervals so that normal queries scan only the current partition.

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 pcur partition holds the current rows; each pN partition stores one month of history.

5. Deleting old history

Drop obsolete history partitions to reclaim space, e.g.:

ALTER TABLE t1 DROP PARTITION p0;

6. Correct usage tips

Set system_versioning_alter_history=KEEP in my.cnf (or at runtime) to allow DDL on versioned tables.

When adding new columns, use the AFTER clause to avoid placing them after the te column, which would break versioning.

7. Backup and recovery considerations

Standard mysqldump does not export history. Use a physical backup tool such as Percona XtraBackup, or export the schema first and then convert tables to versioned tables with a script. For high‑availability, a delayed replica (e.g., 12‑hour delay) provides point‑in‑time recovery.

Example PHP script that converts all tables in a database to system‑versioned tables:

<?php
$conn = mysqli_connect('10.10.100.11','admin','123456','test','3306')
       or die('connection error');
mysqli_query($conn, 'SET NAMES utf8');

$res = mysqli_query($conn, 'SHOW TABLES');
while ($row = mysqli_fetch_array($res)) {
    $tbl = $row[0];
    $sql = "ALTER TABLE $tbl
            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";
    if (mysqli_query($conn, $sql)) {
        echo "$tbl converted
";
    } else {
        echo "failed on $tbl: " . mysqli_error($conn) . "
";
    }
}
mysqli_close($conn);
?>

Install the PHP MySQL driver before running the script (e.g., yum install php php-mysql -y).

8. Limitations

DROP DATABASE, DROP TABLE, and TRUNCATE TABLE cannot be recovered with the export‑import method.

For production environments, configure a delayed slave (e.g., using pt‑slave‑delay) to protect against accidental destructive statements.

Reference

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