I have a MariaDB Galera cluster. One of its nodes is dedicated for the backups (pc.weight=0 and the MaxScale SQL proxy doesn't know it exists).
mariabackup --backup --galera-info --target-dir=/path/to/my/dir to do the daily backups. So this backs up everything.
I want to be able to do 2 kinds of restore:
- full restore in case of disaster (e.g. data center explosion)
- single database restore in case of data loss (e.g. human error/hacking)
The case 1 is easy, I just take the backup I have and use
mariabackup --copy-back on the new freshly installed Galera nodes.
But how can I achieve case 2?
I know I could do a
mariabackup --prepare and then for each table of the database I want to restore:
- discard the table space of table X
- copy back the file(s) of table X from my backup
- import the table space
The problem is that this process is too low-level for Galera/wsrep to notice it and the changes are of course not replicated to the other nodes. So I would need to repeat this process on all the nodes.
I could also:
- take a logical backup (mysqldump)
- drop the database on one of the Galera nodes
- re-create the database
- import the dump I just did
This would be replicated to all the nodes. But it's highly inefficient and I would need to setup a temporary MariaDB server to run the
mysqldump on the backed up data.
There must be a better way, right? How are you doing it?
Your answers are much appreciated!