Master to Master Replication in MySQL server

 *********** MASTER SERVER 1 SETTINGS ************

1. Login to master server 1 machine.
 Open terminal. Login as super user.

2.  Edit mysql configuration file (my.cnf) using vi editor. It is in /etc directory. Set server-id = 1. Basically it should be a unique number in both master servers.

3.  root> service mysqld start;

4.  root> mysql -u<username> -p<password>

5.  mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@ 'ip_addr' IDENTIFIED BY ‘slavepass’;
eg. grant replication slave on *.* to 'm1'@  '%' identified by 'p1';

6.  mysql> show master status\G;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Note the bin log file name & position. This will be used in master 2 settings.

7.   root> CHANGE MASTER TO MASTER_HOST='IP_ADDR', MASTER_USER='<repl_user>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='<log_file_name>', MASTER_LOG_POS=<position>;
eg: change master to master_host='129.1.1.19', master_user = 'm2', master_password='p2', master_log_file='mysql-bin.000004', master_log_pos=107;

8.  mysql> SHOW SLAVE STATUS\G;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 129.1.1.19
                  Master_User: m2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 200
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             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: 200
              Relay_Log_Space: 410
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
1 row in set (0.00 sec)

9. In above step, the value of foll should be as it is shown here for replication to work :-
 Slave_IO_State: Waiting for master to send event
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

10. The value of foll should be of another master server:-
            Master_Host: 129.1.1.19   (IP of master 2)
            Master_User: m2     (repl user name of master 2)
            Master_Port: 3306    (port no of master 2)
            Master_Log_File: mysql-bin.000006 (log file of master 2)
            Read_Master_Log_Pos: 200   (log file position of master 2)

 *********** MASTER SERVER 2 SETTINGS ************

1. Login to master server 2 machine.
 Open terminal. Login as super user.

2.  Edit mysql configuration file (my.cnf) using vi editor. It is in /etc directory. Set server-id = 2. Basically it should be a unique number in both master servers.

3.  root> service mysqld start;

4.  root> mysql -u<username> -p<password>

5.  mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@ 'ip_addr' IDENTIFIED BY ‘slavepass’;
eg. grant replication slave on *.* to 'm2'@ '%' identified by 'p2';

6.  mysql> show master status\G;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      200 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Note the bin log file name & position. This will be used in master 1 settings.

7.   root> CHANGE MASTER TO MASTER_HOST='IP_ADDR', MASTER_USER='<repl_user>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='<log_file_name>', MASTER_LOG_POS=<position>;
eg: change master to master_host='129.1.1.42', master_user = 'm1', master_password='p1', master_log_file='mysql-bin.000001', master_log_pos=107;

8.  mysql> SHOW SLAVE STATUS\G;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 129.1.1.42
                  Master_User: m1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             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: 200
              Relay_Log_Space: 410
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

10. In above step, the value of foll should be as it is shown here for replication to work :-
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

10. The value of foll should be of another master server:-
 Master_Host: 129.1.1.42   (IP of master 1)
 Master_User: m1     (repl user name of master 1)
 Master_Port: 3306    (port no of master 1)
 Master_Log_File: mysql-bin.000001 (log file of master 1)
 Read_Master_Log_Pos: 107   (log file position of master 1)