Tareque's Personal Portal

Running Your Own MySQL Instance on WebFaction

One of my recent projects required me to write Triggers. Only with version 5.1.6 MySQL implemented Trigger/ Routine privileges. In all prior versions that support triggers, require you to have root privileges to create triggers/ routines. WebFaction provides version 5.0.77 by default. So I needed to install and run the latest version of MySQL on my WebFaction account. You might have other reasons to run your own MySQL instance, for example, greater control over your database.

likebike on WebFaction forum has an excellent starting point for having your own MySQL server running. Click here to read his post.

But it is outdated and requires you to compile MySQL source and by doing that you will end up with a limited set of database engines. Triggers, routines and foreign key constraints require the InnoDB engine which does not get installed if you compile and install from MySQL source.

Here's the step by step guide:

Reserve a port number by going into the WebFaction control panel and creating a new app of type "Custom App listenting on port". Note down the port number.

Go to your home directory:

cd ~/

Download MySQL binary for unix/ linux from the official site. Get Linux (x86) version.

wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-glibc23.tar.gz/from/http://mysql.he.net/

Extract the file.

tar xvf mysql-5.1.41-linux-i686-glibc23.tar.gz

Rename the extracted folder to mysql (assuming you don't have one created already):

mv mysql-5.1.41-linux-i686-glibc23 mysql

Create a file name my.cnf in the mysql directory:

touch ~/mysql/my.cnf

Edit the file and put the following information in the file:

[mysqld]
port=$MY_PORT
socket=$HOME/mysql/mysql.sock
basedir=$HOME/mysql
datadir=$HOME/mysql/data

[client]
port=$MY_PORT
socket=$HOME/mysql/mysql.sock

Install the default schema:

cd ~/mysql/scripts
./mysql_install_db --basedir=$HOME/mysql --datadir=$HOME/mysql/data

Go to the mysql binary directory:

cd ~/mysql/bin

Start the MySQL server (parameter order matters):

./mysqld_safe --defaults-file=$HOME/mysql/my.cnf --ledir=$HOME/mysql/bin &

Leave it running in the background by pressing ctrl+c. You should be able to see your MySQL process by typing:

ps -u username -O pid,rss,command | grep 'mysql'

Change your root password:

./mysqladmin -u --socket=$HOME/mysql/mysql.sock root password 'newpassword'

Now to get your django application to recognize and connect to this database, you have to change 'DATABASE_HOST' setting in your django settings file. By default you put 'localhost' here. But the have django recognize your alternate database instance, you can use the socket path instead. So add/ replace the following line in your settings.py:

DATABASE_HOST = '/home/username/mysql/mysql.sock'

If you need to access the MySQL prompt, use the following command:

~/mysql/bin/mysql --socket=$HOME/mysql/mysql.sock -u root -p

You can also create an alias in your .bashrc profile using the command above for easy access to your MySQL prompt.

Enjoy. Remember running MySQL instance will take up 15MB ~ 20MB of memory. So it might not be an option if you're running too many Apache threads/ django instances.

Also WebFaction might kill your MySQL daemon if you're above the memory limit. In order to prevent that, you can setup a cron job to try to start your MySQL instance every 10/ 15 minutes.