Building and running MySQL as a local (non-root) user

I. Introduction
I occasionally need MySQL on a Linux box, on which I don't have root access. (Oddly enough, this happens most frequently at work, where I may be allocated a server for internal work, but root access is jealously guarded by the guardians of the hardware.)

It's surprisingly complicated to configure a binary MySQL distribution to run as a local user, aka in "userspace". Oddly enough, I found it easiest to simply build MySQL from source, with the various directories it needs pre-configured, and then run it from there.

II. Build and install
For the sake of example, I'm assuming that I'm building and installing everything under a userid 'mysql56', and that I'm logged into that user for all of the instructions below.

Note: in case a root-installed MySQL is already running on this server, these instructions install a completely separate MySQL running on a different port and socket.

Install cmake. MySQL requires cmake. If you don't already have cmake ("which cmake"), you can easily download and install it from source.
1. Download source from www.cmake.org/cmake/resources/software.html.
2. Unpack, e.g
```
   tar xvfz cmake-2.8.12.2.tar.gz
```
3. Install, e.g.:
```
   mkdir $HOME/CMAKE
   cd cmake-2.8.12.2
   bootstrip --prefix=$HOME/CMAKE
   make
   make install
```
4. Add $HOME/CMAKE/bin to your PATH.
Build MySQL.
1. Download from dev.mysql.com/downloads/mysql. At "Select Platform", choose "Source Code", then choose "generic linux (architecture independent)".
2. Unpack, e.g.
```
   tar xvfz mysql-5.6.17.tar.gz
```
3. Configure and prepare installation location, e.g.:
```
   cd mysql-5.6.17

   mkdir $HOME/MySQL
   mkdir $HOME/MySQL/data
   mkdir $HOME/MySQL/etc

   cmake -D MYSQL_DATADIR=$HOME/MySQL/data -D SYSCONFDIR=$HOME/MySQL/etc -D CMAKE_INSTALL_PREFIX=$HOME/MySQL .
```
  (notice the trailing ".")
4. Build:
```
   make
   make install
```
5. Prepare your my.cnf.
  Copy an appropriate sample MySQL config (.cnf) file from MySQL/support-files, to $HOME/MySQL/etc/my.cnf. Edit it to match your needs... in particular, change the port and socket to something different from the existing or standard MySQL. For example, since these directions are building MySQL 5.6, I might use port 3307 and socket /tmp/mysql56.sock.
6. Prepare base MySQL tables, e.g.:
```
   cd $HOME/MySQL
   scripts/mysql_install_db --no-defaults
```
7. Start the server, and set the root password:
```
$HOME/MySQL/bin/mysqld_safe --defaults-file=$HOME/MySQL/etc/my.cnf \
          --basedir=$HOME/MySQL                        \
          --datadir=$HOME/MySQL/data                   \
          --pid-file=$HOME/MySQL/mysql.pid             \
          --port=3307                                  \
          --socket=/tmp/mysql56.sock                   \
          --log-error=$HOME/MySQL/err.log              \
          --user=mysql56

$HOME/MySQL/bin/mysqladmin -u root password 'newPassword'
```
8. Create mysql client script. E.g. in my $HOME/bin, I create 'mysql' containing:
```
    #!/bin/sh
    $HOME/MySQL/bin/mysql $@
```
  Make sure it's executable, and in the PATH (before any possibly already-installed mysql elsewhere!).
9. Put the start/stop script in an easy place (or create your own), then bounce the server and verify that you can connect to it: E.g.:
```
    cp support-files/mysql.server $HOME/bin

    mysql.server stop
    mysql.server start

    mysql -u root -p
```
10. Start server at reboot. I also like to use cron to restart MySQL at reboot. I add something like the following to my crontab:
```
   @reboot   /home/myuser/bin/mysql.server start
```
11. Other configuration. There are many configuration changes that can be made to the $HOME/MySQL/my.cnf file, that are outside the scope of this article. See, for example, www.fromdual.com/mysql-configuration-file-sample.