You are here

Using MySQL (and MariaDB) on DICE

Printer-friendly versionPrinter-friendly version
→ If you're looking for our managed database service, please see the PostgreSQL page.

Introduction

DICE carries copies of popular RDBMS MySQL (or the MariaDB fork) 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 carry new versions of MySQL (unless specifically required e.g. for "bug-for-bug" compatibility).

Version Distribution DICE SL7 DICE SL6
(all) 64-bit 32-bit & vDICE
10.x MariaDB on request - -
5.5 MariaDB yes on request -
5.1 MySQL - yes yes

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 MySQL software, perhaps for reasons of third-party compatibility, then it's recommended that you consider PostgreSQL as an alternative. PostgreSQL is largely compatible with MySQL and typically comparable in performance and functionality, and is often supported alongside MySQL 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, 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}
$ 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 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 of MySQL and MariaDB on individual machines. Please see requesting software for details on how to do this.

Last reviewed: 
24/03/2016

System Status

Home dirs (AFS)
Network
Mail
Other services
Scheduled downtime

Choose a topic