Currently at Kinja we are in a middle of big architectural change on database servers, so I have run into a problem regarding this. Sometimes I have to check current connections on database servers, to see what schemas are in use, what servers using a given db server, or even which users are connected to database server. Previously when I had to determine connected hosts, I just used a one-liner script in bash, what parsed through the output of netstat and listed the number of connections from given servers like this:

[root@sql-slave1 banyek]# netstat | grep mysql | awk '{print $5}' | awk -F: '{print $1}' | sort | uniq -c 1 app01.bfc.kinja-ops.c 83 app05.bfc.kinja-ops.c 84 app09.bfc.kinja-ops.c 9 dbcacti.bfc.kinja-ops 1 nagios.bfc.kinja-ops. 1 sql-master1.bfc.kinja

This was enough to quickly see the connected hosts, but the output wasn't too chatty, and there are a lot of information which were hidden.

Advertisement

If you wants to monitor connections to given server you can use in mysql prompt a 'SHOW PROCESSLIST' or a 'SHOW FULL PROCESSLIST' command, this prints all the data what you needed, but it can provide too many information, but can be really annoying if you are only curious to certain information.

Lucky thing, that in Percona MySQL server there is a schema named 'INFORMATION_SCHEMA' in your database, where you can query connection informations via SQL, so the output can be exactly what you needed.

In fact, it is not really easy to use when you needs a quick check, or even when you have to repeatedly check the information. For example, if you need all connection information about hostnames, connection counts, users and schemas, you can use the following query:

SELECT COUNT(*) AS conn_count, SUBSTRING_INDEX(host,':',1) AS ip, user, db FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY ip, user, db ORDER BY conn_count, ip DESC

Pretty, ehh?

So, I've decided to ease my work, and wrote a script called checkconn.pl.

This script can list the active connections to given database server, and you can filter the output for hosts, schemas, users only if you need only a smaller subset of given data.

[root@sql-slave2 banyek]# /usr/local/bin/checkconn.pl -h Usage: ./checkconnections.pl [ -h ] [ -u user ] [ -p password ] [ -s ] [ -c ] [ -r ] [ -d ] if none of -s -c -d are given, script provides a full connection list -h this help screen -u username username with connect to mysql. Defaults 'root' -p password password with connect to mysql. If not provided,asks for it. -s show connections by server -c show connections by users -d show connections by database

Example outputs: [root@sql-slave2 banyek]# /usr/local/bin/checkconn.pl -s Connections Hostname ===================================== 81 app06.bfc.kinja-ops.com 74 app10.bfc.kinja-ops.com 2 1 localhost 1 nagios.bfc.kinja-ops.com 1 app02.bfc.kinja-ops.com [root@sql-slave2 banyek]# /usr/local/bin/checkconn.pl -d Connections Database ===================================== 120 kinja 25 sso 8 hyperion 3 3 chomp 1 information_schema [root@sql-slave2 banyek]# /usr/local/bin/checkconn.pl -c Connections Username ===================================== 120 kinja 25 ssoUser 8 webro2 3 chomp_ro 2 system user 1 root 1 newrelic [root@sql-slave2 banyek]# /usr/local/bin/checkconn.pl Connections Hostname Username Database ============================================================================= 1 localhost root information_schema 1 nagios.bfc.kinja-ops.com newrelic 1 app10.bfc.kinja-ops.com chomp_ro chomp 1 app06.bfc.kinja-ops.com chomp_ro chomp 1 app02.bfc.kinja-ops.com chomp_ro chomp 2 system user 4 app10.bfc.kinja-ops.com webro2 hyperion 4 app06.bfc.kinja-ops.com webro2 hyperion 10 app10.bfc.kinja-ops.com ssoUser sso 15 app06.bfc.kinja-ops.com ssoUser sso 59 app10.bfc.kinja-ops.com kinja kinja 61 app06.bfc.kinja-ops.com kinja kinja [root@sql-slave2 banyek]#