Skip to content

# The importance of report_host & report_port

MySQL Configuration Replication May 13, 2009May 13, 2009

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
4
Leave a Reply

4 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
2 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Xaprb

In my experience they work pretty well until changes are made without restarting servers — and then you can start to see different things from each host in a replication topology 🙁 This is why Maatkit kind of moved away from this, towards detecting masters and slaves from the processlist.

Author
shlomi

Hi Baron,

I agree there’s a need to remember the manual settings. So if you change an instance’s port – you need to remember changing the report_port as well.

How do you overcome the problem of non-default ports for slaves, when going through SHOW PROCESSLIST?

New and Noteworthy in openark kit | code.openark.org

[…] by utilizing the report_port parameter on the slaves, reading the value on SHOW SLAVE HOSTS (see this post). Both utilities support skipping SHOW SLAVE HOSTS and just picking up slaves by looking at SHOW […]

Replication configuration checklist | code.openark.org

[…] report-host, report-port: the host and port identifying the slave when looking at SHOW SLAVE HOSTS on master. Set this up on all hosts. See further discussion here. […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.