Setting up Master-Slave Replication with MySQL

Replication enables data from one MySQL server, called the master, to be replicated to one or more MySQL servers, called slaves. Replication is mostly used as a scale-out solution. With scale-out solution we are basically spreading the load among multiple slaves to improve performance. In this solution, all writes and updates take place on the master server, while reads take place on one or more slaves. This model improves both the write performance as well as the read performance across an increasing number of slaves.

This scale-out solution that I have discussed above is actually master-slave replication, and this is the kind of replication that we will be setting up today.

Introduction:

Broadly speaking replication is basically a three step process which works as follows:

  • The master records changes to data in its binary log.
  • The slave copies the changes recorded in the master’s binlog to its relay log.
  • Then the slave replays the change-set recorded in its relay log, applying these changes to its own data.

Now that we have had a little overview of how replication actually works, let’s get our hands dirty at setting up replication.

For the purpose of this article let’s assume the following:

  • server1 with IP 192.168.10.1 is our master, and
  • server2 with IP 192.168.10.2 is the slave.

Setup replication accounts

The first step to do is to create a user account on the master and give it the proper privileges, so that the slave I/O thread can connect to the master and read master’s binary log.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replicator@'192.168.10.2' IDENTIFIED BY 'somepass';

Let’s also understand what the REPLICATION SLAVE and REPLICATION CLIENT privileges mean.
The REPLICATION SLAVE privilege should be given to accounts that are used by slave servers to connect to the master server. Without this privilege, the slave cannot request updates that have been made to databases on the master server.
The REPLICATION CLIENT privilege enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS.

Configure the master

The next step is to enable binary logging and specify the server id on the master:

log-bin = mysql-bin
server-id = 1

Now let’s understand what these two options mean.
log-bin is the binary log basename to generate binary log file names.
server-id option is used in replication to enable master and slave servers to identify themselves uniquely. On the master and each slave, you must use the server-id option to establish a unique replication ID in the range from 1 to 231.

Configure the slave

Now that we have setup the master the next step is to setup slave so that its ready for replication.
The slave requires the following configuration:

log-bin = mysql-bin
server-id = 2
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1

Out of the settings described above, the only essential one is the server-id part, but I have enabled log-bin too and named it similar to the one on master, so that slave can be promoted to master if and when required.

relay-log specifies the location and name of the relay log
log-slave-updates makes the slave log the replicated events to its binary log
read-only prevents data changes on the slaves, because slave is really usually used for reads

Initialize the slave from the master

Now that the slave has been configured as well, the next step is to initialize the slave with data from the master. This is achieved with the following steps (make sure that you follow the steps in the order mentioned, otherwise you will end up with an inconsistent slave):

  • The first thing to do would be to take a consistent dump of all the tables using mysql dump:
    mysqldump -u root --all-databases --master-data=2 > /root/dbdump.db

    Note the user of master-data option. Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. With the master-data value set to 2, the “change master” to statement (that indicates binlog name and position) is written as a comment at the start of the dump file for information purposes. The master-data option also turns on lock-all-tables option, which acquires a global read lock across all tables which ensures that the dump is consistent.

  • Now what we need is the master’s binlog filename and position, so that they can be used on the slave in the “change master to” statement. In the previous step, when we dumped the data using mysqldump we used the master-data option, and hence we have the binlog coordinates as a comment at the start of the dump, so those are the coordinates that we need to remember, as we will use them when we start the slave.
  • Next import the dump file on the slave:
    mysql -u root -p < /root/dbdump.db

Instruct the slave to connect to and replicate from the master

Now we are all set, we have initialized the slave with data from master, and we also have the binlog coordinates with us to start the replication from. So now let's setup the master host and binlog name and coordinates:

mysql> change master to master_host='192.168.10.1',
    -> master_user='replicator',
    -> master_password='somepass',
    -> master_log_file='mysql-bin.000006',
    -> master_log_pos=906;

And finally let's instruct the slave to start replicating.

mysql> start slave;

See how easy it was setting up the master-slave replication. Soon I will be discussing other replication topologies and replciation problems and solutions.

You might also want to read this article about replication.

