Hello there. This is my first article!
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 on OpenBSD 6.4, and
that brings better security and -a bit better- 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 byroot 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 ...
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/
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:
That's all for today!