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.
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 !
7 Replies to “How to Safely Change the Location of MySQL Data Directory on cPanel/WHM Servers”
This doesn’t quite work with latest version of MySQL (5.7 & possibly 5.6), at least not for me. You get permission errors when you try to restart MySQL.
There’s a service called apparmor – you need to add the following into /etc/apparmor.d/local/usr.sbin.mysqld :-
Where /data is the location you’ve moved your databases to.
(Got this from https://askubuntu.com/questions/758898/mysql-wont-start-after-changing-the-datadir-14-04-mysql-5-7)
Hope this helps anyone who googles and hits this page..
Ok, in my server cPanel/WHM have the same problem. Thanks for this. But in 12 step crash…when
# /etc/init.d/mysql start
Starting MySQL.180410 23:38:26 mysqld_safe Directory ‘/var/lib/mysql’ for UNIX socket file don’t exists.
ERROR! The server quit without updating PID file (/home/mysql/XXXXX.pid).
Then i create folder and asign user, and now is 100% functional
# mkdir -p /var/lib/mysql
# chown mysql:mysql /var/lib/mysql
Very thanks Vipin.
Cheers Vipin. Great article, helped me move to other folder safely.
Great article. Is it require to check mysql port before restarting the services.
No it won’t need to check the port
# cd /var/lib/
# service mysqld stop
# mkdir /home/_system/
# mv mysql /home/_system/_mysql
# ln -s /home/_system/_mysql/ mysql
# service mysqld start
–>> That’s all folks