MySQL Replication-Master-Master Replication

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.

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

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;

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;

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