MySQL DataBase Master-Master Replication
Required Packages
mysql
mysql-server
Master1 server ip: 192.168.0.82
Master2 server ip: 192.168.0.83
Slave username: user
Slave password: user
Your data directory is: /var/lib/mysql/
In Master1 Database machine edit /etc/my.cnf :
# let’s make it so auto increment columns behave by having different increments on both servers
auto_increment_increment=2
auto_increment_offset=1
# Replication Master Server
# binary logging is required for replication
log-bin=/var/log/master1-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
# required unique id between 1 and 2^32 – 1
server-id = 1
#following is the slave settings so this server can connect to master2
master-host = 192.168.0.83
master-user = slaveuser
master-password = slavepw
master-port = 3306
Save and exit.
In Master2 Database machine edit /etc/my.cnf :
# let’s make it so auto increment columns behave by having different increments on both servers
auto_increment_increment=2
auto_increment_offset=2
# Replication Master Server
# binary logging is required for replication
log-bin=/var/log/master2-bin
#Ignore database to replicate
binlog-ignore-db=mysql
binlog-ignore-db=test
# required unique id between 1 and 2^32 – 1
server-id = 2
#following is the slave settings so this server can connect to master1
master-host = 192.168.0.82
master-user = user
master-password = user
master-port = 3306
Save and exit.
Enter the following command on Master1 to create/grant user level access on the database to Master2.
mysql> grant replication slave on *.* to slaveuser@'192.168.0.83' identified by 'slavepw';
mysql>FLUSH PRIVILEGES;
Now, enter the following command on Master2 for create/grant user level access on the database to Master1.
mysql> grant replication slave on *.* to user@'192.168.0.82' identified by 'user';
mysql>FLUSH PRIVILEGES;
Now make slave both machines to each other.
On Master1 enter the following:
mysql> show master status;
+----------------------+----------+--------------+-----------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+-----------------------+
| mysql-bin-log.000017 | 289 | | mysql,test,mysql,test |
+----------------------+----------+--------------+-----------------------+
1 row in set (0.00 sec)
NOTE:This mysql-bin-log file used to read data by the slave servers to replicate Database. Copy file name and position i.e; (289).
Now make Master2 the slave of Master1.
Enter the following command on Master2:
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.82', MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='mysql-bin-log.0000017', MASTER_LOG_POS=98;
mysql>start slave;
mysql>show slave status\G;
It will show you something inside the output:
Master_Log_File: mysql-bin-log.000017
Read_Master_Log_Pos: 289
Relay_Log_File: localhost-relay-bin.000026
Relay_Log_Pos: 239
Relay_Master_Log_File: mysql-bin-log.000017
|
|
Seconds_Behind_Master: 0
If it is showing Seconds_Behind_Master not “NULL” then this slave is working fine.
Then for making Master1 slave to Master2, we need Master2 machine’s mysql-bin-log and position, for that run the following command for required information:
mysql> show master status;
+----------------------+----------+--------------+-----------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+-----------------------+
| mysql-bin-log.000002 | 574 | | mysql,test,mysql,test |
+----------------------+----------+--------------+-----------------------+
1 row in set (0.00 sec)
NOTE:This mysql-bin-log file used to read data by the slave servers to replicate database. Copy file name and position i.e; (574).
Now make Master1 the slave of Master2
Enter the following command on Master1:
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.83', MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='mysql-bin-log.000002', MASTER_LOG_POS=98;
mysql> start slave;
mysql>show slave status\G;
It will show you something inside the output:
Master_Log_File: mysql-bin-log.000002
Read_Master_Log_Pos: 574
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 239
Relay_Master_Log_File: mysql-bin-log.000002
|
|
Seconds_Behind_Master: 0
If it is showing Seconds_Behind_Master not “NULL” then this slave is working fine.
And now do some testing like create/delete database and tables on one machine and check on other, is it showing the changes???? if both are showing same data then that means your replication is working fine.
