You are here
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
- Shinguz's blog
- Log in or register to post comments
Comments
Partial restore on MySQL or PXC
The receipt above seems also to work with Percona Xtrabackup:
First you have to disabled the PXC strict mode:
pxc_strict_mode = disabled
. Then you have to copy the*.{exp|cfg|ibd}
files to the right location and import the tablespaces again.We further found, that an
--export
on an already prepared backup seems to be possible withxtrabackup
.Recover DDL from .frm file out of MariaDB full backup
We just came today across the
mysqlfrm
utility which helps to extract the DDL statement from the .frm file. This is exactly what is needed to automatize and thus simplify the partial restore from MariaDB full backup.See also feature request here: MDEV-18827.