MySQL Master-Master Replication in 10 Steps

When you need to have both MySQL servers synchronised and changes to Server 1 should be reflected on Server 2 and vise-versa then you need Master-Master replication. That’s easy to do, here 10 simple steps.

Prerequisites:

OS: Ubuntu Server (or any Linux server)

RDBMS: MySQL Community Edition

Let’s assume that:

Server 1 IP address: 192.168.3.138

Server 2 IP address: 192.168.3.123

We have two databases with the same names on both servers: ‘database_name’ on Server 1 and ‘database_name’ on Server 2;

STEPS:

 

1. Change /etc/my.cnf of Server 1

server_id  = 1
log_bin  = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index   = /var/log/mysql/mysql-relay-bin.index
expire_logs_days   = 10
max_binlog_size   = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1

2. Change /etc/my.cnf of Server 2

server_id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2

 

3. If there is some data exists on Server 1 database then we need to export data from the DB on Server 1 and import to another DB on the Server 2 to be on the same page:

 

On Server 1:

mysql> FLUSH TABLES WITH READ LOCK;
$> mysqldump -u root -p database_name > ~/database_name.sql
mysql> UNLOCK TABLES;

 

On Server 2:

$> mysqladmin -u root -p create database_name
$> mysql -uroot -p database_name < database_name.sql

 

4. Create replication users on both servers:

on Server 1

mysql> CREATE USER ‘replication’ IDENTIFIED BY ‘password’

on Server 2

mysql> CREATE USER ‘replication’ IDENTIFIED BY ‘password’

 

5. Set up replication permissions on both servers

on Server 1 (Server 2 IP Address is on top of this document):

GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’192.168.3.123’ IDENTIFIED BY ‘password’;

on Server 2 (Server 1 IP Address is on top of this document):

GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’192.168.3.138’ IDENTIFIED BY ‘password’;

(check this by doing following command:

from Server 1:

$> mysql -h 192.168.3.123 -P 3306 -u replication -p

from Server 2:

$> mysql -h 192.168.3.138 -P 3306 -u replication -p

If connection successful then everything is ok by far.

 

6. Check master status and remember information:

on Server 1 (138):

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |  1010    |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

 

on Server 2 (123):

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 |  314     |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

 

7. Set up Slave Server 1 for Server 2:

mysql> CHANGE MASTER TO master_host=’192.168.3.123′, master_port=3306, master_user=’replication’, master_password=’password’, master_log_file=’mysql-bin.000010′, master_log_pos=314;

 

8. Set up Slave Server 2 for Server 1:

mysql> CHANGE MASTER TO master_host=’192.168.3.138′, master_port=3306, master_user=’replication’, master_password=’password’, master_log_file=’mysql-bin.000013′, master_log_pos=1010;

 

9. Start Slave on Server 1:

mysql> START SLAVE;

and check Slave status by this command:

mysql> SHOW SLAVE STATUS\G

 

10. Start Slave on Server 2:

mysql> START SLAVE;

and check Slave status by this command:

mysql> SHOW SLAVE STATUS\G

 

Now your to mysql servers are synchronised and all database changes will be reflected in both databases.