Configuring MariaDB/MySQL server to only use UNIX socket – OpenBSD 6.4

by Özgür Kazanççı · November 25th, 2018 · My OpenBSD Tutorials

Hello there.

Today, I’m going to explain configuring MariaDB/MySQL server to only use UNIX socket – ignoring the TCP networking. This will prevent MariaDB/MySQL from using any TCP/IP communication, setting only Unix socket locally&connecting through it, and that brings better security and much better connection performance.

Unix sockets are faux-files, so they’re accessible ONLY from/within the local server.

I assume you’ve already installed Maria DB/MySQL server on your OpenBSD system, usually by pkg_add.

Steps: Create by root the following directory schema in one-go, that’ll be used for the MySQL socket file:

kript0s# mkdir -p /var/www/var/run/mysql

And then, stop the sql service by: rcctl stop mysqld command, and replace the “socket” lines under both [client] and [mysqld] tag in your SQL server configuration (my.cnf) file. This is usually located in /etc/my.cnf on OpenBSD.

kript0s# nano /etc/my.cnf

Replace the 'socket' line under [client] tag:
socket	= /var/run/mysql/mysql.sock
socket	= /var/www/var/run/mysql/mysql.sock

also under [mysqld]:
socket  = /var/run/mysql/mysql.sock
socket  = /var/www/var/run/mysql/mysql.sock

By default the line 'skip-networking' is commented. Uncomment it:

The reason why I replace ‘/var/run/mysql‘ path with ‘/var/www/var/run/mysql‘ is that, OpenBSD’s httpd (or nginx) is chrooted&jailed under /var/www directory. Save the changes and exit. Now start the sql service, so that it’ll create your socket file, by the command rcctl start mysqld.

I noticed that MariaDB/MySQL server did not start. Wanted to see what’s going on..

kript0s# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/www/var/run/mysql/mysql.sock' (2 "No such file or directory")

Checking if MariaDB/MySQL process is running:
kript0s# ps auwx | grep mysql
root	 54604  0.0  0.0   296  1248 p0  S+p	1:41PM	0:00.01 grep mysql

There is no such process. 
Going deeper and reading the MariaDB's/MySQL's error log file (located in /var/mysql/SERVERNAME.err):
kript0s# cat /var/mysql/kript0s.err
181109 13:40:32 [ERROR] Can't start server : Bind on unix socket: Permission denied
181109 13:40:32 [ERROR] Do you already have another mysqld server running on socket: /var/www/var/run/mysql/mysql.sock ?
181109 13:40:32 [ERROR] Aborting

Actually, there IS such directory structure: /var/www/var/run/mysql as we created earlier, but it seems mysqld couldn’t able to create mysql.sock in that folder. See the line: Can’t start server : Bind on unix socket: Permission denied

The problem is, the folder: /var/www/var/run/mysql has owner&group as root and daemon. MariaDB/Mysqld runs with _mysql user, and with such user it couldn’t create the socket file ‘mysql.sock’ inside that folder which is owned by root. I change the owner&group of that directory from root to _mysql:

kript0s# cd /var/www/var/run/

kript0s# ls -Flha
total 12
drwxr-xr-x  3 root  daemon   512B Nov  9 13:39 ./
drwxr-xr-x  3 root  daemon   512B Nov  9 13:28 ../
drwxr-xr-x  2 root  daemon   512B Nov  9 13:39 mysql/

kript0s# chown -R _mysql:_mysql mysql/

kript0s# ls -Flha
total 12
drwxr-xr-x  3 root	daemon   512B Nov  9 13:39 ./
drwxr-xr-x  3 root	daemon   512B Nov  9 13:28 ../
drwxr-xr-x  2 _mysql  _mysql   512B Nov  9 13:39 mysql/

Rebooted again. And afterwards, first thing I did was checking if it’s running:

kript0s# ps auwx | grep mysql
root	 99774  0.0  0.0   304  1232 p0  S+p	1:48PM	0:00.01 grep mysql
root	 24845  0.0  0.0   812   956 C0- Sp	 1:48PM	0:00.06 /bin/sh /usr/local/bin/mysqld_safe
_mysql   98984  0.6  2.6 751024 110680 C0- S	  1:48PM	0:00.53 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/mysql --plugin-dir=/usr/local/lib/mysql/plugin --user=_mysql --log-error=/var/mysql/kript0s.err

The process is running. And yes, the socket file was created, it’s there now!

kript0s# ls -al /var/www/var/run/mysql/
total 8
drwxr-xr-x  2 _mysql  _mysql  512 Nov  9 13:48 .
drwxr-xr-x  3 root	daemon  512 Nov  9 13:39 ..
srwxrwxrwx  1 _mysql  _mysql	0 Nov  9 13:48 mysql.sock

So it seems MariaDB/MySQL now works only using UNIX socket! Let’s test it.

kript0s# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.36-MariaDB OpenBSD port: mariadb-server-10.0.36p0v1
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> exit

And you might wish to be sure, that no more TCP MySQL port listening among binding ports on the system. Check with: netstat -anf inet (For IPv4) and netstat -anf inet6 (For IPv6) commands.

Post Scriptum: If you use any CMS (Content Management System) such as WordPress working with your MariaDB/MySQL server on your system, please configure it according MySQL socket settings, pointing the socket file.

An example for WordPress configuration would be: in your WordPress config.php file, find the definition called DB_HOST and set it as:
define('DB_HOST', 'localhost:/var/run/mysql/mysql.sock');

Please note that, in WordPress’s config.php above, the line containing the absolute path ‘/var/run/mysql/mysql.sock‘ is actually:
/var/www/var/run/mysql/mysql.sock‘, as under chroot-ed Nginx environment on OpenBSD, websites are chrooted into /var/www/.

That’s all for today!

Leave a Reply

Your email address will not be published. Required fields are marked *