1

I've been working on redoing our database backup routines, and have looked into mariabackup as mysqldump doesn't cut it anymore given the size of our schemas. We have multiple schemas that need to be backed up - they all vary in size, and they also vary in backup frequency (some need to be backed up on a six hour period, others can go a day in between backups). On top of that, our database consists of two database servers, running in a master-slave configuration.

To start, I ran this command against one of our schemas:

mariabackup --backup --user root --password <pass> --databases db1

That generated a copy of the 'db1' schema's data files, along with some files used by MariaDB itself (aria_log, ib_buffer_pool, ib_logfile, ibdata, so on). This works perfectly - we can create separate backups of each of our schemas.

Restoring seems to be another story however. After '--prepare'ing the backup and issuing the mariabackup --copy-back command, it complains that the data directory must be empty. If I have separated backups, would it be possible for me to restore those schemas under one database (or rather, one MariaDB instance)? To be even clearer, if I've made backups of schemas 'db1', 'db2', and 'db3', I don't seem to be able to restore them all under the same database instance - is there something I'm not understanding?

KevinP
  • 11
  • 1
  • 3

1 Answers1

0

Partial backup, like yours, can only be restored with --prepare --export. that generates some .cfg files alone the .ibd files. The "restore" ,is meant to be "ALTER TABLE .. DISCARD TABLESPACE", "ALTER TABLE IMPORT TABLESPACE" for every table on the target system. Unfortunately, it is not 100% automated, because to discard a tablespace on a target system, you first need to create a tablespace on the target system, with the same "CREATE TABLE"