Mysql master-master not replicating

Posted by frankil on Server Fault See other posts from Server Fault or by frankil
Published on 2011-01-13T06:05:48Z Indexed on 2011/01/15 6:54 UTC
Read the original article Hit count: 320

Filed under:
|

I'm setting up a master-master mysql replication on two servers (db1 and db2). I started with setting up db2 as a slave to db1 and that works fine. But when I set up db1 as a slave to db2 it isn't replicating.

On the face of it everything looks fine but the data isn't replicating. There are no errors in either of the error logs. The slave status is updating the bin log position. I have used mysqlbinlog to examine both the binlog on the db2 and the relay log on db1 and all of the queries are going in there, but not being executed to db1.

"show slave status" on both servers shows that both the slave io and sql threads are "Yes" and that the relay log position is updated by the sql thread.

Also on both servers:

>echo "show processlist" | mysql | grep "system user"
166819 system user NULL Connect 3655 Waiting for master to send event NULL
166820 system user NULL Connect 3507 Has read all relay log; waiting for the slave I/O thread to update it NULL

Relevant config for db1:

server-id = 1
log-slave-updates
replicate-same-server-id = 0
auto_increment_increment = 4
auto_increment_offset = 1
master-host = db2
master-port = 3306
master-user = slaveuser
master-password = ***
skip-slave-start
sync_binlog = 1
binlog-ignore-db=mysql

Config for db2

server-id = 2
log-slave-updates
replicate-same-server-id = 0
auto_increment_increment = 4
auto_increment_offset = 2
master-host = db1
master-port = 3306
master-user = slaveuser
master-password = ***
sync_binlog = 1
relay-log=mysql-relay-bin
binlog-ignore-db=mysql

What else can I look for to make sure db1 executes the queries from db2?

© Server Fault or respective owner

Related posts about mysql

Related posts about replication