Mysql – How to move a MySQL data directory to an external hard drive

MySQL

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

Best Answer

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".

Related Question