Using mysqladmin for monitoring MySQL server (Part I)

Published January 14th, 2009

Problem Statement

Unfortunately, the community edition of MySQL server does not come with any fancy monitoring tools so we decided to put together a shell-script / Perl solution until we find something better. Our goals were simple: how can we find out if a MySQL server is up and then how can we find out if something nasty is about to happen or already happening. In this article, we will attempt to provide a simple solution for these questions but be aware, this ain’t your enterprise solution. :)

Is your MySQL server alive and kicking?

First question we want our monitoring solution to answer is: is mysql alive? If you are just interested in finding this answer, here is your solution:

$ mysqladmin -u [username] -p[password] ping

A sample output is shown below:

mysqld is alive

This message is the result of the above command only if MySQL is alive. If MySQL server is dead, you are likely to get something like:

mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!

If you just interested in knowing if MySQl server is alive or not, you can use the following simple shell script:

#!/bin/sh
#
# Simple MySQL Server Monitoring Tool
# Version 1.0
# Copyright (c) EVOKNOW, Inc.
# Use it AS-IS without any guarantees
##############################################

# Change the username and password to suit your environment
USER=godmin
PASS=toomanysecrets

# Change the admin email to your email
ADMIN_EMAIL=you@example.com

# You can change the subject if you like
MYSQL_DOWN_SUBJECT="Alert: MySQL Down on $HOSTNAME at $NOW"

####################################################################
# Do not change anything below unless you know what you are doing!
####################################################################
CMD=/usr/bin/mysqladmin
MAIL=/bin/mail
NOW=`date "+%m/%d/%Y %h:%I:%s"`

IS_ALIVE=`$CMD -u $USER -p$PASS ping | grep -c 'alive'`

if [ "$IS_ALIVE" != "1" ];
then
   # OOPS! MySQL is DEAD!"
   # Mail yourself an emegency note
   echo "MySQL Down on $HOSTNAME" |  $MAIL -s "$MYSQL_DOWN_SUBJECT" $ADMIN_EMAIL;
fi

Is Your MySQL server about to Lock Up?

Often bad queries that take forever to run and perform table-level locks on MYISAM tables are the usual suspects for MySQL lock ups. To find out if your MySQL server is about to lockup you create fancy shell scripts but here we will start out with a simple command such as the following:

$ mysqladmin -u [username] -p[password] -r --sleep [seconds] processlist

For example:

$ mysqladmin -u godmin -ptoomanysecrets -r --sleep 30 processlist

The above sample command shows the MySQL process (thread) list every 30 seconds. Using this command, we can create a log file that can be parsed by a Perl script to find the Locked tables and count them for how long they have been locked. After a table reaches certain threshold of locked state, we can fire a friendly warning email to your DBA to get involved.

TO BE CONTINUED IN PART II

Category: DBA | Tags: