You are here

Partial Restore of a Table into a MariaDB Galera Cluster

In my former Blog Post Partial Table or Schema restore from mariabackup full backup we worked out the basics of a partial restore of a table into a MariaDB database instance.

An now we use this know-how to try the same procedure on a Galera Cluster.

The backup is done in the exact same way as described in the mentioned article. We can even use the backup made there.

For the restore we use the following procedure:

Prepare and Restore a table

# BACKUPDIR="/home/mysql/bck/qamariadb105/daily"
# DATADIR="/home/mysql/database/magal-105-a/data"
# SCHEMA="world"
# TABLE="City"

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

But now comes the little difference to a simple MariaDB database instance. The following operations have to be done on ALL nodes of the Galera Cluster:

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

Restore all the files from the backup:

# scp ${BACKUPDIR}/${SCHEMA}/${TABLE}.ibd mysql@node[1-3]:${DATADIR}/${SCHEMA}/
# scp ${BACKUPDIR}/${SCHEMA}/${TABLE}.cfg mysql@node[1-3]:${DATADIR}/${SCHEMA}/

Then re-import the tablespace again:

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

And finally clean-up:

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