MariaDB

MariaDB

Synopsis #

MariaDB is an open source relational database system that is a drop-in replacement for MySQL. It is used for structured data storage and access by web applications, scripts, and services. MariaDB provides SQL-based access to tabular data, user privilege management, replication, and ACID compliance.

MariaDB is available on OpenBSD via the ports and packages system and is the recommended way to deploy a MySQL-compatible server.

Why MySQL Is Not Included #

OpenBSD does not include Oracle MySQL in its ports tree due to licensing constraints, lack of portability, and maintenance overhead. Oracle MySQL depends on build infrastructure and libraries that are either incompatible with or unacceptable under OpenBSD’s policies.

MariaDB, on the other hand, is a community-maintained fork of MySQL and is kept portable, open, and free of licensing encumbrances. It is therefore the supported and available MySQL-compatible option on OpenBSD.

There is no support for Percona Server or Oracle-provided MySQL in OpenBSD’s package system.

Installation #

Install MariaDB from packages:

# pkg_add mariadb-server

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

The installed version is typically a recent stable release (e.g., 10.6 or newer).

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:

# mysql_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:

# rcctl enable mysqld
# rcctl start mysqld

You may now connect using the mysql client:

# mysql -u root

To stop the server:

# rcctl stop mysqld

Securing the Installation #

MariaDB does not enforce authentication on root@localhost by default. To restrict access:

  1. Run the security script:
# mysql_secure_installation

This allows you to:

  • Set a password for root@localhost
  • Remove anonymous users
  • Disallow remote root login
  • Remove the test database
  1. Restart the service:
# rcctl restart mysqld
  1. Test login:
# 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:
# mkdir -p /etc/ssl/mariadb
# openssl req -x509 -newkey rsa:4096 -keyout /etc/ssl/mariadb/server.key \
  -out /etc/ssl/mariadb/server.crt -days 365 -nodes
  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:
# rcctl restart mysqld
  1. Test from client:
$ mysql --ssl -u appuser -p

Logging and Diagnostics #

MariaDB logs to /var/mysql/ by default:

  • mysql.err: startup and runtime errors
  • mysqld.log: general queries and slow queries (if enabled)

To follow logs:

# tail -f /var/mysql/mysql.err

Query the server status:

SHOW STATUS;
SHOW VARIABLES;
Report a bug