TECHIES WORLD

For Techs.... Techniques.... Technologies....

CpanelLinuxMysql

How to configure Master-Master replication between two Mysql servers

admin

MySQL replication is the process by which a single data set, stored in a MySQL database, will be live-copied to a second server. This configuration, called "master-slave" replication, is a typical setup. Our setup will be better than that, because master-master replication allows data to be copied from either server to the other one. This subtle but important difference allows us to perform mysql read or writes from either server. This configuration adds redundancy and increases efficiency when dealing with accessing the data.

This tutorial explains the steps to configure master-master replication between two mysql servers.

First master IP: 192.168.1.1

Second Master IP: 192.168.10.1

Note: Better use same replication user name and password for both servers, to avoid confusion. While list First master Ip in second Master server and vice versa

First Master configuration (192.168.1.1)
****************************************
Here we are going to replicate db database1

Creating log file

touch /var/lib/mysql/mysql-bin.log
chown mysql. /var/lib/mysql/mysql-bin.log
Open /etc/my.cnf and add below contents

server-id = 1
binlog_do_db = database1
log_bin = /var/lib/mysql/mysql-bin.log

save file and resart mysql

Login to mysql

mysql> create user 'replicate_usr'@'192.168.10.1' identified by 'password';
mysql> grant replication slave on *.* to 'replicate_usr'@'192.168.10.1';
mysql> show master status;

mysql> show master status;
+------------------+----------+---------------------------------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------------------------------------------------+------------------+
| mysql-bin.000001 | 107 | database1 | |
+------------------+----------+---------------------------------------------------------+------------------+
1 row in set (0.00 sec)
Please note above values as we need to use these values in Second Master
Second Master Configuration and replication (192.168.10.1)
**********************************************************

Creating log file

touch /var/lib/mysql/mysql-bin.log
chown mysql. /var/lib/mysql/mysql-bin.log

Open /etc/my.cnf and add below contents

server-id = 2
binlog_do_db = database1
log_bin = /var/lib/mysql/mysql-bin.log

save file and resart mysql

Login to mysql and create database "database1" and restore contents

mysql> create user 'replicate_usr'@'192.168.1.1' identified by 'password';
mysql> grant replication slave on *.* to 'replicate_usr'@'192.168.1.1';
mysql> show master status;
+------------------+----------+---------------------------------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------------------------------------------------+------------------+
| mysql-bin.000002 | 103 | database1 | |
+------------------+----------+---------------------------------------------------------+------------------+
1 row in set (0.00 sec)

Note above values as we need to use these values in First Master

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.1.1', MASTER_USER = 'replicate_usr', MASTER_PASSWORD = 'password',MASTER_CONNECT_RETRY = 10, MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;

mysql> slave start;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: replicate_usr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_SQL_Errno: 0
Last_SQL_Error:
Master_Server_Id: 1
1 row in set (0.00 sec)

Make sure above values are displaying correct values of First Master
First Master replication
************************

Login mysql

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.10.1', MASTER_USER = 'replicate_usr', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000002',MASTER_CONNECT_RETRY = 10, MASTER_LOG_POS = 103;

mysql> slave start;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.1
Master_User: replicate_usr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_SQL_Errno: 0
Last_SQL_Error:
Master_Server_Id: 2
1 row in set (0.00 sec)
Once this is done, server starts replication both ways.
Adding new db for replication
*****************************

Open /etc/my.cnf in both first and second master and add below line(database2 is the new db name that we need to replicate)

binlog_do_db = database2

save file and create database "database2" in both First and second Master.
Restart mysql in both servers

Once restart complete, you can restote DB contents to any one of servers and that data will automatically replicate to other server.
For replicating all DB in a server, remove line binlog_do_db from /etc/my.cnf so that all db from server will start replicating new data after mysql restart

Also if you need to skip mysql replication for a certain database you can use

binlog-ignore-db = dbname1

So it will skip nw replications on this db

Important Note: Replication works only for new updates of thar corresponding db.

For example: consider db1 is already in first master(db1 is not present in second master) and we have enabled replication for that db to second master. In that case we have to create that db1 in second master and need to restore contents of db1 before starting replication. If we doesn't create db1 in second master, replication won't work to second master.

Leave a Reply