MySQL replication working fine, but still shows "Waiting for master to send event" at 280000 seconds

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".

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.

StackExchange Database Administrators Q#69870, answer score: 9