Moving Mysql data directory

Question

I am having trouble moving my mysql data directory to a new location. I am running Linux Mint 17, MySQL 5.7.19. I have tried the instructions in these two locations:

How to change MySQL data directory?

This answer: https://stackoverflow.com/a/10209282/7850358

and this guide: https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04

I believe I am having a similar problem as this question: https://dba.stackexchange.com/questions/101732/how-do-i-move-a-mysql-data-directory-to-an-external-hard-drive

I rsync'ed the data from /var/lib/mysql to /media/mike/DataBase. The directory exists, and is properly specified in the /etc/mysql/mysql.conf.d/mysqld.cnf, and I created an alias in /etc/apparmor.d/tunables/alias. I made sure the permissions on the folder were correct: drwx------ 11 mysql mysql 4096 Jan 21 16:52 mysql.

I restart apparmor, no problems. I restart the mysql server - I get this:

Thudbucket mike # sudo systemctl start mysql
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
Thudbucket mike # 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 Sun 2018-01-21 17:10:34 EST; 12s ago
  Process: 22295 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 22288 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 22295 (code=exited, status=1/FAILURE);         : 22296 (mysql-systemd-s)
   CGroup: /system.slice/mysql.service
           └─control
             ├─22296 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─22332 sleep 1

Jan 21 17:10:34 Thudbucket mysqld[22295]: 2018-01-21T22:10:34.308875Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
Jan 21 17:10:34 Thudbucket mysqld[22295]: 2018-01-21T22:10:34.308930Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Jan 21 17:10:34 Thudbucket mysqld[22295]: 2018-01-21T22:10:34.453972Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server opti
Jan 21 17:10:34 Thudbucket mysqld[22295]: 2018-01-21T22:10:34.454033Z 0 [Warning] Can't create test file /media/mike/DataBase/mysql/Thudbucket.lower-test
Jan 21 17:10:34 Thudbucket mysqld[22295]: 2018-01-21T22:10:34.454054Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.19-0ubuntu0.16.04.1) starting as process 22295 ...
Jan 21 17:10:34 Thudbucket mysqld[22295]: 2018-01-21T22:10:34.455725Z 0 [Warning] Can't create test file /media/mike/DataBase/mysql/Thudbucket.lower-test
Jan 21 17:10:34 Thudbucket mysqld[22295]: 2018-01-21T22:10:34.455735Z 0 [Warning] Can't create test file /media/mike/DataBase/mysql/Thudbucket.lower-test
Jan 21 17:10:34 Thudbucket mysqld[22295]: 2018-01-21T22:10:34.455760Z 0 [ERROR] failed to set datadir to /media/mike/DataBase/mysql/
Jan 21 17:10:34 Thudbucket mysqld[22295]: 2018-01-21T22:10:34.455774Z 0 [ERROR] Aborting
Jan 21 17:10:34 Thudbucket systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

this may sound stupid, but did you restart the machine? I was struggling with permissions recently and they magically disappeared after I restarted. It might have something to do with the machine still pointing to old files in memory. — Ibu, Jan 21 '18 at 22:58
Did you copy the directory while the server was running? that would result in a corrupted data directory. — Evert, Jan 22 '18 at 03:52
@Evert I had shut it down prior to copying. I don't think it was corrupted, because when I mounted the new location at /var/lib/mysql, it was functioned perfectly. — Manny Wilson, Jan 22 '18 at 12:36

Manny Wilson · Answer 1 · 2018-01-23T21:45:28.883

I've tried just about everything I could find online. I even read the manual, for a change. For some reason, moving the data directory to any location but /var/lib/mysql results in errors for me.

I ultimately had better luck with this hack: https://askubuntu.com/a/663945/785778

## copy with permissions intact:
rsync -avzh /var/lib/mysql /path/to/new/place

## back up original
mv /var/lib/mysql /var/lib/_mysql 

## create a new empty directory in place of old:
mkdir /var/lib/mysql 

## bind mount the new location to the old:
mount -B /path/to/new/place /var/lib/mysql

After restarting the mysql, everything worked fine.

Ironically - symbolic linking did not work

EDIT

This appears to be a bug, but the provided solution doesn't resolve the issue for me:

https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1574782 https://forums.mysql.com/read.php?11,653198,653198#msg-653198

The bug works like this: MySQL at startup looks for the presence of the /var/lib/mysql/ and /var/lib/mysql/mysql directories. The solution provided to leave the /var/lib/mysql and /var/lib/mysql/mysql directories but remove the contents and add the new datadir path to the apparmor configuration file (/etc/apparmor.d/usr.sbin.mysqld) and reload it (/etc/init.d/apparmor reload).

But to repeat, while it appears I have the same issue (MySQL keeps looking for the datadir at /var/lib/mysql), the only solution that worked for me was to mount the data directory at /var/lib/mysql/.

Have the same question so I appreciate your effort. I have done this with MySQL before (don't remember the version though) so I was surprised that it didnt work this time with 5.7. If you have any updates on the issue I would like to hear about them. Not fond of the "mount" solution which seems lika a hack. — Peter Andersson, Oct 27 '18 at 08:14
Nope - I still use the hack. It doesn't work to restart mysql either. It functions otherwise and it works for my purposes. — Manny Wilson, Oct 29 '18 at 00:06

score -1 · Answer 2 · answered Feb 12 '21 at 19:03

Return this error

2021-02-12T19:02:55.910704Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2021-02-12T19:02:55.910710Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2021-02-12T19:02:55.910731Z 0 [ERROR] Aborting

Moving Mysql data directory

2 Answers2