MULTIMASTER REPLICATION MYSQL

Multi-master replication is a method of database replicaton which allows data to be stored by a group of computers, and updated by any member of the group. All members are responsive to client data queries. The multi-master replication system is responsible for propagating the data modifications made by each member to the rest of the group, and resolving any conflicts that might arise between concurrent changes made by different members.
Multi-master replication can be contrasted with master-slave replication, in which a single member of the group is designated as the "master" for a given piece of data and is the only node allowed to modify that data item. Other members wishing to modify the data item must first contact the master node. Allowing only a single master makes it easier to achieve consistency among the members of the group, but is less flexible than multi-master replication.
Multi-master replication can also be contrasted with failover clustering where passive slave servers are replicating the master data in order to prepare for takeover in the event that the master stops functioning. The master is the only server active for client interaction.

Multi-master replication is a method of database replication which allows data to be stored by a group of computers, and updated by any member of the group. All members are responsive to client data queries.Master1 acts as the master for Master2, Master2 acts as the master for master1.


We have three mysql server.

1. 192.168.122.47 (Master1/ Slave2 redhat 6.5 )
2. 192.168.122.251 (Master2 /Slave1 redhat 6.5)
3. 192.168.122.235 (Slave redhat 6.5)

Now start with master1

[root@master1~]# yum install mysql mysql­server ­y

[root@master1~]# mysql_secure_installation

[root@master1~]# yum install mysql mysql­server ­y

[root@master1~]# mysql_secure_installation

[root@master1 ~]# vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic­links=0
server­id = 1
relay­log = /var/lib/mysql/mysql­relay­bin
relay­log­index = /var/lib/mysql/mysql­relay­bin.index
log­error = /var/lib/mysql/mysql.err
master­info­file = /var/lib/mysql/mysql­master.info
relay­log­info­file = /var/lib/mysql/mysql­relay­log.info
log­bin = /var/lib/mysql/mysql­bin
save 

[root@master1 ~]# mysql ­u root ­p
password:

mysql> slave stop;

GRANT REPLICATION SLAVE ON *.* TO 'raj'@'%' IDENTIFIED BY 'keenable@123';

mysql> FLUSH PRIVILEGES;

mysql> FLUSH READ LOCK WITH TABLES;

mysql> salve start; 

mysql> show master status;

mysql> show master status;

+------------------+----------+--------------+-------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+-------------------------+

| mysql-bin.000004 | 331 |               |                              |

+------------------+----------+--------------+-------------------------+

mysql> \q

Now take dump of all database and copy to master2 before this once again going to master1 for 
unloack table
[root@master1 ~]# mysqldump -u root -p --all-databases > /root/dbdump.db

[root@master1 ~]# mysql -­p
password:

mysql> uloack tables;

mysql> \q

[root@master1~]# scp ­r alldb.dbroot@master2.replication.com:
password:

Now going to master2 and import db


[root@master2 ~]#mysql ­u root ­p < /root/dbdump.db

