Linux Admin Zone

Sometimes in large deployments, there are cases when MySQL server, setup by you long time back which has been in use by multiple teams in your organization, needs some change or update or intrupption in its service and you are in need to know how many clients are there which connects to this server.

One way to know is to check user table in ‘mysql’ database, you might have created individual users/databases for your clients/users. But over the time, unless taking care seriously, we tend to forget the exactly how many users/scripts/machines are there making connections. May be you have used same read-only user in various scripts scattered over many machines which you’ve created just for a single client.

The other way, I’ve used here is to monitor your connections over a period of time and noting down IP addresses of machines making connections. After some time, you will have a good idea about your clients machines/users. In MySQL server, the ‘processlist’ command shows information about clients connected to server.

I have 1 MySQL master server and few of its slave servers for which I want to know who are using these servers. Store IP addresses of these servers in “serversip.txt” file. In an unfortunate case, if you have forgotten your mysql root password, check here to quickly reset it. Execute “show processlist” command into each of servers and extract and save IP addresses from output in a file. We have to execute this frequently, so you can run through cron (every minute) or just add infinite loop and sleep to do the same, like below:

$ vi searchclient.sh

#!/bin/bash
 
while [ 1 ]; do
 
for serverip in `cat serversip.txt`; do 
        mysql -u root -p password -h $serverip -e "show processlist;" | grep -v "Id\|system user\|Commands" | awk '{ print $3 }' | cut -d':' -f 1 >> clientsip.txt  
done
 
sleep 60
done

execute this script and keep it running for some time, preferrably for several hours to fetch all IPs who tried connecting your servers except system (replication etc) connections. Then kill this script as it will not stop automatically. Now we have got client’s IP addresses in “clientsip.txt” but there will be lots of duplicate IPs. Let’s clear out duplicates:

# cat clientsip.txt | sort | uniq > ipclients.txt

Your clients IP list is in “ipclients.txt” file. further, if you want to know hostnames, you can issue another command:

# for ips in `cat ipclients.txt`; do host $ips >> clienthosts.txt; done

Your client host names are in “clienthosts.txt”. Let me know your suggestions/tips for any better way to do this.

Related articles on MySQL:

* Setup MySQL Cluster 7.0 on Redhat based Linux
* How to Setup MySQL Cluster in Amazon EC2 cloud computing
* Setup multiple MySQL server instances in single server
* Quickly repair a huge corrupted table
* Optimize very large MySQL database server
* Ignore MySQL errors while executing bulk statements
* Fix MySQL server running slow without any load