, , , ,

MySQL replication allows you to propagate changes made in a “master” database to other “slave” databases. What’s the point in that? Well for starters it gives you a running backup in case one of your database servers fails. Though it’s not a replacement for “real” backups it can form part of a highly available architecture. The master slave structure is also used to scale web applications; having multiple slaves that the application reads from and then all writes go to the master server. Or some variation.

There’s quite a few other replication tutorials out there, but one more won’t hurt.. will it?

On the face of it MySQL replication is actually quite simple and there isn’t an awful lot that needs to be done to get it up and running. Having an idea of how it all fits together is probably a good place to start.

A master server will commit a change to one of it’s databases, it will also record what the action it performed was, in the form of a binary log. A slave server will be set up to log into the master server and read the log of changes. When it finds a change that it hasn’t carried out itself it will run the action locally, essentially mimicking what the master server did. This form of replication is asynchronous, meaning that there will be a delay in the time between a master and a slave committing the same record. If a slave goes offline it will be able to catch up on the actions it missed by going back in the log and continuing from where it left off.  Though if the slaves lag behind your master you will get different responses depending on which node you query.

The master server doesn’t actually need to do anything special to enable replication the main things are:

  1. It needs to be accessible via the network so other nodes can read its logs.
  2. It needs to be logging the changes it makes so the slaves can read them.
  3. It needs to have a user account that allows a slave to log in.

The slave needs a few more things setting up but not that many:

  1. It will need networking enabled
  2. It needs to know what master to contact and how to log in.
  3. It needs to know what log to read
  4. It will need to know the position in the log to start reading from
  5. It will need to have its slave function enabled

Ok onto something more practical: setting up a master-slave configuration with two nodes. I’m using Ubuntu for this but most steps should be the same for other Liunx distros you may want to double check some defaults though.

For simplicity I have two machines: master (ip:, slave (ip: and I’ll be replicating the database names.

If you haven’t already got mysql installed on both nodes, do that now:

sudo apt-get install mysql-server mysql-client

On master open my.conf:

$ sudo vi /etc/mysql/my.cnf

Check the bind-address is set to your external IP address:

bind-address            = #your IP address here#

In the [mysqld] section find and uncomment or add these lines:

log_bin                 = /var/log/mysql/mysql-bin.log
server-id               = 1
expire_logs_days        = 10
max_binlog_size         = 500M
binlog_do_db            = names

Where log_bin refers to the location of the bin log on your machine and binlog_do_db is the name of the database you want to replicate. Server-id needs to be unique for each node in the replication set.

I also set:

auto-increment-increment = 10
auto-increment-offset = 1

To increase auto-incrementing keys by 10 and start at an offset by one. This isn’t necessary for a single master cluster but if you add another master later it will stop you getting clashing keys.

That’s it for the master’s config. Save the file and restart mysql:

sudo /etc/init.d/mysql restart

If mysql starts up again we’re ok to move on. If not, take a look at the /var/logs/syslog for error messages.

Next we need to add a user to masters database to allow the slave to login.

Login to mysql:

mysql -u root -p

And enter this at the mysql promt:

grant replication slave on *.* to 'slave'@'%' identified by 'slave';

This will set up a a user called ‘slave’ on the master database server with access to all databases with replication rights. The slave user’s password is slave. For production use this should be tightened up with a stringer password and perhaps limiting the hosts the user is allowed to log in from.

The last thing to do on the master is to make a copy of the database we want to replicate and copy it to our slave node and find out what position it is up to in the log file. We need this so we can tell the slave where to start reading from.

at the mysql promt of the master:

use names;
flush tables with read lock;
show master status;

We lock the table to make sure it doesn’t get modified before we make a copy of it. Make sure you stay in the mysql session otherwise you will loose the lock and make a note of the position and file output of the show master status statement. We’ll need it in a minute.

Open a new session on the master node and make a copy of the database. I’ll use msqldump for this but other methods might be more applicable for large databases.

cd /tmp/
mysqldump -u root -p  exampledb > exampledump.sql
scp exampledump.sql andy@

This made a dump of our locked database and copied it to our slave host. We can now unlock our database:

(on master at the mysql prompt)

unlock tables;

We’re now done with our master server, it should be logging transactions to it’s log file and be ready to accept connections from the slaves.

On our slave node now we need to configure the slave options, import our exiting database and start it slaving.

Open up the my.conf on slave:

sudo vi /etc/mysql/my.cnf

make sure it’s bound to an ecternal IP address:

bind-address    =

Again within the [mysqld] section make sure these options are set:

server-id               = 2

replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 2

master-host =
master-user = slave
master-password = slave
master-connect-retry = 60

replicate-do-db = exampledb

The server-id is different from the master’s, replicate-same-server-id =0 will stop it applying actions that it initiated if it becomes a master. The master-host, master-user, master-password, master-connect-retry and replicate-do-db specify what master to connect to and what database to replicate.

Save the changes and restart mysql:

sudo /etc/init.d/mysql restart

Next we need to import the database that we copied over from the master.

(on slave)

mysql -u root -p exampledb < exampledump.sql

Log into mysql on the slave and stop the slave service:

stop slave;

Now we can tell the slave where to start reading our master’s log from:

change master to master_host = '', master_user='slave',
master_password='slave',master_log_file = 'mysql-bin.000001', master_log_pos=306;

Substitute “mysql-bin.000001” and “306” for the values you noted earlier when you ran “show master status” on the master, also set master host, user password etc for your master.

Lastly start the slave:

start slave;

You should now be replicating from your master to slave. As a check run:

show slave status;

on the slave. Check to make sure Slave_IO_Running and Slave_SQL_Running are set to Yes. If not check the logs for possible errors.

You can also set up multi-master replication where changes to both nodes are propagated. To do this, you apply the slave settings above to the master and vice versa.

The guide above should only be used for experimentation. There are a number of other settings that can be altered to tailor the replication for your needs. You should also be familiar with how to monitor the replication and fixing it if it stops. The MySQL documnentation is a good start.