How to find slave's IP address and user name from Master server?

Question

I am writing code to extract the IP address and username of all slave servers in a MySQL replication environment.

Does anyone know any function, variable, or something else that I can run in the console and retrieve these infos without looking into the my.cnf file?

Solution 1

On the master server:

SELECT * FROM information_schema.PROCESSLIST AS p WHERE p.COMMAND = 'Binlog Dump';

This shows all connected slaves, their ipaddresses, user, and even how long they have been connected since they last connected as slaves.

Solution 2

There is an interesting way to report all registered slaves connect to the master.

The command is called SHOW SLAVE HOSTS;

This will not directly show the IP of the slaves but you can configure the master and slaves to do so in a unique way.

With MySQL 5.5, just run SHOW SLAVE HOSTS; and you just get something like this:

MySQL> show slave hosts;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
| 106451148 |      | 3306 | 106451130 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)

MySQL> show variables like 'server_id';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| server_id     | 106451130 |
+---------------+-----------+
1 row in set (0.00 sec)

As shown
column 1 is the Slave's server-id
column 2 is the Slave's name as specfied in report-host variable (blank by default)
column 3 is the Slave's port number connecting to master
column 4 is the Slave's Master server-id (run this from the Master)

With versions MySQL 5.1 and back, you get this by default:

MySQL> show slave hosts;
Empty set (0.01 sec)

MySQL>

You can assign a hostname to each slave by adding this to the slave's /etc/my.cnf

report-host=MySQLSlave_10.1.2.3

Restart mysql and hopefully the name will appear as you typed it in /etc/my.cnf
If the periods are not acceptable, make them dashes like this:

report-host=MySQLSlave_10-1-2-3

Then do the following

1. SHOW SLAVE HOSTS;
2. Use the PHP explode function, delimiting by underscore character, and take the second element of the array
3. Use the PHP function str_replace, replacing dash (-) with period (.)

And WA LA, you have an IP address

Solution 3

Log into mysql and execute SHOW FULL PROCESSLIST. You will get slaves IP addresses.

Comments

[Pradip Parmar]

you will not be able to find topology that easy. there is so many diff settings. and the details may be available in any of it. so instead of trying this one by one you should use orchestrator. this is the link for that github.com/openark/orchestrator you can also search how to install it in windows or ubuntu.

[Kourosh Samia]

I checked it but couldn't find anything about slave ip address.

[drewrockshard]

Not sure then - hopefully an actual mySQL DBA can help you out here.

[RolandoMySQLDBA]

Unfortunately, using SHOW PROCESSLIST shows "system user" as the user column but shows nothing for the host column.

[Ryaner]

Check for a line saying "Binlog Dump". Host usually appears up there.

[Jeff Clayton]

For setups with name-based lookups the ip is not listed here but the hostname of the slave instead. It does work, but the type of setup changes the result.

[Jeff Clayton]

For setups with name-based lookups the ip is not listed here but the hostname of the slave instead. It does work, but the type of setup changes the result.