How to move your MySQL database directory

If you have a dedicated database server and the partition with the MySQL database fills up (or starts getting low), you can move your MySQL databases to a different location.

Let’s say you want to move the database to /home/mysql

1. The first step is to stop MySQL so that all your data

gets copied correctly.

$ /etc/rc.d/init.d/mysql stop

2. Create the new database directory in the new location

$ mkdir /home2/mysql

(At this point many people would use the move command (mv) instead of the copy command (cp). I ALWAYS copy the files… so that I can go back and undo my changes later!)

3. Copy the database files from the first HDD to the second HDD

$ cp -R /var/lib/mysql/ /home/mysql

4. Set the correct owner and group, permissions of the new database directory

$ chown -R mysql.mysql /home/mysql/

5. Rename your old database directory

$ mv /var/lib/mysql/ /var/lib/mysql_old

6. Create a symbolic link from the old database directory to the new one for any programs that rely on the default location

$ ln -s /home/mysql/ /var/lib/mysql

7. Set the correct owner and group on the symbolic link

$ chown mysql.mysql /var/lib/mysql

8. Edit the configuration file (/etc/my.cnf) to update the changes

Comment out the old settings and add a line for the new one as you can see below

[mysqld]

#datadir=/var/lib/mysql

datadir=/home2/mysqldata

#socket=/var/lib/mysql/mysql.sock

socket=/home2/mysqldata/mysql.sock

save my.cnf and exit your text editor

9. Restart MySQL

$ /etc/rc.d/init.d/mysql start

10. Update the AppArmor configuration file (if necessary).

11. If MySQL refuses to start look in /var/log/mysqld.log for the reason