How to Monitor MySQL Connections per IP Using SQL and Shell Commands

To monitor the number of database connections per IP, instead of only counting the total connections, you can use the following SQL query:

SELECT SUBSTRING_INDEX(host, ':', 1) AS ip, COUNT(*) FROM information_schema.processlist GROUP BY ip;

The query extracts the IP part from the host column and groups the results to count connections per IP.

Alternatively, you can achieve the same using shell commands that invoke MySQL and process the output with egrep, awk, sort, and uniq:

# /usr/local/mysql/bin/mysql -u root -h127.0.0.1 -e"show processlist\G;" | egrep "Host:" | awk -F: '{ print $2 }' | sort | uniq -c

Or a more compact version:

# /usr/local/mysql/bin/mysql -u root -h127.0.0.1 --skip-column-names -e"show processlist;" | awk '{print $3}' | awk -F":" '{print $1}' | sort | uniq -c

These commands list the processlist, extract the host IP, and count occurrences to show the number of connections from each IP.