Question

Unable to To Move a MySQL Data Directory to a New Location on Ubuntu 18.04

Posted February 3, 2020 419 views
MySQL

I’m trying to setup a personal clound server on my home desktop with Ubuntu 18.04. I have an external 10TB hdd attached and would like to move the data that mysql stores on to the drive because I plan to use it as a storage/backup for multiple machines in the house. I followed this tutorial and when I try to restart mysql I get an error and after using

journalctl -xe

I get this output

-- Unit mysql.service has finished shutting down.
Feb 02 22:10:08 Skynet-Desktop systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has begun starting up.
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: mysqld: Can't change dir to '/media/Skynet/10TB/mysql/' (Errcode: 13 - Permission denied)
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: 2020-02-03T03:10:08.592734Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: 2020-02-03T03:10:08.592814Z 0 [Warning] Can't create test file /media/Skynet/10TB/mysql/Skynet-Desktop.lower-test
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: 2020-02-03T03:10:08.592835Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-0ubuntu0.18.04.1) starting as process 17283 ...
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: 2020-02-03T03:10:08.594232Z 0 [Warning] Can't create test file /media/Skynet/10TB/mysql/Skynet-Desktop.lower-test
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: 2020-02-03T03:10:08.594238Z 0 [Warning] Can't create test file /media/Skynet/10TB/mysql/Skynet-Desktop.lower-test
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: 2020-02-03T03:10:08.594454Z 0 [ERROR] failed to set datadir to /media/Skynet/10TB/mysql/
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: 2020-02-03T03:10:08.594458Z 0 [ERROR] Aborting
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: 2020-02-03T03:10:08.594468Z 0 [Note] Binlog end
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: 2020-02-03T03:10:08.594490Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
Feb 02 22:10:08 Skynet-Desktop mysqld[17281]: Initialization of mysqld failed: 0
Feb 02 22:10:08 Skynet-Desktop systemd[1]: mysql.service: Control process exited, code=exited status=1
Feb 02 22:10:08 Skynet-Desktop systemd[1]: mysql.service: Failed with result 'exit-code'.
Feb 02 22:10:08 Skynet-Desktop systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has failed.

There’s more in the error, but this is the important part. If anyone needs to see the rest let me know and I’ll paste it in, but I don’t want to waste space in this question.

edited by MattIPv4

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer

Hi @Pachuca,

Looking at the error_log it seems it’s finding a path -

/media/Skynet/10TB/

Should it be there? If yes, please make sure you’ve actually set the correct permissions, I would assume that’s the source of the problem.

Regards,
KDSys

  • In the tutorial I followed the instructions to use rsync -av in order to keep the same permissions on the files. I tried even setting sudo chmod -R 777 /media/Skynet/10TB/mysql and it still doens’t work.

    • Hi @Pachuca,

      Thank you for the confirmation, in that case the problem would be related to the ownership. The mysql folder needs to be owned by mysql:mysql.

      Regards,
      KDSys

      • did that as well, no change. Does the drive need to have the same permissions and owner or just the directory?

        • Hi @Pachuca,

          Well,in theory just the directory would need to have the same permissions however make sure the drive’s permissions actually are permissive enough for MySQL to actual change it’s directory.

          Regards,
          KDSys

Submit an Answer