How to Change MySQL Data Directory to New Location on CentOS 7

Databases are constantly growing and it is likely to outgrow your / partition at some point, especially these days in the age of “the cloud” where you may be limited to a small / partition, but can pay to add bulk storage, or attach more disks. Alternatively, you may wish to move your database to a separate location because you want to configure some sort of snapshotting technique, such as with ZFS or LVM.

In this tutorial I will show you how you can change or move your data directory to new location on CentOS 7.

Step #1: Move MySQL Data Directory

Before moving MySQL’s data directory lets see the current location using mysql command line as shown below.

# mysql -u root -p

mysql> select @@datadir;

+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

In the above output in can see the MySQL default data direcror /var/lib/mysql/. So we are going to move this directory. Once you’ve confirmed this, type exit and press “ENTER” to leave the monitor:

mysql> exit

Before changing the data directory you will need to shut down MySQL service.

# systemctl stop mysqld

systemctl doesn’t display the outcome of all service management commands, so if you want to be sure you’ve succeeded, use the following command:

# systemctl status mysqld
Jun 17 11:24:20 centos-512mb-nyc1-01 systemd[1]: Stopped MySQL Community Server.

Now MySQL service is shut down lets start copy the existing data directory to new location with rsync command using with –a option to preserves the permissions and other directory properties while-v provides verbose output so you can follow the progress.

# rsync -av /var/lib/mysql /mnt/data/

Once the rsync is complete, rename the current folder with a .bak extension and keep it until we’ve confirmed the move was successful. By re-naming it, we’ll avoid confusion that could arise from files in both the new and the old location.

# mv /var/lib/mysql /var/lib/mysql.bak

Step #2: Pointing to the New Data Location

By default MySQL data directory set to /var/lib/mysql in the /etc/my.cnf file. Now edit this file to reflect the new data directory as shown below.

# vim /etc/my.cnf

[mysqld]
. . .
datadir=/mnt/data/mysql
socket=/mnt/data/mysql/mysql.sock
. . .

Save and close file.

After updating the existing lines, we’ll need to add configuration for the mysql client. Insert the following settings at the bottom of the file so it won’t split up directives in the [mysqld] block.

# vim /etc/my.cnf

[client]
port=3306
socket=/mnt/volume-nyc1-01/mysql/mysql.sock

Save and close file.

Step #3: Restart MySQL Service

Now restart the MySQL service after updated its configuration to use the new location.

# systemctl start mysqld
# systemctl status mysqld

To make sure that the new data directory is indeed in use, start the MySQL monitor.

# mysql -u root -p

mysql> select @@datadir;

+----------------------------+
| @@datadir                  |
+----------------------------+
| /mnt/data/mysql/ |
+----------------------------+
1 row in set (0.01 sec)

In the above output you can see the MySQL new data directory location is /mnt/data/mysql/.