MySql/MariaDB cannot change default datadir on Debian 9.1 server

I have a problem to move the default datadir of Maria DB to another partition, it appears to be very common but i tried everything I can without luck.

Mysql is installed as Mariadb 10.1.26 with the default debian package (apt-get install mysql-server) on a Debian 9.1 (stretch) server, mysqld -v returns mysqld 10.1.26-MariaDB-0+deb9u1

Default_mysql_datadir : /var/lib/mysql

New_mysql_datadir : /home/mysql

/var/lib/mysql is mounted to "/" (/dev/md3)

/home/mysql is mounted to "/home" (/dev/md4)

What I've tried

# systemctl stop mysql
# mv /var/lib/mysql /home

Change datadir in /etc/mysql/my.cnf

datadir = /home/mysql

Check if the rights/permissions are ok

# chown -R mysql.mysql /home/mysql

apparmor is NOT installed nor running on the system though the /etc/apparmor.d/usr.sbin.mysqld file is existing with the following rules :

/home/mysql/ r,
/home/mysql/** rwk,

I even tried to create and empty /var/lib/mysq folder refering to this bug

But when I start I always get the same error :

# systemctl start mysql
[Warning] Can't create test file /home/mysql/.lower-test
#007/usr/sbin/mysqld: Can't change dir to '/home/mysql/' (Errcode: 13 "Permission denied") 2017-09-07  0:16:59 140119808397888 [ERROR] Aborting
mariadb.service: Main process exited, code=exited, status=1/FAILURE
Failed to start MariaDB database server.
mariadb.service: Unit entered failed state.
mariadb.service: Failed with result 'exit-code'.

Any suggestion ?

Thanks

You must set ProtectHome=false in the systemd config file in order to move your datadir to the /home directory.

According to Debian policy rules, you should set this option value in a custom file like /etc/systemd/system/mariadb.service.d/YOUR_CUSTOM_FILE.conf containing:

[Service]

# Prevent accessing /home, /root and /run/user
ProtectHome=false

Then reload systemctl daemon:

systemctl daemon-reload

After that you should be able to change MariaDB datadir from /var/lib/mysql to /home/mysql.

StackExchange Database Administrators Q#185302, answer score: 5