How to setup MySQL Master/Slave Replication with existing data

mysqlThis is a step-by-step guide on how to replicate an existing MySQL server. The server is live and contains data and needs a constant backup companion.

Many tutorials focus on how to setup replication when no data is present on the system. That’s an ideal solution if you’re building a new setup, but in case you’ve got a server that already has data present then here’s how to accomplish the this:

  1. setup your existing MySQL server (with data) as a Master
  2. export all your databases and user accounts
  3. create a slave and import all your data
  4. start replication

I’ve done this several times and always forgot to take some notes – until today. Without further ado, let’s replicate MySQL.

Prerequisites

We need the following ingredients for our project:

  • a currently running MySQL Server we want to use as a master – it’s live so we want to avoid downtime as much as we can
  • a new MySQL server which contains no data – soon to be the slave
  • root access to both servers, both for the OS and MySQL

Note that any MySQL data on the slave we’re creating will be wiped out. There are ways to keep several databases unsynchronized, but this is not covered here.

I’m working on CentOS 6.4 here with MySQL 5.1. Whenever I issue commands to the OS the cursor prefix is “root# “, and when I’m issuing MySQL commands my cursor prefix is “mysql# ” – hope this makes sense.

Note that if you have access to phpMyAdmin on both servers, you’ll be pleased to hear that there’s a much easier and faster way to setup replication. This article however will focus on doing all this via the command line.

Setting up the Master

Our current server needs to write a log of everything he’s doing from now on. This log is read later by the slave and contains a collection of statements of what has been executed by the master. Therefore the slave just remembers the position of where he last read the log, and from then onwards just does what the master does. Hence the slave ends up being in sync.

Login to your Master Server as root and edit the MySQL configuration file called /etc/my.cnf.

We need to add the following statements to give our master an ID and setup logging:

Save your file and restart MySQL using

To check that your server is logging things, head over to

and see if a file by the name of mysql-bin.000001 has been created. MySQL will write these files in sizes of up to 1GB, then start the next one. Hence it’s worth cleaning out those logs from time to time.

You can also check which file is currently being used by checking mysql-bin.index which contains a list of all log files that have been used over time.

Note that you may find the following statements in your configuration file:

These need to be deleted or commented out for replication to work properly. Add a # (hash) in front of each line to do this. If they’re already commented out or not even present – even better.

Getting the Master Coordinates

Before we continue, we need to know where the master’s last statement was written in the log. We also need to make sure nobody is writing anything to the master while we look at this.

To do this, login to MySQL from a different session and issue the following command:

Just so that we’re on the same page here: you now have TWO SSH sessions open, one still connected to your physical server’s OS, and another which shows your MySQL prompt.

Thanks to the above command, database writes cannot be committed to disk for now. Notice that MySQL still works fine and all writes are committed to memory – so there’s no downtime. We like!

Let’s see where the master’s last log entry was:

You’ll see something like this:

My server has been logging for some time, hence the .000037 at the end of the log fie. Make a note of these coordinates. If all goes well you won’t need them – but if you run into trouble, these will come in handy.

Backing up the Master

With the lock still in place, let’s go back to the first SSH session and dump all databases. Issue the following to create a dump of everything MySQL currently knows:

Depending on how much data you have and how fast your server is, this can take some time… in which you’ll get no feedback whatsoever. Sit tight and grab a coffee.

This command will create a file called everything.sql which can be several gigabytes in size. Since we need to copy it later, let’s ZIP it up to make it smaller:

That should bring it down by about 80% of its previous size. Feel free to use another compression utility, or leave this step out if you feel the file is small enough for your taste.

If you’re an avid Linux command line user you can probably pipe the output of the mysqldump into zip or gunzip, but sadly this knowledge eludes me. Besides, I’m a big believer in smaller, more readable steps.

Now that we have everything from the Master, go back to the MySQL session and unlock the tables so writes can be committed to disk again:

Now the Master will start writing to disk again. You can verify this by issuing the “show master status” command again, and you should see that it’s at a different position now. Is this exciting or what?

Creating a Replication User

