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:
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, huh?
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]#