start mysql/mariaDB service after datadir change

Question

I installed the mariadb server and create a root user with a password with

sudo apt install mariadb-server
sudo mysql_secure_installation

Since I use a Rasperri Pi I want to swap the DB to an external hard disk. For this I have taken this tutorial

After I changed the cnf files (all under /etc/mysql/mariadb.conf/ and also the debian.cnf file under /etc/mysql) starting the mysql service fails:

 $ sudo service mysql start
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

The log says that a test file could not be created:

$systemctl status mariadb.service
Okt 30 22:56:31 raspberrypitouch systemd[1]: Starting MariaDB 10.3.31 database server...
Okt 30 22:56:31 raspberrypitouch mysqld[4694]: 2021-10-30 22:56:31 0 [Note] /usr/sbin/mysqld (mysqld 10.3.31-MariaDB-0+deb10u1) starting as process 4694 ...
Okt 30 22:56:31 raspberrypitouch mysqld[4694]: 2021-10-30 22:56:31 0 [Warning] Can't create test file /media/pi/DBDRIVE/MariaDB/raspberrypitouch.lower-test
Okt 30 22:56:31 raspberrypitouch mysqld[4694]: [101B blob data]
Okt 30 22:56:31 raspberrypitouch mysqld[4694]: 2021-10-30 22:56:31 0 [ERROR] Aborting
Okt 30 22:56:31 raspberrypitouch systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Okt 30 22:56:31 raspberrypitouch systemd[1]: mariadb.service: Failed with result 'exit-code'.
Okt 30 22:56:31 raspberrypitouch systemd[1]: Failed to start MariaDB 10.3.31 database server.

after an extensive search, I found and ruled out the following possible causes:

1. apparmour and SELinux are blocking access to the new directory -> they are not installed

   getenforce
   -bash: getenforce: command not found
   

   and

   aa-status
   
   -bash: aa-status: command not found
   

2. typo

   I have copied all paths and a typo would be a really poor reason to be busy for two days

3. Missing permissions

   I have copied with this, so the permissions should be correct: cp -R -p /var/lib/mysql/* /media/pi/DBDRIVE/MariaDB/

   parent folder

   /media/pi/DBDRIVE $ ls -l
   total 20
   drwx------ 2 mysql mysql 16384 Oct 29 12:25 lost+found
   drwxrwxrwx 6 mysql mysql  4096 Oct 30 23:07 MariaDB
   

   the db Folder itself

   /media/pi/DBDRIVE/MariaDB $ ls -l
   
   total 110632
   
   -rw-rw---- 1 mysql mysql    16384 Oct 30 15:30 aria_log.00000001
   -rw-rw---- 1 mysql mysql       52 Oct 30 15:30 aria_log_control
   -rw-r--r-- 1 root  root         0 Oct 30 13:02 debian-10.3.flag
   -rw-rw---- 1 mysql mysql      976 Oct 30 15:30 ib_buffer_pool
   -rw-rw---- 1 mysql mysql 12582912 Oct 30 15:30 ibdata1
   -rw-rw---- 1 mysql mysql 50331648 Oct 30 15:30 ib_logfile0
   -rw-rw---- 1 mysql mysql 50331648 Oct 30 13:02 ib_logfile1
   -rw-rw---- 1 mysql mysql        0 Oct 30 13:02 multi-master.info
   drwx------ 2 mysql mysql     4096 Oct 30 13:02 mysql
   drwxrwxrwx 2 mysql mysql     4096 Oct 30 17:50 mysqld
   drwx------ 2 mysql mysql     4096 Oct 30 13:02 performance_schema
   drwx------ 2 mysql mysql     4096 Oct 30 15:12 testDB
   

   the drive

   /media/pi/DBDRIVE $ ls -l
   total 20
   drwx------ 2 mysql mysql 16384 Oct 29 12:25 lost+found
   drwxrwxrwx 7 mysql mysql  4096 Oct 30 23:58 MariaDB
   

   pi

   /media/pi $ ls -l
   total 4
   drwxr-xr-x 4 mysql mysql 4096 Oct 30 16:53 DBDRIVE
   

   media

   /media $ ls -l
   total 4
   drwxr-x---+ 3 root root 4096 Oct 31 01:42 pi
   

4. wrong format for external drive

   df -Th
   Filesystem     Type      Size  Used Avail Use% Mounted on
   
   /dev/root      ext4       29G  3.8G   25G  14% /
   devtmpfs       devtmpfs  776M     0  776M   0% /dev
   tmpfs          tmpfs     937M     0  937M   0% /dev/shm
   tmpfs          tmpfs     937M  8.6M  928M   1% /run
   tmpfs          tmpfs     5.0M  4.0K  5.0M   1% /run/lock
   tmpfs          tmpfs     937M     0  937M   0% /sys/fs/cgroup
   /dev/mmcblk0p1 vfat      253M   49M  204M  20% /boot
   tmpfs          tmpfs     188M  4.0K  188M   1% /run/user/1000
   /dev/sda       ext4      916G  186M  870G   1% /media/pi/DBDRIVE
   

5. error in a cnf file

   here is basically the same case but the solution does not work for me

• my.cnf

• 50-client.cnf

• 50-mysql-clients.cnf

• 50-mysqld_safe.cnf

• 50-server.cnf

• debian.cnf

Every hint in the right direction is appreciated

Why can't I start the service ?

Answer 1

As noted in the comments, the issue was reproducible manually by running sudo -u mysql touch /media/pi/DBDRIVE/MariaDB/raspberrypitouch.lower-test. This resulted in a 'permission denied' error.

Since the question shows that the 'MariaDB' directory is fully owned by the 'mysql' user, the problem would most likely be with one of the parent directories - '/media', '/media/pi', & '/media/pi/DBDRIVE'. These directories need at least execute permissions (for all users) so that the 'mysql' user can access the '/media/pi/DBDRIVE/MariaDB/' directory.

OP has confirmed that the /media directory was missing the required permissions, and that adding them fixed the issue.

Related note from the MariaDB knowledgebase: https://mariadb.com/kb/en/what-to-do-if-mariadb-doesnt-start/#cant-create-test-file