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