= MariaDB =

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

<<TableOfContents>>

----


== 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
}}}

[[Linux/Systemd|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 [[Docker/Hub|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 [[Docker/BindMounts|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 [[Docker/BindMounts|bind mount]] to `/etc/mysql/conf.d/`.

See [[MariaDB/Configuration|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 ==

[[https://mariadb.com/kb/en/documentation/|MariaDB knowledge base]]

[[https://man.archlinux.org/man/mariadb.1|mariadb(1)]]

[[MariaDB/Configuration|MariaDB configuration]]



----
CategoryRicottone