Create new database from a MariaDB backup

Question

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?

danblack · Accepted Answer · 2024-01-12 02:47:07Z

0

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.

edited Jan 12, 2024 at 2:47

answered Jan 11, 2024 at 23:49

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create new database from a MariaDB backup

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related