Thursday, April 23, 2009

Restoring a backup of a Circular Replication config

So say you have two mySQL servers called A and B. A and B are in a circular replication ring meaning

A replicates from B
B replicates from A

In addition, log-slave-updates is on, so if one server of either A or B goes down you can recover all the data from either side.

Therefore, here are some steps.
Take a snapshot of a server in the ring, with your favorite backup tool (mine is ibbackup)

When either server A or B dies, restore from said backup. Here are the steps. Let us assume server A goes down, and backups are done from server B.

restore the backup of server B to server A

On server-A turn off log-slave-updates (you will see why on the next line)
Add replicate-same-server-id on server A to my.cnf

CHANGE MASTER TO to the position and file of the binary log that is reported from your tool on server-A.

IF the binary log did not get corrupted on server-B your cool, else flip the binary log to the next binary log from server-A on server-B.

Let server-A catch up
Stop server-A
Add log-slave-updates on server-A
remove replicate-same-server-id on server A
Start server-A

done.

3 comments:

Edmar said...

Hi! I haven't tried master-master MySQL replication yet, but your post is the best (and only?) explanation I've seen so far about server recovery in this setup. Very clear and to the point. Thanks!!!

TCP said...

Hi, your blog is amazing. I'm blogging on PHP. Please visit my blog, PHP Coach and let me know your views too. Thanks.

MySql Jobs in India said...

Was in a search for MySql replication... Gr8 post and keep it up...