1

I have two MySQL 5.5 databases which replicate each other. What's the best way to back these machines up? I would like to prioritise speed/ease of restoration. Should I add extra slaves to both machines and also replicate there, and then periodically stop those slaves and copy the data somehow? Is a mysqldump enough, or should I be copying the whole data directory somehow?

  • If your concern is HA, but the two servers are sitting on the same earthquake fault / tornado plain / hurricane path / etc., it is not very HA. – Rick James Oct 18 '16 at 4:30
1

This is a Master/Master setup, so I would advise the following

  • Do not mysqldump from either Master. Both Masters should be used as Active/Passive
    • Active : Receives all writes
    • Passive : Replicates from Active (Hot Standby for Failover)
  • Add an additional slave from each Master
    • Can be low commodity in CPU since SQL thread processes one command at a time
    • Should have the same RAM and plenty of Diskspace for Backups

If you do go with additional slaves, you can use either mysqldump or xtrabackup.

0
  • Master master and write to both nodes? Recipe for disaster!! Avoid doing that.

Now points for answer:

  • you don't need slaves to both nodes. As they are in master master, slaving from 1 of them is fine.

  • if one node is passive, configure backup there.

  • if writing to both, create slave and setup backup there.

  • You need logical backup? Use mysqldump.

  • all innodb? Large db? Use xtrabackup. Check out how to setup xtrabackup with Holland framework.. physical backup restore will be faster than logical as db grows.

  • +1 to this, however, if for some reason your mysql servers are running on Windows then it's only mysqldump for you or maybe mydumper run from a linux server, but not xtrabackup :) – jerichorivera Oct 12 '16 at 13:52

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.