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?
Best Answer
To halt replication from starting up on a restart of mysqld you should add this to
my.cnf
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 watchingRelay_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 stillAs 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
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: