MariaDB

MariaDB is a relational database. It is designed to be 1:1 equivalent to MySQL. Both daemons are mysqld(8); both have a userland utility CLI named mysql(1).

Installation and Setup

Most Linux and BSD distributions offer a mariadb package.

Before installing to filesystems such as Btrfs or ZFS, investigate file system-specific configurations.

Arch

Install the mariadb package and manually initialize the database:

$ mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

Note that if datadir is anything other than the default, a configuration must be set as well.

[mysqld]
datadir=/var/lib/mysql

start and enable mariadb.service.

CONVERT_TZ

Timezone tables (as used by CONVERT_TZ()) are not populated by default. If this feature is necessary, additionally run:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Debian

Install the mariadb-server package. Then run the interactive initialization script.

$ sudo mysql_secure_installation

Setting custom passwords is not encouraged, as the Debian project has automated package upgrades. Instead, consider creating a privileged admin user.

$ sudo mysql
MariaDB> GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB> FLUSH PRIVILEGES;
MariaDB> exit

Containers

Docker container images are also available for the current version. The image is available from DockerHub as docker.io/library/mariadb (or simply mariadb when using docker(1) specifically).

Try:

docker run --detach --name my-mariadb \
  -p 127.0.0.1:3306:3306 \
  --env MARIADB_ROOT_PASSWORD=my-secret-passwd \
  mariadb:latest

Best practice is to set the MARIADB_ROOT_PASSWORD environment variable instead of using the password as plaintext.

docker run --detach --name my-mariadb \
  -p 127.0.0.1:3306:3306 \
  --env MARIADB_ROOT_PASSWORD \
  mariadb:latest

It will likely be necessary to also bind mount directories for configuration files and the database file.

docker run --detach --name my-mariadb \
  -p 127.0.0.1:3306:3306 \
  --env MARIADB_ROOT_PASSWORD \
  --mount type=bind,src=path/to/conf,dst=/etx/mysql/conf.d \
  --mount type=bind,src=path/to/db,dst=/var/lib/mysql \
  mariadb:latest

Usage

Create a New User

A privileged user can create another user.

$ mysql -u root -p
MariaDB> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
MariaDB> GRANT ALL PRIVILEGES ON mydb.* TO 'monty'@'localhost';
MariaDB> FLUSH PRIVILEGES;
MariaDB> quit

To create a user with remote access, replace 'localhost' with an interface that is exposed to the network and that MariaDB is configured to listen on. This will often look like '192.168.1.1'.

Change Encoding

As documented below, the utf8mb4 encoding is recommended over utf8. To update an existing database, use the below template.

MariaDB> ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
MariaDB> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
MariaDB> ALTER TABLE table_name CHANGE column_name column_name VARCHAR(varchar_size) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

PHP

For PHP to connect to MariaDB, /etc/php/php.ini must be configured:

extension=mysqli
extension=pdo_mysql

[Pdo_mysql]
pdo_mysql.default_socket=/var/run/mysqld/mysqld.sock

Configuration

MariaDB reads (in order) /etc/my.cnf, files in /etc/my.cnf.d/ ending in cnf, and ~/.my.cnf.

For Docker containers, use a bind mount to /etc/mysql/conf.d/.

Networking

By default, MariaDB listens on 0.0.0.0 (i.e. all interfaces) and port 3306. Consider restricting to the loopback address:

[mysqld]
bind-address = 127.0.0.1

Alternatively, block networking entirely and restrict MariaDB to sockets:

[mysqld]
skip-networking

Encoding

To fully support Unicode, consider using utf8mb4 instead of utf8. That configuration looks like:

[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4

Some distributions, including the mariadb package in Arch, have this as the default.

Data Management

MariaDB writes to /var/lib/mysql. (This can be configured.)

Backups

$ mysqldump --single-transaction --flush-logs --master-data=2 --all-databases -u root -p | gzip > all_databases.sql.gz

A backup can be reloaded with:

$ zcat all_databases.sql.gz | mysql -u root -p

Docker Backups

For a containerized MariaDB instance, try:

$ docker exec some-mariadb sh -c 'exec mysqldump --all-databases -uroot -p"$MARIADB_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql
$ docker exec -i some-mariadb sh -c 'exec mysql -uroot -p"$MARIADB_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sql

Upgrades

To upgrade a database between major software releases, update the package and run:

$ mysql_upgrade -u root -p

After running successfully, restart the database daemon. If the daemon exited before successfully running the upgrade script, it should be restarted using mysqld_safe --datadir=/var/lib/mysql/.

Repairs and Optimizations

$ mysqlcheck -A --auto-repair -f -o -u root -p

Audit User Access

Users are stored in the mysql.user table, which can be queried. For example, to list all users with remote access:

MariaDB> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

CategoryRicottone