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

[sachinj]

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

Many cPanel servers running out of disk space due to MySQL data directory on “/var” partition. to resolve the issue you need to move your MySQL data directory to a new location. here 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.

moving MySQL data directory is simple and has no impact on cPanel functionality.

You can proceed with the following steps for moving MySQL data directory:

1.Create a backup

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

Code: [Select]

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

2.Edit the my.cnf file

Code: [Select]

# vim /etc/my.cnf

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

Code: [Select]

datadir=/home/mysql
3.Create the new MySQL data directory

Code: [Select]

# mkdir /home/mysql

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

Code: [Select]

# 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 “

Code: [Select]

# tail -f nohup.out

Note: 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.

Code: [Select]

# /etc/init.d/mysqld stop

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

Code: [Select]

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

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

Code: [Select]

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

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

Code: [Select]

# 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.

Code: [Select]

# /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 “testdb” for checking.

Code: [Select]

# mysqladmin create testdb

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

Code: [Select]

# ls -d /home/mysql/testdb

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

Code: [Select]

# rm -rf  /var/lib/mysql
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
That's it.