Sample Header Ad - 728x90

Galera - single database backup & restore

2 votes
1 answer
2348 views
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 --prepareand 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