It is customary to use a dedicated MySQL user to read replication statements from the Master. All this user needs is the “replication slave” privilege. Let’s do this while we’re in this session:

I’ll name mine “replicator” and give it a good password. Since I may want to replicate my data to different slaves, I’ll also specify that any host can do this. To make this more secure you can specify a single host if you like.

Still on the MySQL prompt, the following command should take care of this:

This is a long command and for readability I’ve split it across 4 lines here. Replace ‘***’ with your actual password. If you don’t like creating users on the MySQL prompt you can also use phpMyAdmin or Sequel Pro for this (if you have access to either).

You can now quit both sessions; we’re done with the master.

Telling your Slave that he’s a Slave

Just like we configured the Master, we also need to configure the slave. Open a new SSH session and edit /etc/my.cnf, then give your slave a unique ID:

It doesn’t have to be just 1 or 2, you can go crazy and use a 10 digit number if you like. Just make sure the master and slave to not have the same ID. Just as before, restart MySQL on the Slave for the change to take effect:

Importing your data on the Slave

The (potentially zipped) snapshot we’ve created on the Master needs to be copied over to our slave. I like using rsync for this, but scp or even FTP will do just fine. Use whatever you’re comfortable with, as long as the file ends up on your Slave machine.

For example, to copy everything.sql to my slave in its current directory I can issue this on the slave:

But as I said, use whatever you know best. We’ll assume that everything.sql has made it onto your slave, and that we’re in the directory where the file currently resides. We’ll also assume that you’ve un-zipped it so we end up with everything.sql.

We can now import the file, wiping out all existing databases on the slave. This shouldn’t be a problem if it’s a new machine that’s built for the purpose of being a slave, but if you have any data you may want to keep, this is the time to export it.

Issue the following command:

This will read in all your databases and user accounts, line by line, from the backup file. Since writes take longer than reads this can take some time again – time in which you will get no feedback whatsoever (again). I guess it’s time for another coffee/meal/movie/trip to New York.

A note on user accounts: each database has its own directory in /var/lib/mysql – including a database called “mysql”. This is MySQL’s own database in which it stores users and their privileges. Hence, when you import all your previous databases into the slave, you’ll automatically copy all user accounts from the master too.

At the same time, all information about your slave’s user accounts prior to the import will be eliminated. Therefore your previous MySQL root password for your Slave will no longer work – as it’s been replaced with that of the master.

When the command has finished (without any feedback if all went well) you can verify that it’s done a good job: login to MySQL with the Master’s root credentials:

Then have a look at all your server’s handy work:

You should see a list of all your Master’s databases. Neat, huh?

Starting the Replication Station

With the Master and Slave now more or less in sync, we need to make sure the Slave picks up where the Master’s last statement was. Before we go ahead, restart your MySQL Slave – trust me, this will avoid syncing issues:

Remember when we dumped all our databases, we used a switch called “–master-data”? This is something that will automatically set the log coordinates on the Slave to where the Master left off writing statements. There’s no need to give our Slave this data again. Keep in mind that for troubleshooting purposes you can set those manually if you wish.

So all we have to do now is tell the Slave who the Master is and start it. Still on the MySQL prompt, issue the following:

With a bit of luck, this will start your Slave, which will in turn add everything that’s happened on the Master since you took the snapshot. If all went well, this is the end of your setup. Go celebrate!

Quick notes about the above statement:

You can hit return at any time on the MySQL prompt, in which case a “->” is displayed so you can add more. Only when you enter a semicolon will your statement be executed. So don’t add the “->”, MySQL will do this when you hit enter.

CaSe sEnsiTiviTY doesn’t matter to MySQL: “MASTER_PASSWORD” is the same as “master_password”.

You can add the following if you wish – you see this in many tutorials:

This is necessary if you take a mysqldump without the “–master-data” option, or if you want to point the Slave manually. If you set “–master-data”, then these two coordinates will be added automatically upon import.

Verify that Replication is working

I don’t like leaving things to chance and need “proof” that everything is working as intended. The simplest way to test your replication setup is by adding a new database on the master, then check if it exists on your slave.

