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

Charles Roth, 16 April 2014       (Techblog top)

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

Note: these instructions assume that MySQL is not already running on this server.  If it is, you'll need to figure out how to tell the build not to use the same port, typically 3306.

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

  2. 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 base MySQL tables, e.g.:
         cd $HOME/MySQL
         scripts/mysql_install_db
      
    6. Start the server, and set the root password:
         bin/mysqld_safe &
         bin/mysqladmin -u root password 'newPassword'
      
    7. 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!).
    8. 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
      
    9. 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
      
    10. 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.