Installing MySQL 5.5 from source

I recently wrote about Moving WordPress into RAM to gain a significant performance boost. This is however a bold move and there are a lot of other things you can do to gain performance, one of them is using a newer version of MySQL (and why not take a look at Percona Server?).

If you are running Ubuntu like me, you will have noticed that just using the apt-get install mysql-server will install MySQL Server 5.1. Moving to MySQL Server 5.5 will get you a performance increase and Oracle is claiming it will get you the following results:

In recent benchmarks using the release candidate for MySQL 5.5, compared to MySQL 5.1, results demonstrated marked performance improvements:

• On Windows: up to 1,500 percent performance gains for Read/Write operations, and up to 500 percent gain for Read Only(1).
• On Linux: up to 360 percent performance gain in Read/Write operations and up to 200 percent improvement in Read Only(2).

First of all, download the source code from the MySQL site then un-tar it wherever you like. You need to change the version number below to the one you downloaded.

tar -xvzf mysql-5.5.16.tar.gz

To be able to compile it we need to have the proper tools to do it:

apt-get install cmake c++ libncurses5-dev bison libaio-dev

We also need to create the mysql user and group before proceeding:

groupadd mysql
useradd -r -g mysql mysql

Enter the directory you extracted and issue the following commands to compile and install MySQL. This will install MySQL into the /usr/local/mysql directory:

cmake .
make
make install
make install scripts

Change the ownership of the installed directory to the mysql user:

chown -R mysql:mysql /usr/local/mysql

If this is the first installation of MySQL on this machine, you need to initiate the MySQL data directory and tables:

cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql

We need to provide a configuration file in the /etc/mysql folder with the same name as the server instance (if you like to use the startup script provided shortly). There are multiple configuration samples available in the /usr/local/mysql/support-files folder, you will need to review one to start off with and then change it according to your needs. In this case I am using the “medium size” MySQL server config.

mkdir /etc/mysql
cp /usr/local/mysql/support-files/my-medium.cnf /etc/mysql/my.cnf

If you want your MySQL server instance available on all network cards (0.0.0.0), then you can skip this part. But you would probably like to limit access to the localhost (127.0.0.1).

nano /etc/mysql/my.cnf

Add this to the [mysqld] section of the file:

bind-address = 127.0.0.1

Now the MySQL Server 5.5 is installed, but you might want to create a startup and shutdown script for it too. At Planet MySQL I found the following script that use the Ubuntu Upstart to manage MySQL. Create a file…

nano /etc/init/mysql.conf

…and paste the following script into it:

#
# 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/mysql
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

Finally you will need to create the mysql.pid file referenced by the MySQL Ubuntu Upstart script:

touch /var/run/mysql.pid

Restart your Ubuntu server and it will be ready to use, enjoy!