Move MySQL Directory to Another Location

Lets say your MySQL data has fully utilized the disk space and you have another new hard disk specifically mounted for this purpose, you will need to migrate the data over to this new location. By default, MySQL will occupied following locations in the server:

Data -/var/lib/mysql
Config – /etc
Log -/var/log

We will not migrate data in /etc or /log because it usually not required much space. I will use following variables:

Server OS: CentOS 5.6 64bit
Old MySQL data: /var/lib/mysql
New MySQL data: /mnt/disk1/mysql

Warning: You MUST able to afford downtime on MySQL service to do this!

1. First of all, we need to make sure the service is stopped (if you are running cPanel, please disable MySQL monitoring under WHM > Service Manager > Untick ‘monitor’ for MySQL):

[[email protected] ~]# service mysql stop

2. Make sure there is no MySQL services running on the server:

[[email protected] ~]# ps aux | grep mysql | egrep -v 'grep'

3. Lets copy over the data directory. We are using rsync command so we can see the copy progress with estimate time remaining (this is really good for monitoring), and also preserve the permission and ownership of the MySQL directory:

[[email protected] ~]# rsync -avpP /var/lib/mysql /mnt/disk1/mysql

4. Now we need to tell MySQL to look at new data directory. Open /etc/my.cnf via text editor and change following server options:

[mysqld]
......
datadir=/mnt/disk1/mysql
socket=/mnt/disk1/mysql/mysql.sock
......

5. Now lets start back the MySQL with this new settings:

[[email protected] ~]# service mysql start

If everything are running back as expected, you can then delete the old /var/lib/mysql directory so you can save more free space.