Backing up Master-Master replicated MySQL databases

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

Answer 2

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