Mysql – How to backup a Galera node

galeramariadbMySQL

I have a new production 3-node Galera (MariaDB 10.2) cluster, and am looking for advice on backups. My current plan is twofold:

Replicate one node to a non-clustered slave, and schedule mysqldumps on the slave.
Do full VM backups of one Galera node with a pre-freeze / post-thaw script which stops and then restarts mysqld.

I am particularly interested for feedback on the second point (VM backup). Is this an acceptable strategy?

Thanks,
Sera

Best Answer

This would work, although you might want to consider a physical backup solution with percona-xtrabackup (innobackupex) or the new mariabackup. Depending on the load on your cluster, you can even do these physical backups directly on one of the cluster nodes. (Though in my experience, xtrabackup does lock (FLUSH TABLES WITH READ LOCK) for about 15-20 seconds during backup of our 30G database.)
I don't have experience with this, but it sounds like it should work, obviously provided that when you restore from this backup you bootstrap the cluster from this node.

Related Solutions

Mysql – MariaDB Galera cluster node won’t start

Please post the tail end of your MySQL error log. Usually, it will be in /var/log/syslog or /var/log/mysql/mysql.log (depending on your setup).

Without having some details, it is hard to say, but it almost sounds like you have network connectivity problems. Please ensure that the necessary ports are open on the third node to properly allow for all Galera traffic.

Mysql – Galera Cluster not syncing from a standalone Mysql Server

The question comment guess by jerichorivera was absolutely right:

log-slaves-updates on node1 enabled?

..but you seem to have misunderstood.

log_bin=/var/log/mysql/mysql-bin

(or whatever file basename) to enable writing the binary logs a slave can read from. However, this alone lets the node write empty binary logs (a size of a few hundred bytes) if it is slave on its own. To act as intermediate master, also set

log_slave_updates

in the my.cnf.

Best Answer

Related Solutions

Mysql – MariaDB Galera cluster node won’t start

Mysql – Galera Cluster not syncing from a standalone Mysql Server

Related Question