There may be some situations where you have to move the location of MySQL, for example, if you’re out of disk space or perhaps looking to host it on another device to increase performance. Whatever the reason, moving MySQL is simple and has no impact on cPanel’s functionality.
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 “test” for checking.
# mysqladmin create test
11. Check whether the new database is created at new data directory.
# ls -d /home/mysql/test
12. After confirming everything works properly.You can remove the old data directory.
# rm -rf /var/lib/mysql