Apr 01 2007

Mysql Replication

Published by at 12:00 am under Mysql

Having a few databases running on Mysql with tables growing to several millions of rows, backups become pretty heavy. If the server fails, it can mean several hours lost, working on setting up a new server and reimporting the data from the backup. I’m not even talking about the potential financial loss due to the downtime. A better reliability can be achieved with Mysql either with a cluster or the replication feature. We will focus on replication in this article. However, this is a quick tutorial on how to setup replication; Check Mysql documentation to get more information about it.


The master can replicate to many slaves, to which different web clients can send their requests. Writes always have to be sent to the master. If they were sent to a slave, they wouldn’t be replicated on other servers.
Note Replication is asynchrone as the slave needs a small delay to be up to date. This is particularly suited to applications such as datawarehouses.


Replication doesn’t provide auto fail-over as it requires a manual intervention. Another article has been written about implementing failover with Heartbeat. It provides the ability to switch automatically to the slave server within seconds.

Getting started

We’ll assume a standalone server is already running with the precious data, and redundancy needs to be implemented. Proceed to the following steps to get your server ready fr replication:

  • Activate binary log on the master server. This is absolutely needed, the slave reads the binary logs to synchronize. my.cnf contains log-bin=mysql-bin in the [mysqld] section in my case
  • Add server-id=1 in my.cnf.
    This id should be unique for each server (The slave will be 2). Restart the server if those options were not activated.
  • Create an account for the slave on the master that’s only allowed to replicate:
    TO 'slave'@'' IDENTIFIED BY 'mypassword';

    where ‘slave’ is the username the slave server is going to connect with, the IP address of the slave.

  • Setup a new machine for the slave server and install Mysql. my.cnf should contain the following:
    . Don’t start the service yet!


Transfer data accross to the slave

  • First block write operations on the master and record the last values from the binary log:

    You should get a result similar to the following:

    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    | my-db1-bin.000002 |      239 |              |                  |

    Make sure to record those values, they will be needed after starting the slave.

  • Shutdown Mysql process on the master and make a copy of the data directory with tar for example. After the tar was done, restart the process. This is the only time the master will be turned off.
  • Copy the tar file accross to the slave into the temp folder, uncompress it and copy the files to the data directory. Don’t copy the log files.
    Make sure the rights are correct (They should be identical to the original files). You now have the master data on the slave.


Activate Replication on the Slave

Start the slave with the following option:


You can also log warnings in the error log to have a better idea of what is going on


Now that the server is started, connect on to it with Mysql client, and add the master’s details:

mysql> MASTER_HOST='',
mysql> MASTER_USER='slave',
mysql> MASTER_PASSWORD='mypassword',
mysql> MASTER_LOG_FILE='my-db1-bin.000002',
mysql> MASTER_LOG_POS=239;

The last 2 lines contain of course the values that were collected on the master.


Replication can start!
The slave status can be checked via the following command:

mysql> show slave status;
| Slave_IO_State             | Master_Host | Master_User |     | Connect_Retry | Master_Log_File   |
| Waiting for master to s... | | slave       |     |            60 | my-db1-bin.000006 |

| Read_Master_Log_Pos | Relay_Log_File       | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running |
|           514457737 | bak-relay-bin.000007 |      26082745 | my-db1-bin.000006     | Yes              |

| Slave_SQL_Running |     | Exec_Master_Log_Pos | Relay_Log_Space |     | Seconds_Behind_Master |
| Yes               |     |           514457737 |        26082745 |     |                     0 |


If replication stops working for some reasons, the last error will be displayed here. This can also find them in mysql error log file.
Disable the –skip-slave-start option from the startup script so replication is activated after a server reboot.

4 responses so far

4 Responses to “Mysql Replication”

  1. auta ze szwecjion 21 Nov 2008 at 12:27 am

    Very interesting article, i bookmarked your blog
    Best regards

  2. Joeon 18 Oct 2009 at 6:06 am

    If you are re-starting a previously running replication, you may need delete the relay-log.info file.

  3. Joeon 18 Oct 2009 at 6:07 am

    If you are restarting a previously running slave and it errors out when synching it, you may need to delete the relay-log.info file

  4. school grantson 01 Nov 2010 at 8:48 pm

    Beneficial info and excellent design you got here! I want to thank you for sharing your ideas and putting the time into the stuff you publish! Great work!

Comments RSS

Leave a Reply