Can't change data directory of mysql (Errcode: 13 - Permission denied)

Question

I have changed the data directory of mysql.

For that

I moved the folder /var/lib/mysql to a newDirectory
changed the datadir in /etc/mysql/mysql.conf.d/mysqld.cnf,
changed the apparmor config in /etc/apparmor.d/usr.sbin.mysqld:
```
/newDirectory/mysql/ r,
/newDirectory/mysql/** rwk,
```
added alias /var/lib/mysql/ -> /newDirectory/mysql/, to
```
/etc/apparmor.d/tunables/alias
```

granted ownership and permission to mysql

sudo chown -R mysql.mysql /newDirectory/mysql
sudo chmod -R 775 /newDirectory/mysql

But still I can not start mysql and get the following error:

mysqld: Can't change dir to '/newDirectory/mysql/' (Errcode: 13 - Permission denied)

What else could be the reason for that error?

EDIT:

journalctl -xe
Aug 24 13:29:30 Server audit[2288]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/2288/status" pid=2288 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=121 ouid=121
Aug 24 13:29:30 Server audit[2288]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=2288 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=121 ouid=0
Aug 24 13:29:30 Server audit[2288]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/2288/status" pid=2288 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=121 ouid=121
Aug 24 13:29:30 Server kernel: audit: type=1400 audit(1535110170.413:3083): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/2288/status" pid=2288 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=121 ouid=121
Aug 24 13:29:30 Server kernel: audit: type=1400 audit(1535110170.413:3084): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=2288 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=121 ouid=0
Aug 24 13:29:30 TIE-Server kernel: audit: type=1400 audit(1535110170.413:3085): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/2288/status" pid=2288 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=121 ouid=121
Aug 24 13:29:30 Server mysqld[2288]: mysqld: Can't change dir to '/newDirectory/mysql/' (Errcode: 13 - Permission denied)

EDIT2:

Inspired by this post https://askubuntu.com/a/953988/863451, I added in the file /etc/apparmor.d/usr.sbin.mysqld

