Mysql server master master active active replication

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)

Click on the picture to view

Verify Log File

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)

Click on the picture to view

Slave(Server1) and Master (Server2)

Slave(Server1) and Master (Server2)

Leave a Reply

*