Pablowe

Managing MySQL Configuration Files

It is good practice to manage changes to MySQL configuration files (/etc/my.cnf) by using a version control system. I usually use a home-brewed (not brewed by me!) svn+cfengine application to propagate my.cnf (and other configuration file) changes to defined classes of machines (classes are based on application role, replication role, etc).

When managing hundreds of different database servers with dozens of roles, templating my.cnf becomes a necessity! If I have to change a variable for a given class, that can mean editing a dozen my.cnf files on as many servers. Not a productive use of time!!! Usually, the only parameters that differ between database hosts in the same class are the replication options. As such, I find it useful to have a base template for each class of machine and use the !include startup option to specify host-specific startup options.

Here is a sample of what I would have as a base my.cnf for billing-class MySQL Replication Slaves:

And then I would store host-specific replication options in a smaller file:

To re-iterate, templating MySQL configuration files can help ensure consistency between servers of the same class as well as making it easier to push changes to all relevant hosts instead of doing it individually.

MySQL’s PAGER

Baron Schwartz wrote a neat article on MySQL’s pager command yesterday. As a followup, I thought I would post what I use as my default PAGER environmental variable. It simply substitutes mk-visual-explain for MySQL’s EXPLAIN command and uses less if the output is taller than your current terminal window (note, this currently renders \G non-functional):

#!/usr/bin/perl

use strict;
use warnings FATAL => ‘all’;
use English qw ( -no_match_vars );
use Term::ReadKey;

my $out;

while ( my $line = <STDIN>) {
$out .= $line;
}

# Log current result to a file (always useful)
open (PAGER_LOG, ‘>/tmp/mysql_pager.log’);
print PAGER_LOG “$out\n”;
close (PAGER_LOG);

# See if it is an EXPLAIN
eval {
local $SIG{’__WARN__’};
system(’/usr/local/bin/mk-visual-explain /tmp/mysql_pager.log 2> /dev/null’);
};

if ($?) {
# Use less if the output is taller than your terminal
my ( $cols, $lines ) = GetTerminalSize();
my @out_array = split("\n", $out);
if ( scalar ( @out_array ) > $lines ) {
system('less /tmp/mysql_pager.log');
} else {
print $out;
}
}

An up-to-date version will always be kept here

Using Nagios as a MySQL Performance Profiler

Everybody knows than Nagios can be used as a service monitor to monitor things like Load Averages, MySQL Replication Status, RAID Array States, etc… Fewer know that there are plug-ins to monitor MySQL Performance Status, such as check_mysql_perf. Fewer still utilize Nagios’ built-in triggering mechanism to execute an additional script on the event of a critical alert.

It is not uncommon to experience a load spike in the middle of the night, only to discover that it immediately cleared itself. Many times, retroactive log analysis will not reveal anything out of the ordinary. In order to get an immediate snapshot of what is going on at the time of a Nagios alert, simply call a script via the event handler similar to this one:

#!/usr/local/bin/bash

Usage() {
echo 1>&2 "Usage: $0 -h <hostname> -u <username> -p <password> -s <state> -t <state_type>"
exit 1
}

MYSQL="/usr/local/bin/mysql"
MAIL="/usr/bin/mail"
RECIPS="dba@domain.com"
PERL="/usr/bin/perl"
[ $# -ne 10 ] && Usage

while getopts "h:u:p:s:t:" option; do
case $option in
h ) HOSTNAME="$OPTARG";;
u ) USERNAME="$OPTARG";;
p ) PASSWORD="$OPTARG";;
s ) STATE="$OPTARG";;
t ) STATE_TYPE="$OPTARG";;
* ) Usage;; # DEFAULT
esac
done

[ $STATE = "CRITICAL" -a $STATE_TYPE = "HARD" ] || exit 0
$MYSQL -h$HOSTNAME -u$USERNAME -p$PASSWORD --connect_timeout=10 -ss -e'SHOW FULL PROCESSLIST' | $PERL -pe 's/\\n/\n/g' | $MAIL -s"$HOSTNAME post-alert processlist" $RECIPS
exit 0

This will email dba@domain.com with the output of SHOW FULL PROCESSLIST;

This type of profiling is especially useful because it can easily be turned on and off without affecting your production site (and it is very inexpensive to execute). Don’t forget to grant your Nagios user the PROCESS privilege!