{"id":818,"date":"2009-05-13T09:09:41","date_gmt":"2009-05-13T07:09:41","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=818"},"modified":"2009-05-13T10:12:26","modified_gmt":"2009-05-13T08:12:26","slug":"the-importance-of-report_host-report_port","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/the-importance-of-report_host-report_port","title":{"rendered":"The importance of report_host & report_port"},"content":{"rendered":"

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

For a replication environment, I find these two variables to be very useful.<\/p>\n

Where are these variables used?<\/h4>\n

Here’s a slightly modified exerpt from a MySQL master node I have, and which has two slaves:<\/p>\n

\n
mysql> SHOW SLAVE HOSTS;\r\n+-----------+------------+------+-------------------+-----------+\r\n| Server_id | Host       | Port | Rpl_recovery_rank | Master_id |\r\n+-----------+------------+------+-------------------+-----------+\r\n|        21 | webhost1   | 3306 |                 0 |        25 |\r\n|       117 | webhost2   | 3306 |                 0 |        25 |\r\n+-----------+------------+------+-------------------+-----------+\r\n2 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\n
A SHOW SLAVE HOSTS<\/strong> command lists down all servers participating in a replicated environment. The above excerpt is very simple: it lists two slaves for the given master.<\/p>\n
Well, the above description is misleading. What SHOW SLAVE HOSTS<\/strong> 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.<\/p>\n
The relevance to this post is that the “Host” and “Port” attributes are reported as defiend in report_host<\/strong> & report_port<\/strong> per instance.<\/p>\n
Using SHOW PROCESSLIST<\/h4>\n
When you SHOW PROCESSLIST<\/strong>, you also get the replicating hosts, right? Let’s see the output of SHOW PROCESSLIST\\G<\/strong>:<\/p>\n
\n
*************************** 1. row ***************************\r\n     Id: 18491376\r\n   User: replication_user\r\n   Host: 192.168.0.21:43198\r\n     db: NULL\r\nCommand: Binlog Dump\r\n   Time: 61316\r\n  State: Has sent all binlog to slave; waiting for binlog to be updated\r\n   Info: NULL\r\n*************************** 2. row ***************************\r\n     Id: 18491804\r\n   User: replication_user\r\n   Host: 192.168.0.117:32988\r\n     db: NULL\r\nCommand: Binlog Dump\r\n   Time: 61208\r\n  State: Has sent all binlog to slave; waiting for binlog to be updated\r\n   Info: NULL\r\n*************************** 3. row ***************************<\/pre>\n<\/blockquote>\n
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<\/strong>. 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.<\/p>\n
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.<\/p>\n
Proper reporting<\/h4>\n
If you had two MySQL instances running on the same machine, the output of SHOW PROCESSLIST<\/strong> 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?<\/p>\n
It is therefore important to properly set up the two variables. Set report_port<\/strong> to the same port<\/strong> used for [mysqld]<\/strong>. Set report_host<\/strong> to whatever value that can be used to trace back from the master (or from the monitoring machine).<\/p>\n
SHOW SLAVE HOSTS<\/strong> 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<\/strong> – you are doing well.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,8],"class_list":["post-818","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-replication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-dc","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/818","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=818"}],"version-history":[{"count":11,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/818\/revisions"}],"predecessor-version":[{"id":831,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/818\/revisions\/831"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=818"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=818"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=818"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}