Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a weird replication problem I have not seen before. It's basic mysql replication with single master and two slaves. One of the slaves is not executing replication events and seems just stuck at some point yesterday. The other is current.

  • Running show slave status on the problematic slave shows no increases in any of the counters.
  • It lists both the IO and SQL threads as running.
  • Seconds behind reports 0.
  • None of the log counters are increasing.
  • No errors are reported. Running stop/start slave return no errors. Bouncing the server reports nothing out of the ordinary in the .err log and says it's picking up replication from the relay log position it's stuck on
  • The master shows the slave as connected
  • The slave shows two system user replication threads reporting "Waiting for master to send event" and "Slave has read all relay log; waiting for the slave I/O thread to update it". Their Time counters in the process list are just steadily increasing.
  • Attempts to connect from the slave w/ the replication credentials to the master via commandline client work fine
  • There is plenty of disk space in both the datadir and logdir
  • The Master_log_file it's reporting still exists on the master according to both show binary logs and looking at the actual filesystem (it wasn't pruned or manually deleted from the FS)
  • The master and both slaves are running the same percona build (5.5.29-30.0-log) and have been as such for many months.

I'm at a loss on what to further troubleshoot. Help?

share|improve this question
    
Please run the following: 1) SHOW SLAVE STATUS\G on both Slaves, 2) SHOW VARIABLES LIKE 'server_id'; on both Slaves, 3) SHOW PROCESSLIST; on the Master. Please put the output of them all in the question. –  RolandoMySQLDBA Jul 25 '13 at 18:02
    
I'd also like to see pager grep 'system user';SHOW PROCESSLIST from the slave that is not progressing. –  Derek Downey Jul 25 '13 at 18:45
add comment

1 Answer 1

This turned out to not be a mysql issue at all. The network team recently installed a new security device that will block packets on certain rules. A legitimate database write contained a sequence of characters the device deemed nefarious.

The overall connection handshake for replication was able to make it fine but then it just sat there asking the master for the next log entry whose packets were never making it back.

As far as the broken slave was concerned, it was up to date b/c it had executed the most recent event in the relay logs it had received.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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