Hi Kevin,
you are creating partial backups .The way to restore them preparing with –export option, and this would export every Innodb table in the backup.
Now you also need ALTER TABLE IMPORT TABLESPACE to get exported tables to their final destination, something similar to what is described here.
From: Kevin Poitra
Sent: Wednesday, February 28, 2018 6:26 PM
To: maria-discuss@lists.launchpad.net
Subject: [Maria-discuss] Backing up a single database with mariabackup?
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?