Cannot stop MySQL 5.5 replication threads

Question

We have a master-master replication setup using MySQL 5.5. On one of the servers I am trying to stop the slave thread, but without success. To ensure that the replication does not connect to a real server, I set MASTER_HOST to a nonexistent address. I have run the STOP SLAVE command multiple times, but I am still seeing Slave_SQL_Running: Yes when I run SHOW SLAVE STATUS;.

This is what gets logged (every few seconds):

[Note] Error reading relay log event: slave SQL thread was killed
[ERROR] Slave I/O: Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: 1593
[Note] Slave I/O thread killed while connecting to master
[Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
[Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/log/mysql/mysql01-relay-bin.000002' position: 4

I want all the replication threads to go away. How can I do this?

Answer 1

To halt replication from starting up on a restart of mysqld you should add this to my.cnf

[mysqld]
skip-slave-start

Don't worry about the SQL Thread. It reads from the local relay logs. Once all SQL statements and changes are read from the relay logs and processed, the SQL threadwill just sit there. You can prove that by running SHOW SLAVE STATUS\G and watching

• Relay_Log_Space: If the IO Thread is disabled, this should no longer increase.
• Exec_Master_Log_Pos: Once all SQL is processed from relay logs, this remains still

As long as the IO Thread is dead (run STOP SLAVE IO_THREAD;), replication from the remote master is no longer possible, especially since you set the address to a nonexistent server.

My guess is that you are using row-based replication, If you are, the SQL thread is probably staring at an incomplete transaction and waiting for the IO thread to complete a block of changes that never comes over from the master. When that is the case, you can kill the SQL thread by running

KILL connectionid;

for the connection of the system user still active for replication's SQL thread. I say this because the MySQL Documentation on STOP SLAVE says:

In MySQL 5.5, STOP SLAVE waits until the current replication event group affecting one or more nontransactional tables has finished executing (if there is any such replication group), or until the user issues a KILL QUERY or KILL CONNECTION statement. (Bug #319, Bug #38205)

Answer 2

To be sure that in case replication were to start I set the MASTER parameters to be wrong so the replication would not work. After doing this, even if I ran STOP SLAVE and STOP SLAVE SQL_THREAD; the server kept trying to restart replication, causing the log file to fill up.

The only way to get through MySQL's head that I wanted replication to stop was to follow the suggestion in How do I completely disable MySQL replication and stop the mysql service and delete the master.info and relay-log.info files.