0

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:

Server 2:

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?

  • Check your MySQL logs, do you see anything unusual? – Tan Hong Tat Aug 6 '14 at 2:26
  • @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 7 '14 at 8:20
0

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 5 '14 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.