Set Up MySQL Master-Master Replication

                                        MySQL Master-Master replication allows data to be copied from either server to the other one. This subtle but important difference allows us to perform mysql read or writes from either server. This configuration adds redundancy and increases efficiency when dealing with accessing the data.

                                    This article will be based on two VPS, named Master -1 and Master -2.

Master - 1 = 10.3.3.95

Master - 2 = 10.3.3.96

1. Install and Configure MySQL on Master - 1

sudo apt-get install mysql-server mysql-client

2. Modify the MYSQL Parameters

vi /etc/mysql/my.cnf

                 There are four lines that we need to change, which are currently set to the following:

server-id               = 1

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = aview

# bind-address            = 127.0.0.1

Restart  MYSQL : sudo service mysql restart

3. Configuration In MYSQL

mysql -u root -p

mysql>create user 'replicator'@'%' identified by 'amma';

mysql>grant replication slave on *.* to 'replicator'@'%';

mysql>show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |      107 | aview      |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

4. Install and Configure MySQL on Master - 2

sudo apt-get install mysql-server mysql-client

5. Modify the MYSQL Parameters

vi /etc/mysql/my.cnf

                 There are four lines that we need to change, which are currently set to the following:

server-id               = 2

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = aview

# bind-address            = 127.0.0.1

Restart  MYSQL : sudo service mysql restart

6. Configuration In MYSQL

mysql -u root -p

mysql>create user 'replicator'@'%' identified by 'amma';

mysql>grant replication slave on *.* to 'replicator'@'%';

mysql>show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000004 |      107 | aview      |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

7. Replication Configuration In Master - 2

mysql>

slave stop;

CHANGE MASTER TO MASTER_HOST = '10.3.3.95', MASTER_USER = 'replicator', MASTER_PASSWORD = 'amma', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;

slave start;

8. Replication Configuration In Master - 1

mysql>

slave stop;

CHANGE MASTER TO MASTER_HOST = '10.3.3.96', MASTER_USER = 'replicator', MASTER_PASSWORD = 'amma', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 107;

slave start;

9. Check the Status In Both Master - 1 and Master - 2

mysql>SHOW SLAVE STATUS\G;

If there is an issue in connecting, you can try starting slave with a command to skip over it:

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

10. Testing Dump aview DB in to Master -1 via SQLYOG, it automatically reflected it in to the Master - 2.

      If u delete any tables from Master - 2, the table is also deleted from Master - 1

Verification Master - 1

mysql>  SHOW SLAVE STATUS\G;

*************************** 1. row ***************************           
Slave_IO_State: Waiting for master to send event
Master_Host: 10.3.3.96
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 100977
Relay_Log_File: mysqld-relay-bin.000014
Relay_Log_Pos: 101123
Relay_Master_Log_File: mysql-bin.000005
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: 100977
Relay_Log_Space: 101426
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)
ERROR:
No query specified

Verification Master - 2

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************           
Slave_IO_State: Waiting for master to send event
Master_Host: 10.3.3.95
Master_User: replicator
Master_Port: 3306

Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 219
Relay_Log_File: mysqld-relay-bin.000011
Relay_Log_Pos: 365
Relay_Master_Log_File: mysql-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: 219
Relay_Log_Space: 668
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)

ERROR:

No query specified