Sunday, March 4, 2012

How to create a portable MySQL installation on GNU/Linux

This tutorial shows how to create a portable MySQL installation on GNU/Linux. It applies to:

MySQL 5.5.19
CentOS 6.x / Ubuntu 11.10 Oneiric Ocelot

At the end of this guide you will obtain a portable MySQL installation on a target directory with its own databases, binaries, logs, pid files, etc. Consider always the use of a permission preserving packaging (like TAR) for moving the installation between systems or removable storages.

Requirements

Download MySQL 5.5.19 source code (mysql-5.5.19.tar.gz) from MySQL site
A previously installed CentOS 6.x / Ubuntu 11.10 Oneiric Ocelot

Steps

Define some environment variables to make the installation smoothly:
$ TARGET=$HOME/mysql
$ BASEDIR=$TARGET/usr/local/mysql
$ DATADIR=$TARGET/usr/local/mysql/data
$ PORT=9797
$ VERSION=5.5.19
Install cmake, ncurses and bison:
- On CentOS:
  $ sudo yum install cmake ncurses-devel bison
- On Ubuntu:
Unpack and make (NOTE: mysql-5.5.19.tar.gz is already downloaded in /tmp):
$ pushd /tmp
$ tar zxvf mysql-${VERSION}.tar.gz
$ cd mysql-${VERSION}
$ cmake .
$ make
Install into target directory:
$ mkdir -p $TARGET
$ make install DESTDIR="$TARGET"
Create system databases:
$ pushd $BASEDIR
$ scripts/mysql_install_db --user=$USER \
--basedir=$BASEDIR \
--datadir=$DATADIR \
--ldata=$DATADIR
$ mkdir -p $TARGET/var/run/mysql
$ mkdir -p $TARGET/var/log/mysql
$ popd
$ popd

Post Install Steps

Running the portable MySQL (NOTE: bind-address is settled to 0.0.0.0, it means listening on all network interfaces, you can change it to 127.0.0.1 for local connections only or to an specific network interfaces address like 192.168.122.45):
$ $BASEDIR/bin/mysqld_safe --user=$USER \
--basedir=$BASEDIR \
--datadir=$DATADIR \
--pid-file=$TARGET/var/run/mysql/mysql.pid \
--skip-syslog \
--log-error=$TARGET/var/log/mysql/mysql.err \
--port=$PORT \
--socket=$TARGET/var/run/mysqld/mysqld.sock \
--ledir=$BASEDIR/bin \
--mysqld=mysqld \
--bind-address=0.0.0.0
Connecting locally (via socket) to the portable MySQL:
$ $BASEDIR/bin/mysql -u root --socket=$TARGET/var/run/mysqld/mysqld.sock
Creating a sample database and granting all privileges to a user:
$ $BASEDIR/bin/mysql -u root --socket=$TARGET/var/run/mysqld/mysqld.sock <<EOT
create database alfresco;
grant all privileges on alfresco.* to alfresco@'%' identified by 'alfresco';
EOT
Connecting remotely to the created database (NOTE: verify firewall settings on the server before connecting remotely):
$ mysql -u alfresco -h SERVER --port=9797 -p alfresco
Changing the root password:
$ $BASEDIR/bin/mysqladmin -u root password 'root' --socket=$TARGET/var/run/mysqld/mysqld.sock