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
