MariaDB

Synopsis #

MariaDB is a relational database server available through OpenBSD packages. It supports SQL queries, transactions, user privileges, and replication. Check application compatibility with the selected MariaDB version before replacing a MySQL server.

Why MySQL Is Not Included #

OpenBSD 8.0 provides MariaDB 12.3 packages. Oracle MySQL and Percona Server are not installation alternatives documented here. MySQL compatibility varies by version, SQL feature, and client.

Installation #

Install MariaDB from packages:

doas pkg_add mariadb-server

This installs the server daemon, utilities such as mysql and mysqldump, and the default configuration file.

For an existing database, back up the data and follow /usr/local/share/doc/pkg-readmes/mariadb-server before upgrading to a new server series. The initialization instructions below are for a new installation.

Configuration #

MariaDB uses a configuration file located at:

/etc/my.cnf

A minimal configuration example:

[mysqld]
datadir=/var/mysql
socket=/var/mysql/mysql.sock
user=_mysql
bind-address=127.0.0.1

[client]
socket=/var/mysql/mysql.sock

Create and initialize the data directory:

doas mariadb-install-db

This creates the system database under /var/mysql, sets permissions, and populates the initial schema.

Enabling the Service #

Enable and start the daemon:

doas rcctl enable mysqld
doas rcctl start mysqld

You may now connect using the mysql client:

doas mysql -u root

To stop the server:

doas rcctl stop mysqld

Securing the Installation #

Modern MariaDB installations normally authenticate local root through the Unix socket using the operating-system identity. This is authentication, not unrestricted root access. Review the initialization output and existing authentication before changing it:

  1. Run the security script:
doas mariadb-secure-installation --socket=/var/mysql/mysql.sock

Use the server socket configured in /etc/my.cnf when running the security script.

The script can review or change local root authentication, remove anonymous users, disable remote root login, and remove the test database.

  1. Restart the service:
doas rcctl restart mysqld
  1. Test login:
doas mysql -u root -p

User and Database Management #

Create a new database:

CREATE DATABASE appdb;

Create a user and grant privileges:

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'secretpass';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

List existing databases:

SHOW DATABASES;

Connect to a specific database:

mysql -u appuser -p appdb

Enabling Remote Access (Optional) #

To allow remote TCP access, modify /etc/my.cnf:

[mysqld]
bind-address=0.0.0.0

Then use GRANT to permit access:

GRANT ALL ON appdb.* TO 'appuser'@'192.0.2.10' IDENTIFIED BY 'secretpass';

Restrict port 3306 using pf(4):

pass in on $int_if proto tcp from 192.0.2.0/24 to port 3306

TLS Support (Optional) #

To enable encrypted client connections:

  1. Generate server certificate and key:
doas mkdir -p /etc/ssl/mariadb
doas openssl req -x509 -newkey rsa:4096 -keyout /etc/ssl/mariadb/server.key \
  -out /etc/ssl/mariadb/server.crt -days 365 -nodes -subj /CN=localhost
doas chown _mysql:_mysql /etc/ssl/mariadb/server.key
doas chmod 600 /etc/ssl/mariadb/server.key

This self-signed certificate is for localhost. For a public server, use its DNS name and a trusted certificate chain.

  1. Add to /etc/my.cnf:
[mysqld]
ssl-ca=/etc/ssl/mariadb/server.crt
ssl-cert=/etc/ssl/mariadb/server.crt
ssl-key=/etc/ssl/mariadb/server.key
  1. Restart the daemon:
doas rcctl restart mysqld
  1. Test over TCP with certificate verification:
mysql --protocol=TCP --host=localhost --ssl \
  --ssl-ca=/etc/ssl/mariadb/server.crt --ssl-verify-server-cert -u appuser -p

Use --protocol=TCP to test TLS; a local Unix-socket connection does not use it. The hostname must match the certificate. Check that the session reports a TLS version:

SHOW SESSION STATUS LIKE 'Ssl_version';

Logging and Diagnostics #

Log destinations depend on the configured error, general-query, and slow-query settings. Inspect the running values before selecting a file to read:

SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'general_log%';
SHOW VARIABLES LIKE 'slow_query_log%';

Query the server status:

SHOW STATUS;
SHOW VARIABLES;
Report a bug