0

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?

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)

| improve this answer | |
  • Thanks for the information. I cannot stop the SQL thread because the process id keeps changing. It is as if the SQL thread tries to do something, fails, and a new thread is created. How can I stop the continual thread creation? – rlandster Feb 18 '14 at 22:35
  • Have you tried running STOP SLAVE SQL_THREAD;? If you have, does MySQL hang ? – RolandoMySQLDBA Feb 18 '14 at 22:37
  • Yes: running STOP SLAVE SQL_THREAD; does not give me an error message, but does not work, either. If I do a SHOW SLAVE STATUS; immediately after running STOP SLAVE SQL_THREAD; I see Slave_SQL_Running: Yes. – rlandster Feb 19 '14 at 16:02
  • Looks like you are encountering Bug #38205 as the MySQL Doc says. (bugs.mysql.com/bug.php?id=38205). What bothers me is 1) the bug originates with MySQL 5.1 2) The bug is closed 3) that the MySQL Doc for STOP SLAVE still mentions that bug in MySQL 5.5 (dev.mysql.com/doc/refman/5.5/en//stop-slave.html) and MySQL 5.6 (dev.mysql.com/doc/refman/5.6/en//stop-slave.html) Doc on STOP SLAVE as well. – RolandoMySQLDBA Feb 19 '14 at 16:10
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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