1

I'm using MariaDB 10.4 in a standard master slave setup.

Everything works fine for about 20min, I can see changes replicating across but then after about 20min replication just stops.

However, if I restart the slave MySQL instance, replication begins again and catches up.

Restarting the master has no effect on the replication it stays broken.

When in the broken non-replicating state this is the output from the slave and master.

enter image description here enter image description here

So it looks like it thinks its caught up, but it hasn't, no matter how long I leave it.

I've used netcat to test the connection and I know it can see the master still, plus when I restart MySQL it works right away.

The my.cnf config has not been changed apart from adding the server ID and this is how I configured the slave initally CHANGE MASTER TO MASTER_HOST='dynamicip.xxxxxxx.org', MASTER_PORT = 4302, MASTER_USER='remote_replication', MASTER_PASSWORD='xxxxxxxxxx', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=328;

The error logs contain no errors also.

Where would I begin to troubleshoot this?

EDIT / UPDATE

So it appears the data does replicate over eventually but it takes a least 50 minutes, and its tiny bits of data I'm testing it with.

I've switched the hostname dymanic.****.org to an IP address in case it was Cloudflare doing something weird but that hasn't fixed it. I also tried skip-host-cache before that.

I've also switched innodb_flush_log_at_trx_commit from 0 to 1. Traceroute looks fine. I can see the statements in the binlog file on the master.

Interestingly using STOP SLAVE and START SALVE kicks it back into action for a while before it begins having the issue again.

SHOW PROCESSLIST on the Master shows that the I/O thread is sometimes present and sometimes not, not sure what that means..

5
  • Does it go if you do SLAVE STOP; SLAVE START;?
    – Rick James
    Jul 14 '20 at 15:04
  • So it turns out the data does get replicated over but it can take up to an hour. And I'm entering tiny bits of test data. Interestingly, it does start replicating straightway when I STOP and START the slave. But what does that mean? Is there some sort of buffer that I'm not filling because I'm testing with small data then it gets flushed every hour or something?
    – Mr J
    Jul 14 '20 at 15:19
  • 1
    I first noticed the occasional need for STOP + START almost 20 years ago. But I failed to discover the root cause or solution. Sorry.
    – Rick James
    Jul 14 '20 at 15:36
  • No worries you've helped me that little bit closer :-)
    – Mr J
    Jul 14 '20 at 16:07
  • Did you also configure skip-name-resolve ? If you did, you will have to create another user on the Master from repluser@'dynamic....org to repluser@'actual IP Address' or repluser@'netblock'. See my posts dba.stackexchange.com/questions/7193/… and dba.stackexchange.com/questions/3320/… Jul 15 '20 at 13:57
1

The first thing I would do is check the network between the Master and Slave. Why ???

Almost 8 years ago, I wrote this post : Slave I/O thread dies very often

I once had an occasion where I saw a Slave with SHOW SLAVE STATUS\G looking exactly as you have it, but it was not replicating. This is what actually happened:

  • The firewall on the Slave would become intermittent
  • The Master could not talk back to the Slave through the I/O thread
  • SHOW PROCESSLIST on the Master revealed that the I/O thread was no longer there
  • Slave assumed the Master was OK

What I recommend is this

  • Start troubleshooting the traceroute on dynamic....org
  • See if DNS is acting up. If DNS is involved, add skip-name-resolve and skip-host-cache to my.cnf on Master and Slave and restart mysqld

Please let us know what you found in your troubleshooting

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.