You can do this via the command line, phpMyAdmin, Sequel Pro – or any other utility. Let’s create two MySQL sessions, one on the Master and one on the Slave. On both, issue the following:

This shows you all databases – hopefully an identical list on both servers. Now create a new database on the Master:

List your databases again and you should see “zzz” on either server. You can create as many as you like – it’s magic seeing them come to life almost instantly on your Slave. Everything works this way: tables, rows, users all change in a flash.

If your test databases only show up on the Master, then you have a problem (see below). Remember that if you’ve only just started the Slave, he may have some catching up to do – so give it a few minutes, as he may still be working through earlier statements from the Master.

When you’ve finished testing, delete those test databases databases with

Replication Troubleshooting

You’d think once setup replication you can forget about it – but sadly that’s not the case. MySQL Replication can be extremely unreliable which is why you need to check from time to time that your Slave is still up to date.

This is an extensive topic, which is why I’ve decided to break this segment into this separate article.

Beyond the Master/Slave Setup

You can do all kinds of crazy things with MySQL Replication: if you’ve setup one Slave, you can setup several others. They’d all replicate the same data – which is extremely useful for very busy services which usually do more read queries than write queries.

In addition, you have constant access to “test data” and never have to take your live server down. You can swap a Slave out for a Master, or rebuild a Standby-Master from a Slave.

Apart from Master/Slave – imagine more than one Master: in case one breaks down you can still write to another.

A Master could also be a Slave to another Master, which in turn could be the Slave to the first Master. Or you could have several Masters which are also Slaves to the previous one. Circular Replication is one such setup. In theory it’s great, but imagine what happens when something goes wrong – and all Masters are out of sync… it’s fun, trust me – I’ve been there.

In a nutshell: go forth, dream and experiment!

Further Reading

Jay is the CEO and founder of WP Hosting, a boutique style managed WordPress hosting and support service. He has been working with Plesk since version 9 and is a qualified Parallels Automation Professional. In his spare time he likes to develop iOS apps and WordPress plugins, or draw on tablet devices. He blogs about his coding journey at http://wpguru.co.uk and http://pinkstone.co.uk.

9 thoughts on “How to setup MySQL Master/Slave Replication with existing data

  1. A couples of errors:

    mysql# release lock; -> this should be mysql# unlock tables;

    root# mysql -p > everything.sql -> this should be root# mysql -p < everything.sql

    And some code error:

    mysql# CREATE USER 'replicator'@'%' IDENTIFIED BY '***';

    mysql# GRANT REPLICATION SLAVE ON * . * TO 'replicator'@'%'
    IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0
    MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0
    MAX_USER_CONNECTIONS 0 ;

    Remove the "" parts

    1. Hi Nathan, that’s a complex subject 😉 These command line tools are commonly referred to as SSH Clients, SSH being the type of connection you typically make between your local system and the remote server. On Mac and Linux systems there’s a built-in tool you can use. It’s called Terminal. On Windows you have to install one, for example PuTTY. You can even get utilities for iOS and Android devices.

      Note that this article discusses two types of connections: the first is to the remote server’s operating system, and the second is to the MySQL server. Although they may be installed on the same system, the command line syntax is very different (shell commands vs. MySQL commands).

      1. It includes stored procedures and functions in the dump. It’s crucial to back those up too. Thanks for this article btw.

        From the man page:

        · –routines, -R

        Include stored routines (procedures and functions) for the dumped
        databases in the output. This option requires the SELECT privilege
        for the mysql.proc table.

        The output generated by using –routines contains CREATE PROCEDURE
        and CREATE FUNCTION statements to create the routines. However,
        these statements do not include attributes such as the routine
        creation and modification timestamps, so when the routines are
        reloaded, they are created with timestamps equal to the reload
        time.

        If you require routines to be created with their original timestamp
        attributes, do not use –routines. Instead, dump and reload the
        contents of the mysql.proc table directly, using a MySQL account
        that has appropriate privileges for the mysql database.

Add your voice!