Creating a portable MySQL in CentOS 6 and Ubuntu 11.10 Linux from sources

This is a how to create a portable MySQL installation on GNU/Linux from the sources. 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 it's 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

1. 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

2. Install cmake, ncurses and bison:
• On CentOS:

$ sudo yum install cmake ncurses-devel bison

• On Ubuntu:

$ sudo apt-get install cmake libncurses5-dev bison 

3. 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 

4. Install into target directory

$ mkdir -p $TARGET
$ make install DESTDIR="$TARGET"

5. 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