How to Safely Change the Location of MySQL Data Directory on cPanel/WHM Servers

I had seen many cPanel servers running out of disk space due to MySQL data directory on “/var” partition.To solve this issue you need to move your MySQL data directory to a new location.There are also other situations like moving your MySQL data’s to a new standalone database server or moving it to a separate solid-state-drive partition for increasing MySQL server performance.Whatever the reason, moving MySQL data directory is simple and has no impact on cPanel functionality.

In this article I am moving MySQL data directory to “/home” partition.You can proceed with the following steps for moving MySQL data directory:

1.Create a backup

Please make full database backup(including system tables) before moving your data directory. This action will prevent data losing in case if something goes wrong.

# tar -cvf mysql.tar /var/lib/mysql

2.Edit the my.cnf file

# vi /etc/my.cnf

Now in the mysqld section add the following. Don’t restart MySQL after adding new entry.

datadir=/home/mysql

3.Create the new MySQL data directory

# mkdir /home/mysql

4.Now migrate the data to the new location using rsync command.

# nohup rsync -avp /var/lib/mysql/ /home/mysql

The nohup will keep rsync running even when your session with the server end, the other part “

# tail -f nohup.out

Notice that you have to do the syncing process twice , because when moving large size of data can take some time to complete and the tables may have changed in between. When we run it the second time we hopefully get it so that when the switch over happens there is very little, if any, lost data. If you can afford the downtime simply shut down MySQL before running this command.If you cannot though running it twice then quickly copy/pasting the other commands is a valid substitute.

5.Typically you want to stop MySQL for syncing data completely.

# /etc/init.d/mysqld stop

6.Start the re-sync process once again to copy data’s completely.

# rsync -avp --delete /var/lib/mysql/ /home/mysql/

7. Change ownership of new created MySQL data directory to MySQL.

# chown -R mysql:mysql /home/mysql/

8. Now, re-link the socket file to /tmp:

# rm -rf /tmp/mysql.sock
# ln -sf /home/mysql/mysql.sock /tmp/mysql.sock

9. Since you already added the data directory entry to my.cnf , all you need to do is restart again and everything should be working.

# /etc/init.d/mysqld start

Check whether your MySQL logs are written at the new location (Eg: /home/mysql/hostname.err)

10. Create a sample database named “foo” for checking.

# mysqladmin create foo

11. Check whether new database is created at new data directory.

# ls -d /home/mysql/foo

12. After confirming every thing works properly.You can remove the old data directory.

# rm -rf  /var/lib/mysql

That’s it !