Book Image

MySQL Admin Cookbook LITE: Replication and Indexing

Book Image

MySQL Admin Cookbook LITE: Replication and Indexing

Overview of this book

Table of Contents (3 chapters)

Setting up slaves to report custom information about themselves to the master

When you issue a SHOW SLAVE HOSTS command on a replication master server, you will get a list of slaves connected, provided that they are set up correctly.

Unfortunately, by default they are not, so unless you specifically configure them to do so, slaves will not register themselves with the master. In a default setup you might not see any slave in the output of the above command or in the Replication Status pane in MySQL Administrator at all, even though there might be several configured against this master.

In this recipe, we will show you how to configure a slave to tell its master some details that might come in handy when troubleshooting


Please note that due to a bug in MySQL the output of the SHOW SLAVE HOSTS command is not always reliable! Sometimes it will report hosts being available when in fact they are currently not. The only way that seems to fix an erroneous display is to stop and start the master server.

This effectively makes this feature unsuitable for the purpose of the actual current health of the slaves. It, nevertheless, provides a way to gather some inventory information on their location and some other details described below.

The bug report is tracked at

Getting ready

To follow along, you will need sufficient operating system privileges to modify the slave's configuration file (my.cnf or my.ini depending on your operation system). To actually see that status on the master you will need a MySQL user there as well.

How to do it...

  1. Shut down the slave.
  2. Open its configuration file in a text editor.
  3. Make sure the following line is present in the [mysqld] section:
  4. Save the configuration.
  5. Restart the slave.
  6. On the master, issue the following command to verify your change was successful:
    mysql> show slave hosts \G
    How to do it...

You might of course see many more slaves here, depending on how they are configured.


Should you ask yourself what the Rpl_recovery_rank line in the output means, you may simply ignore it. It seems it was introduced some years ago but never put to active use.

How it works...

Usually, slaves do not report any details about themselves when they connect to the master. By adding some options in their configuration you can, however, make them announce details about themselves.

We strongly recommend setting up all your slaves to register with the master, especially when you are dealing with many masters and slaves. This can be very helpful to keep on top of things.

MySQL Administrator allows you to remember all slaves it has seen once and display a warning on its Replication Status pane when a machine previously known does not register later. This particular piece of information is not reliable. however; see the warning in this recipe's introduction for more information.

How it works...

There's more...

The general idea behind the report-host setting is to give an idea about how to reach the slave machine via the network. As the slave might be located behind some sort of firewall or NAT router, its IP address might not be very helpful. So, in general, it can be helpful to have the slave report a fully qualified domain name that can be used to reach it, if applicable.

However, it is by no means mandatory to do so. If you do not intend to access the slave remotely, you might just enter any other piece of information you like to see in the output of the command mentioned in this recipe or the MySQL Administrator pane. As you can see in the previous screenshot, I set up the slaves to report back a name suffixed with the server-ID value. Doing so works around a bug in MySQL Administrator that knows how to remember slaves it has seen before, but sometimes forgets their server-id.

Showing slaves currently unavailable is a feature of MySQL Administrator; the SHOW SLAVE HOSTS command will not mention them at all. To leverage this you must click the Add Host to Monitoring List button for each slave once it is connected. Otherwise, they will not appear at all when they are not connected.

Apart from the report-host configuration setting there are three more options you should know about:




Informs about the port that must be used to reach the slave on the domain name reported by report-host. This can be sensible if port forwarding has been set up.


Report a username that can be used to connect to the slave. Not recommended to use!


Report a password that can be used to connect to the slave. Not recommended to use!

For completeness, this is what the output of SHOW SLAVE HOSTS will look like if you go against our advice and configure the slave to report a set of login credentials and the master has been started with the show-slave-auth-info option:

There's more...


While the report-port setting might be useful, we strongly suggest to refrain from using the report-user and report-password options for security reasons.

Even though the master server will only display these values when it is started with the show-slave-auth-info option, it is still very risky to send login credentials over the network in this manner. You should always use more secure ways to exchange login information!