[root@master2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic­links is recommendedsymbolic­links=0
to prevent assorted security risks
server­id = 2
relay­log­index = /var/lib/mysql/mysql­relay­bin.index
log­error = /var/lib/mysql/mysql.err
master­info­file = /var/lib/mysql/mysql­master.info
relay­log­info­file = /var/lib/mysql/mysql­relay­log.info
log­bin = /var/lib/mysql/mysql­bin


##############Entry for Master#############

master­host=192.168.122.47
master­connect­retry=60
master­user=raj
master­password=keenable@123
relay­log = /var/lib/mysql/mysql­relay­bin
relay­log­index = /var/lib/mysql/mysql­relay­bin.index
log­error = /var/lib/mysql/mysql.err
master­info­file = /var/lib/mysql/mysql­master.info
relay­log­info­file = /var/lib/mysql/mysql­relay­log.info
log­bin = /var/lib/mysql/mysql­bin
[mysqld_safe]
log­error=/var/log/mysqld.log
pid­file=/var/run/mysqld/mysqld.pid
save & quite


NOTE: Login into master2 as root user and stop the slave. Then tell the slave to where to look for Master1 log file, that we have write down on master with SHOW MASTER STATUS; command as File (mysql­bin.000004) and Position (331) 
numbers. You must change 192.168.122.47to the IPaddress of the Master1 Server, and change the user and password accordingly.

[root@master2~]#mysql ­u root ­p
password:

mysql> STOP SLAVE;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.122.47', 
MASTER_USER='raj', 
MASTER_PASSWORD='keenable@123', MASTER_LOG_FILE='mysql­bin.000004', 
MASTER_LOG_POS=331;

mysql> show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to sendMaster_Host: 192.168.122.47
Master_User: raj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql­bin.000004
Read_Master_Log_Pos: 331
Relay_Log_File: mysql­relay­bin.000013
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql­bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0
Last_Error: 
Skip_Counter: 0
Exec_Master_Log_Pos: 331
Relay_Log_Space: 551
Until_Condition: None
Until_Log_File: 
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
1 row in set (0.00 sec)
event

mysql> show master status;

+------------------+----------+--------------+-------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+-------------------------+

| mysql-bin.000004 | 185 |               |                              |

+------------------+----------+--------------+-------------------------+

Now time to configure master master replication

Come back to msater1 and edit config file (my.cnf)

[root@master1 ~]# vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic­links=0
server­id = 1
relay­log = /var/lib/mysql/mysql­relay­bin
relay­log­index = /var/lib/mysql/mysql­relay­bin.index
log­error = /var/lib/mysql/mysql.err
master­info­file = /var/lib/mysql/mysql­master.info
relay­log­info­file = /var/lib/mysql/mysql­relay­log.info
log­bin = /var/lib/mysql/mysql­bin

####Second step After done Master­Slave [This entry for master]####

master­host=192.168.122.251
master­connect­retry=60
master­user=raj
master­password=keenable@123
relay­log = /var/lib/mysql/mysql­relay­bin
relay­log­index = /var/lib/mysql/mysql­relay­bin.index
log­error = /var/lib/mysql/mysql.err
master­info­file = /var/lib/mysql/mysql­master.info
relay­log­info­file = /var/lib/mysql/mysql­relay­log.info
log­bin = /var/lib/mysql/mysql­bin
[mysqld_safe]
log­error=/var/log/mysqld.log
pid­file=/var/run/mysqld/mysqld.pid

save & quite

restart the mysql service and check master1.

[root@master1 ~]# /etc/init.d/mysqld restart

[root@master1 ~]# mysql ­u root ­p
password:

mysql> STOP SLAVE;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.122.251', MASTER_USER='raj', 
MASTER_PASSWORD='keenable@123', MASTER_LOG_FILE='mysql­bin.000004', 
MASTER_LOG_POS=185;

mysql> show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to sendMaster_Host: 192.168.122.251
Master_User: raj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql­bin.000004
Read_Master_Log_Pos: 185
Relay_Log_File: mysql­relay­bin.000024
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql­bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0
Last_Error: 
Skip_Counter: 0
Exec_Master_Log_Pos: 185
Relay_Log_Space: 551
Until_Condition: None
Until_Log_File: 
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
1 row in set (0.00 sec)
event

Now go to master2 and edit my.cnf file for create master­-master

[root@master2 ~]#vim /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic­linkssymbolic­links=0
is recommended to prevent assorted security risks
server­id = 2
relay­log­index = /var/lib/mysql/mysql­relay­bin.index
log­error = /var/lib/mysql/mysql.err
master­info­file = /var/lib/mysql/mysql­master.info
relay­log­info­file = /var/lib/mysql/mysql­relay­log.info
log­bin = /var/lib/mysql/mysql­bin

########Entry for salve server as mster#############3
master­host=192.168.122.47
master­connect­retry=60
master­user=raj
master­password=keenable@123
relay­log = /var/lib/mysql/mysql­relay­bin
relay­log­index = /var/lib/mysql/mysql­relay­bin.index
log­error = /var/lib/mysql/mysql.err
master­info­file = /var/lib/mysql/mysql­master.info
relay­log­info­file = /var/lib/mysql/mysql­relay­log.info
log­bin = /var/lib/mysql/mysql­bin
[mysqld_safe]
log­error=/var/log/mysqld.log
pid­file=/var/run/mysqld/mysqld.pid

save & quite 

[root@master1 ~]# /etc/init.d/mysqld restart

[root@master1 ~]# mysql -p
password:

Now create database school on master1 & keenable on master2 and check on both master server

mysql> create database school;=====[Master1]

mysql> create database keenable; ===[Master2]

mysql> show databases;

+------------------------------+

| Database                    |

+------------------------------+

| information_schema   |

| mysql                          |

| school                         |

| keenable                     |

+------------------------------+

4 rows in set (0.00 sec)

Master2

mysql -­p
password:

mysql> show databases;

+----------------------------+

| Database                  |

+-------- -------------------+

| information_schema |

| mysql                        |

| school                       |

| keenable                   |

+----------------------------+

4 rows in set (0.00 sec)

Now time to configure slave server

Slave [192.168.122.235]


[root@slave ~]# yum install mysql mysq­server

[root@slave ~]# mysql_secure_installation

[root@slave ~]# vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic­links is recommended to prevent assorted securitysymbolic­links=0
relay­log = /var/lib/mysql/mysql­relay­bin
relay­log­index = /var/lib/mysql/mysql­relay­bin.index
log­error = /var/lib/mysql/mysql.err
master­info­file = /var/lib/mysql/mysql­master.info
relay­log­info­file = /var/lib/mysql/mysql­relay­log.info
log­bin = /var/lib/mysql/mysql­bin
[mysqld_safe]
log­error=/var/log/mysqld.log
pid­file=/var/run/mysqld/mysqld.pid 
risks

#####Entry for Slave####### 
master­host=192.168.122.47
master­connect­retry=60
master­user=slave_user
master­password=keenable@123
server­id = 3
relay­log = /var/lib/mysql/mysql­relay­bin
relay­log­index = /var/lib/mysql/mysql­relay­bin.index
log­error = /var/lib/mysql/mysql.err
master­info­file = /var/lib/mysql/mysql­master.info
relay­log­info­file = /var/lib/mysql/mysql­relay­log.info
log­bin = /var/lib/mysql/mysql­bin

save & quite

Restart mysql service

[root@slave ~]# /etc/init.d/mysqldrestart

[root@slave ~]# /etc/init.d/mysqld restart

[root@slave ~]# mysq ­uroot ­p
password:

mysql> slave stop;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.122.47', MASTER_USER='raj', 
MASTER_PASSWORD='keenable@123', MASTER_LOG_FILE='mysql­bin.000005', 
MASTER_LOG_POS=588;

mysql> slave start;

mysql> show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.122.47
Master_User: raj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql­bin.000005
Read_Master_Log_Pos: 588
Relay_Log_File: mysql­relay­bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql­bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0
Last_Error: 
Skip_Counter: 0
Exec_Master_Log_Pos: 588
Relay_Log_Space: 406
Until_Condition: None
Until_Log_File: 
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert:Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
1 row in set (0.00 sec)
No

NOTE: All database show into slave. And check on all server  connecting master1 master2 and slave each other master to master and master 
slave.

[root@slave ~]# mysq ­u root ­p
password:

mysql> show databases;

+-------------------------+

| Database |

+-------------------------+

| information_schema |

| mysql |

| school |

| keenable |

+-------------------------+

4 rows in set (0.00 sec)

Now successfully run multi-master replication.