Ref: http://www.howtoforge.com/mysql_master_master_replication
a) Create user name and password for replication on both servers by using this command
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO noslave@’host-name’ IDENTIFIED BY ’some-pass’;
b) Configuration for Server 1 To make it primary Server for Server2
vi /etc/my.cnf
log-bin=mysql-bin
binlog-do-db=fosiul # which Database to replicate
binlog-do-db=hesk # Which Database to replicate
binlog-ignore-db=mysql # Which Database to ignore
binlog-ignore-db=test # Which Database to ignore
server-id = 1 # Primary Server id
auto_increment_increment= 2 # to solved the issue for auto indexing problem
auto_increment_offset = 1 # to solved the issue for auto indexing problem
Configuration For server2 to make as Slave for Server1
server-id = 2
master-host = IP_Of_Server1
master-user =noslave
master-password = SomeStrongPassword
master-port = 3306
auto_increment_increment= 2 # Avoid Auto Indexing problem
auto_increment_offset = 2
Now restart both Server and look for bellow report:
For Server 1 (Master Report):
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000008 | 565444 | fosiul,hesk | mysql,test |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
For Server2:( Slave report)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: IP-Of-Server1
Master_User: noslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 565444
Relay_Log_File: web-relay-bin.000092
Relay_Log_Pos: 153971
Relay_Master_Log_File: mysql-bin.000008
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: 565444
Relay_Log_Space: 154124
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:
1 row in set (0.00 sec)
ERROR:
No query specified
note :
a) Make sure Master_Log_File: mysql-bin.000008 From Slave Report matches with the Master_Log_file name with Master Reports.
b)Make sure Read_Master_Log_Pos: 565444 at Slave Report matches with Position field at Master Reports
c) Make sure Seconds_Behind_Master is always 0(Zero)
Master(Server1) Slave (Server2)
c)Configuration for server 2 as Master for Server1
#Bellow section for acting as Master for server1
log-bin=mysql-bin
binlog-do-db=fosiul # Which Database to repliacate
binlog-do-db=hesk # Which Database to replicate
binlog-ignore-db=mysql # Which Database to ignore
binlog-ignore-db=test # Which Database to ignore
#Configuration for Server1 to make as slave for Server 2
master-host = IP-Of-Server2
master-user = noslave
master-password = SomeSTrongPassowrd
master-port = 3306
log-slave-updates # To make this Master Server act as slave
d) Now Restart both mysql server and look for bellow report:
Slave Status report for Server1
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: IP_OF_Sever2
Master_User: noslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 106
Relay_Log_File: mail-relay-bin.000025
Relay_Log_Pos: 251
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: 106
Relay_Log_Space: 550
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:
1 row in set (0.00 sec)
Master Report for Server2 :
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000006 | 106 | fosiul,hesk | mysql,test |
+——————+———-+————–+——————+
1 row in set (0.01 sec)
Note :
a) Make sure Master_Log_File: mysql-bin.000006 From Slave Report matches with the Master_Log_file name with Master Report.
b)Make sure Read_Master_Log_Pos: 106 at Slave Report matches with Position field at Master Report.
c) Make sure Seconds_Behind_Master is always 0(Zero)
Slave(Server1) and Master (Server2)