How to move MySQL database -- the simple way

There are plenty of articles about how to move mysql to another directory or partition by telling you to mess with the my.cnf by changing all the references of datadir and socket to the new location.

Example:


[mysqld]
datadir=/new/location
socket=/new/location/mysql.sock

Don't do this... Not only will it waste your time, but likely won't work for half the server setups. Reason being there are other factors at play when you're changing things, it's not only the my.cnf that you have to worry about. For example, if you're using Ubuntu, you may be using AppArmor and you'll also have to specify the new directory in /etc/apparmor.d/usr.sbin.mysqld

Never mind all that hassle, simply create a symlink to the new mysql directory.

Here's the proper way to move the mysql database.... Let's assume, we're moving it to /home/mysql:

Stop MySQL service

service mysqld stop

Copy the entire directory to the new location

cp -a /var/lib/mysql /home/.

Rename the old directory--we can later delete it once we know everything is working fine at the new location.

mv /var/lib/mysql /var/lib/mysql-01

Create a symlink to the new mysql location

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

Now we start mysql, and everything should be working.

service mysqld start

Done!

Tags: mysql Server Linux database