When this happens, I immediately go to the Slave and
If you have MySQL 5.1+
SELECT id FROM information_schema.processlist WHERE user='system user';
This will show you the Process ID of the IO Thread and SQL Thread of Replication.
If you have MySQL 5.0
You would have look for the two rows in the display that has
'system user' as the user.
Once you find the IDs...
You can run the
KILL QUERY command against the Process IDs.
This is a possibility that evn the KILL command can hang if the query must come to a completion (such as a transaction that must fully commit or fully rollback).
Master is oblivious to all this.
SHOW MASTER STATUS; work just fine ???
SHOW MASTER STATUS; simply checks for binary logs on the DB Server. There are no DB threads to read, parse, examine, and coalesce.
SHOW SLAVE STATUS\G has to reconcile three(3) aspects:
- Aspect #1 : the Contents of
- Aspect #2 : the current list of relay logs
- Aspect #3 : information acquired from the IO and SQL threads (live)
This reconciliation helps produce the output of
SHOW SLAVE STATUS\G. It's Aspect #3 that makes
SHOW SLAVE STATUS\G freeze attempts to kill the SQL thread on a long running query.