What does running "show slave status" on a master server show?

Question

We are using monitoring software that is throwing an error saying that Slave_IO_Running is returning No for replication on our master server. It is in fact true that when running show slave status on the master server, that Slave_IO_Running shows No but Slave_SQL_Running shows Yes.

I understand that the show slave status command needs to be run on the slave server, and not the master and that any server can be a master and/or slave. To my knowledge, this server is truly only a master and doesn't serve as a slave to any other server.

I'm wondering why the monitoring software is showing this as a critical error, and if it is any cause for concern. Replication is running smoothly as far as I can tell, but looking to clear up this error from the monitoring software. Am I right in that, if this is the master server, Slave_IO_Running: No as well as any other output from show_slave_status on the master isn't meaningful?

Answer 1

MASTER / SLAVE REPLICATION

In Master/Slave Replication, you should not have any output from SHOW SLAVE STATUS\G on the Master. If you see any output, it is more than likely stale and no longer needed. You can remove that with the following commands

STOP SLAVE;
RESET SLAVE ALL;

After running these commands on the Master, you should get this from SHOW SLAVE STATUS\G

mysql> show slave status\G
Empty set (0.00 sec)

mysql>

WARNING !!! : Do not run those commands on the Slave (Breaks Replication)

MASTER / MASTER REPLICATION

In Master/Master Replication, both Masters are Slaves to Each Other. If your DB Topology is Master/Master Replication, then this is a legitimate error condition. The message Slave_IO_Runninng: Yes means there is a DB Connection going to another DB Server and collecting binlog events that have executed on that other DB Server already. When you have Slave_IO_Runninng: No, there is no DB Connection back to the other DB Server.

This be remedied with START SLAVE; but please find out why (Firewall Issue, Network Outage Between Servers, etc) before running that.

YOUR ACTUAL QUESTION

You asked

Am I right in that, if this is the master server, Slave_IO_Running: No as way as any other output from show_slave_status on the master isn't meaningful?

Output from SHOW SLAVE STATUS\G on a Master that is Master/Slave Replication (and not in Master/Master Replication) is not meaningful. You can run STOP SLAVE; RESET SLAVE ALL; on that Master so the monitoring software can stop alerting you.