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.