Master-Master Replication Between MySQL Servers

by Applied Informatics

Overview

Most people are familiar with the concept of master-slave replication, where the master database keeps a log of all the statements it gets (selects, inserts, updates, and deletes), and the slave follows along by replaying those statements. And as such, the two databases are kept in sync.

A master-master setup is an extension of that concept, it’s your standard master-slave setup, but the master database is made to be a slave to the original slave database — so each master is also a slave.

This allows us to write on either database and have it automatically replicate to the other. Reads are also replicated so the caches will be hot on both machines.

Setup Details

We shall be using the below listed servers and test databases for achieving the configuration.

Master-A: 123.123.123.1

Master-B: 123.123.123.2

Database: test_db

Steps Involved

The complete set up actually involves following steps.

  • Enable binary logging for both databases
  • Export data from database A
  • Import data into database B
  • Setup an SSH tunnel between database A and B
  • Setup replication permissions
  • Make B a slave of A
  • Make A a slave of B

 

Step 1. Setting Up MySQL Server Master-A

To enable binary logging, edit the MySQL configuration file and add following lines under
[mysqld] section, 

$ vim /etc/mysql/my.cnf  OR  nano /etc/mysql/my.cnf

[mysqld]

log-bin=mysql-bin

binlog-do-db=test_db

server-id=1

Restart MySQL server for changes to take effect.

$ service mysql restart

Create an mysql account on Master-1 server with REPLICATION SLAVE privileges through which replication client will connect to master.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'pass123';
mysql> FLUSH PRIVILEGES;

Block write statement on all the tables, so no changes can be made after taking backup. 

mysql> use test_db;
mysql> FLUSH TABLES WITH READ LOCK;

Check the current binary log file name (File) and current offset (Position) value using following command.

mysql> SHOW MASTER STATUS;

+——————+———-+————–+——————+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000011 |      332 | test_db         |                  |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

The above output shows that the current binary file is using mysql-bin.000001 and offset value is 332. Note down these values to use on Master-2 server in next step.  

Take a backup of database and copy it to another mysql server.

$ mysqldump -u root -p test_db > test_db.sql
$ scp test_db.sql xxx.xxx.xxx.xx:/opt/

After completing backup remove the READ LOCK from tables, So that changes can be made.

mysql> UNLOCK TABLES;

 

Step 2. Setting Up MySQL Server Master-B

Edit mysql Master-2 configuration file and add following values under [mysqld] section.  

 

$ vim /etc/mysql/my.cnf  OR  nano /etc/mysql/my.cnf

[mysqld]

log-bin=mysql-bin

binlog-do-db=test_db

server-id=2

Note: server-id will never be similar with other master or slave servers.

Restart MySQL server. If you have already configured replication use –skip-slave-start in start so as not to connect immediately to master server.

$ service mysql restart

Restore database backup taken from master server.

$ mysql -u root -p test_db < /opt/test_db.sql 

Create an mysql account on Master-1 server with REPLICATION SLAVE privileges through which replication client will connect to master.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'pass123';
mysql> FLUSH PRIVILEGES;

Check the current binary log file name (File) and current offset (Position) value using following command.

mysql > SHOW MASTER STATUS;

+——————+———-+————–+——————+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000022 |      847 | test_db         |                  |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

The above output is showing that the current binary file is using mysql-bin.000001 and offset value is 847. Note down these values to use in Step 3.  

Setup option values on slave server using following command.

mysql>CHANGE MASTER TO MASTER_HOST='123.123.123.1', MASTER_USER='repl_user',MASTER_PASSWORD='pass123',

MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=332;

Step 3: Complete Setup on MySQL Master-1 

Login to MySQL Master-1 server and execute following command.

 

mysql>  CHANGE MASTER TO MASTER_HOST='123.123.123.2',

    MASTER_USER='repl_user',MASTER_PASSWORD='pass123',

    MASTER_LOG_FILE='mysql-bin.000022',MASTER_LOG_POS=847;

 

Step 4: Start SLAVE on Both Servers

Execute following command on both servers to start replication slave process.

mysql> SLAVE START; 

MySQL Master-Master Replication has been configured successfully on your system and in working mode. To test if replication is working make changes on either server and check if changes are reflecting on the other server.

Leave a Reply

Your email address will not be published. Required fields are marked *

Tools & Practices

Tools and Technologies we use at Applied

Contact us now

Popular Posts