MariaDB

MariaDB is a relational database. It is designed to be 1:1 equivalent to MySQL.

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

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/.

See here for details on configuration.

Usage

The mysql(1) utility is the primary client for interfacing with a MariaDB database.

To create a new user role, try:

$ 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 new user role with remote access, replace 'localhost' with an interface (like '192.168.1.1') that is exposed to the network and that MariaDB is configured to listen on.

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

extension=mysqli
extension=pdo_mysql

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

Administration

Changing the Encoding

The utf8mb4 encoding is recommended over utf8. To convert an existing database, try:

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;

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

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';

See also

MariaDB knowledge base

mariadb(1)

MariaDB configuration

CategoryRicottone