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?

Answer 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.

Answer 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

Answer 3

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

Answer 4

I'd try logging into mysql and looking at the global variables:

mysql -u [dbuser] -p -e "show global variables";