The importance of report_host & report_port

The two server variables, report_host and report_port, are not commonly used. One of the reasons, I suspect, is that they do not appear in any of the standard .cnf files provided with a MySQL installation.

For a replication environment, I find these two variables to be very useful.

Where are these variables used?

Here’s a slightly modified exerpt from a MySQL master node I have, and which has two slaves:

mysql> SHOW SLAVE HOSTS;
+-----------+------------+------+-------------------+-----------+
| Server_id | Host       | Port | Rpl_recovery_rank | Master_id |
+-----------+------------+------+-------------------+-----------+
|        21 | webhost1   | 3306 |                 0 |        25 |
|       117 | webhost2   | 3306 |                 0 |        25 |
+-----------+------------+------+-------------------+-----------+
2 rows in set (0.00 sec)

A SHOW SLAVE HOSTS command lists down all servers participating in a replicated environment. The above excerpt is very simple: it lists two slaves for the given master.

Well, the above description is misleading. What SHOW SLAVE HOSTS really shows is the entire replication setup. That is, if I ran the above on one of the replication slaves (say webhost1) – I still get the same output. Moreover, if there are second level slaves, they’re listed as well. So what I actually get is a complete picture of the participating slave instances in a replication environment, and I get this picture from any of those instanced.

The relevance to this post is that the “Host” and “Port” attributes are reported as defiend in report_host & report_port per instance.

Using SHOW PROCESSLIST

When you SHOW PROCESSLIST, you also get the replicating hosts, right? Let’s see the output of SHOW PROCESSLIST\G:

*************************** 1. row ***************************
     Id: 18491376
   User: replication_user
   Host: 192.168.0.21:43198
     db: NULL
Command: Binlog Dump
   Time: 61316
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 18491804
   User: replication_user
   Host: 192.168.0.117:32988
     db: NULL
Command: Binlog Dump
   Time: 61208
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 3. row ***************************

Well, here’s the difference: you do get the replicating host, but not necessarily the way you wanted it. You get the IP, or you could get a hostname if you had one declared in /etc/hosts. But you do not get the port. The reported port is the socket which is used by the replication process, not the port on which the replicating slave listens.

You also only get one level down. If you want to map the entire topology, you’d have to drill down to the slaves and recursively continue the process.

Proper reporting

If you had two MySQL instances running on the same machine, the output of SHOW PROCESSLIST would be useless. You’d get two rows listing the same host – but on which port does each one listen? Perhaps you need the host to be reported in a very specific way, so that the firewall allows you access back to the machine?

It is therefore important to properly set up the two variables. Set report_port to the same port used for [mysqld]. Set report_host to whatever value that can be used to trace back from the master (or from the monitoring machine).

SHOW SLAVE HOSTS only lists slaves which are currently replicating. Any slave which has been stopped, or which has no relay log space (therefore temporarily suspending the IO thread) – are not listed. Knowing in advance the number of machines in your replication setup is useful: if you find the same number of rows in SHOW SLAVE HOSTS – you are doing well.