Installing MariaDB

On Linux, the MariaDB server, client and other packages can be installed from your Linux distro's repositories, or preferably, from MariaDB's own repository so you get the most recent version. E.g. the default MariaDB version on CentOS 7 is 5.5 which is not supported by the ispyb-database repo. On Debian 9 the default is 10.1 which may work.

Note that it's also possible to download the binaries into e.g. your home directory and run MariaDB as a non-privileged user - see further down on this page. This can be useful in restricted environments where you don't have privileges to install packages, or maybe for installing multiple versions on a single computer.

Setting up the package repository

Use the MariaDB configuration repository tool to get the repository configuration file needed.

Example install commands

Here are just some example install commands, but please refer to MariaDB for authoritative and exhaustive documentation - see here.

Ubuntu/Debian

sudo apt update
sudo apt install mariadb-server

Optionally, to set up a password and so forth, which is crucial for production environments:

sudo mysql_secure_installation

CentOS/Redhat

sudo yum install MariaDB-server MariaDB-client

Optionally, to set up a password and so forth, which is crucial for production environments:

sudo mysql_secure_installation

Fedora

dnf install MariaDB-server MariaDB-client

Optionally, to set up a password and so forth, which is crucial for production environments:

sudo mysql_secure_installation

Installing and running MariaDB server as a non-privileged user

Download the binaries

You can download binaries for a different version and install e.g. in your /scratch area:

E.g. download the latest stable version from downloads.mariadb.org : https://downloads.mariadb.org/mariadb/10.3.9/#file_type=tar_gz&bits=64

Unpack the archive and rename the installation directory (assumes you've downloaded the tar.gz file into your /tmp dir):

cd /scratch
tar xvfz /tmp/mariadb-10.3.9-linux-x86_64.tar.gz

mv mariadb-10.3.9-linux-x86_64 mariadb

export MARIADB_HOME=/scratch/mariadb

Data directory installation

Create the data directory e.g. like this:

cd $MARIADB_HOME
scripts/mysql_install_db --basedir=$MARIADB_HOME --datadir=/scratch/mariadb/data --no-defaults

Create a config file at $HOME/.my.cnf with the following content:

[client]
user = root
protocol = tcp

Server start

bin/mysqld_safe --no-defaults --datadir=/scratch/mariadb/data &

Note: --no-defaults must be the first parameter!

Client connect

You can now connect to the server as the MariaDB root user – note that no password is required:

bin/mysql

Upgrade data directory

The version of the binaries needs to match the version of the data directory. So if you upgrade to newer binaries, then you also need to upgrade your data directory.

Assuming you've already done "module load mariadb-server", the server is started and you have a $HOME/.my.cnf file as outlined above, then all you have to do is:

mysql_upgrade

Note that it it's fine to run this more than once, or on a data directory that doesn't need upgrading. Useful client commands

A few useful commands you can run from the client

help;

shutdown;

SHOW DATABASES;

SELECT user, host FROM mysql.user;

exit;

CREATE DATABASE mydb;

USE mydb;

CREATE USER webapp@'%' IDENTIFIED BY 'myPasswordHere...';

GRANT SELECT, UPDATE, INSERT, DELETE ON mydb.* TO webapp@'%';

DROP DATABASE mydb;

DROP USER webapp@'%';

REVOKE SELECT, UPDATE, INSERT, DELETE ON mydb.* FROM webapp@'%';

Note that database, table and user names are (by default) case sensitive.

The '%' character is the wildcard character in SQL, though '*' is used in some contexts (as seen in the examples above).