Ubuntu – How to move the MySQL data directory

MySQL

I am trying to move the data directory of my MySQL database to a second disk array that I have as a mount point /array2/.

The problem I am having is I have tried everything and after I modify the location of datadir in my.cnf mysql will not start up again.

All I get is:

start: Job failed to start

Best Answer

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
Related Question