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
save my.cnf and exit your text editor
9. Check on the new mysql directory path /home/mysql/mysql the “host.MDI” files.
10. Check the file permissions
11. Restart MySQL
$ /etc/rc.d/init.d/mysql start
12. Update the AppArmor configuration file (if necessary).
13. If MySQL refuses to start look at your mysql error log for the reason
It’s works for me 🙂