發佈日期:

How to move the MySQL data directory

Typical DirectAdmin installations have the MySQL data directory located in /var/lib/mysql. The data directory contains the data of the databases and the size increases depending on the usage. In this article you will learn how to move the MySQL data directory to a new location. This is useful when the majority of the disk space is located in /home.

Prerequisites

  • Article is based on CentOS, other OSes may require a small change.
  • A backup of MySQL. Tip: use our backup service.
  • If you are not running the commands as root, use sudo.
  • rsync installed:
yum install rsync -y

Step 1 – Choose the new location

Our CentOS templates have no /home partition but stores it in the / partition. However, if you have a big /home partition, it is best to store the data there. Create the folder /home/mysql using the following command:

mkdir /home/mysql

Step 2 – Shut down MySQL

systemctl stop mysqld

Step 3 – Move the data

rsync -av /var/lib/mysql/ /home/mysql/
mv /var/lib/mysql /var/lib/mysql_backup

Step 4 – Configure MySQL

Open /etc/my.cnf and update (or add) the following settings in the [mysqld] section:

datadir=/home/mysql
socket=/home/mysql/server.sock

Step 5 – Start MySQL

systemctl start mysqld

Check if MySQL works. Log in as root and execute the following query:

mysql -u root -p
select @@datadir;

This should return the new database path.

Step 6 – Remove the old database path

Once MySQL has run for a few days without problems, you can delete the old data directory:

rm -rf /var/lib/mysql_backup