Installing MariaDB 10.1 on Ubuntu 16.04

I got tired of waiting for a MariaDB 10.1 backport on our Ubuntu 16.04 boxes. The stock repo ships 10.0, and 10.1 has the things I actually want on new servers: built-in Galera, table-level encryption, and unix_socket auth for root out of the box. So I install it straight from the MariaDB repository.

I'm assuming a fresh 16.04 server with sudo, outbound HTTP to a mirror, and no MySQL or MariaDB packages yet. If you already have mysql-server from the Ubuntu repos, mysqldump everything first. The package swap stops the old server.

Add the repository and key

The packages are signed, so import the key first, then add the repository line. I copy the mirror layout from the MariaDB download page.

sudo apt-get install -y software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.osuosl.org/pub/mariadb/repo/10.1/ubuntu xenial main'
sudo apt-get update

If apt-get update complains about a missing key, just re-run the apt-key command. The Ubuntu keyserver is slow on port 80 sometimes and the first attempt can time out silently.

Install the server and client

sudo apt-get install -y mariadb-server mariadb-client

The Debian-style packaging does not prompt for a root password on 10.1. Instead the MariaDB root@localhost account authenticates through the unix_socket plugin, which means only the operating-system root user (or anyone with sudo) can log in as the database root user without a password. I like this. There's no password to leak into shell history or provisioning scripts.

Check the version that actually landed:

mysql --version
# mysql  Ver 15.1 Distrib 10.1.21-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Lock it down

sudo mysql_secure_installation

When it asks for the current root password, press Enter. I answer yes to everything: anonymous users, remote root login, the test database, reloading privileges. You can set a root password here, but with unix_socket you don't need one for local admin.

Config tweaks

On Ubuntu the packaged configuration lives under /etc/mysql/, and the server options are in /etc/mysql/mariadb.conf.d/50-server.cnf. These are the settings I change on every install:

[mysqld]
bind-address            = 127.0.0.1
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci
innodb_buffer_pool_size = 1G
innodb_file_per_table   = 1
log_error               = /var/log/mysql/error.log

Leave bind-address on the loopback address unless the application runs on another host. Restart after editing:

sudo systemctl restart mariadb
sudo systemctl enable mariadb

Note that the unit is called mariadb.service; mysql.service exists only as an alias for compatibility.

An application user

Never hand root to the application. Log in with sudo and make a dedicated account.

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

Did it work?

Check that the service is running, listening where you expect, and that both the socket login and the new user work.

sudo systemctl status mariadb
sudo ss -ltnp | grep 3306
sudo mysql -e "SELECT VERSION(), @@character_set_server;"
mysql -u appuser -p appdb -e "SHOW TABLES;"

Reboot once and run systemctl status mariadb again. I skipped this once and spent an evening chasing an "it worked yesterday" problem that was just a service nobody had enabled.

Things that bit me

ERROR 1698 (28000): Access denied for user 'root'@'localhost' when running mysql -u root without sudo. This is the unix_socket plugin doing its job. Either use sudo mysql, or switch root back to password authentication:

UPDATE mysql.user SET plugin = '' WHERE User = 'root';
FLUSH PRIVILEGES;

E: Unable to locate package mariadb-server means apt-get update did not pick up the new repository. Check /etc/apt/sources.list for a typo in the mirror path; the xenial main suffix is easy to mangle.

Job for mariadb.service failed because the control process exited with error code almost always points at a bad line in 50-server.cnf. Run sudo journalctl -xe -u mariadb and look for unknown variable; option names differ slightly between MySQL 5.7 and MariaDB 10.1, and copying a MySQL config wholesale is the usual cause.

AppArmor denials appear in /var/log/syslog if you move the data directory somewhere other than /var/lib/mysql. Add the new path to /etc/apparmor.d/usr.sbin.mysqld and reload the profile with sudo apparmor_parser -r.

Next I usually set up backups, then think about Galera.