MySQL Master To Master Replication

Replication is used to keep two or more databases synchronized by replicating either executed DML/statement or data set changes from a master server to one or multiple slave servers.

There are three methods of logging the data for replication: Statement-based replication (SBR) replicates SQL statements that modify data. Row-based replication (RBR) replicates only the changed rows. Mixed-based replication (MBR) is statement-based replication and row-based replication. Formerly for all versions of mysqld, statement-based replication is the default method offlogging. Utmost all storage engines support all three approaches to logging data.

MySQL Master-Master replication scales up the system and improves performance by reducing the overhead of the backup and provide redundancy for the active application. If you are not getting what is it, It’s just like two MySQL servers keeping each other updated. With replication, two separate MySQL servers act as a cluster. Database clustering is mainly suitable for high availability application configurations

Master/master replication is achieved by setting up ServerA to be a slave of ServerB and setting up ServerB to be a slave of ServerA.

Assumption: This article does not provide the steps for installation of MySQL server, I am assuming that Mysql server (single instance) has been installed on both servers:

To configure a MySQL master to master replication on Linux Servers we need two servers, information is like the following:

server A: 192.168.1.2

server B: 192.168.1.3

OS: RHL 6.4 x86_64

MySQL server: 5.6.17 x86_64

Prechecks:

Disable the firewall on both servers:

service iptables stop
chkconfig iptables off
service iptables status

Both servers should be accessible to each other, In order to check the accessibility ping each server.

Comment out the bind-address attribute or set it to (bind-address= 0.0.0.0) in my.sandbox.cnf or my.cnf file on both servers. I have mentioned my.sandbox.cnf, this for in case you are using MySQL::Sandbox

First, we will Enable replication from server B to A

STEP 1:

ON server A: 192.168.1.2

edit my.sandbox.cnf or my.cnf and add below values server-id should be > 0; in case you don’t know the location of my.cnf file use this command on os shell “mysql –help | grep my.cnf”, usually my.cnf is in /etc/ directory

server-id=1
replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=1
relay-log=serverA-relay-bin
relay-log-index=serverA-relay-bin.index
master-info-file=mysqk-master.info
relay-log-info-file=serverA-relay-log.info
log-bin=mysql-bin

ON server B: 192.168.1.3

edit my.sandbox.cnf or my.cnf and add below values server-id should be > 0;

server-id=2
replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=2
relay-log=serverB-relay-bin
relay-log-index=serverB-relay-bin.index
master-info-file=mysqk-master.info
relay-log-info-file=serverB-relay-log.info
log-bin=mysql-bin

STEP 2:

ON server A: 192.168.1.2

create user 'syncstndby' identified by 'warsaw';
grant replication slave on *.* to 'syncstndby'@'192.168.1.3'  identified by 'warsaw';
flush privileges;

On server B: 192.168.1.3

create user 'syncstndby' identified by 'warsaw';
grant replication slave on *.* to 'syncstndby'@'192.168.1.2'  identified by 'warsaw';
flush privileges;

STEP 3:

ON SERVER B: 192.168.1.3

mysql > SHOW GLOBAL VARIABLES LIKE 'PORT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 5617 |
+---------------+-------+
1 row in set (0.00 sec)


mysql > show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      423 | |             | |
+------------------+----------+--------------+------------------+-------------------+

ON SERVER A: 192.168.1.2

CHANGE MASTER TO 
MASTER_HOST='192.168.1.3',
MASTER_USER='syncstndby',
MASTER_PASSWORD='warsaw',
MASTER_PORT= 5617, 
MASTER_LOG_FILE='mysql-bin.000002',  
MASTER_LOG_POS=423; 

Now you will be thinking from where I am picking the values for these attributes, don’t worry I am explaining it, Pick the MASTER_PORT  value from the above query (SHOW GLOBAL VARIABLES LIKE ‘PORT’) in this case, our port is 5617, Pick the MASTER_LOG_FILE value from column File in the above query (“show master status;”) that is  Mysql-bin.000002), Pick the MASTER_LOG_POS value from column position in the above query that is 423)

STEP 4:

Now you can start the slave

mysql>start salve:
mysql> show slave status \G;

NOW replication from B to A has been enabled

STEP 5 

In this step we will test the replication from server B to A:

ON MASTER (server B): 192.168.1.3

show schemas;
create database reptest;
create table reptest.simples (id int not null primary key) ;
insert into reptest.simples values (999),(1),(2),(3);

ON SLAVE: 192.168.1.2 (server A)

show schemas;
use reptest;
select * from reptest.simples;

Now we will enable replication from server A to B

STEP 6:

ON SERVER A: 192.168.1.2

mysql > SHOW GLOBAL VARIABLES LIKE 'PORT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 5617 |
+---------------+-------+
1 row in set (0.00 sec)

mysql > show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 | |             | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

ON SERVER B: 192.168.1.3

CHANGE MASTER TO 
MASTER_HOST='192.168.1.2',
MASTER_USER='syncstndby',
MASTER_PASSWORD='warsaw',
MASTER_PORT= 5617, 
MASTER_LOG_FILE='mysql-bin.000004', 
MASTER_LOG_POS=120; 

Pick the MASTER_PORT  value from the above query (SHOW GLOBAL VARIABLES LIKE ‘PORT’) in this case, our port is 5617, Pick the MASTER_LOG_FILE value from column File in the above query (“show master status;”) that is  Mysql-bin.000004), Pick the MASTER_LOG_POS value from column position in the above query that is 120)

Now you can start the slave

mysql> show slave status \G;
mysql>start salve:
mysql> show slave status \G;

STEP 7

OK so our environment is set, Now we are going to test if our environment is working or not. 

ON 192.168.1.2 (server A)

insert into reptest.simples values (777),(41),(15),(61);

Here we have not created the table simple in DB name reptest as it was already replicated to server A when created the DB reptest and table at server B.

ON 192.168.1.3  (Server B)

use reptest;
select * from reptest.simples;

Hurray!! you can see that all is set and our Master to Master replication is set.

WARNING: It is not brainy to have your application executing DML on both servers at the same time. Writing to both servers at the same time makes a reasonably durable fast failover for high availability but has no performance gains. Both servers have to perform all DML, whether the DML come directly from clients or come via replication