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