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
  • 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 Jun 30 '16 at 23:40
  • Yeah that's the plan :) – chadianscot Jul 1 '16 at 6:19
-1
  • create replication user on backup
  • configure replication on live as slave and backup as master
  • start slave
  • profit!
  • 1
    "Profit" should the 5th step. – mustaccio Jun 18 '16 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 Jun 20 '16 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 Jun 20 '16 at 9:46
  • @mustaccio how so? care to explain? – jerichorivera Jun 20 '16 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 Jun 20 '16 at 10:56

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.