How do I move a MySQL data directory to an external hard drive?

Question

I tried moving MySQL data directory to an external Hard disk (which has been formatted using NTFS). I followed the steps given in the answer to the question How to change MySQL data directory?

1. Stop MySQL
2. Copy MySQL data directory to new location
3. Edit the MySQL configuration file (update datadir variable).
4. Update the new path in apparmor
5. Reload apparmor
6. Start MySQL (/etc/init.d/mysql restart)

But at the 6th step, when I restart MySQL it's failing. When I look into /var/log/mysql/error.log I get the following message:

2015-05-17 12:07:05 4317 [Warning] Can't create test file /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/ neo.lower-test

/usr/sbin/mysqld: Can't change dir to '/media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/' (Errcode: 13 - Permission denied)

What should I do to fix this problem?

I guessed that it is because the mysql user does not have permissions to write to the directory on the external hard drive. So I tried to change the ownership of the entire path (chown mysql:mysql -R /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/). But it didn't get reflected.

So I tried mounting that partition again with sudo mount -t ntfs -o rw,auto,user,fmask=0022,dmask=0000 /dev/sdb1 /media/prasanth/SeagateExpansionDrive/ with no luck.

Setup Details:
Host - Ubuntu 14.04
External Drive: NTFS FileSystem, 4TB single partition
MySQL: mysqld Ver 5.6.19-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64

Answer 1

The new directory must be accessible by mysqld. In step 2, did you copy all ownership and permissions?

But probably you started mysqld as a user without permissions to the new directory? Note that it can't even do a cd: "Can't change dir".

Check for SELinux or AppArmor being in the way.

Answer 2

Try umask=0000 first (without fmask, dmask). It will be clearer whether it is selinux casing troubles or not. I could start mysql with mount options fmask=077,dmask=0077 and with uid=999,gid=1002 (mysql:mysql in my passwd; check your numbers). It is like workaround if webserver+cgi run as root. Before mysql could not start even as root. Pretty strange. Maybe a bug in mysql or mount.

Answer 3

There are several issues relating to permissions and mysqld/mariadb:

1. Basic ugo/rwx Eunuchs(tm) permissions
1.2. This should be taken care of by
1.2.1. [cp --permissions ...]
1.2.2. OR [rsync -av ...]
1.2.3. OR [chown -R ...] (as in the OP)

2. ACLs
2.1. This should be handled by Selinux [semanage fcontext -a .../restorecon -R],
2.2. OR by Apparmor (as in the OP; I don't know the syntax)

3. Systemd configuration**
3.1. Systemd imposes default restrictions about what files service processes can access  
3.2. This should be handled by [Service] directives
3.1.1. Under e.g. in file /etc/systemd/system/mariadb.service.d/something.conf
3.1.2. Such as ProtectHome=, ProtectSystem=, ReadWritePaths=

I have successfully moved MariaDB files, normally under /var/lib/mysql/, to other locations using the first two steps; I have another system where the first two steps were not enough, I am going to try it again tonight (production system, cannot just do it at any time).

Answer 4

Change the permissions of the parent directories of the external harddisks to have access by the root user. For me that worked.

i mounted on folder /media/user/dir

sudo chmod 777 media
sudo chown root:root media