Backing up MariaDB Temporal Database

Question

Generally, I am excited by the Temporal Database feature.

However, mysqldump is not supported for database export and restore.

I can find no resource in the documentation (linked to above) that indicates which methods of backup and restore are safe to use for this type of database. Google searches do not seem to help.

Does anyone have any insights into using these MariaDB temporal databases in production environments? Or more specifically, in using them in development environments, and then transferring the database to a production environment and still keeping the history of the database intact?

I understands this something of a dev-ops question, but it seems pretty central issue to how to work with and around this new feature. Does anyone have an insights in moving these databases around and relying on that process in-production? Just wondering how mature this technology is, given that this issue (which seems pretty central) is not covered in the documentation.

Psychological

Answer 1

Unfortunately, as the documentation states, while mysqldump will dump these tables, the invisible temporal columns are not included - the tool will only backup the current state of the tables.

Luckily, there are a couple of options here;

You can use mariadb-enterprise-backup or mariabackup which should support the new format of the temportal data and correctly back it up (these tools do binary backups instead of table dumps);

https://mariadb.com/docs/usage/mariadb-enterprise-backup/#mariadb-enterprise-backup https://mariadb.com/kb/en/library/full-backup-and-restore-with-mariabackup/

Unfortunately, we have found the tool to be somewhat unreliable - especially when using the MyRocks storage engine. However, it is constantly improving.

To get around this, in our production servers we take advantage of the slave replication - which keeps the temporal data (and everything else) intact across all our nodes. We then do secondary backups by taking the slave nodes down and doing a straight copy of the database data files. For more information on how to set up replication, please refer to the documentation;

https://mariadb.com/kb/en/library/setting-up-replication/

So you could potentially set up dev-copy of the database with replication and just copy the data from there. However, in your case, mariabackup might also do the trick.

Regardless of how you do it, be wary of the system clock when setting up replication or when moving these files between systems. You can get some problems when the clock is not in sync (or if the systems are in different time zones). There is some official documentation (and mitigation) on this topic also;

https://mariadb.com/kb/en/library/temporal-data-tables/#use-in-replication-and-binary-logs

Looking at your additional comment - I am not aware of any way to get a complete image of a database as it looked at a given date (with temporal data included), directly from MariaDB itself. I don't think this information is stored in a way that makes this possible. However, there is a workaround even for this. You could potentially use the above method in combination with incremental rdiff backups. Then what you would do to solve it would be to;

1. Backup the database with any of the above methods.
2. Use rdiff-backup (https://www.nongnu.org/rdiff-backup/) on those backup files, running it once per day.

This would allow you to fetch an exact copy of how the database looked at any given date of your choice. rdiff-backup also fully supports ssh, allowing you to do things like,

rdiff-backup -r 10D host.net::/var/lib/mariadb /my/tmp/mariadb

This would fetch a copy of those backup files as they looked 10 days ago.

Phonation

Answer 2

For future planning, according to https://mariadb.com/kb/en/system-versioned-tables/#limitations:

Before MariaDB 10.11, mariadb-dump did not read historical rows from versioned tables, and so historical data would not be backed up. Also, a restore of the timestamps would not be possible as they cannot be defined by an insert/a user. From MariaDB 10.11, use the -H or --dump-history options to include the history.

10.11 is still in development as of writing this answer.

Sheugh