Changing MySQL data folder

Today a client wanted to move his MySQL database to second hard disk. Moving MySQL database to its own hard disk improve IO performance as the hard disk only need to serve MySQL data, nothing OS related. This was a cpanel server, so start with stoping chkservd, so mysql or httpd will not start while i copy the files.

First created a copy of MySQL database on second hard disk, which is mounted as /backup

rsync -vrplogDtH /var/lib/mysql /backup/

Now stop chkservd, mysql and apache

service chkservd stop
service mysql stop
service httpd stop

Wait few minutes and run rsync again to update the database

rsync -vrplogDtH /var/lib/mysql /backup/

We have now MySQL database copied to /backup/mysql folder.

Rename orginal MySQL data files with

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

Now edit /etc/my.cnf

vi /etc/my.cnf

You need to add following line

datadir = /backup/mysql

To start of the file. If datadir already set, you need to change path.

Everything is finished, just restart mysql, apache and checkservd

service chkservd start
service mysql start
service httpd start

Posted in MySQL