Martello

Some clever one-liners that print a neatly organised version of your MySQL configuration file without all the comments:

Perl
$ perl -ne 'm/^([^#][^\s=]+)\s*(=.*|)/ && printf(”%-35s%s\n”, $1, $2)’ /etc/my.cnf

Awk
$ awk '! /^#/ && ! /^$/ {if($1 ~ /^\[/ ){gsub("\[","\n[",$1) };printf("%-35s%s %s\n",$1, $2, $3)}' /etc/my.cnf

Grep
$ egrep -v '^$|^#' /etc/my.cnf

Source: MySQL Performance Blog

If you have ever installed of MySQL on a *nix system, you may have come across the mysql_secure_installation script. It takes you through a number of interactive questions/steps that help you to increase the security of your MySQL installation, and is quite a useful script.

As we have a high deree of automation in our server environment, we decided to break this script down to its raw MySQL command, and in a nut shell, here is what it actually does behind the scenes (assuming you want to answer Yes to all of the questions it asks in interactive mode):

UPDATE mysql.user SET Password=PASSWORD('XXXX') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
DROP DATABASE test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

Naturally, you should replace the XXXX in the first command with an appropriate password.

I was trialling a MySQL database upgrade today (from 5.0 to 5.1) and hit a nasty little gotcha that I feel warrants a mention (mostly to myself). In its simplest form, the upgrade process is very straightforward and involves simply loading data dumped from MySQL 5.0 into a 5.1 database (using a regular mysql command), followed by a once-off execution of the mysql_upgrade command (to bring the various tables and grants up to date):

$ mysql -u root -p testdb < dumped50.sql
$ mysql_upgrade -u root -p testdb

However, when I tried the latter command, I received this error:

Looking for 'mysql' in: mysql
FATAL ERROR: Can't find 'mysql'


I did fine a simple solution here which was to first change to the directory containing the mysql_upgrade program itself and execute it from there:

$ cd /opt/coolstack/mysql/bin
$ ./mysql_upgrade -u root -p testdb

As I said, its a simple solution and I do tend to follow this general rule most of the time but nevertheless, the error generated when you don’t do this is far for informative.

A colleague of mine just sent me this hugely useful MySQL command for seeing which users have privileges in a database, and from which host. It prints a list of other MySQL commands which can then be used to show more precisely what privileges that user has been granted.

SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM mysql.user;

Prior to this, I found myself having to connect to the database from the appropriate (remote) MySQL client and issue a SHOW GRANTS from there.

Thanks to Mate Racz for this one.

I got an opportunity to run a trial migration of several applications from Solaris Coolstack version 1.2 to version 1.3.1 recently. The Coolstack applications that we use include MySQL, Apache (including mod_jk), Tomcat and Memcached.

I had no problems at all with the Tomcat or Memcached upgrades but did hit 2 minor issues with MySQL and Apache (mod_jk actually).

1. MySQL

This involved going from MySQL 5.0.45 to 5.1.25 and whilst I found that a regular mysqldump of my MySQL 5.0 databases imported without error into MySQL 5.1, I did encounter some minor issues with user permissions later on. Fortunately, the guide to Upgrading from MySQL 5.0 to 5.1 proved very useful and revealed that I had missed an essential step in the upgrade process which was the use of the mysql_upgrade utility. There is lots more to read about in the upgrade guide and depending on your configuration, there may be more steps to be considered.

2. Apache (mod_jk)

Whilst the Apache upgrade (2.2.6 to 2.2.9) worked a treat, I found that mod_jk 1.2.26 employs stricter rules in relation to the placement of JkMount/JkMountFile commands in VirtualHosts. In my previous configuration, I had a single JkMount command inside my mod_jk.conf file (which in turn was included from the main httpd.conf file). However, using this configuration with Apache 2.2.9 and mod_jk 1.2.26, I found that my AJP load balancer was not working correctly for one of my VirtualHosts. It was only when I moved my JkMount command inside the scope of the VirtualHost that it started working again.

Other Comments

Most of the SMF service names in the Coolstack 1.3.1 applications have been renamed slightly. In Coolstack 1.2, all of the SMF service names began with csk- but now they all end with -csk instead. This was apparently done to make the service names more compatible with OpenSolaris but, to be honest, I find it a little bit annoying as we’ve written a number of useful scripts that manage these services explicitly by name.

Coolstack 1.3.1 includes two different versions of Tomcat (5.0 and 6.0) which install to different locations and have different SMF service names.