Master-Master Replication

Creating and maintaining a duplicate copy of a database or file system on a different computer, typically a server.  The term usually implies the intelligent copying of parts of the source database which have changed since the last replication with the destination.

Replication may be one-way or two-way.  Two-way replication is much more complicated because of the possibility that a replicated object may have been updated differently in the two locations in which case some method is needed to reconcile the different versions.

We have seen how the one-way replication works i.e. Master-Slave; here whatever changes are updated on the Master server is replicated onto the Slave server.

Two-way replication i.e. Master-Master replication will be bit different. As the name itself suggests both the servers will be Master to another.

Master-Master replication allows data in multiple databases to be in sync. For example in a master-master replication, if a row is inserted into one of the databases in a system, it will be automatically propagated to other databases in system. Similarly the updates and deletes too get propagated automatically to other databases.

Configuring the Master-Master Replication

To avoid any confusion we will define our servers as below.  Server 1 and Server 2 will be used as reference wherever required

Server 1: Master 1 and Slave 2

Server 2: Slave 1 and Master 2

On Server 1 (Master 1 and Slave 2):

We will be configuring the Server 1 which will act as both Master and Slave at the same time.

At the prompt> vi /etc/my.cnf (Configuration file where you define the variables for setting up master and slave)

Insert the following information as show in the screen below

If you look at the my.cnf file, we are providing information to the Server 1 to act as Master to Server 2 as well as Slave at the same time. Similarly we would be configuring the Server 2 to act as Slave and Master at the same time to Server 1 later.

Master 1 Information in the my.cnf

‘log-bin’: location where the master log files is saved, whenever there is a change to the database, the log is updated with the changes. These logs are read by the slave who will replicate the same changes onto its server.

You need to specify the location for these logs to be saved on Master; the default location is /var/lib/mysql.

‘binlog-do-db’: As the name suggest, it needs input of a databases name for which you want to create a binary log and save it to above mentioned location for ex: /var/log/mysql. You can specify any number of databases.

It is a good practice to specify a binlog-do-db separately for the number of databases you want to replicate. However there are dba’s who would mention the database names to a single binlog-do-db by use commas as separators.

‘server-id’: You tell the server what is it?  Master or a Slave, id 1 would suggest that it’s a master and id 2 will suggest it is a slave.

After you save and exit the my.cnf, at the mysql prompt grant the access privileges to the server which will be configured as Slave

Slave 2 Information in my.cnf

‘master-host’: IP address of the Server 2.

‘master-connect-retry’: Retries slave need to try connecting to Master 2 on Server 2.

‘master-user’: User name which was granted replication slave privilege on Server 2.

‘master-password’: Password provided by using identified by clause to connect to Master 2 on Server 2.

‘relay-log’: Relay log save events that describes database changes on Server 2.

‘relay-log-index’: Index file contains the names of all used relay log files on Server 2.

After you save and exit the my.cnf, at the mysql prompt grant the access privileges to the server 2 which will be configured as Slave

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’ @ 10.X.X.X   IDENTIFIED BY ‘password’;

Now it is very important to restart the mysql daemon, since you can defined all your variables under [mysqld] in your /etc/my.cnf file, the changes will be picked up only after you restart you daemon.

On Server 2 (Slave 1 and Master 2):

At the prompt> vi /etc/my.cnf (Configuring the Server 2 to act as Slave 1 and Master 2)

Follow the screen below configuration

Once done with the my.cnf file save and exit and at the mysql>

Grant Replication slave on *.* to ‘slave_user’@10.X.X.X identified by ‘mysql123’;

Restart the daemon for the changes made to my.cnf take effect. To start the daemon at the prompt>

mysqld –u root &

Once both the servers have started, use the following commands.

On Server 1:

Show Master Status;

Similarly you will get the same screen on Server 2 on the command Show Master status.

Slave Start;

Show Slave Status\G;

Slave_IO_Running , Slave_SQL_Running should show the status as YES and Slave_IO_State show be ‘waiting for master to send event’.

Use the above commands on Server 2 to check if everything is running fine.

Try creating tables and test the functionality, the tables and data should get replicated both ways. If everything goes fine you have setup your Master-Master replication.