Partial Table or Schema restore from mariabackup full backup

For me it was for a long time not clear if a mariabackup full backup can be used to do partial table or schema restores. Now we faced this challenge with a customer. So time to try it out...

This test was made with MariaDB 10.5.5. So it may not work with some older MariaDB releases...

Backup

Because I do not know during the backup if I need a full or a partial restore I always want to do a full mariabackup backup!

The full backup can be done as normal but the prepare should not be done yet during the backup (or I am not sure if the prepare can be done twice, first without --export during the backup and next with --export during the restore). Further I am not sure yet if a backup treated with --export can later be used for a full restore once more. Further research has to be done in this area...

For a partial table or schema restore we need the CREATE TABLE statements as well. So it makes sense to also backup the table structures already during backups. This avoids troubles or cumbersome and time consuming extracting operations during restore.

# BACKUPDIR="/home/mysql/bck/qamariadb105/daily"
# DATADIR="/home/mysql/database/qamariadb105/data"

# # Clean-up
# rm -rf ${BACKUPDIR}/*

# mariabackup --backup --user=root \
   --datadir=${DATADIR} \
   --target-dir=${BACKUPDIR}

# # Backup also table structure for partial table/schema restore
# mysqldump --user=root --no-data --all-databases > ${BACKUPDIR}/full_structure_dump.sql

Prepare and Restore one Schema

# BACKUPDIR="/home/mysql/bck/qamariadb105/daily"
# DATADIR="/home/mysql/database/mariadb-105/data"
# SCHEMA="world"

# mariabackup --prepare --export \
   --databases="${SCHEMA}" \
   --datadir=${DATADIR} \
   --target-dir=${BACKUPDIR}

Additionally you can use the --tables option to only restore some tables: --tables='bla*bla'. The --export option creates the *.cfg files but further does not touch the *.ibd or *.frm files but ibdata?, ib_logfile? and aria_log* files!!! So I guess a backup treated like this cannot be used for a full restore any more... As mentioned above further research has to be done in this area.

-rw-rw---- 1 mysql mysql    551 Nov 11 20:41 world/CountryLanguage.cfg
-rw-rw---- 1 mysql mysql   1215 Nov 11 20:41 world/Country.cfg
-rw-rw---- 1 mysql mysql    578 Nov 11 20:41 world/City.cfg

From the structure dump we have to extract the CREATE DATABASE and the CREATE TABLE statements.

SQL> DROP SCHEMA world;
SQL> CREATE SCHEMA world;

SQL> CREATE TABLE ...;

Or more easy:

# mysql --user=root < ${BACKUPDIR}/${SCHEMA}_structure_dump.sql

Then we have to discard all the tablespaces we want to restore:

SQL> ALTER TABLE `world`.`City` DISCARD TABLESPACE;
SQL> ALTER TABLE `world`.`Country` DISCARD TABLESPACE;
SQL> ALTER TABLE `world`.`CountryLanguage` DISCARD TABLESPACE;

Restore all the files from the backup:

# cp ${BACKUPDIR}/${SCHEMA}/*.ibd ${DATADIR}/${SCHEMA}/
# cp ${BACKUPDIR}/${SCHEMA}/*.cfg ${DATADIR}/${SCHEMA}/

Addendum: Depending on the O/S user you are using you have to change the owner of the files yet:

# chown mysql: ${DATADIR}/${SCHEMA}/*.ibd
# chown mysql: ${DATADIR}/${SCHEMA}/*.cfg

And then re-import the tablespaces:

SQL> ALTER TABLE `world`.`City` IMPORT TABLESPACE;
SQL> ALTER TABLE `world`.`Country` IMPORT TABLESPACE;
SQL> ALTER TABLE `world`.`CountryLanguage` IMPORT TABLESPACE;

That is it! We have restored one single schema with a physical MariaDB backup...

Possibly the *.cfg files can be cleaned-up now:

# rm -f ${DATADIR}/${SCHEMA}/*.cfg

Literature

• Full Backup and Restore with mariabackup
• Partial Backup and Restore with mariabackup
• Mariabackup --export Option
• InnoDB File-Per-Table Tablespaces