Running a private mysqld instance and keeping the databases in your home directory

I needed a DBMS running for some experiments with Hibernate. I wanted to use the
packages delivered by Fedora but did not want to run it as a system service,
because this usually requires special user permission; in the worst case root
permission. Running the DBMS as a system service has further
disadvantages. Programmers are of the lazy folk and tend to run the service at
boot time. It saves a few keystroke. It also wastes computing resources, opens
potential security holes into your computer, and slows down the start-up time of
the system. As a further detriment this approach fails on systems where you
do not have administrator permissions. You know, the ones at work. So, it is best
to run it as local user from the start.

I chose MySQL for my DBMS for no other reason that I have used it before
somewhere in far past. Installing it on Fedora is simple:

su -c "yum install mysql-server"

The MySQL manual contains detailed installation instructions for all major
distributions.

Then copy the default MySQL configuration file at /etc/my.cnf into your home
directory as ~/.my.cnf and adapt it. Mine is looking like this:

[mysqld]
datadir=/home/jhunovis/tmp/mysqld
socket=run/mysqld.sock

[mysqld_safe]
log-error=log/error.log
pid-file=run/mysqld.pid

It overrides the settings of the global configuration file. The «datadir» is the
location where MySQL stores its run-time data, like the actual databases, but
also log-files and the Unix socket. Note that all the file options are relative
to «datadir». Unfortunately, the tilde is not accepted for referring to a home
directory.

Update 08/09/12, 21:30 UTC: I just realized, that the command «mysqlshow» will show the directories «log» and «run» as databases, which is awkward, if not disturbing. I then decided to place all the non-database files into a sibling directory of the datadir. End update.

Update 08/10/12, 13:15 UTC: When I was filling some tables with sample data I
saw that my database used the character set «latin1» instead of
«utf8». Furthermore it used * Swedish* for collating, which was just weird (I am
far from Sweden). Adding the line «character_set_server=utf8» to the
configuration file fixed this. You can check your character settings by issuing:

mysqladmin variables | grep -E 'character|collation'

End update.

You will have to create those directories prior starting the MySQL
daemon:

mkdir -p ~/tmp/mysqld/{run,log}

Starting mysqld at this point will fail because the system databases are still
missing. Create them by issuing on the command line:

mysql_install_db

Now start the service:

mysqld_safe

Then you can connect to your database:

mysql -u root -S ~/tmp/mysqld/run/mysqld.sock

You have to pass the socket to the MySQL client to which it should connect to.
Alternatively, you may add a section like the following to your ~/.my.conf that
saves you the trouble:

[mysql]
socket=/home/jhunovis/tmp/mysqld/run/mysqld.sock

You will have to repeat this for every MySQL command you
use. Better point the shell environment variable «MYSQL_UNIX_PORT» to that
socket file and be done with it once and for all:

export MYSQL_UNIX_PORT=~/tmp/mysqld/run/mysqld.sock

Your private MySQL service instance is now ready. You may now test it, adjust
permissions, and create a database for your own purposes.

Update 08/10/12, 13:15 UTC: Changed title from «Starting a private mysqld instance» to «Running a private mysqld instance and the keeping databases in your home directory».