0

Recently I installed MySQL, version 8.4.3, on my Ubuntu 22.04.5 OS.

I then changed the datadir from /var/lib/mysql to new location /media/cat/hekla/mysql, following the approach described in Ergest Basha's answer to the question Error after changing/moving mysql datadir in ubuntu sys. Restarting MySQL however fails, and systemctl status mysql.service tells me:

 × mysql.service - MySQL Community Server
  Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
  Active: failed (Result: exit-code) since Wed 2024-11-06 11:18:27 CET; 2min 31s ago
    Docs: man:mysqld(8)
          http://dev.mysql.com/doc/refman/en/using-systemd.html
 Process: 11628 ExecStartPre=/usr/share/mysql-8.4/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Process: 11667 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
Main PID: 11667 (code=exited, status=1/FAILURE)
  Status: "Server shutdown complete (with return value = 1)"
   Error: 13 (Permission denied)
     CPU: 292ms

nov 06 11:18:27 golem mysqld[11667]: 2024-11-06T10:18:27.019616Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.3) starting as process 11667
nov 06 11:18:27 golem mysqld[11667]: 2024-11-06T10:18:27.021198Z 0 [Warning] [MY-010091] [Server] Can't create test file /media/cat/hekla/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
nov 06 11:18:27 golem mysqld[11667]: 2024-11-06T10:18:27.021202Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /media/cat/hekla/mysql/ is case insensitive
nov 06 11:18:27 golem mysqld[11667]: 2024-11-06T10:18:27.021216Z 0 [ERROR] [MY-013276] [Server] Failed to set datadir to '/media/cat/hekla/mysql/' (OS errno: 13 - Permission denied)
nov 06 11:18:27 golem mysqld[11667]: 2024-11-06T10:18:27.021275Z 0 [ERROR] [MY-010119] [Server] Aborting
nov 06 11:18:27 golem mysqld[11667]: 2024-11-06T10:18:27.021685Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.3)  MySQL Community Server - GPL.
nov 06 11:18:27 golem mysqld[11667]: 2024-11-06T10:18:27.021689Z 0 [System] [MY-015016] [Server] MySQL Server - end.
nov 06 11:18:27 golem systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
nov 06 11:18:27 golem systemd[1]: mysql.service: Failed with result 'exit-code'.
nov 06 11:18:27 golem systemd[1]: Failed to start MySQL Community Server.

Interestingly, I made a backup copy /var/lib/mysql.bak of /var/lib/mysql, and when I change the datadir to this backup directory then it does work!

Any ideas what the problem can be, and how to solve it? Any other things that could require adjustment, perhaps involving location /var/lib/ considering that the backup directory sitting next to the original does work?

The content of /etc/apparmor.d/usr.sbin.mysqld is:

include <tunables/global>

/usr/sbin/mysqld {
 #include <abstractions/base>
 #include <abstractions/nameservice>
 #include <abstractions/user-tmp>
 #include <abstractions/mysql>
 #include <abstractions/winbind>


Allow system resource access


/sys/devices/system/cpu/ r,
  /sys/devices/system/node/ r,
  /sys/devices/system/node/** r,
  /proc/*/status r,
  capability sys_resource,
  capability dac_override,
  capability setuid,
  capability setgid,
  capability sys_nice,


Allow network access


network tcp,


/etc/hosts.allow r,
 /etc/hosts.deny r,


Allow config access


/etc/mysql/** r,


Allow pid, socket, socket lock and other file access


/run/mysqld/* rw,
 /var/run/mysqld/* rw,


Allow systemd notify messages


/{,var/}run/systemd/notify w,


Allow execution of server binary


/usr/sbin/mysqld mr,
 /usr/sbin/mysqld-debug mr,


Allow plugin access


/usr/lib/mysql/plugin/ r,
 /usr/lib/mysql/plugin/.so mr,


Allow error msg and charset access


/usr/share/mysql/ r,
 /usr/share/mysql/** r,
 /usr/share/mysql-8.4/ r,
 /usr/share/mysql-8.4/** r,


Allow data dir access


/var/lib/mysql/ r,
 /var/lib/mysql/** rwk,


/var/lib/mysql.bak/ r,


/var/lib/mysql.bak/** rwk,


/media/cat/hekla/mysql/ r,


/media/cat/hekla/mysql/** rwk,


Allow data files dir access


/var/lib/mysql-files/ r,
 /var/lib/mysql-files/** rwk,


Allow keyring dir access


/var/lib/mysql-keyring/ r,
 /var/lib/mysql-keyring/** rwk,


Allow log file access


/var/log/mysql/ r,
 /var/log/mysql/** rw,


Allow access to openssl config


/etc/ssl/openssl.cnf r,


Site-specific additions and overrides. See local/README for details.


#include <local/usr.sbin.mysqld>
}
Bart
  • 101
  • 2

2 Answers2

1

Things to check:

  1. Who is the owner of these directories?
  2. What are permissions on these directories?

Both can be obtained by running ls -ld on them.
Personally, I recommend also using ls -ldn because Unix permissions revolve around uids (and gids) and not around usernames and I've worked in places where they do "strange things" with usernames and uids!

Remember, the database does not run under your account.
Just because you can see / update these directories doesn't mean that your database process can!

Phill W.
  • 9,889
  • 1
  • 12
  • 24
0

So I managed to make it work. In an answer on Stack Overflow it was said that all parent directories of my new datadir /media/cat/hekla/mysql should have x (execute) permissions, which wasn't the case yet: it was for

drwxr-xr-x 9 cat cat 4096 nov 15 16:08 hekla

and

drwxr-xr-x 3 root root 4096 jun 6 2022 media

but not for

drwxr-x---+ 4 root root 4096 nov 15 16:03 cat

By applying sudo chmod o+x cat/ I changed that to

drwxr-x--x+ 4 root root 4096 nov 15 16:03 cat

This as an extra step just before restarting apparmor and MySQL, no error now.

Bart
  • 101
  • 2