Backing up Master-Master replicated MySQL databases

Posted on

Question :

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?

Answer :

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.

  • 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.

Leave a Reply

Your email address will not be published. Required fields are marked *