Mysql master-master not replicating

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?


Solution 1:

Your configs don't look complete - do you have 'log-bin=mysql-master-bin' (sic) somewhere in both configs? In general, replication from a master to a slave (either direction) looks like:

mysql -> master binlog -> (network to slave) -> slave relaylog -> slave

You need to do this in both directions; if you're not logging transactions on db2 then it has nothing to send back to db1. So basically, ensure you set up log-bin and relay-log on both servers.

You do not need log-slave-updates, that's used for chaining (master -> slave -> slave). It would be useful if you attached a 3rd slave off the dual-master config for backing up for example. Because you'll never send db1->db2->db1 (bad loop) it's useless logging.

Solution 2:

We upgraded the mysql servers on both machines to 5.0.91 and it's working now. Apparently 5.0.32 & 5.0.33 are quite buggy, and replication in mysql started to smooth out around 5.0.70 or so.