Setting up Master-Slave Replication with MySQL

Ovais Tariq -

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.