DrDamnit
asked on
Two-way MySQL replication not working?
I have used this tutorial to setup two way replication:
http://www.neocodesoftware.com/replication/
The problem is, if I shut down server 1, then make a change on server 2, and then restart server 1, the changes on 2 do not replicate to 1.
however, If I shut down server 2, and make a change to server 1, then restart server 2, the changes made on server 1 are propogated to server 2.
So it is working but only server 1->server 2, and not the other way around.
Here are the configs (below). How do I troubleshoot this?
http://www.neocodesoftware.com/replication/
The problem is, if I shut down server 1, then make a change on server 2, and then restart server 1, the changes on 2 do not replicate to 1.
however, If I shut down server 2, and make a change to server 1, then restart server 2, the changes made on server 1 are propogated to server 2.
So it is working but only server 1->server 2, and not the other way around.
Here are the configs (below). How do I troubleshoot this?
my.cnf at Master1
server-id = 1
auto-increment-increment = 10
auto-increment-offset = 1
replicate-same-server-id = 0
log-bin = /var/lib/mysql/mysql-bin
relay-log = /var/lib/mysql/mysql-relay
relay-log-index = /var/lib/mysql/mysql-relay.index
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = 'MIXED'
my.cnf at Master2
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 2
#expire_logs_days = 10
#max_binlog_size = 100M
log-bin = /var/log/mysql/mysql-bin
#relay-log = /var/log/mysql/mysql-relay-bin
#relay-log-index = /var/log/mysql/mysql-relay.index
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = 'MIXED'
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.30.164.115', MASTER_USER='repl', MASTER_PASSWORD='60NmzdnsLBUBLnu44MSq';
mysql> start slave;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.30.164.115
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000030
Read_Master_Log_Pos: 1027
Relay_Log_File: mysqld-relay-bin.000020
Relay_Log_Pos: 1173
Relay_Master_Log_File: mysql-bin.000030
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: 1027
Relay_Log_Space: 1476
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)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000030 | 207 | | |
+------------------+----------+--------------+------------------+
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Show slave status you posted, is that from server 1 or from server 2?
Make sure that the master_server_id reflects the other sserver id:slave on 1 should reflect 2 as the master. Slave on 2 should reflect 1.
Why are you changing master?
You are missing the replicate-db directives and binlogemail-do-DB. This is that you only replicate specific database excluding the mysql database.