MySQL Utilities

3.4.5 How do you find all of the slaves attached to a master server?

When you have a topology that has grown over time - many slaves have been added from time-to-time - it may not be so easy to remember which servers are connected as slaves and even which are slaves to a given master.

Most often you want to know the state of those slaves at-a-glance. Rather than connect to each slave individually, it would be nice to know what the state of each slaves threads using a single command.


Show a map of the slaves connected to a master including the state of each slaves threads (IO and SQL). We can do this with a single command using the mysqlrplshow utility.

Example Execution

shell> mysqlrplshow --master=root:root@localhost:13001 \
          --disco=root:root --verbosity
# master on localhost: ... connected.
# Finding slaves for master: localhost:13001

# Replication Topology Graph
localhost:13001 (MASTER)
   +--- localhost:13002 [IO: Yes, SQL: Yes] - (SLAVE)
   +--- localhost:13003 [IO: Yes, SQL: Yes] - (SLAVE)
   +--- localhost:13004 [IO: Yes, SQL: Yes] - (SLAVE)
   +--- localhost:13005 [IO: Yes, SQL: Yes] - (SLAVE)      


Notice the use of the mysqlrplshow utility. Not only did it show us the slaves attached to the master, it also displayed the state of the IO and SQL thread for each slave.

We used the master server for the --master option but for the slaves, we provided the option --discover-slaves-login which provides the user name and password for the account used to connect to each slave. Without this, we would not be able to determine if the slave is attached (currently) or the state of its threads.

The --discover-slaves-login option applies to all slaves. If you do not have the same user defined on all of your slaves, you can use the --prompt option to prompt for the user and password for each slave.

To get the state of the slave threads, use the --verbose option.

Permissions Required

The user connected to the master must have the REPLICATION SLAVE privilege.

The user specified with the --discover-slaves-login option that logs into each slave must have the REPLICATION CLIENT privilege.

Tips and Tricks

You can also display multiple tiered topologies by providing the --recurse option.

Notice in the example we used the option --discover-slaves-login but specified only --disco=. This is a shortcut feature built into every utility. If you type the first N letters of a utility that uniquely identifies it among the options for said utility, the utility accepts it as if you typed the entire string. For example, the full name of the option we used is --discover-slaves-login.