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).
I'm using
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:
1. full restore in case of disaster (e.g. data center explosion)
2. 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 --prepare
and 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:
1. discard the table space of table X
2. copy back the file(s) of table X from my backup
3. 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:
1. take a logical backup (mysqldump)
2. drop the database on one of the Galera nodes
3. re-create the database
4. 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!
Asked by HORF
(21 rep)
Nov 21, 2018, 10:57 AM
Last activity: May 6, 2025, 12:05 AM
Last activity: May 6, 2025, 12:05 AM