Pablowe

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

OmniMySQL 0.0.4 Released [BUGFIX]

OmniMySQL 0.0.4 is a minor release fixing two bugs that I should have caught (it now crashes less). The current release can be downloaded 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!

OmniMySQL version 0.0.3 released

This release fixes bugs and changes the parser from SQL::Parser to DBIx::MyParsePP. This change was made in order to accommodate MySQL-specific deviations from Standard SQL. Unfortunately, this change also comes with the expense of a slow startup, but I think it is worth the trade-off. OmniMySQL can be downloaded here.

CHANGELOG:

0.0.3 - 15 June 2008

- Migration from SQL::Parser to DBIx::MyParsePP

Setting Up WordPress on FreeBSD

I suppose that it is only fitting that my first blog post be an instructional tidbit on installing WordPress on FreeBSD. As primarily a Linux user, I find the BSD package management system tedious at times. First, version information:

FreeBSD 7
Apache 2
MySQL 5.0.51a
PHP 5.2.6

This tutorial assumes two jails; one that serves as a MySQL database server, the other that functions as the web server.

Step 1: Install MySQL

On the host that will serve as your MySQL database server, run the following commands:

bash> cd /usr/ports/databases/mysql50-scripts
bash> portinstall -pvf
bash> cd /usr/ports/databases/mysql50-client
bash> portinstall -pvf
bash> cd /usr/ports/databases/mysql50-server
bash> portinstall -pvf
bash> /usr/local/etc/rc.d/mysql-server rcvar

Add the following line to /etc/rc.conf:

mysql_enable="YES"

Issue the following commands:

bash> mysql_install_db
bash> mysql_secure_install

Add a WordPress database and user in MySQL:

bash> mysql -uroot -p
mysql> CREATE DATABASE IF NOT EXISTS `wordpress_db`;
mysql> GRANT ALL PRIVILIGES ON `wordpress_db`.* TO 'wordpress_user'@'' IDENTIFIED BY ' ';

The rest of the steps will be on your webserver:

Step 2: Install Apache

bash> cd /usr/ports/www/apache20
bash> portinstall -pvf

Step 3: Install PHP

bash> cd /usr/ports/lang/php5
bash> make config

Check the “Build Apache Module” checkbox to enable mod_php:

bash> portinstall -pvf

Step 4: Configure & Start Apache

Add the following line to /usr/local/etc/apache2/httpd.conf in the “Dynamic Shared Object (DSO) Support” section:

LoadModule php5_module libexec/apache2/libphp5.so

Start Apache:

bash> apachectl restart

Step 5: Install MySQL Client

bash> cd /usr/ports/databases/mysql50-client
bash> portinstall -pvf
bash> cd /usr/ports/databases/php5-mysqli/
bash> portinstall -pvf

Step 6: Install and Configure WordPress

bash> cd /usr/ports/www/wordpress
bash> portinstall -pvf
bash> cd /usr/local/www/data-dist/wordpress/
bash> mv wp-config-sample.php wp-config.php