MySQL Master Master Repliction

This tutorial describes how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). In my case I need two masters that are synchronized with each other so that if one of them drops down, other could take over and no data is lost. Similarly when the first one goes up again, it will still be used as slave for the live one.

Here  is a basic step by step tutorial, that will cover the mysql master and slave replication and also will describe the mysql master and master replication.

Notions: we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.

Step 1:

Install mysql on master 1 and slave 1. configure network services on both system, like

Master 1/Slave 2 ip: 10.11.218.251

Master 2/Slave 1 ip : 10.11.218.252 

Step 2: Setup Master 1

On Master 1, make changes in my.cnf:

#vim /etc/my.cnf

log-bin=mysql-bin

server-id=1

binlog-ignore-db=”mysql”

* Restart MySQL Master Service

# killall mysql mysqld mysqld_safe

# mysqld_safe –user=mysql &

* Create an user on the Master 1 that allows replication on the Slave 1.

mysql> GRANT REPLICATION SLAVE ON *.* TO replicator@”10.11.218.252″ IDENTIFIED BY ‘123456’;

mysql> flush privileges;

mysql> quit

* And then lets see the Master status

mysql> show master status;

+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 340 | | mysql |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

Note: the File and Position row for use on the Slave server.

Step 3: Setup Master 2

On Master 2, make changes in my.cnf:

#vim /etc/my.cnf

log-bin=mysql-bin

server-id=2

binlog-ignore-db=”mysql”

* Restart MySQL Master Service

# killall mysql mysqld mysqld_safe

# mysqld_safe –user=mysql &

* Create an user on the Master 2 that allows replication on the Slave 2.

mysql> GRANT REPLICATION SLAVE ON *.* TO replicator@”10.11.218.251″ IDENTIFIED BY ‘123456’;

mysql> flush privileges;

mysql> quit

* And then lets see the Master status

mysql> show master status;

+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000002 | 330 | | mysql |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

Note: the File and Position row for use on the Slave server.

Step 4: Setup Slave 1

Configure replication and start the Slave 1 service.

mysql>STOP SLAVE;

mysql> CHANGE MASTER TO MASTER_HOST=’10.11.218.251′, MASTER_USER=’replicator’, MASTER_PASSWORD=’123456′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=340, MASTER_CONNECT_RETRY = 10 ;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

Step 5: Setup Slave 2

Configure replication and start the Slave 2 service.

mysql>STOP SLAVE;

mysql> CHANGE MASTER TO MASTER_HOST=’10.11.218.252′, MASTER_USER=’replicator’, MASTER_PASSWORD=’123456′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000002′, MASTER_LOG_POS=330, MASTER_CONNECT_RETRY = 10 ;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G