Configure MariaDB on OpenBSD 6

Last Updated: Fri, Aug 4, 2017
BSD Databases
Archived content

This article is outdated and may not work correctly for current operating systems or software.

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.

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.

Want to contribute?

You could earn up to $600 by adding new articles