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.

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]#