Setting up MySQL Master – Master Replication

Lets assume you have the following configuration

DBServer1 : 172.20.10.217 = (IP Address of Master1/Slave 2)
DBServer2 : 172.20.10.218 = (IP Address of Master2/Slave 1)

Procedure
1. Install mysql on Master1 and Slave1
2. On Master1, make changes in my.cnf :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

# Replication
log-bin=mysql-bin.log
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib/mysql

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

3. On Master1, restart mysql.

 
[root@db1 ~]# /etc/init.d/mysqld start
Starting MySQL:                                            [  OK  ]

4. On Master1, create a replication slave account in mysql.

mysql> grant replication slave on *.* to 'repl'@172.20.10.218
identified by 'slave';

5. On Master1 Check the current log position of the database

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 100      |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

6. Now edit my.cnf on Slave1:

 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

# Replication
log-bin=mysql-bin.log
server-id=2

[mysql.server]
user=mysql
basedir=/var/lib/mysql

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

7. Restart Mysql on Slave1

 
[root@db2 ~]# /etc/init.d/mysqld start
Starting MySQL:                                            [  OK  ]

8. Login to MySQL stop and reset the slave.

 
mysql> stop slave
mysql> reset slave

9. Follow the current position of the logs from Master DB based on the master status above

 
mysql> CHANGE MASTER TO MASTER_HOST='172.20.10.217', MASTER_USER='repl', MASTER_PASSWORD='slave',  MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=100;
Query OK, 0 rows affected (0.09 sec)

10. Restart mysql Slave1 and check the slave status:

mysql> start slave;
mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.10.217
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MASTERMYSQL01-bin.000004
Read_Master_Log_Pos: 100
Relay_Log_File: MASTERMYSQL02-relay-bin.000015
Relay_Log_Pos: 3630
Relay_Master_Log_File: MASTERMYSQL01-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 4
Relay_Log_Space: 3630
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: 1519187

1 row in set (0.00 sec)

Take note of the highlight lines Slave_IO_Running and Slave_SQL_Running state value must be YES.

11. On Master1:

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000004 |      100 |              |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec

The above scenario is consider as Master-Slave!

Next we will create a slave master scenario for the same systems and so the scenario when combined can be called as master master.

12. On Master2/Slave1 Create a replication slave account:

mysql> grant replication slave on *.* to 'repl'@172.20.10.217 identified by 'slave2';

13. On Master2/Slave1 Check the current log position of the database

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 100      |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

14. On Master1/Slave2 Follow the current position of the logs from Master1 DB based on the master status above

 
mysql> CHANGE MASTER TO MASTER_HOST='172.20.10.218', MASTER_USER='repl', MASTER_PASSWORD='slave2',  MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=100;
Query OK, 0 rows affected (0.09 sec)

15. On Master1/Slave2 Start Slave:

mysql> start slave;

16. On mysql Master1/Slave2:

mysql> show slave status\G;

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 172.20.10.218 
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: Mysql1MYSQL02-bin.000004
        Read_Master_Log_Pos: 100
             Relay_Log_File: Mysql1MYSQL01-relay-bin.000004
              Relay_Log_Pos: 120
      Relay_Master_Log_File: Mysql1MYSQL02-bin.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        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: 100
            Relay_Log_Space: 120
            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: 103799
1 row in set (0.00 sec)

ERROR:
No query specified

Check the Slave_IO_Running and Slave_SQL_Running make sure states are in Yes . To test that its working fine create tables in the database and you will see changes in slave.

You have now a setup called Master-MasterReplication!

Spread the love

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.