CODEWITHSUNDEEP
mariadbdatabase-backupsbackup-strategies
mickburkejnr
mickburkejnr

Reputation: 3690

Create new database from a MariaDB backup

I have a large database (over 150GB and growing) in production. Currently I make a full back up of it using the mariabackup command, and then twice a day I do incremental backups of the database.

I need to make a development version of the database and would like to use the backup I already have for it. From the documentation I've found for the command it seems the restoration of it is straightforward but effectively replaces what is there. I can't see anything that allows me to change the name of the database and effectively create a new one from the backup. How is it possible?

Upvotes: 0

Views: 154

Answers (1)

danblack
danblack

Reputation: 14736

Its slightly manual, but from partial backup and restore:

At the prepare stage use --export

 mariadb-backup --prepare --export \
   --target-dir=/var/mariadb/backup/

Then you need to have a dev database with all the tables there with discarded tablespaces. Creating tables can be done with mariadb-dump --no-data prod | mariadb dev. Discarding tables spaces is ALTER TABLE tablename DISCARD TABLESPACE. I haven't seen an easy automation for this. Will need ' SET SESSION foreign_key_checks=0;' if there are foreign key constraints on the table.

Copy the tablespaces from the backup:

scp /var/backup/prod/*.{cfg,ibd} db:/datadir/mariadb/dev/

Then for all the tables:

use dev;
ALTER TABLE tablename IMPORT TABLESPACE;
...

Partitioned tables are more complicated.

Note for MariaDB-11.2, no CREATE TABLE or DISCARD TABLESPACE is needed, however the .frm table structure file should be copied.

Upvotes: 0

Related Questions