How do I move the MySQL data directory?

Solution 1:

Forgot about app armour.

For anyone that is interested I did the following to move the folder.

Stop the mysql server:

stop mysql

Create the new directory:

mkdir /array2/mysql

Copy over ONLY the database folders:

cp -R /var/lib/mysql /array2/mysql
cp -R /var/lib/mysql/users /array2/mysql

Backup the my.cnf file:

cp /etc/mysql/my.cnf /root/my.cnf.backup

Edit the my.cnf file:

nano /etc/mysql/my.cnf

Change all mentions of the old datadir and socket to your new location

Mine became:

datadir=/array2/mysql

socket=/array2/mysql/mysql.sock

Update the directory permissions:

chown -R mysql:mysql /array2/mysql

Rename the old directory:

mv /var/lib/mysql /var/lib/mysql-old

Create a symlink, just in case:

ln -s /array2/mysql /var/lib/mysql 

Let AppArmor know about the new datadir:

echo "alias /var/lib/mysql/ -> /your/new/datadir/," >> /etc/apparmor.d/tunables/alias

Reload the apparmor profiles

sudo /etc/init.d/apparmor reload

Then start mysql:

start mysql

Solution 2:

I found that AppArmor was the culprit by examining the syslog, and was able to successfully change the mysql data location by following this process.

Please note that, in files edited below, lines starting with + were added, and lines starting with - were removed. You should not actually type/paste the + signs when adding lines to these files.

I cloned the mysql directory to the new location:

sudo rsync -av /var/lib/mysql /new_dir

Then I edited the datadir line in /etc/mysql/my.cnf:

sudo vi /etc/mysql/my.cnf

-datadir     = /var/lib/mysql
+datadir     = /new_dir/mysql

Then I edited /etc/apparmor.d/usr.sbin.mysqld:

sudo vi /etc/apparmor.d/usr.sbin.mysqld

-  /var/lib/mysql/ r,
-  /var/lib/mysql/** rwk,
+  /new_dir/mysql/ r,
+  /new_dir/mysql/** rwk,

Then I restarted mysql.

Solution 3:

Be aware.

If you have InnoDB tables you MUST copy over the ibdata* and ib_logfile* files or you will not be able to use the the tables. You will get:

'Table 'databaseName.tableName' doesn't exist'

errors.

Run this copy command to copy over the ibdata* and ib_logfile* files.

sudo cp -p /var/lib/mysql/ib* /array2/mysql/

Solution 4:

I prefer to use mount with bind option so I avoid any further changes in Apparmor and Mysql configuration.

For example:

Suppose I want to move everything in /var/www. Lets say this dir is my dev environment and it is mounted in a different partition

1. First we need to stop mysql:

   sudo systemctl stop mysql.service
   

2. We move files (preserving permission)

   sudo rsync -av /var/lib/mysql /var/www
   

   This will generate a directory /var/www/mysql/ with all the content.

3. We remove everything in the old directory:

   sudo rm -r /var/lib/mysql/*
   

4. We mount the new directory with bind option in the old one.
   edit /etc/fstab and add this line:

   /var/www/mysql /var/lib/mysql  none  bind 0 0
   

   This will mount the /var/www/mysql in our empty dir /var/lib/mysql
   The bind option here do the magic, it will populate /var/lib/mysql with the content of /var/www/mysql so for mysql and apparmor it will be the like nothing has changed.

5. Now we do the mount:

   sudo mount -a
   

   and restart mysql.