How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu.
What happens if,
Any help is appreciated. Thank you!
QUESTION #1
I issue or schedule mysqldump on master servers.
ANSWER TO QUESTION #1
DEPENDS ON STORAGE ENGINE
If your database is all InnoDB, you can launch mysqldump to get a live snapshot of the Master, but you can run the great risk of slowing down transactions. (See my old post MySQL quick database transfer between two servers for caveats)
DEPENDS ON DB TRAFFIC
The slowdown of transaction incurred would be due to maintaining snapshots of data to support repeatable reads.
MORAL OF THIS STORY
It is best not to do it on a live server, even if it is possible
QUESTION #2
I allow developers to make changes directly connecting to master servers with the GUI tools(workbench)
ANSWER TO QUESTION #2
You can and will break the transaction issued by the mysqldump --single-transaction. See my old post row locking within ACID transaction innodb to see the commands that will disrupt a consistent backup if executed on the master during a mysqldump
MORAL OF HIS STORY
Use a MySQL Replication Slave and issue mysqldumps without disturbing the Master