/proc/*/status r,
/sys/devices/system/node/ r,

Unfortunately this did not change anything.

EDIT 3:

After trying to run the solution from George Udosen (https://askubuntu.com/a/1068832/863451) the error logs look like this:

**journalctl -xe**
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.673061Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.844943Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.845053Z 0 [Warning] Can't create test file /newDirectory/Server.lower-test
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.845104Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-0ubuntu0.16.04.1) starting as process 19214 ...
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.849145Z 0 [Warning] Can't create test file /newDirectory/mysql/Server.lower-test
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.849168Z 0 [Warning] Can't create test file newDirectory/mysql/Server.lower-test
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.850029Z 0 [ERROR] failed to set datadir to /newDirectory/mysql/
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.850049Z 0 [ERROR] Aborting
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.850071Z 0 [Note] Binlog end
Aug 27 11:04:33 Server mysqld[19214]: 2018-08-27T09:04:33.850133Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
Aug 27 11:04:33 Server systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Aug 27 11:05:03 Server systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has failed.
-- 
-- The result is failed.
Aug 27 11:05:03 Server systemd[1]: mysql.service: Unit entered failed state.
Aug 27 11:05:03 Server systemd[1]: mysql.service: Failed with result 'exit-code'.
Aug 27 11:05:04 Server systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Aug 27 11:05:04 Server systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has finished shutting down.
Aug 27 11:05:04 TIE-Server systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has begun starting up.
Aug 27 11:05:04 Server mysqld[19313]: mysqld: Can't change dir to '/newDirectory/mysql/' (Errcode: 13 - Permission denied)
Aug 27 11:05:04 Server mysqld[19313]: 2018-08-27T09:05:04.170717Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
Aug 27 11:05:04 Server mysqld[19313]: 2018-08-27T09:05:04.170788Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Aug 27 11:05:04 Server mysqld[19313]: 2018-08-27T09:05:04.345141Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_
Aug 27 11:05:04 Server mysqld[19313]: 2018-08-27T09:05:04.345239Z 0 [Warning] Can't create test file /newDirectory/mysql/Server.lower-test
Aug 27 11:05:04 Server mysqld[19313]: 2018-08-27T09:05:04.345288Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-0ubuntu0.16.04.1) starting as process 19313 ...
Aug 27 11:05:04 Server mysqld[19313]: 2018-08-27T09:05:04.349475Z 0 [Warning] Can't create test file /newDirectory/mysql/Server.lower-test
Aug 27 11:05:04 Server mysqld[19313]: 2018-08-27T09:05:04.349498Z 0 [Warning] Can't create test file /newDirectory/mysql/Server.lower-test
Aug 27 11:05:04 Server mysqld[19313]: 2018-08-27T09:05:04.350320Z 0 [ERROR] failed to set datadir to /newDirectory/mysql/
Aug 27 11:05:04 Server mysqld[19313]: 2018-08-27T09:05:04.350339Z 0 [ERROR] Aborting
Aug 27 11:05:04Server mysqld[19313]: 2018-08-27T09:05:04.350357Z 0 [Note] Binlog end
Aug 27 11:05:04 TIE-Server mysqld[19313]: 2018-08-27T09:05:04.350414Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
Aug 27 11:05:04 TIE-Server systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE



**systemctl status mysql.service**
    mysql.service - MySQL Community Server
       Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
       Active: activating (start-post) (Result: exit-code) since Mon 2018-08-27 11:18:01 CEST; 11s ago
      Process: 21747 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
      Process: 21730 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
     Main PID: 21747 (code=exited, status=1/FAILURE);         : 21749 (mysql-systemd-s)
        Tasks: 2
       Memory: 6.2M
          CPU: 291ms
       CGroup: /system.slice/mysql.service
               └─control
                 ├─21749 /bin/bash /usr/share/mysql/mysql-systemd-start post
                 └─21779 sleep 1

    Aug 27 11:18:01 Server systemd[1]: Starting MySQL Community Server...
    Aug 27 11:18:01 TIE-Server mysqld[21747]: mysqld: Can't change dir to 'newDirectory/mysql/' (Errcode: 13 - Permission denied)
    Aug 27 11:18:01 Server mysqld[21747]: 2018-08-27T09:18:01.671709Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
    Aug 27 11:18:01 Server mysqld[21747]: 2018-08-27T09:18:01.671780Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
    Aug 27 11:18:01 Server mysqld[21747]: 2018-08-27T09:18:01.844978Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_
    Aug 27 11:18:01 Server mysqld[21747]: 2018-08-27T09:18:01.845090Z 0 [Warning] Can't create test file /newDirectory/mysql/Server.lower-test
    Aug 27 11:18:01 Server mysqld[21747]: 2018-08-27T09:18:01.845141Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-0ubuntu0.16.04.1) starting as process 21747 ...
    Aug 27 11:18:01 Server systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

EDIT 4:

The current status of mysql apparmor profile:

sudo aa-status | grep -e "^[[:alnum:]]" -e mysql
apparmor module is loaded.
80 profiles are loaded.
41 profiles are in enforce mode.
39 profiles are in complain mode.
   /usr/bin/mysql
   /usr/sbin/mysqld
12 processes have profiles defined.
8 processes are in enforce mode.
4 processes are in complain mode.
   /usr/sbin/mysqld (8455) 
0 processes are unconfined but have a profile defined.

EDIT5:

Bind mounting did not help either: https://askubuntu.com/a/663945/863451

did you try edit /etc/mysql/mysql.conf.d/mysqld.cnf with your new data dir /newDirectory/mysql/ sir? — abu-ahmed al-khatiri, Aug 24 '18 at 11:31
Yes, I changed it from datadir = /var/lib/mysql to datadir = /newDirectory/mysql — jwarz, Aug 24 '18 at 11:37
@ abu-ahmed al-khatiri I did the suggested steps from George Udosen (https://askubuntu.com/a/1068832/863451). Unfortunately I still get errors as you can see in this EDIT3. — jwarz, Aug 27 '18 at 09:47
the systemctl status said mysql.service: Unit entered failed state. what the Unit that sir? with type nano mysql.service. — abu-ahmed al-khatiri, Aug 27 '18 at 10:09
i try with you steps very completely and nothing error. please check your directory $HOME/newDirectory/mysql or something, instead /newDirectory/mysql sir. if you said "/newDirectory" that under root directory. — abu-ahmed al-khatiri, Aug 27 '18 at 12:17
did you deleted #include <abstractions/mysql> on like your post sir. i think that the problem, set that include again. don't forget to stop mysql service before setup configuration file and restart again mysql and apparmor service sir. — abu-ahmed al-khatiri, Aug 27 '18 at 12:56
The /newDirectory/ is a path to a mounted hard drive on the server — jwarz, Aug 27 '18 at 14:36
Yes, I included the lines #include <abstractions/mysql> again. — jwarz, Aug 27 '18 at 14:50
did you sure the mounted all parents directory of /newDirectory/ is right sir?. on the link of EDIT5 is very completely, why that didn't works sir? — abu-ahmed al-khatiri, Aug 28 '18 at 07:01
I could copy the folder /var/lib/mysql to that directory with the above mentioned command. That is why I am pretty sure it is correct. — jwarz, Aug 28 '18 at 08:57

score 0 · Answer 1 · answered Aug 24 '18 at 11:52

0

I think you can try,

adding r permission the following lines to /etc/apparmor.d/usr.sbin.mysqld inside /usr/sbin/mysqld section, it so will be :

/proc/*/status r,
/sys/devices/system/node/ r,

