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
with:
socket = /var/www/var/run/mysql/mysql.sock
also under [mysqld]:
socket = /var/run/mysql/mysql.sock
with:
socket = /var/www/var/run/mysql/mysql.sock
By default the line 'skip-networking' is commented. Uncomment it:
skip-networking
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
Bye
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!