**Update: Thanks to Gavin Towey for pointing out a mistake in the "Initialize the slave from the master" section of this post. The steps mentioned in "Initialize the slave from the master" have been updated, be sure to go through them again.

  • Gavin Towey

    There’s a critical problem — you tell users to issue SHOW MASTER STATUS; and grab the binlog filename and position, but that only works if that’s done while the database export is happening, or you otherwise coordinate your global read lock and the export! You don’t talk about the critical nature of the timing of that statement. But getting that manually is not necessary — it’s written to the database export — you need to tell your readers to use the values written by mysqldump, and not the ones retrieved manually.

  • http://www.ovaistariq.net/ Ovais Tariq

    Thanks Gavin for pointing that out, I have updated the “Initialize the slave from the master” section of this post, do check it out!

  • Pingback: Setting up Master-Slave Replication with MySQL

  • nav

    user of master-data option ?????

  • http://www.ovaistariq.net/ Ovais Tariq

    the master-data option outputs the replication coordinates (binlog name and binlog position) to the dump file

  • Ric

    Great tutorial – However on 5.5 I also needed to add binlog_format=ROW to the my.ini of both master and slave.

  • http://www.ovaistariq.net/ Ovais Tariq

    Ric,
    By default binlog_format has the value “mixed”, but of course if you want to use row based replication then you need to change that to ROW :)

  • Charles

    Hi,

    Above you mentioned that the slave could have log-bin enabled as well so it could be promoted to master.  If this happens and the master is now a slave, how could i tell later on logging on either machine which one is the master and which one is the slave?

    Thanks!

  • EastGhostCom

    Have read many toots.  Yours is the only one to mention this important command and explain it:

    mysqldump -u root –all-databases –master-data=2 > /root/dbdump.db

    Thanks from near DC, EastGhostCom

  • http://www.ovaistariq.net/ Ovais Tariq

    Firstly, the slave is not going to be promoted to a master automatically by MySQL, you would either be using another tool such as MHA or doing it manually. EIther way you could run ‘SHOW SLAVE STATUS’ and check out the value of ‘Master_Host’ on both the master and slave to see who is replicating from where.

  • Pingback: Setting up Replication for MySQL data « KaixersofT { ScriptBlocK } Weblog

  • http://profiles.google.com/andhos Amjad Mohamed

    master_host=’50.16.78.107′Is that the IP of the master database. It is different from the IP mentioned at the top of the article.

  • http://www.ovaistariq.net/ Ovais Tariq

    Fixed it, that was a typo and thanks for highlighting.

  • Pingback: Transfer data in one of mysql table

  • http://profile.yahoo.com/43Q5NAUBCWAZSQZ3VUH3ZJU5ZI alfredo

    i have a problem here:
    when i execute this command:(eliminate double quote just to highlight the message)
    “mysqldump -u root –all-databases –master-data=2 > /root/dbdump.db”
    then i got this message:
    “mysqldump: Got error: 1045: “Access denied for user ‘root’@’localhost’ (using password: NO)” when trying to connect”

    kindly help me how to fix my problem? thanks

  • http://profile.yahoo.com/43Q5NAUBCWAZSQZ3VUH3ZJU5ZI alfredo

    i have a problem here:
    when i execute this command:(eliminate double quote just to highlight the message)
    “mysqldump -u root –all-databases –master-data=2 > /root/dbdump.db”
    then i got this message:
    “mysqldump: Got error: 1045: “Access denied for user ‘root’@’localhost’ (using password: NO)” when trying to connect”

    kindly help me how to fix my problem? thanks

  • http://www.ovaistariq.net/ Ovais Tariq

    That is because you have to provide a username and password, use msyqldump like this:

    mysqldump –user=root –password= –all-databases –master-data=2 > /root/dbdump.db

  • Lwx2615

    I think the id range is from 1 to 232-1;the manual descrip as follows:Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group, and must be a positive integer between 1 and (232)–1 

  • Lwx2615

    oh , bad editor ! 

  • Sreeraj.

    Thanks Ovais for the instructions. Now one question, is this row-based replication or statement-based? I have setup replication following this doc and could you tell me how do I check whether the setup is replication statement-based or row-based? Thanks in advance. :)

  • Sreeraj.

    Got it using ‘SHOW VARIABLES LIKE ‘binlog_format”. Now is there a way to setup the replication as row-based?

  • http://www.ovaistariq.net/ Ovais Tariq

    Sreeraj,

    Binlog format is really a format in which replication events are replicated to the slaves. You can change the binlog_format at anytime by simply updating the variable, you don’t need to re-setup replication. Simply execute the following from the mysql client:
    SET GLOBAL binlog_format=ROW;

    Don’t forget to add the following to the my.cnf file so that correct logging format is used upon MySQL restart:
    binlog_format = ROW