How to Change the Location of MySQL on cPanel

3/5 - (2 votes)

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 ct on cPanel’s functionality.

 

1) Create a backup

 

This should go without saying, but never mess with your data without making a backup of it. One simple way:

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

 

2) Modify my.cnf

 

In the [mysqld] section of /etc/my.cnf, add/modify this line:

 

datadir=/new/path/to/mysql

 

For example, if you are moving MySQL from /var/lib/mysql to /home2/mysql:

 

datadir=/home2/mysql

 

Don’t restart MySQL yet.

 

3) Sync the data

 

Now migrate the data to the new location using rsync. Typically you’ll want to stop MySQL, sync the data, then start it up again. If you have a lot of data and know the sync will take a while, do several syncs while the server is running, until they take less time. However, your last sync should always be done with MySQL stopped, especially if you have InnoDB tables. Here’s the command to sync with the example of MySQL being moved to /home2/mysql:

rsync -av /var/lib/mysql /home2

Now, relink the socket:

ln -sf /home2/mysql/mysql.sock /tmp

 

4) Restart MySQL

 

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

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Log in