Step-by-step guide to MySQL master-master replication without downtime

In my previous entry I talked about my plan to move from Linode to Digital Ocean. But, I wanted to do it in a way that ensured no data loss if for some unexpected reason I need to switch back. I also couldn’t have any down time so I knew I needed to setup my databases first and get them replicating right away so that I could build the rest of the network around it.

master-master1
It’s actually very easy to setup, but that simplicity goes out the window when you want to have zero down-time. It requires a hack of some kind. I’m sure each person has their own solution, but I can only write about what I did. Now, I know there are many articles out there dedicated to this topic already. But, where I hope mine will be different from the rest is that I intend for it to be clear and easy to understand because all the examples, at least the ones I came across weren’t. Or, the ones that were depended on you locking your tables and having down-time or starting from a fresh database. This isn’t real-world, and I’m all about real-world.

This article is as much for my benefit as anyone elses, because this isn’t the first time I’ve had to do it and finding information is always a difficult task.

Note: There will be minor data loss. Queries that do things like field = field + 1 may be lost on the new master depending on how long the process takes you to get up and running. Take this in to consideration, if you cannot afford to lost a single piece of data then you will need to implement your own hack — it shouldn’t be hard. But, be mindful of it.

Preparing

In a nut shell, this is what I’m going to cover:

  1. Configuring your primary master
  2. Configuring your secondary master

Now, if you’re here for something that isn’t in this list then I’m glad to meet you, and I hope you have a nice day. This article isn’t for you.

What you will need:

  • A few terminal windows. I recommend Terminator for GNOME
  • Python
  • Python-mysqldb extension
  • mysql-client (apt-get install mysql-client-5.5)

Already have a master slave set up?

It’s best to sever this connection. I spent a day trying to attach my secondary master while already having a slave set up and it was a waste of time. Break down that wall! We’ll put it back up later

 Configuring your Primary Master

You should receive an output that looks something like this

Hold on to this, we’re going to need it when we configure our Secondary Master.

Transfer data from Primary Master to Secondary Master

There are a few ways you can do this. If you have enough space, the BEST way is to output it to a file, then rsync that file to your new server and restore it. However, if like me, your data is quite large and space is a rare commodity then you can send your data straight to your Secondary Master via a pipe. I’ll cover both options.

Dump to file + rsync + import

Ok, once that’s done you’ll get some neat time stats about how long it took. Post those to Twitter, or Facebook, or wherever the cool kids are hanging out today. Then, get back to work.

Dump straight to destination server

WOAH, that’s huge! Why the ….? Well, like I said, my data was large and some of the data in my rows were large and sometimes things would go wrong for no reason. I’m saving you the headache, trust me.

First, we login to the master servers and increase the settings that would otherwise ruin the delightful day we’re about to have. Then, we grab all the tables and exclude some of the ones that aren’t needed for migration, and then we save it all and wait ….

Ok, once that’s done you’ll get some neat time stats about how long it took. Post those to Twitter, or Facebook, or wherever the cool kids are hanging out today. Then, get back to work.

After data transfer completion

Once you’ve got your data transfered, go back to your mysql-client window and setup replication permissions

 Configuring your Secondary Master

Ok, remember that snippet of information I told you to hold on to earlier?

We need that now. In your mysql-client window for the Secondary Master, put this:

Remove the variables and replace them with the actual values:

__LOGFILE__ mysql-bin.000001
__LOGPOS__ 107

Start slave? That’s right, your mysql database can be both a master and a slave, this is how we achieve the master-master set up. So, your secondary master, is really just a slave at the end of the day. Now, if we’re lucky we got a clean output with no errors

However, we probably wont. More than likely, the Last_SQL_Error will contain a Duplicate entry error, and your replication has probably haulted. DON’T PANIC! I told you, I’ve got your back.

 Dealing with Duplicate entry error messages

Like I said, this isn’t all fun and games. To do this task with no down time requires some hacker techniques. In your secondary master terminal window follow my lead.

Paste this, then edit your host/user/passwd details

Save, and exit.


This will clean your database of all Duplicate Entry error messages, but ONLY Duplicate entry error messages. If it cannot detect one then it will exit and say done. Duplicate entry messages are OK, because it means the primary master is trying to send us information that we already got because we didn’t lock the tables before we got our master status information.

Log back in to you secondary master with the mysql-client.

Is the Last_SQL_Error message clean? If so the, you’re done! Your secondary master should be successfully retrieving new records, and you can verify that by creating a new database on your primary master and then checking for it on your secondary master.

If, you have another type of error then this is where our conversation ends. I cannot predict what your problem may be so, unfortunately you will need to Google your problem :( I’m sorry

 Adding slaves

Since the master-master set up is really just a master-slave you can replicate these steps to attach a slave to any one of your masters.

 

Dave

About David Parlevliet

Dave is long time developer with a passion toward teaching. He divides his time between his wife, her cat and his projects. He recently started using twitter so make sure to follow him!