The Ultimate Guide to High Availability with MariaDB

Individual Database restores with MariaBackup

You are viewing an old version of this article. View the current version here.

This method is to solve a flaw with Mariabackup, it cannot do single database restores from a full backup easily. There is a blog post from 2018 that details a way to do this mariadb.com/resources/blog/how-to-restore-a-single-database-from-mariadb-backup/. But it is a manual process which is fine for a few tables but if you have hundreds or even thousands of tables then it would be impossible to do quickly.

We can't just move the data files to the datadir as the tables are not registered in the engines, so the database will error. We are able to do a partial backup, however, this won't be useful if we have a full backup script running and one schema needs restoring. Currently, the only effective method is to a do full restore in a test database and then dump the database that requires restoring or running a partial backup.

Some of the issues that this method overcomes:

  • Tables not registered in the InnoDB engine so will error when you try to select from a table if you move the data files into the datadir
  • Tables with foreign keys need to be created without keys, otherwise it will error when you discard the tablespace

Below is the process to perform a single database restore.

Firstly, we will need the table structure from a mysqldump backup with the --no-data option. I recommend this is done at least once per day or every six hours via a cronjob. As it is just the structure, it will be very fast. 

Mysqldump -u root -p –all-databases --no-data > createtable.sql

I won’t go over the backup process, as this is done earlier in other documents, such as full-backup-and-restore-with-mariabackup. Prepare the backup with any incremental-backup-and-restores that you have, and then run the following on the full backup folder using the --export option to generate files with .cfg extensions which InnoDB will look for.

Mariabackup –prepare –export –target-dir=/media/backups/fullbackupfolder

Once we have done these steps, we can then import the table structure. If you have used the --all-databases option, then you will need to either use SED or open it in a text editor and export out tables that you require. You will also need to log in to the database and create the database if the dump file doesn't. Run the following command below:

Mysql -u root -p schema_name < nodata.sql

Once the structure is in the database, we have now registered the tables to the engine. Next, we will run the following statements in the information_schema database, to export statements to import/discard table spaces and drop and create foreign keys which we will use later. (edit the CONSTRAINT_SCHEMA and TABLE_SCHEMA WHERE clause to the database you are restoring. Also, add the following lines after your SELECT and before the FROM to have MariaDB export the files to the OS)

SELECT ...
into outfile '/tmp/filename.sql'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM ...

The following are the statements that we will need later.

USE information_schema
select concat("ALTER TABLE ",table_name," DISCARD TABLESPACE;")  AS discard_tablespace
from information_schema.tables 
where TABLE_SCHEMA="DATABASENAME";

select concat("ALTER TABLE ",table_name," IMPORT TABLESPACE;") AS import_tablespace
from information_schema.tables 
where TABLE_SCHEMA="DATABASENAME";

SELECT 
concat ("ALTER TABLE ", rc.CONSTRAINT_SCHEMA, ".",rc.TABLE_NAME," DROP FOREIGN KEY ", rc.CONSTRAINT_NAME,";") AS drop_keys
FROM REFERENTIAL_CONSTRAINTS AS rc
where CONSTRAINT_SCHEMA = 'DATABASENAME';

SELECT
CONCAT ("ALTER TABLE ", 
KCU.CONSTRAINT_SCHEMA, ".",
KCU.TABLE_NAME," 
ADD CONSTRAINT ", 
KCU.CONSTRAINT_NAME, " 
FOREIGN KEY ", "
(`",KCU.COLUMN_NAME,"`)", " 
REFERENCES `",REFERENCED_TABLE_NAME,"` 
(`",REFERENCED_COLUMN_NAME,"`)" ," 
ON UPDATE " ,(SELECT UPDATE_RULE FROM REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA)," 
ON DELETE ",(SELECT DELETE_RULE FROM REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA),";") AS add_keys
FROM KEY_COLUMN_USAGE AS KCU
WHERE KCU.CONSTRAINT_SCHEMA = 'DATABASENAME';

Once we have run those statements, and they have been exported to a Linux directory or copied from a GUI interface. You may need to remove NULL rows from the add key results.

Run the ALTER DROP KEYS statements in the database

ALTER TABLE schemaname.tablename DROP FOREIGN KEY key_name;
...

Once completed, run the DROP TABLE SPACE statements in the database

ALTER TABLE test DISCARD TABLESPACE;
...

Exit out the database and change into the directory of the full backup location. Run the following commands to copy all the .cfg and .ibd files to the datadir (Change the datadir location if needed). Learn more about files that Mariabackup generates with files-created-by-mariabackup

cp *.cfg /var/lib/mysql
cp *.ibd /var/lib/mysql

After moving the files, it is very important that MySQL is the owner of the files, otherwise it won't have access to them and will error when we import the tablespaces.

sudo chown -R mysql:mysql /var/lib/mysql

Run the import table spaces statements in the database.

ALTER TABLE test IMPORT TABLESPACE;
...

Run the add key statements in the database

ALTER TABLE schmeaname.tablename ADD CONSTRAINT key_name FOREIGN KEY (`column_name`) REFERENCES `foreign_table` (`colum_name`) ON UPDATE NO ACTION ON DELETE NO ACTION;
...

We have successfully restored a single table. To test that this has worked, we can do a basic check on some tables.

use database
SELECT * from test limit 10;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.