MariaDB move database to user home

acllinuxMySQLpermissionsUbuntu

I'm trying to move some databases to the home directory of a user but facing issues on a Ubuntu 20.04 LTS
Here's what I've done:
Created a new database for this setup. Named it "test"

  1. Create 'databases' directory in /home/admin/
  2. Change ownership of /home/admin/databases to mysql:mysql
  3. Move database directory mv /var/lib/mysql/test /home/admin/databases/test
  4. Create symlink sudo -u mysql ln -s /home/admin/databases/test /var/lib/mysql/test
  5. Restart mariadb service

Now in syslog I'm getting these errors

[ERROR] InnoDB: Operating system error number 13 in a file operation.
[ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
[ERROR] InnoDB: Cannot open datafile for read-only: './test/test.ibd' OS error: 81
[ERROR] InnoDB: Operating system error number 13 in a file operation.
[ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
[ERROR] InnoDB: Could not find a valid tablespace file for ``test`.`test``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
[Warning] InnoDB: Ignoring tablespace for `test`.`test` because it could not be opened.

I already tried giving mysql user all file permissions to /home and /home/admin (using setfacl).

getfacl /home
# file: home
# owner: root
# group: root
user::rwx
user:mysql:rwx
group::r-x
mask::rwx
other::r-x

getfacl /home/admin
#file: home/admin
# owner: root
# group: root
user::rwx
user:mysql:rwx
user:admin:rwx
group::r-x
group:admin:r-x
mask::rwx
other::r-x

There is also no apparmor rule for mysql/mariadb:

apparmor module is loaded.
15 profiles are loaded.
14 profiles are in enforce mode.
   /usr/bin/freshclam
   /usr/bin/man
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/lib/NetworkManager/nm-dhcp-helper
   /usr/lib/connman/scripts/dhclient-script
   /usr/sbin/clamd
   /usr/sbin/tcpdump
   /{,usr/}sbin/dhclient
   docker-default
   lsb_release
   man_filter
   man_groff
   nvidia_modprobe
   nvidia_modprobe//kmod
1 profiles are in complain mode.
   /usr/sbin/named
3 processes have profiles defined.
0 processes are in enforce mode.
0 processes are in complain mode.
3 processes are unconfined but have a profile defined.
   /usr/bin/freshclam (571) 
   /usr/sbin/clamd (455) 
   /usr/sbin/named (495) 

Any ideas?
Thanks in advance.

Best Answer

It took me a while, but just after posting the question I solved it.

In /var/lib/systemd/system/mariadb.service is a line

ProtectHome=true

Changed to false and it works now.

Edit: in newer ubuntu versions the file is located here /usr/lib/systemd/system/mariadb.service

Related Question