and then you can reload apparmor using,

service apparmor reload

and start again your MySql using,

service mysql start

EDIT

i think try remove your /sys/devices/system/node/node0/meminfo r, where is on your systemctl don't have.

Hope this helps.

answered Aug 24 '18 at 11:52

abu-ahmed al-khatiri

1,739

Thanks for your response. Unfortunately it did not help. – jwarz Aug 24 '18 at 12:02
did you try sudo rsync -av /var/lib/mysql /newDirectory/mysql sir – abu-ahmed al-khatiri Aug 24 '18 at 12:04
Yes I did that. Still the same result. – jwarz Aug 27 '18 at 09:22

George Udosen · Answer 2 · 2018-08-25T13:46:38.570

WARNING: Backup /etc/apparmor.d/usr.sbin.mysqld, and your mysql database

Let me share with a much simpler way to fix this. Now I followed your steps in:

I moved the folder /var/lib/mysql to a newDirectory with the command:
```
sudo cp -r --preserve /var/lib/mysql /new/location
```
changed the datadir in /etc/mysql/mysql.conf.d/mysqld.cnf,
changed the apparmor config in /etc/apparmor.d/usr.sbin.mysqld:
```
/newDirectory/mysql/ r,
/newDirectory/mysql/** rwk,
```

Note : I didn't do step 4 as step 1 takes care of that.

Now install apparmor tools:

 sudo apt install apparmor*

This will install:

apparmor-easyprof
apparmor-notify
apparmor-profiles
apparmor-profiles-extra
apparmor-utils

The apparmor profile of mysql those not permit it to access that new directory location, here represented by the enforce status seen when you run

sudo aa-status | grep -e "^[[:alnum:]]" -e mysql

Results:

[sudo] password for georgek: 
apparmor module is loaded.
114 profiles are loaded.
72 profiles are in enforce mode.
   /usr/sbin/mysqld
42 profiles are in complain mode.
9 processes have profiles defined.
7 processes are in enforce mode.
   /usr/sbin/mysqld (2066) 
2 processes are in complain mode.
0 processes are unconfined but have a profile defined.

Now we are going to modify the current profile for mysql with the sudo aa-genprof command. This will scan the /var/log/syslog file and look for apparmor exceptions for mysql and use that to generate a new profile for it. Lets begin, run the commands from two terminals separately. Run the aa-genprof one first and the other when directed to by the aa-genprof command:

 sudo systemctl restart mysql
 sudo aa-genprof /usr/bin/mysql

You will be presented with several options to pick from in this format

[(S)can system log for AppArmor events] / (F)inish
...
(A)llow / [(D)eny] / (I)gnore / Audi(t) / Abo(r)t / (F)inish

Read the rules to be added and click these keys on your keyboard to accept or reject them as you see fit. In my case I accepted all. Then when it's done you will see this line:

(S)ave Changes / Save Selec(t)ed Profile / [(V)iew Changes] / View Changes b/w (C)lean profiles / Abo(r)t

Click S to finish. At the end my /etc/apparmor.d/usr.sbin.mysqld will be updated. Now run sudo systemctl restart mysql it should now be allowed to access that folder.

Many thanks. I followed all your steps. Still not working. Even though I tried to put mysql into the complain mode. I updated the question with the current error logs. Is there a way to check, if I did it correctly and why this is not helping? — jwarz, Aug 27 '18 at 09:25
Run sudo aa-status | grep -e "^[[:alnum:]]" -e mysql to see the current status of mysql apparmor profile — George Udosen, Aug 27 '18 at 10:37
Exactly. It works with the directory /var/lib/mysql. But it does not with the new directory. — jwarz, Aug 28 '18 at 08:55
I have done it from my end and it works please see if you can work through the strep again... — George Udosen, Aug 28 '18 at 09:03
Thank you for your support and you patience. I will try it again from scratch and let you know, if it works with your manual. — jwarz, Aug 28 '18 at 09:08

Can't change data directory of mysql (Errcode: 13 - Permission denied)

2 Answers2

WARNING: Backup /etc/apparmor.d/usr.sbin.mysqld, and your mysql database

Linked