0 
Ubuntu 18.04
mysql  Ver 14.14 Distrib 5.7.29, for Linux (x86_64)

I moved my mysql installation from the default /var/lib/mysql location to an external drive, using the following:

sudo /etc/init.d/mysql stop
sudo rsync -av /var/lib/mysql /media/E
sudo mv /var/lib/mysql /var/lib/mysql.bak

and then edited the /etc/mysql/mysql.conf.d/mysqld.cnf file to point the datadir param to its new location media/E/mysql.

Attempting to restart mysql, I got:

[....] Starting mysql (via systemctl): mysql.serviceJob for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

On the off chance, I went back and edited the log_error param within the mysql.conf.d file to point to its new location as well (as it previously pointed to the original /var/lib/mysql location). Still wouldn't start, and left with the following output from journalctl -xe:

chris@chris-x1c6:/usr/share/mysql$ journalctl -xe
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.356540Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.357247Z 0 [Warning] Can't create test file /media/E/mysql/chris-x1c6.lower-test
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.357287Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-0ubuntu0.18.04.1) starting as process 26261 ...
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359205Z 0 [Warning] Can't create test file /media/E/mysql/chris-x1c6.lower-test
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359247Z 0 [Warning] Can't create test file /media/E/mysql/chris-x1c6.lower-test
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359818Z 0 [ERROR] Could not open file '/media/E/mysql/error.log' for error logging: Permission denied
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359828Z 0 [ERROR] Aborting
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359846Z 0 [Note] Binlog end
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: Initialization of mysqld failed: 0
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359874Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
Feb 29 12:36:46 chris-x1c6 systemd[1]: mysql.service: Control process exited, code=exited status=1
Feb 29 12:36:46 chris-x1c6 systemd[1]: mysql.service: Failed with result 'exit-code'.
Feb 29 12:36:46 chris-x1c6 systemd[1]: Failed to start MySQL Community Server.

I've found other answers (here, here) that suggest tweaking AppArmor policy settings, but seems a little janky and wonder:

(1) if I did something wrong in moving the existing mysql installation;

(2) whether there's a solution preferable to editing the AppArmor policy file.

Improve this question
3
  • Just as a matter of interest, is it possible for you to go back to square 1 - i.e. move everything back into /var/lib/mysql?
    – Vérace
    Commented Feb 29, 2020 at 22:12
  • Shouldn't be an issue, but haven't yet. Is there a better way to do this if I'm in fact able to?
    – Chris
    Commented Feb 29, 2020 at 23:01
  • Just to make sure everything was copied over correctly - if you copy it back (not from backup - you did take a filesystem backup right?) and it works, at least that proves that it's not something missing (or added) during that part of the procedure. Backtracking is a good way to find errors.
    – Vérace
    Commented Feb 29, 2020 at 23:07

1 Answer 1

Reset to default
0

After a bit of trial and error, was able to get it to work using this approach plus modifying the log_error variable within the mysqld.cnf file to point to the new location as well.

Adding a line to deal with AppArmor was required (it's included in the linked tutorial), but otherwise works without issue.

Improve this answer
1
  • Maybe you'd like to include the relevant bits from the linked page? Link-only (or link-main) answers are frowned upon here - bit rot &c. Just be aware that when you run stuff from a USB stick/drive, changing machines can be a problem as the host may well assign the stick a different letter/mount point! p.s. welcome to the forum and congrats on solving your own problem! :-)
    – Vérace
    Commented Mar 1, 2020 at 3:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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