1

I've just recently taken on the task of looking after our mySQL databases, and have been given the task of setting master-master replication. We have a live server which has a mySQL database which is constantly receiving data and a backup server which has been configured as a replication slave to the master. What I need to do now is set the live server up as a slave to the backup. I have an idea of what is necessary, and I guess I'm just looking for someone to confirm I've got it right before I go and do it (or tell me a better way of doing it).

  • Create slave user on backup-server
  • Grant replication privileges to the user
  • stop slave on backup-server
  • set database lock(read only) on live-server so no new data can be added (and hence replicated to backup)
  • Get master status from backup-server -open new ssh session to live-server and issue change master command to point at backup-server
  • start slave on live
  • unlock database on live
  • start slave on backup
Improve this question
2
  • A bit of advice -- only write to one Master at a time. That is, treat Master-Master as Master-Slave, but with the ability to swap roles quickly.
    – Rick James
    Commented Jun 30, 2016 at 23:40
  • Yeah that's the plan :)
    – chadianscot
    Commented Jul 1, 2016 at 6:19

1 Answer 1

Reset to default
-2
  • create replication user on backup
  • configure replication on live as slave and backup as master
  • start slave
  • profit!
Improve this answer
8
  • 1
    "Profit" should the 5th step.
    – mustaccio
    Commented Jun 18, 2016 at 19:53
  • So I don't need to stop the slave on the backup at all? Or stop the Live from receiving data?
    – chadianscot
    Commented Jun 20, 2016 at 8:10
  • afaict there is no need. you obviously don't want replicated events from live to go back to the same server. what you'll need is all direct trx on backup to be replicated to live. as long as binary logging is enabled on backup just configure replication slave on live and you should be good to go. but for safety make sure to checksum live and backup first.
    – jerichorivera
    Commented Jun 20, 2016 at 9:46
  • @mustaccio how so? care to explain?
    – jerichorivera
    Commented Jun 20, 2016 at 9:48
  • Ah ok, see that was my concern (that replicated events from live would go back to live). I assume by your comment that that doesn't happen with replication since replication only replicates direct trx and not binary logging stuff?
    – chadianscot
    Commented Jun 20, 2016 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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