1

This question already has an answer here:

I have changed the data directory of mysql.

For that

  1. I moved the folder /var/lib/mysql to a newDirectory

  2. changed the datadir in /etc/mysql/mysql.conf.d/mysqld.cnf,

  3. changed the apparmor config in /etc/apparmor.d/usr.sbin.mysqld:

    /newDirectory/mysql/ r,
    /newDirectory/mysql/** rwk,
    
  4. added alias /var/lib/mysql/ -> /newDirectory/mysql/, to

    /etc/apparmor.d/tunables/alias
    
  5. 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

marked as duplicate by George Udosen, N0rbert, Eric Carvalho, guiverc, Uri Herrera Sep 9 '18 at 23:45

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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 – jstuhh Aug 24 '18 at 11:37
  • please check my update answered sir – abu-ahmed al-khatiri Aug 24 '18 at 11:59
  • @jstuhh on your EDIT3, what you do to do sir? – abu-ahmed al-khatiri Aug 27 '18 at 9:20
  • @ abu-ahmed al-khatiri I did the suggested steps from George Udosen (askubuntu.com/a/1068832/863451). Unfortunately I still get errors as you can see in this EDIT3. – jstuhh Aug 27 '18 at 9:47
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.

  • Thanks for your response. Unfortunately it did not help. – jstuhh 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. – jstuhh Aug 27 '18 at 9:22
0

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:

  1. I moved the folder /var/lib/mysql to a newDirectory with the command:

    sudo cp -r --preserve /var/lib/mysql /new/location
    
  2. changed the datadir in /etc/mysql/mysql.conf.d/mysqld.cnf,

  3. 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? – jstuhh Aug 27 '18 at 9: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
  • In EDIT 4 you can see the current status. – jstuhh Aug 27 '18 at 14:33
  • So it's complain mode then. – George Udosen Aug 27 '18 at 17:15
  • Exactly. It works with the directory /var/lib/mysql. But it does not with the new directory. – jstuhh Aug 28 '18 at 8:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.