“show slave hosts” on master not reporting hostname

If you have multiple MySQL slaves or complex topology, its difficult to remember what slaves are replicating from which master.

We can go to slave servers and check “show slave status” which list master host, but if we want to find out other way, we have can run “show slave hosts” from master.
This will list down all the slaves that are getting replicated from this master.

Only problem I faced was, “show slave hosts” was not reporting hostnames. They were empty.

root [fleetdb] >show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3306 |         2 | c21f28d2-c243-11e4-baf5-2c600c20dba4 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.01 sec)

After bit of RTFM, I came to know we need to set a parameter – “report_host” while starting slave. This will register slave hostname while connecting to master (for replication) and same will be shown in “show slave hosts”

This is a read-only parameter meaning, we need to bounce mysql instance after setting this parameter in /etc/my.cnf

So I set the parameter in /etc/my.cnf as below

report_host=slave-host1.mydb.example.com

We can specify any name here and same will be registered in master. But it make sense to provide actual host name.

Once I bounce mysql instance, my slave is started and it connects to master.
Now I check “show slave hosts” and it shows this host.

root [fleetdb] >show slave hosts;
+-----------+------------------------------+------+-----------+--------------------------------------+
| Server_id | Host                         | Port | Master_id | Slave_UUID                           |
+-----------+------------------------------+------+-----------+--------------------------------------+
|         3 | slave-host1.mydb.example.com | 3306 |         2 | c21f28d2-c243-11e4-baf5-2c600c20dba4 |
+-----------+------------------------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

So we can easily know what slaves are connected to this master.

Hope this helps !!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s