7

To repeat the title: MySQL replication working fine, but mytop still shows a thread "Waiting for master to send event". It has been like this for a few days, and is now at 280000 seconds. But replication is just fine.

I can see another thread which is doing the replication work, right now it is sitting on "Slave has read all relay log; waiting for the slave I/O thread to update it" but it has been running queries, and I can verify that the slave is up to date just by viewing the data.

Can I kill this process?

I recently had to re-set up replication, and that involved creating a new replication user, so I'm not sure if this is what has caused this "ghost replication process".

1
  • 1
    No, not really.
    – jcolebrand
    Jul 7, 2014 at 17:39

2 Answers 2

9

Don't you dare kill that process. Why ?

Run this query

SELECT COUNT(1) FROM information_schema.processlist WHERE user='system user';

The answer has to be two. Why ?

Standard MySQL Replication functions on 2 threads: 1) IO Thread, 2) SQL Thread. In my old post Does MySQL support replicating all databases?, I explain the symbiosis between the two threads.

When you run SHOW SLAVE STATUS\G, you will see three lines:

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes
  • Seconds_Behind_Master: 0

This is the sign of a healthy replication process.If either Slave_IO_Running or Slave_IO_Running is No, then Replication is not running for one of six(6)reasons:

  • Somebody ran STOP SLAVE;, which turns off both the IO Thread and SQL Thread
  • Somebody ran STOP SLAVE IO_THREAD;, which turns off the IO Thread
  • Somebody ran STOP SLAVE SQL_THREAD;, which turns off the SQL Thread
  • There was a replication user authentication error, which turns off the IO Thread
  • There was a network error, which turns off the IO Thread
  • There was an SQL Error, which turns off the SQL Thread

When any of these cases show up, Seconds_Behind_Master is NULL. What about the number 280000 ? That is simply the length of time (over 82 hours) that the IO Thread has been running. That's OK. It is not a ghost process. Replication requires an IO Thread to read binlog events from a Master's binary logs.

In conclusion, your IO Thread has been up for over 82 hours. Do not mess with replication.

1
  • Ah, awesome. Thanks for this important background info.
    – dmgig
    Jul 7, 2014 at 15:09
0

In my case binary log position (through show master status) at master and Read_Master_Log_Pos (show slave status) at slave had a huge gap, i simply stopped the slave and start the slave. The difference decreased to nominal.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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