Master-Master Replication In MySQL

About Master-Master Replication

MySQL replication is the process by which a single data set, stored in a MySQL database, will be live-copied to a second server.

Master-Master replication allows data to be copied from either server to the other one. This subtle but important difference between Master-Slave and Master-Master allows us to perform mysql read or writes from either server. This configuration adds redundancy and increases efficiency when dealing with accessing the data. 

The examples in this article will be based on two cloud servers, named Server C and Server D. . 
Server C:  172.16.1.17
Server D:  172.16.1.28

1.      Setup

>  sudo su

>  apt-get install mysql-client

>  apt-get install mysql-server

2.      Configure MySQL On Server C

>  nano etc/mysql/my.cnf

There are four lines that we need to change, which are currently set to the following:

#server-id       = 1

#log_bin         = /var/log/mysql/mysql-bin.log

#binlog_do_db           = include_database_name

Bind-address  = 127.0.0.1

Change it to :

server-id         = 1

log_bin           = /var/log/mysql/mysql-bin.log

binlog_do_db = example

#bind-address = 127.0.0.1

Now, Refresh MySQL

>  service mysql restart

Open up the MySQL Shell

>  MySQL -u root –p

We need to create a pseudo-user that will be used for replicating data between our two cloud servers. The examples in this article will assume that you name this user "replicator". Replace "password" with the password you wish to use for replication.

>  create user ‘replicator’@’%’ identified by ‘password’;

>  GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ ;

Switch to database

>  USE example;

>  SHOW MASTER STATUS;

Note : Remember File name and position. Here, I’m assuming File- mysql-bin.000001 and Position- 107

>  exit;

3.      Configure MySQL On Server D

>  sudo su

>  apt-get install mysql-client

>  apt-get install mysql-server

Now we need to configure the mysql server:

>  nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes same as server C. The changes are :

server_id                     = 2

relay-log                     = /var/log/mysql/mysql-relay-bin.log

log_bin                       = /var/log/mysql/mysql-bin.log

binlog_do_db             = example

#bind-address             = 127.0.0.1

Restart MySQL

>  service mysql restart

>  mysql –u  root –p

>  create user ‘replicator’@’%’ identified by ‘password’;

>  create database example;

>  grant replicator slave on *.* to ‘replicator’@’%’;

>  slave stop;

>  CHANGE MASTER TO MASTER_HOST=’172.16.1.17’,MASTER_USER=’repliactor’,MASTER_PASSWORD=’password’,MASTER_LOG_FILE=’mysql-bin.000001’,MASTER_LOG_POS=107;

>  slave start;

>  show master status;

 Note : Remember File name and position. Here, I’m assuming File- mysql-bin.000004 and Position- 107

4.      Complete Replication On Server C

>  slave stop;

>  CHANGE MASTER TO MASTER_HOST=’172.16.1.28’,MASTER_USER=’repliactor’,MASTER_PASSWORD=’password’,MASTER_LOG_FILE=’mysql-bin.000004’,MASTER_LOG_POS=107;

>  slave start;

All Done.

Happy to help you !!