MySQL Master-Master Replication Issue

Question

I have two databases that sync data through Master-Master replication.

The setup was created using the following guide:

digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

Until 2 days ago the system had been running fine for over a year, but now I have this weird issue.

Server 1:

Slave status,Master status & my.cnf replication settings: https://i.cloudup.com/IjK7fPVaN9-3000x3000.jpeg

Server 2:

Slave status,Master status & my.cnf replication settings: https://i.cloudup.com/sDyBxeuqt2-1200x1200.jpeg

As you can see, Server 2 is synced correctly but Server 1 is way behind the master position of Server 2. Server 1's slave read position does not attempt to "catch up" or attempt to match the master status. When I manually make them match using:

CHANGE MASTER TO MASTER_HOST = 'xx.xx.xx.xx, MASTER_USER = 'username', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 001;

Server 1's slave matches Server 2's master bin log position but after an hour or two, it falls behinds and stays behind.

We are running:

Ubuntu (Server 1) & debian (Server 2) OS
Both MySQL running 5.5.3

I have no idea why up till 2 days ago, this was working fine and now Server 1 is extremely lagging behind server 2. Is it possible an issue with the connection between the two servers?

Can anyone shed some light on this issue?

@TanHongTat we saw nothing new. We pinned the issue down to a connection problem between the servers as each could not SCP between each other without the transfer instantly stalling. — Caleuanhopkins, Aug 07 '14 at 08:20

score 0 · Answer 1 · answered Aug 05 '14 at 09:15

Without having the proper knowledge about this topic, though i have several master-master replications, i would say that the log position might refer to the local server's position. Writing data to master1 which is replicated to master2, would, from my perspective, only increase the log position on master1. Writing directly to master2 instead, would increase it's log position.

I've checked one of my replicas which is showing the same difference as you are experiencing, though the content of each databases are identical. So i wouldn't worry about this difference.

I personally just worry about Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master and errors on the "slaves".

I wish that was the only issue, but we had data that was not converting over between the systems. If I created a table on server 2, it wouldn't go over to server 1. We've now blamed it on the server as scp between the two servers was stalling and never starting so we've rebuilt server 1 — Caleuanhopkins, Aug 05 '14 at 10:18

MySQL Master-Master Replication Issue

1 Answers1