Uppsala Multidisciplinary Center for Advanced Computational Science

Running an own MySQL instance as a batch job

UPPMAX currently (as of 2011-03-10) does not offer a centralized database daemon for use by users. Instead, one has to run an own database daemon. This page will contain hints and tips about how to do that.

Using "MySQL Sandbox"

There is a software called "MySQL Software", that is designed especially for the purpose of installing MySQL in non-standard locations. Here we will show how to do the trick using it.

  1. As an UPPNEX user, log in to one of the Milou login nodes on UPPMAX:

    ssh milou.uppmax.uu.se
     
  2. Download MySQL Sandbox from here.

    cd ~/glob

    mkdir -p install/mysqlsandbox

    cd install/mysqlsandbox

    wget http://launchpad.net/mysql-sandbox/mysql-sandbox-3/mysql-sandbox-3/+download/MySQL-Sandbox-3.0.17.tar.gz
     
  3. Staying in the same directory, extract the tarball:

    tar -zxvf MySQL-Sandbox-3.0.17.tar.gz
     
  4. Enter the newly created directory:

    cd MySQL-Sandbox-3.0.17/
     
  5. Open the README file for editing:

    nano README
  6. Edit the lines under "installation", and change all instances of "$HOME" to "$HOME/glob", or use the following, already fixed, commands instead:

    export PATH=$HOME/glob/usr/local/bin:$PATH
    export PERL5LIB=$HOME/glob/usr/local/share/perl5/:$PERL5LIB
    perl Makefile.PL PREFIX=$HOME/glob/usr/local

    make
    make test
    make install
     
  7. Download MySQL. 5.1, the "generic version", for x86_64bit, as a "Compressed TAR" archive (Important!), is the one to choose (or use this direct link).

    (Don't use MySQL 5.5, as it tends to cause problems because InnoDB is enabled by default on it, and causes errors.)

    cd

    mkdir -p glob/mysql/tarballs/

    cd glob/mysql/tarballs/

    wget https://downloads.skysql.com/archives/mysql-5.1/mysql-5.1.73-linux-x86_64-glibc23.tar.gz
  8. Create a folder where to contain the new sandboxes:

    cd

    mkdir -p glob/sandboxes
  9. Execute the command make_sandbox (should be available after install of MySQL Sandbox according to steps 1-6, if not, run the two "export" lines in step 6 again) on the downloaded tarball, and specify the newly created sandbox directory as the "upper" directory for all sandboxed databases:

    cd ~/glob/mysql/tarballs && make_sandbox mysql-5.1.56-linux-x86_64-glibc23.tar.gz -- --no_run --upper_directory=$HOME/glob/sandboxes

    NOTE: The part "--upper_directory=$HOME/glob/sandboxes" is important, since otherways, the database gets intalled in your homefolder, which will slow down and cause troubles for the backed up parallell file system used for home folders. Therefore, the glob-folder is the right place to install databases.

    The above command will install MySQL in a sandbox under the folder like "~/glob/sandboxes". The actual sandbox for the 5.1.56 release of MySQL will be "~/glob/sandboxes/msb_5_1_56".
     
  10. Go inside the folder "~/glob/sandboxes/msb_5_1_56":

    cd

    cd glob/sandboxes/msb_5_1_56
     
  11. copy the file "start", to a copy named "start_with_slurm"

    cp start start_with_slurm
     
  12. Modify the file in the following way:

    nano start_with_slurm
    • Remove the trailing '&' character on the following two lines:

      $MYSQLD_SAFE --defaults-file=$SBDIR/my.sandbox.cnf $@ > /dev/null 2>&1 &

      $MYSQLD_SAFE --defaults-file=$SBDIR/my.sandbox.cnf $@ > "$SBDIR/start.log" 2>&1 &

      (They are not immediately close to each other).
       
  13. If you want to modify any MySQL settings, you can do it in the my.sandbox.cnf file, in the ~/glob/sandboxes/msb_5_1_56 folder:

    nano my.sandbox.cnf

    (Note that if changing the port, you might need to change things in other files also, such as the "start" file ... though not sure about this ...).
     
  14. Start a batch job, using the new start-script file:

    sbatch -A your_project -p node -n 8 -t 0-08:00:00 -J job_name start_with_slurm

    Example:

    sbatch -A b2014999 -p node -n 8 -t 0-08:00:00 -J MySQL_Server start_with_slurm
     
  15. use jobinfo -u username to check whether your job has got started, and when this has happened, on which node it has started on.

    jobinfo -u username

    Doing it, it could look like so:

    Running jobs:
    JOBID PARTITION NAME USER ACCOUNT ST START_TIME TIME_LEFT NODES CPUS NODELIST(REASON)
    374876 node MySQL_Server samuel uppmax_staff R 2011-03-11T22:24:49 7:57:34 1 8 q240

    On the last column, we see that the job runs on node (or "computer") with hostname "q240".
     
  16. Supposing that you see with jobinfo that your job gets started on node q240, then log in to this node with ssh:

    ssh q240
     
  17. After logging in, you can check if the database seems running, by issuing the following command:

    ps -ef | grep mysql

    It should return something similar to the following (somewhat messy) lines:

    [samuel@q240 ~]$ ps -ef | grep mysql
    samuel 18430 18420 0 22:24 ? 00:00:00 /bin/sh /bubo/home/h22/samuel/glob/mysql/tarballs/5.1.56/bin/mysqld_safe --defaults-file=/bubo/home/h22/samuel/glob/sandboxes/msb_5_1_56/my.sandbox.cnf
    samuel 18505 18430 0 22:24 ? 00:00:00 /bubo/home/h22/samuel/glob/mysql/tarballs/5.1.56/bin/mysqld --defaults-file=/bubo/home/h22/samuel/glob/sandboxes/msb_5_1_56/my.sandbox.cnf --basedir=/bubo/home/h22/samuel/glob/mysql/tarballs/5.1.56 --datadir=/bubo/home/h22/samuel/glob/sandboxes/msb_5_1_56/data --log-error=/bubo/home/h22/samuel/glob/sandboxes/msb_5_1_56/data/msandbox.err --pid-file=/bubo/home/h22/samuel/glob/sandboxes/msb_5_1_56/data/mysql_sandbox5156.pid --socket=/tmp/mysql_sandbox5156.sock --port=5156
    samuel 18587 18522 0 22:25 pts/0 00:00:00 grep mysql

    ... if you on the other hand only see the following, it means the database has NOT started:

    [samuel@q240 ~]$ ps -ef | grep mysql
    samuel 18815 18750 0 22:28 pts/0 00:00:00 grep mysql

    (Only the "grep" command we just fired off, is showing up)

    If the database has not started, you might find some useful hints at the end of the error-log file, by doing:

    tail -n 25 ~/sandboxes/msb_5_1_56/data/msandbox.err
     
  18. Anyhow, if the database has started successfully, you should now be able to connect to the database with the following command (unless you have changed some default settings, like port -- you might have to try with 5160 which is the default port in some versions):

    mysql -u msandbox -p --protocol=TCP -P 5156

    (password is also 'msandbox' by default).
     
  19. Then use standard MySQL syntax to create databases etc.

    (Importing a mysql dump, naturally goes like (NOTE: it seems that the port number is by default reflecting the MySQL version number):

    mysql -u msandbox -p --protocol=TCP -P 5156 < mysql-dump-file.sql

    ... etc )
     
  20. Happy MySQL:ing!

External links