MariaDB

MariaDB is a fork of MySQL, a multi-threaded multi-user relational database management system that aims for drop-in compatibility with MySQL. MySQL was developed by Swedish company MySQL AB in 1995, acquired by Sun Microsystems in 2008, and in turn acquired by Oracle Corporation in 2010. Due to the conflict of interest between MySQL and Oracle DB, as well as the community's distrust of Oracle (compounded by lack of response from Oracle during the Sun acquisition), Monty Widenius forked MySQL into MariaDB.

MariaDB is a middle ground (of sorts) between SQLite's easier setup and lesser performance and resiliency, and PostgreSQL's increased capabilities and complexity.

Prerequisites

• If using BTRFS, disable Copy-On-Write for the datadir.
• If using ZFS, create a dataset for the datadir using -o recordsize=8K -o primarycache=metadata -o logbias=throughput.

Required Packages

sudo pacman -Syu mariadb

Configuration

Data Directory

If you want to put your databases somewhere other than the default /var/lib/mysql:

sudo mkdir <dir>
sudo chown -R mysql:mysql <dir>

tmpfs for tmpdir

The directory used by MariaDB for storing temporary files is named tmpdir. For example, it is used to perform disk based large sorts, as well as for internal and explicit temporary tables.

Create the directory with appropriate permissions:

sudo mkdir -pv <dir>
sudo chown mysql:mysql <dir>

Find the id and gid of the mysql user and group:

$ id mysql

uid=27(mysql) gid=27(mysql) groups=27(mysql)

Add to your /etc/fstab file.

/etc/fstab

...
tmpfs	/srv/sqltmp	tmpfs	rw,noatime,gid=27,uid=27,size=100M,mode=0750	0 0
...

Note: If you are using ZFS, add x-systemd.after=zfs-mount.service to the list of mount options

Configuration File

/etc/my.cnf.d/server.cnf

...
[mysqld]
datadir=/srv/mdb
skip-networking
tmpdir=/srv/mdb/tmp
innodb_file_per_table=1
innodb_file_format=Barracuda
# ZFS-specific
skip-innodb_doublewrite
innodb_use_native_aio=0
innodb_use_atomic_writes=0
...

Note: The skip-networking option means the database will be unavailable over TCP. Local programs that need access must use the UNIX socket.

Initial Setup

sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=<dir>

Start the Server

sudo systemctl enable --now mariadb

Security Settings

sudo mysql_secure_installation

Create a user:

$ mysql -u root -p

MariaDB> CREATE USER '<user>'@'localhost' IDENTIFIED BY '<password>';
MariaDB> GRANT ALL PRIVILEGES ON <database>.* TO '<USER>'@'localhost';
MariaDB> FLUSH PRIVILEGES;
MariaDB> quit