Ubuntu Upstart for automatic MySQL start and stop

Here at Recorded Future we use Ubuntu (running on Amazon EC2), but so far we have not explored Ubuntu Upstart that much. During the holidays I made an effort to get acquainted with Upstart and to implement proper MySQL start and stop with it.

If you do not know Upstart, this is the way you start and stop services in Ubuntu, and it serves the same purpose as the old /etc/init.d scripts, but are a bit more structured and powerful. That said, Upstart is regrettably far from complete, although the functionality is much better and Upstart has some cool features, some things do not work that well. For one thing, documentation, where it exists, is useless, at best. Secondly, there is very limited ability to test and develop Upstart scripts. And this is made worse by the fact that the documentation is so bad. Another thing is that Upstart insist on stopping services, by default, by sending a brutal kill signal. Not good for databases, mostly.

In the /etc/init directory are the Upstart scripts you have. In difference to the old init.d scripts, you cannot disable a service in Upstart curenntly. If it is in /etc/init it will be started at system start. That's it. And this is something that I am sure will be fixed, but for now, again, is something we have to live with. Upstart scripts have the suffix .conf (don't ask me why), so the default MySQL Upstart script, for example, is called /etc/init/mysql.conf.

In an Upstart script, there are Stanzas that determine what to do. Like the exec Stanza that runs a program for example. And you may then ask, when is it run? Startup? Shutdown? And the answer is startup. For shutting things down, as I said before, Upstart will by default just send a kill -9 signal.

The minimal startup script you can have, and this actually works in a reasonable way, is to just have one line with an exec stanza, like this:
exec /usr/bin/mydaemon
Which will start the daemon. For stopping the daemon, Upstart will send a -9 signal to the started process by default, and nothing more is needed in the Upstart script.

For MySQL, we need to make things a bit more complicated. The default mysql.conf Upstart script really is not good. For one thing, it will not do a controlled shutdown of MySQL (this is possible even if Upstart will eventually send a kill -9 anyway). Secondly, this script assumes that what we use is a standard Ubunty installed MySQL distribution, so if you have installed MySQL in /usr/bin/mysql5147 or somethings like that, you are out of luck.

So what I wanted to create was an Upstart script for MySQL that fullfilled these requirements:

1. Starts MySQL automatically.
2. Waits for MySQL to be available before exiting.
3. Be configurable to support different MySQL install locations, data directories etc.
4. Do a clean shutdown of MySQL when stopping the MySQL services.

Before I show you what I ended up with, I want to comment on the points 2 and 4 above. With Upstart, you can define a script or command to run just before or after a services has been started or stopped, and this is what I use to wait for MySQL to become available, and to send a SIGTERM to the MySQL Server when stopping (which will do a clean MySQL shutdown).

So here we go, a complete MySQL Upstart script, the way I want it to work:

#
# MySQL Service for Recorded Future
#
description     "MySQL Server"
author          "Anders Karlsson, Recorded Future"

start on (net-device-up
          and local-filesystems
          and runlevel [2345])
stop on runlevel [016]

expect fork
kill timeout 2

# Set variables.
env MYSQL_ETC=/etc/mysql
env MYSQL_PIDFILE=/var/run/mysql.pid
env MYSQL_HOME=/usr/local/mysql5.5
env MYSQL_INSTANCE=my
umask 007

exec $MYSQL_HOME/bin/mysqld_safe --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf >> /tmp/x.out &

post-start script
    loop=600
# Wait for MySQL to start.
    while [ $loop -gt 0 ]; do
        if $MYSQL_HOME/bin/mysqladmin --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf ping; then
            break
        fi
        loop=$(($loop - 1))
        sleep 1
    done
    exit 0
end script

# Send a soft SIGTERM to MySQL before Upstart will kill it.
# A Sigterm to mysqld will cause a controlled shutdown.
pre-stop script
    exec kill -SIGTERM `cat $MYSQL_PIDFILE`

# Wait for MySQL to end. Flushing buffers and all.
    loop=600
    while [ $loop -gt 0 ]; do
# If the pidfile is found, then continue waiting.
        if [ -e $MYSQL_PIDFILE ] ; then
            loop=$((loop - 1))
            sleep 1
            continue
        fi
        break
    done
end script

To be honest, this is not what I create for all our MySQL servers. Instead I used this to create a chef template, chef is what we use for configuration management here (see http://www.opscode.com/ for more on chef), and here it is put to good ude to generate an Upstart script for MySQL. The above is just an example.

/Karlsson