Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 at 4:50

1 Answer 1

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.

share|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 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 at 7:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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