I have a 3-node MariaDB/Galera cluster. I need to do two things:

  1. Back up the database regularly (multiple gigs in size) for a normal, in-case-everything-crashes-I-can-restore-from-this backup.
  2. Have a sanitized version in which private data is obfuscated and key values are changed, for the purpose of distributing to developers. (The idea is that every day, they can grab a fresh "devel snapshot" that is a content- and structural- facsimile of the real thing, but that in which any potentially sensitive data is sanitized, and devel-centric settings are changed.

I know how to do a mysqldump, but I'm afraid this will lock the db.

I know how to write the queries to obfuscate the data, but obviously I can't do this on the live db. (I'm assuming that I need to import this into a different database... perhaps on a separate machine not in the cluster?)

I need this to be scripted (which I'm not scared of doing, I'm just not sure where to start.)

Can someone point me in the right direction?

  • Take a daily copy from one of the nodes with XtraBackup and distribute it among the devs. It will be quite fast and won't block the cluster – akuzminsky Jul 9 '14 at 4:50

Setup a slave.

Once everything is sync'd up and good.

  • Stop the slave (sql thread).
  • mysqldump slave data to orig.sql
  • run your queries to modify the data
  • mysqldump again to dev.sql
  • Restore the data using orig.sql
  • start slave to resume collection from the master.

This will take some time with a large db of course but it seems pretty straight forward to me. It won't lock your master cluster at all (at least until you create the slave). If you are modifying only a few tables you can tweak the mysqldump.

You can cron the operation and upload it to s3 or something for the devs. Use some fast SSDs.

| improve this answer | |
  • Simple and elegant... I like it. My main concern is that the cluster is set up as a multi-master. How will the modifications and restore affect the cluster when the node is re-attached? – coreyp_1 Jul 9 '14 at 4:35
  • You don't make this "node" part of the cluster; it's an ordinary async slave, whose master is one of the galera nodes. The only issue with this answer is running queries to munge the data (on a slave). That's usually ill-advised, since replication expects identical data. But you can use mysqldump on this system all day long, zero impact to the cluster. – Michael - sqlbot Jul 9 '14 at 7:04

Matthew's answer is really straightforward, but has one major drawback, it's the restoration part, which can take hours on huge databases (thus increasing the replication delta).

To avoid that part, one could leverage LVM snapshots.

I'm assuming the following:

  • the database data partition is a Logical Volume (version 2 to allow write operations on snapshot volumes)
  • the Volume Group has enough space to accommodate the snapshot volume
  • you have mysqld_multi installed (should be by default) AND configured

The procedure becomes (see important notes below):

  • Stop mysqld
  • Create the lvm snapshot volume
  • Start mysqld (replication starts too, keeping your data in sync in the main DB)
  • Mount the lvm snapshot volume
  • Start the mysql2 (snapshot) instance which uses the mounted snapshot directory as datadir
  • mysqldump mysql2 data to orig.sql
  • Run your queries to modify the data on mysql2
  • mysqldump again to dev.sql
  • Stop mysql2
  • Unmount the snapshot volume
  • Destroy the snapshot volume

Important Notes: I haven't personally tested this procedure, it may need tweaking and adjusting, and you're using it at your own risk.

| improve this answer | |
  • Why is this CW? – ypercubeᵀᴹ Feb 2 '15 at 15:49
  • Hi @ypercube, just because I think this answer can and should be improved, I don't have the time to test, so other editors are welcome. You can revert if you want. – Thomas Feb 3 '15 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.