2

I'm trying to fix a replication error (or an ongoing failure to replicate) between our master and slave MySQL databases.

The procedure I have asks me to use MySQL Monitor to stop the slave.

stop slave;

When I try this, however, the prompt never returns. I've left it for several minutes before Ctrl+C'ing out.

Interestingly, the same thing occurs with:

show slave status;

...but

show master status;

Works fine. Is there some other way I can kill the slave process off, ideally without interrupting the master?

Improve this question

1 Answer 1

Reset to default
3

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

SHOW PROCESSLIST;

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 or KILL QUERY command against the Process IDs.

CAVEAT

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.

Why does SHOW MASTER STATUS; work just fine ???

Running SHOW MASTER STATUS; simply checks for binary logs on the DB Server. There are no DB threads to read, parse, examine, and coalesce.

Running SHOW SLAVE STATUS\G has to reconcile three(3) aspects:

  • Aspect #1 : the Contents of master.info
  • 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.

Improve this answer
1
  • 2
    +1 as always; to expand on one of your points, for future reference for those who may not find it intuitive: SHOW MASTER STATUS does not show the status of the master of the server where the command is executed -- it's not "show the status of my master" -- although that's a reasonable assumption. Instead, it gives information about the binlogs being created on the local server where the statement is executed... so unless the slave is also a master to one or more downstream slaves, running this on a slave provides information that is not generally useful.
    – Michael - sqlbot
    Mar 20, 2013 at 3:10

Your Answer

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

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