MySQL connections listing

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 the database server.

Previously when I had to determine connected hosts, I just used a one-liner script in bash, what parse 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 want 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 information via SQL, so the output can be exactly what you needed.

In fact, it is not really easy to use when you need 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:

        COUNT(*) AS conn_count,
        SUBSTRING_INDEX(host,':',1) AS ip,
        conn_count, ip

Pretty, huh?

So, I’ve decided to ease my work and wrote a script called

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/ -h
Usage: ./ [ -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/ -s
Connections                  Hostname
           1                localhost

[root@sql-slave2 banyek]# /usr/local/bin/ -d
Connections             Database
         120                    kinja
          25                      sso
           8                 hyperion
           3                    chomp
           1       information_schema

[root@sql-slave2 banyek]# /usr/local/bin/ -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/
Connections                  Hostname            Username            Database
           1                localhost                root  information_schema
           1            newrelic
           1            chomp_ro               chomp
           1            chomp_ro               chomp
           1            chomp_ro               chomp
           2                                  system user
           4              webro2            hyperion
           4              webro2            hyperion
          10             ssoUser                 sso
          15             ssoUser                 sso
          59               kinja               kinja
          61               kinja               kinja

[root@sql-slave2 banyek]#