Using MariaDB (and MySQL) on DICE

Introduction

DICE carries copies of MariaDB, the modern, open fork of popular RDBMS MySQL on most PCs. With it comes the ability to create self-managed SQL server instances, but in our managed environment this is not always straightforward.

Available Versions

As stated on the compatibility page MariaDB is a drop-in replacement for MySQL in most circumstances, including the name of the mysql commands. We will no longer provide versions of MySQL but you will see the mysql commands below, while these are what most users are familiar with.

Running your own server vs. managed server

Before you begin this process, it is worthwhile making sure that this is the best option for you. If you do not specifically need to use the MariaDB software, perhaps for reasons of third-party compatibility, then it's recommended that you consider PostgreSQL as an alternative. PostgreSQL is largely compatible with MariaDB and typically comparable in performance and functionality, and is often supported alongside MariaDB for web applications and the like.

It's recommended that you consider PostgreSQL mainly because it's better supported and used within the school; for example DICE carries the latest version wherever possible, and support can offer more detailed assistance in its operation, including on servers. Crucially there are also a number of managed postgresql servers on which we can easily grant accounts to students and staff; if you don't need to run your own server, it's strongly recommended that you investigate this as your first option.

However if you have determined that you must use MySQL/MariaDB, setting up a local server is straightforward. Bear in mind that, if you need to use this with large or important data, it's worth contacting computing support so that a managed configuration can be discussed. In most cases support will not be able to offer dedicated hardware for the purpose but if you have a server machine in mind, support should be able to help.

Setting up

The important part of this is to choose a location for your data. You must also choose a location on local disk to store the socket file for server communication: your home directory is a remote filesystem which does not support this type of file - in any event neither it nor the mysql PID file would be of use or relevance on another machine. Your home directory will accept a data store but for reasons of performance and data integrity is strongly discouraged. In particular, your server's access to this directory will expire with your AFS credentials, causing the server to fail. The only recommended configuration is to store all data locally on disk, and to ensure that regular backups are taken into your home directory as your data is only as safe as your desktop's hard disk (if not less).

Having made these decisions, initialising a database is straightforward. Assuming the following variables (it might be worth setting these in a shell script for example):

mydatadir=/chosen/data/path/db/
mysockfile=/chosen/data/path/mysql.sock
mypidfile=/chosen/data/path/mysql.pid

You should initialise and start the database, then set a "root" password:

$ mkdir -p ${mydatadir}
$ bash mysql_install_db --datadir=${mydatadir}

$ /usr/libexec/mysqld --skip-networking \
  --datadir=${mydatadir} \
  --socket=${mysockfile} \
  --pid-file=${mypidfile}

$ mysqladmin --socket=${mysockfile} -u root password

In future you need only run the 'mysqld' command line above to start the server. You should now be able to connect to your database with:

$ mysql --socket=${mysockfile} -u root

Make sure you *always* issue the shutdown command when you are finished:

$ mysqladmin --socket=${mysockfile} -u root shutdown 

More advanced usage, including IP-based access, administration and security are beyond the scope of this document — they are well documented online.

Software versions

We will typically only provide the version of MariaDB/MySQL provided by the upstream operating system. If however you have specific needs which could be provided by a newer version we can provide newer versions on individual machines. Please see requesting software for details on how to do this.