Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am writing a code to extract the ip address and username of slave server in a MySQL replication environment. Does anyone know any function, variable, or something that I can run on the console and retrieve those info without looking into the my.conf file?

thansk

share|improve this question
add comment

3 Answers

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

share|improve this answer
add comment

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

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

share|improve this answer
    
I checked it but couldn't find anything about slave ip address. –  IDS-master Dec 1 '10 at 15:04
1  
Not sure then - hopefully an actual mySQL DBA can help you out here. –  drewrockshard Dec 2 '10 at 5:18
add comment

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

share|improve this answer
1  
Unfortunately, using SHOW PROCESSLIST shows "system user" as the user column but shows nothing for the host column. –  RolandoMySQLDBA May 11 '11 at 15:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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