Quick and dirty monitoring of mysql connections

connections.sh

#!/usr/bin/env bash

# 5 minutes between alerts
SECONDS_BETWEEN_ALERTS=300

# Track when we sent the last alert
LAST_ALERT=0

while true; do
    NUMBER_CONNECTIONS=$(mysql --defaults-extra-file=/data/.prod.cnf -sNe "select count(*) as connection_count from INFORMATION_SCHEMA.PROCESSLIST;")

    RIGHTNOW=$(date +"%s")

    if [ "$NUMBER_CONNECTIONS" -ge "750" ] && [ "`expr $RIGHTNOW - $LAST_ALERT`" -ge "$SECONDS_BETWEEN_ALERTS" ]; then
        CONNECTIONS_PER_USER=$(mysql --defaults-extra-file=/data/.prod.cnf -e "select USER, HOST, count(*) as connection_count from INFORMATION_SCHEMA.PROCESSLIST group by USER order by connection_count desc;")

        curl -X POST https://hooks.slack.com/services/xxx/xxx/xxx --data-urlencode "payload={\"username\": \"RDS Police\", \"text\": \"\nTOTAL CONNECTIONS: $NUMBER_CONNECTIONS\n$CONNECTIONS_PER_USER\n\", \"icon_emoji\": \":alert:\"}"

        LAST_ALERT="$RIGHTNOW"
    fi

    sleep 5
done

dbmon.conf

description "Monitoring Database Connections"

start on filesystem or runlevel [2345]
stop on runevel [!2345]

respawn
respawn limit 5 2

script
    /opt/dbmon.sh
end script

dbmon.service

[Unit]
Description=Monitoring Database Connections

[Service]
Restart=on-failure
ExecStart=/opt/dbmon.sh

[Install]
WantedBy=multi-user.target

Monitoring your DB connections:

Quick and dirty

connections.sh (referred to as dbmon.sh in the systemd / init configs):

1. pings a database every 5 seconds asking for total # of connections.
2. Tracks the last time an alert was sent
3. If total connections is over 750 and an alert was sent 5 minutes or more ago, then send slack an alert
4. The alert content shows the number of connections per user (and host). This is convenient when you use one user per host against your database.

Keep Script Running

You can use init or, more likely, systemd to keep the bash script alive and running:

Init

Add file dbmon.conf to /etc/init/db.conf and then:

# Test syntax
init-checkconf /etc/init/dbmon.conf

# Check status and start
sudo service dbmon status
sudo service dbmon start

Reference: https://serversforhackers.com/c/process-monitoring-with-upstart

Systemd

Add file dbmon.service to /lib/systemd/system/dbmon.service and then:

sudo systemctl enable dbmon
sudo systemctl status dbmon
sudo systemctl start dbmon

Reference: https://serversforhackers.com/c/process-monitoring-with-systemd

this checks db every 5 secs, right?