|
Size: 6043
Comment:
|
Size: 5481
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 3: | Line 3: |
| '''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). |
'''MariaDB''' is a relational database. It is designed to be 1:1 equivalent to [[MySQL]]. |
| Line 12: | Line 10: |
| == 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. ---- |
== 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. |
| Line 37: | Line 33: |
| Once the database is initialized, services (i.e. `systemd` unit files) can be enabled. | [[Linux/Systemd|start and enable]] `mariadb.service`. |
| Line 72: | Line 68: |
| === 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 }}} |
=== 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/BindMount|bind mount]] to `/etc/mysql/conf.d/`. See [[MariaDB/Configuration|here]] for details on configuration. |
| Line 101: | Line 119: |
| === Create a New User === A privileged user can create another user. |
The `mysql(1)` utility is the primary client for interfacing with a MariaDB database. To create a new user role, try: |
| Line 113: | Line 131: |
| 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. |
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: |
| Line 129: | Line 163: |
| === 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.) |
|
| Line 210: | Line 174: |
==== Docker Backups ==== |
|
| Line 221: | Line 183: |
| Line 249: | Line 212: |
| ---- == See also == [[https://mariadb.com/kb/en/documentation/|MariaDB knowledge base]] [[MariaDB/Configuration|MariaDB configuration]] |
MariaDB
MariaDB is a relational database. It is designed to be 1:1 equivalent to MySQL.
Contents
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';