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.<\/p>\n
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:<\/p>\n
[root@sql-slave1 banyek]# netstat | grep mysql | awk '{print $5}' | awk -F: '{print $1}' | sort | uniq -c\n 1 app01.bfc.kinja-ops.c\n 83 app05.bfc.kinja-ops.c\n 84 app09.bfc.kinja-ops.c\n 9 dbcacti.bfc.kinja-ops\n 1 nagios.bfc.kinja-ops.\n 1 sql-master1.bfc.kinja\n<\/code><\/pre>\nThis was enough to quickly see the connected hosts, but the output wasn\u2019t too chatty, and there are a lot of information which were hidden.<\/p>\n
<\/p>\n
If you want to monitor connections to given server you can use in mysql prompt a \u2018SHOW PROCESSLIST\u2019 or a \u2018SHOW FULL PROCESSLIST\u2019 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.<\/p>\n
Lucky thing, that in Percona MySQL server there is a schema named \u2018INFORMATION_SCHEMA\u2019 in your database, where you can query connection information via SQL, so the output can be exactly what you needed.<\/p>\n
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:<\/p>\n
SELECT\n COUNT(*) AS conn_count,\n SUBSTRING_INDEX(host,':',1) AS ip,\n user,\n db\n FROM\n INFORMATION_SCHEMA.PROCESSLIST\n GROUP BY\n ip,\n user,\n db\n ORDER BY\n conn_count, ip\n DESC\n<\/code><\/pre>\nPretty, huh?<\/p>\n