Configure MariaDB on OpenBSD 6

In this article, I'll show you how to install MariaDB on OpenBSD 6 and configure it to be accessible to a chrooted web server (Apache or Nginx). You will also learn how to make make MariaDB available to non-chrooted applications.

Note: All commands in this tutorial need to be executed with root privilege in a command line window.

Steps were tested on OpenBSD 6.1 AMD64 installation.

Preparations

Make sure the environment variable PKG_PATH is configured.

echo $PKG_PATH

If it's empty, configure it with the following command:

export PKG_PATH=http://ftp3.usa.OpenBSD.org/pub/OpenBSD/$(uname -r)/packages/$(uname -m)/

Step 1: Install MariaDB

pkg_add -v mariadb-server

Step 2: Enable MariaDB to start at system boot

rcctl enable mysqld

Step 3: Start MariaDB

/usr/local/bin/mysql_install_db

Step 4: Performance tweak (optional)

If your MariaDB is expected to serve a large amount of traffic, append the following lines to the file /etc/login.conf:

 mysqld:\
   :openfiles-cur=1024:\
    :openfiles-max=2048:\
    :tc=daemon:

Run the following command to generate the binary format of the login.conf file.

cap_mkdb /etc/login.conf

Step 5: Secure the installation

Run the secure installation command to make MariaDB even harder to compromise.

/usr/local/bin/mysql_secure_installation

Answer questions as prompted to set the root password, remove the test database and disable the remote root login. The initial password for root is blank.

Step 6: Change MariaDB socket location

Change the MariaDB socket location so that it's accessible from a chrooted environment.

The default httpd in base or nginx in port are chrooted to /var/www, so let's put the socket in /var/www/var/run/mysql/mysql.sock, so that it appears to be /var/run/mysql/mysql.sock for the chrooted web server.

Create the directory:

install -d -m 0711 -o _mysql -g _mysql /var/www/var/run/mysql

Edit /etc/my.cnf. In both the [client] section, and the [mysqld] section, change the line that reads:

socket = /var/run/mysql/mysql.sock

To:

socket = /var/www/var/run/mysql/mysql.sock

Step 7: Start MariaDB manually

 rcctl start mysqld

Step 8: Test MariaDB

Now test MariaDB by running the following command.

  mysql -uroot -p

Check if you can login with the password you set in the previous step.

Step 9: Create soft links

We need to create soft links for the mysql folder to make MariaDB accessible for non-chrooted applications. After having changed mysql.sock to the /var/www/var/run/mysql directory, some non-chrooted applications may not able to find it. If that's the case, we can resolve it by creating a soft link to /var/www/var/run/mysql directory in /var/run/ (the default mysql.sock location).

 ln -s /var/www/var/run/mysql /var/run/

Now to make this change permanent. When OpenBSD starts, it clears everything under the /var/run directory, so we need to re-create the soft link at every reboot. We can do this by putting the command from step 9 in a file called /etc/rc.local. This file will be executed by the startup script at the end of the startup process. The /etc/rc.local file does not exist by default. You can create one by copying from the example file shipped with the OpenBSD, and appending the soft link to it.

cp /etc/examples/rc.local /etc/
echo "ln -s /var/www/var/run/mysql /var/run/" >> /etc/rc.local

Conclusion

That's it. Your MariaDB server is now good to go.

Another note regarding MariaDB is the max_allowed_packet setting in /etc/my.cnf. The default value is 1M for [mysqld] section. If you encounter problems when importing a dump file from another server, try increasing it to 16M or 64M, and then restarting MariaDB.