Replication in MYSQL-Part 3 Chain Replication

This is the third part of my series of documentation on MySQL replication. In this part I will describe chain replication technique.

The chain replication setup looks as follows:

In the above figure I have shown simplest of chain replication using three servers. There can be many servers in the chain.

In the above Chain replication setup, the Server1 is the master server; Server2 is the slave of the master Server1 and Server2 acts as the master of the server Server3. i.e. Server2 receives changes from Server1 and provides the changes to Server3. This way we can add many servers to this setup as per our requirement.

Chain replication can be useful in the following scenario:

1) We can have many replicated servers without having load on the main master server.

2) We have the master server in the data center; we need two replicated servers in our local office (suppose one server is for developers, one server for accounts/management team reporting). In this case we can configure chain replication. We can have one replicated server (Server2 in the diagram) from the live server (Server1 in the diagram) and another replicated server (Server3 in the diagram) from the Server2. This will save our bandwidth and load on the live server. 

Now we are going to see how to build this setup

Please read my previous two postings on MySQL replication before reading the below part of this document. In this document I am going to show only the configuration settings that are required for this setup. For loading data and gathering binary log name and offset for setting up replication, please read my previous posts on replication.

The IPs of the servers are as follows:
Server1 : 172.16.0.104
Server2: 172.16.0.105
Server3: 172.16.0.106

Server1 (Master Server, IP 172.16.0.104):

Configurations required in the master server’s configuration file for replication are as follows:

 Take backup of Server1 and note the current binary log name and offset (Please read my previous post Replication in MYSQL-Part 2 ). We will use this log name and offset in Server2 

Server2 (Slave of Server1 and Master of Server3, IP 172.16.0.105):

We have to enable binary log in the Server2, as it will act as a master also. Normally, slave server does not write in its own binary log the updates it receives from the master server. We have to instruct it to write the changes it receives from Master server into its own binary log. Configurations required in Server2’s configuration file are as follows:

log-slave-updates tells MySQL to write the changes it receive from master server into the binary log.

Restore the backup of Server1 in Server2 (Please read my previous post Replication in MYSQL-Part 2 ).

We have to setup Server2 to receive updates from the Server1.

Start the slave threads in Server2

Now take backup (Please read my previous post Replication in MYSQL-Part 2 ) and record the binary log name and position. We will use the backup and binary log name and position in the Server3

Server3 (Slave of Server2, IP 172.16.0.106):

Assign server-id to Server3.

Open the MySQL configuration file and add the following line in [mysqld] section

server-id=3

Restore the backup of the Server2 (Please read my previous post Replication in MYSQL-Part 2 )

 Setup Server3 to receive updates from Server2

Start the slave threads in Server3:

These are the configurations required to make our chain replication work.

NOTE: Please refer to my previous posts: Replication in MYSQL-Part 1 and Replication in MYSQL-Part 2

জয় আই অসম,

প্রণব শর্মা