I was fighting with the exact same problem in a more intricate replication setup
M1 <-> M2 -> S1 -> S2 where
M2 is an active-passive (passive server = read-only) master-master replication setup and
S2 are regular slaves chained from
I looked at Rolando's answer which seemed irrelevant to me and I tried Shlomi's answer in several different test setups but couldn't get it working.
In the end I had to study MySQL's source code to find out where data in
SHOW SLAVE HOSTS comes from and how to "flush" it.
Turns out that when a slave registers to a master it runs
SHOW SLAVE HOSTS on the master and add that output to its own slave_list hash structure which is used as output for
SHOW SLAVE HOSTS.
In my case with master-master replication it meant it did not matter what I did locally, I had to reset data at master for it to be refreshed at slave.
To answer this actual question a restart of the MySQL server should clear the output if it's a master -> slave setup and the master itself is not a slave of any other server.
Additionally, if anyone face the same situation as me above with master-master replication here are the steps to solve it:
STOP SLAVE on both masters (stops replication temporarily).
skip-slave-start to my.cnf (server config file) on both servers. This prevents the slave thread from automatically starting upon restart.
- Restart mysqld on both servers (e.g.
service mysql restart)
SHOW SLAVE HOSTS on both servers should now give empty outputs.
START SLAVE on both. Each will fetch the other master's empty list to begin with.
skip-slave-start again from both my.cnf files
SHOW SLAVE HOSTS on both servers should only list active servers.