How to move MySQL data directory?

It is always tricky to set the size of the /var partition while installing Linux for cPanel. Since the /home partition stores the website data, it has to be allocated the largest size. Linux Administrators normally set the size of the /var partition enough to store all the logs. But they usually forget that MySQL stores all the data in the /var/lib/mysql directory which fills up quickly as the number and size of the MySQL databases increase. Fortunately, there is a handy solution to address this issue. We simply move the MySQL data directory to a larger partition which can be either the /home partition or a new hdd. We can do that with a few easy steps.

First of all we will edit the my.cnf file which is the MySQL configuration file.

nano /etc/my.cnf

I use nano as it is my favorite editor. You can use any editor of your choice.

Add the following in the /etc/my.cnf file under the mysqld section and save the file

pid-file = /home/mysql/mysqld.pid
socket = /var/lib/mysql/mysql.sock

datadir = /home/mysql

Now, create the mysql directory under your desired partition. I create in /home partition. Assign the mysql user and group permission to the new mysql directory.

Now we are going to copy all of the data to the new partition. Notice that we do the copy twice, that is because moving large data can take some time and there are high chances that some records in some databases are updated during the copying. We can run the command to copy more than two times in case we feel that the data may have changed during the second copy.

rsync -vrplogDtH /var/lib/mysql/ /home/mysql/

Now we need to setup the mysql.sock so that it operates correctly

ln -s /home/mysql/mysql.sock /var/lib/mysql/mysql.sock
rm -rf /tmp/mysql.sock
ln -s /home/mysql/mysql.sock /tmp/mysql.sock

Ignore any error encountered while doing creating the symbolic links.

Restart mysql so it is on the new partition

killall -9 mysqld
service mysql start

If the MySQL service does not start and you encounter an error like “ERROR! MySQL manager or server PID file could not be found!”, do not panic. You will have to reboot the server to start the MySQL server. After the server is reboot, MySQL will be accessing the data from the new location and all new databases will be created in the new directory.

You can now delete the mysql data from the /var/lib/mysql directory. Make sure not to delete the directory itself as the mysql.sock file will fail to create. If you do so, then re-create the mysql directory under /var/lib/ directory.