Sunday, November 24, 2013

How to Change the Location of MySQL on cPanel

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

No comments:

Post a Comment