MariaDB

MariaDB is a database that is 1:1 equivalent to MySQL. The below documentation can be used for either interchangeably.

Michael Widenius, co-creator of MySQL, forked the codebase when Oracle acquired Sun (which owned MySQL AB).

Installation

MariaDB is available on most Linux distributions but the package names and installation processes differ.

Before attempting to use MariaDB on 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

Once the database is initialized, services (i.e. systemd unit files) can be enabled.

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

Docker

A containerized MariaDB instance can be setup by running:

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

It is strongly recommended that you not enter the root password in plain text as shown above. Instead, set $MARIADB_ROOT_PASSWORD in the current environment and pass the variable into the docker environment.

$ docker run ... --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 ... --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