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
Note
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 http://bugs.mysql.com/bug.php?id=13963.
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.
- Shut down the slave.
- Open its configuration file in a text editor.
- Make sure the following line is present in the
[mysqld]section:report-host=slave_1701.example.com
- Save the configuration.
- Restart the slave.
- On the master, issue the following command to verify your change was successful:
mysql> show slave hosts \G
You might of course see many more slaves here, depending on how they are configured.
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.
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:
|
Setting |
Description |
|---|---|
|
report-port |
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-user |
Report a username that can be used to connect to the slave. Not recommended to use! |
|
report-password |
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:
Note
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!