2

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,

  1. I issue or schedule mysqldump on master servers.
  2. I allow developers to make changes directly connecting to master servers with the GUI tools(workbench).

Any help is appreciated. Thank you!

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.