MyLinuxTips

In this tutorial, we will learn how to change the MySQL Data Directory or relocate the MySQL Database data to the new location.

This situation may be used when the database is growing very fast, or for some security reasons we want to move the data directory to the new location.

NOTE: The below mentioned steps are performed on CentOS 7 server.

1. Moving the MySQL Data Directory

First let’s verify the current location by starting an interactive MySQL session using the administrative credentials.

mysql -u root -p

Enter the MySQL root password when prompted, then from MySQL prompt select datdir.

mysql > select @@datadir;

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

From the above output it is confirmed that MySQL is configured to use the default data directory i.e /var/lib/mysql. Now type exit to leave the monitor.

NOTE: You may also take mysqldump of your databases before making any changes.

Now, shut down MySQL & check for the status:

sudo systemctl stop mysql
sudo systemctl status mysql

Now that our MySQL server is shut down, we’ll copy the existing database directory to the new location with rsync. We’ll use -a flag to preserve the permissions and other directory properties, while-v provides verbose output so you can follow the progress.

sudo rsync -av /var/lib/mysql /mnt/mysql-partition 

Once the rsync is complete, we’ll rename /var/lib/mysql to /var/lib/mysql.bak To avoid any confusions. Keep the backup until we have successfully changed the datadir.

sudo mv /var/lib/mysql /var/lib/mysql.bak

2. Pointing to the New Data Location

By default, the datadir is set to /var/lib/mysql in the /etc/my.cnf. Edit this file to reflect the new data directory:

sudo vim /etc/my.cnf 

In my.cnf file, update “datadir” & “socket” to new location.

datadir=/mnt/mysql-partition/mysql
socket=/mnt/mysql-partion/mysql/mysql.sock

Now, Append the following lines in my.cnf

[client]

port=3306
socket=/mnt/mysql-partion/mysql/mysql.sock

Now save & exit the file & restart MySQL.

NOTE : Make sure selinux is Disabled on your server.

sudo systemctl restart mysql
sudo systemctl status mysql

Now, verify the new datadir location :

mysql -u root -p

mysql > select @@datadir;

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