By Marcel van der Veer
March 2024

Published in Tech Tips

More on Debian, MariaDB, MySQL

Frequent visitors of my blog will have noticed that I like to repurpose old computers and fix electronics. It is a small contribution to controlling the waste problem - stuff is not obsolete while it is not beyond repair.

Recently I turned an old Windows 8 machine, not suited for a Windows upgrade, into a server that I called WALL-E. On a home network it serves files through Samba and printers through CUPS and Gutenprint, extending the life of perfectly good printers for which no Windows 11 drivers will be made available, and that would otherwise have ended on the scrap heap.

WALL-E also is a LAMP server, hosting a few legacy websites that should remain available locally for reference purposes. Installing the LAMP stack is well documented on the internet, and I will not address that. Here I want to describe a few issues that actually required RTFM (Reading The Formal Manual).

Some of those websites are Wordpress sites, and those need a database. However, WALL-E's /var partition where websites and databases sit, is a bit small whereas I have plenty of space on /home. The solution to migrate websites and databases to /home appears obvious enough. This is not as trivial as it seems on first sight, and I had to investigate a little.

Moving web pages

I produced a directory /home/www to host the web pages. Apache however does not accept that out of the box and will produce "Forbidden 403" notifications. We must give directory /home/www a same status as /var/www/. Do not manage that in the top Apache configuration file as it may be silently superseded at the next update. Instead, I inserted in every virtual server configuration file a directive like that for /var/www/.

# systemctl stop apache2
# vi /etc/apache2/sites-available/example.lan.conf
<VirtualHost *:80>
        ServerName example.lan
        DocumentRoot /home/www/example.lan/htdocs
        <Directory /home/www/>
                Options Indexes FollowSymLinks
                AllowOverride None
                Require all granted
# systemctl start apache2
# systemctl status apache2

Moving databases

Debian sports MariaDB, which is a good drop-in substitute for MySQL. These notes pertain to Debian 12 with stock MariaDB installed from the Debian repository. Other systems may require a different approach. Please read these notes entirely, before meddling with your system. You best do this after installing MariaDB and before creating databases. Otherwise, moving databases is not without risk so please do make sure you have an up-to-date copy of your databases before you touch anything.

First we stop MariaDB. Then we create a directory on /home and make sure that user mysql owns it. When that is done, we migrate the data.

# systemctl stop mariadb
# mkdir /home/databases
# chown mysql:mysql /home/databases
# cp -rpv /var/lib/mysql/* /home/databases

Now we must change the MariaDB default directory. The top configuration file may be replaced at the next MariaDB update, and we would experience weird problems. Hence I inserted the new default in the local configuration files, in the [mysqld] block. This is the Debian way, please check the documentation for your particular system.

# vi /etc/mysql/mariadb.conf.d/50-server.cnf

Here most tutorials suggest you restart MariaDB and continue with your daily chores. However, restarting MariaDB resulted in failure.

# systemctl start mariadb
[Warning] Can't create test file /home/databases/XXXX.lower-test
[ERROR] Aborting
systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
systemd[1]: mariadb.service: Failed with result 'exit-code'.
systemd[1]: Failed to start MariaDB 10.11.6 database server.

At first I suspected a straightforward permission issue, but that appeared not to be the case. I found out that for security reasons, without written consent MariaDB refuses certain file systems, including /home. We consent in writing by dropping a short file at the right spot in /etc/systemd/system. This way, our consent is not overwritten at the next update of MariaDB. Again, this is the Debian way. Your distribution may require another approach.

# mkdir /etc/systemd/system/mariadb.service.d
# vi /etc/systemd/system/mariadb.service.d/wall-e.conf

Now we restart daemons and MariaDB, which should be uneventful now.

# systemctl daemon-reload
# systemctl start mariadb

You should test that the new configuration indeed works. As a minimum, check that MariaDB runs, and request the data directory.

# systemctl status mariadb
● mariadb.service - MariaDB 10.11.6 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
     Active: active (running) since ...
# mariadb -u root -e "select @@datadir;"
| @@datadir        |
| /home/databases/ |

All blog posts