MySQL Master-Master Replication on CentOS

In this post i want to describe step installation of MySQL Master-Master Replication.

in this case for example:

Master 1 IP Address : 192.168.1.100
Master 2 IP Address: 192.168.2.200
Database to replicate: db1, db2, and db3

1. We need to install MySQL and dependencies on both server (master1 and master2)

#yum install mysql mysql-server

set mysql password because default password is null

#mysqladmin -u root password new_mysql_password

2. Create a user with replication privileges on both server:

On master 1:

#mysql -u root -p
Enter Password:
mysql> CREATE USER 'slave_user'@'192.168.2.200' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.2.200' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)


Reload your privileges;
mysql> FLUSH PRIVILEGES;

Do the same with master 2 with master 1 hosts:
#mysql -u root -p
Enter Password:
mysql> CREATE USER 'slave_user'@'192.168.1.100' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.1.100' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

Reload your privileges:
mysql> FLUSH PRIVILEGES;

3. Configure MySQL nodes

On master 1 make changes my.cnf configuration:

Comment this line:
#bind-address = 127.0.01

or change the ip address without comment the line:
bind-address = 0.0.0.0

add this line below on master 1 (server-id=1):


server-id = 1

master-host = 192.168.2.200
master-user = slave_user
master-password = password
master-connect-retry = 30
slave-net-timeout = 30
replicate-do-db = db1
replicate-do-db = db2
replicate-do-db = db3

log-bin = /var/log/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/master-log-bin.index
binlog-do-db = db1
binlog-do-db = db2
binlog-do-db = db3

relay-log = /var/lib/mysqld-relay-bin
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

Do the same with master 2 (server-id=2):

server-id = 2

master-host = 192.168.1.100
master-user = slave_user
master-password = password
master-connect-retry = 30
slave-net-timeout = 30
replicate-do-db = db1
replicate-do-db = db2
replicate-do-db = db3

log-bin = /var/log/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/master-log-bin.index

binlog-do-db = db1
binlog-do-db = db2
binlog-do-db = db3

relay-log = /var/lib/mysqld-relay-bin
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

now restart your mysql service

#service mysqld restart
Stopping MySQL:                                            [  OK  ]
Starting MySQL:                                             [  OK  ]


4. Slaves Configuration:

Enter your MySQL shell:
#mysql -u root -p

Unlock your db tables with read lock on both server:
mysql>USE db1;
mysql>UNLOCK TABLES;
mysql>FLUSH TABLES WITH READ LOCK;

Setup slave replication on master 1:
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.2.200', MASTER_USER = 'slave_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS=98;

Setup slave replication on master 2:
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.1.100', MASTER_USER = 'slave_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS=158;

Now, start your replication on both server:
mysql> SLAVES START;

Check your slave states and your configuration:
mysql>SHOW SLAVE STATUS\G

If your configuration running well, output must be like this for example:

mysql> show slave status\G
************************ 1. row *************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.200
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: slave-relay.000001
Relay_Log_Pos: 198
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2,db3
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: 1581
Relay_Log_Space: 235
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
1 row in set (0.00 sec)

Check your master replication:
mysql>SHOW MASTER STATUS;
+------------------+----------+----------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+----------------------+-------------------+
| mysql-bin.000006 | 98 | radius,smbind,Syslog | |
+------------------+----------+----------------------+-------------------+
1 row in set (0.00 sec)

Load database from master:
mysql> LOAD DATA FROM MASTER;

That’s all…your MySQL database will be replicate on both side of servers. to make sure your configuration running with no errors please check your log when you do the start or stop slaves command.

#tail -f /var/log/messages

Download this guide in PDF