MySQL and SSL

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 $tempout

        expire_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

    }