We've been moving our databases over to MySQL for a while, and for any mysql server that contains private information, we've been requireing SSL connections.
I recently ran into an issue where a MySQL SSL certificate expired without my knowledge and the Web App in front of it went down. I don't like it when things go down without being the first one to know. Especially when it's preventable!
Now typically I have Nagios checking all of my SSL certs to warn me when they are expiring, but I was unable to find a Nagios check against MySQL SSL certificates... so I wrote one.
I determined the only way to do this easily is on the server side using an NRPE script. The following is a simple BASH script that uses `openssl verify` to check certificates and warn if they are expiring.
#!/bin/bash# /usr/lib/nagios/plugins/check_certfile
if [ "${1}" == "" ] || [ "${2}" == "" ] || [ "${3}" == "" ]; then
echo "usage:
${0}"
exit 2
elif [ ! -f "${3}" ]; then
echo "File: '${3}' does not exist."
exit 2
else
tempout=`mktemp /tmp/certdates.XXXXXXXXX`
now=`date +%s`openssl x509 -noout -dates -in "${3}" \
| sed -e 's/=/="/' \
| sed -e 's/$/"/' > ${tempout}
. "${tempout}"
rm -f $tempoutexpire_date=`date --date="${notAfter}" +%s`
days="$((( (expire_date - now) / 86400 )))"report="certificate expired in ${days} days (${notAfter})"
if ((( $days < ${2} ))); then
echo "CRITICAL: ${report}"
exit 2
elif ((( $days < ${1} ))); then
echo "WARN: ${report}"
exit 1
else
echo "OK: ${report}"
exit 0
fi
fi
Then add a line to your /etc/nagios/nrpe_local.cfg file like such:
# Check MySQL SSL Certificate
command[check_mysql_sslcert]=/usr/lib/nagios/plugins/check_certfile 21 7 /etc/mysql/sql.ncs.umn.edu.crt.pem
And add a service definition to your nagios host config file
# check that MySQL SSL certificate is valid
define service{
use generic-service
host_name mysql.dept.umn.edu
service_description MySQL SSL Certificate
check_command check_nrpe!check_mysql_sslcert
# servicegroups ssl-cert # optional, but handy
notification_interval 0 ; set > 0 if you want to be renotified
}
