Wondering what best practices would be for logical backup / restore process in the PXC 8? I’ve read number of blogs and seen some webinars on this topic, but none of them mentioned the exact procedure of setting up the node prior and past the backup itself. What I’m searching for is the actual steps to be taken on the backup node / cluster. In the typical 3 node cluster with proxysql running scheduler script (galera_check.pl) with one writer at time, what steps should be taken before proceeding the backup? Also how to setup the node or cluster prior starting the restore process? I’m concerned about db locking and availability during backup/restore in the cluster. The backup tools: mysqldump or mydumper.
While logical dumps are rarely recommended (mainly because they are the slowest to take and slowest to restore), I highly recommend mydumper. It can do parallel dumping of multiple tables, gives you per-table dump files, and can compress them on the fly.
There’s nothing really to be done to prep for the backup. Simply decided which node you want to use and start the process. Provided all of your tables are InnoDB, there’s no locking involved at all. If you find that this node causes flow-control during the backup, you can set wsrep_desync=ON during the backup to prevent flow control from kicking in.
For restore, simply stop all nodes, pick your favorite and erase all data, bootstrap the node, import your backup using myloader, then start the other nodes. They will perform an SST from the first and sync.
Thank you for your reply @matthewb !
So no preparation is needed before proceeding the mysqldump, that is great.
I just want to make sure I’m using the right options which does make sense:
–single-transaction - for consistent snapshot
–skip-add-locks - as PXC strict mode prohibits usage of LOCK TABLES (while importing the file back)
–lock-for-backup - can be probably omitted, when backing up only innodb tables, right?
I would actually recommend GitHub - maxbube/mydumper: Official mydumper project over mysqldump. mydumper has many additional features that make logical backups easier, faster, and more convenient.