Computing, run a MySQL server from /scratch

How to run a MySQL or MariaDB server with data in /scratch

In order to process SQL data on the compute servers, it is possible to use the system installed version of the MySQL server and run it from your own directory, for example in /scratch.

Follow these steps to do so. We assume that you start in /scratch/username.

  1. Create a file named my.cnf, and edit it to read as follows:
    [mysqld]
    log-error = /scratch/username/mysql/log/errorlog
    pid-file  = /scratch/username/mysql/run/mysqld.pid
    datadir   = /scratch/username/mysql/data
    socket    = /scratch/username/mysql/private/mysql.sock
    skip_networking
    
    [mysqladmin]
    socket    = /scratch/username/mysql/private/mysql.sock
    
    [mysql]
    socket    = /scratch/username/mysql/private/mysql.sock
    

    Replacing
    • /scratch with another base directory, if it does not exist on your system.
    • username with your own username.

    We intentionally suggest the file my.cnf to be used later with
    --defaults-file=my.cnf
    so that this is the only file that is applied.

    You may also use .my.cnf starting with dot:

    --defaults-file=.my.cnf
    so that this is still the only file that is applied, but if you forget this option, it will be part of the row:
    /etc/my.cnf, $HOME/.my.cnf
    and maybe more, from which settings are read.

    It is up to you to decide which settings you want to apply.

  2. Create the necessary directories and set the correct permissions:
    $ cd /scratch
    $ mkdir mysql mysql/{log,run,data,private}
    $ chmod 700 mysql mysql/private

  3. Initialise the MySQL database:
    $ mysql_install_db --defaults-file=my.cnf

  4. You can now run the server by hand using:
    $ /usr/libexec/mysqld --defaults-file=my.cnf < /dev/null &

  5. You can now locally connect using the mysql commandline client, as root:
    $ mysql --defaults-file=my.cnf -u root

  6. Please set a root password and remove default users using this commandline client:
    mysql> update mysql.user set password=password('yourpass') where user='root';
    mysql> delete from mysql.user where user='';
    mysql> flush privileges;

  7. If you do not need to access the server from other hosts, you are done. Please shutdown the MySQL server when your experiments are not running (see below).

  8. If necessary, you can enable network access by removing the skip_networking line in my.cnf and restarting the server. If other MySQL servers are running on this compute host, you can change the network port that the server listens on in your my.cnf by adding port= to the [mysqld] section.

Shutting down the MySQL server

Because the compute servers are shared resources, we ask you to shutdown your server when not running other jobs. You can do this using the mysqladmin command:

$ mysqladmin --defaults-file=my.cnf -u root -p shutdown

Logging of the server

To troubleshoot the server, or clients that cannot connect, it is possible to have the server log it's output to a file. Add the following snippet to the [mysqld] section in the configuration file:

general_log = 1
general_log_file = /scratch/username/mysql/log/general_log
log_output = FILE

Note that this log file might grow fast, be sure to turn this feature off when not necessary.

Security

The setup without networking needs appropriate permission on various directories to ensure security on our multi user systems. Both the data and private directories cannot be accessible.

For networked setups, be sure to set a good password for your remote user.


Contact us | Webmaster

Questions? Mail to helpdesk.win@tue.nl