9

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?

1
  • 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. Mar 12, 2020 at 12:03

4 Answers 4

17

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.

9

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

1
  • 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. Mar 3, 2018 at 13:51
4

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

3
  • 1
    Unfortunately, using SHOW PROCESSLIST shows "system user" as the user column but shows nothing for the host column. May 11, 2011 at 15:59
  • 1
    Check for a line saying "Binlog Dump". Host usually appears up there.
    – Ryaner
    Jun 20, 2014 at 11:39
  • 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. Mar 3, 2018 at 13:47
-2

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

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

3
  • I checked it but couldn't find anything about slave ip address. Dec 1, 2010 at 15:04
  • 1
    Not sure then - hopefully an actual mySQL DBA can help you out here. Dec 2, 2010 at 5:18
  • For this to work you may have to be using ip based lookups, my servers are using name based and i do not see ip addresses here. I do see the hostname of the machine I am currently in, however and not the master or slave name or ip. Mar 3, 2018 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.