Hi,

3 MariaDB servers are running Galera on three bare-metal machines adjacent to each other in a data center rack. Two of them handle traffic from a web app while the third machine has other duties including backups. A recent upgrade in the web app completely changed its schema. Previously there were many databases (schemas), each with a modest volume of data, and soon all that data will be in one database.

The current backup procedure mysqldumps each schema every hour on the third machine. Then a geographically distant host copies those files out and stores them in a rotating series.

I'm thinking of changing this because I don't really like how the Galera rx queue on the third machine climbs for two minutes every hour. The idea I want to check with y'all is: Every hour on that third machine

1. Shutdown mysqld and wait for it to exit
2. Snapshot its datadir using BTRFS
3. Restart that mysqld
4. Start an LXC container that
   • mounts the snapshot read-only and
   • starts its own mysqld with innodb_read_only
5. Run mysqldump in the LXC container
6. Stop the container, which shuts down its mysqld
7. Then let the geographically distant host do its thing

Pros

• Keep using the existing system for remote copies and rotating series of mysqld files.
• We can add a script on the third machine to maintain a series of the BTRFS snapshots.
• Confidence boost from having both physical backups on the cluster and remote logical backups

Cons? or other thoughts?

Tom