Skip to content

How to Move MySQL Data Directory to Another Folder on CentOS?

Move MySQL Data Directory

MySQL data directory is a directory used to store MySQL/MariaDB database data. By default, both databases use the /var/lib/mysql or as known as datadir. If you have a /var partition that is still joined to the / partition but the partition is getting smaller, then we can move the MySQL data directory to another folder for example to the /data folder. Look at the picture below:

The initial size of the hard drive

 

Problem

How to move MySQL/MariaDB data directory to another folder on CentOS?

 

Solution

Although this article uses CentOS, it looks like this can be done in all Linux distros. Here are the steps to move mysql data directory to another folder:

1. Turn off anything related to the database
To avoid a corrupt database when moving the database to another folder, then there should be no transaction on the database. You can turn off the application that connects to the database or turn off the webserver such as tomcat, Nginx, Apache, and others or you can unplug the LAN cable which connects to the server.

2. Create a New Folder
Create a new folder for datadir and assign the user and group permissions to the folder:

mkdir /data/mysql
chown -R mysql:mysql /data/mysql

 

3. Check the datadir database location

Then we check the location of datadir by:

mysql -u root -p -e "SELECT @@datadir;"

 

Please look at the picture below:

The initial configuration of the datadir

From the picture above, we can see that the current datadir database is still in the /var/lib/mysql folder.

4. Turn off the service database

Next, turn off the service of the database using the command:

### For MariaDB on CentOS 7/8:

systemctl stop mariadb

 

### For MySQL on CentOS 7/8:

systemctl stop mysqld

 

### For MySQL on CentOS 5/6:

service mysqld stop

 

5. Copy the Database to the new folder

Then copy the database using the command:

cp -Rp /var/lib/mysql/* /data/mysql

 

Or if we have the rsync package in the server, we can use the command below:

rsync -av /var/lib/mysql/* /data/mysql

 

6. Configure the Database Parameters

By default, the database parameter is in the file /etc/my.cnf. In this file, we can see the MySQL/MariaDB database parameters. Before we change the file, it is better to back up the file first:

cp /etc/my.cnf /etc/my.cnf.ori

 

Then we change the parameters to be like the image below:

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

[client]:
port=3306
socket=/data/mysql/mysql.sock

 

Please look at the picture below:

Configuration of my.cnf file

7. Configure SELinux

If you disable SELinux on the CentOS server, then skip this step and continue with the next step. To see whether your server uses SELinux, use the command:

getenforce

 

If we use SELinux on the server, we must configure the server. Type the following commands:

yum install policycoreutils-python
semanage fcontext -a -t mysqld_db_t '/data/mysql(/.*)?'
restorecon -Rv /data/mysql

 

8. Turn on the database service

After that, turn on the database service by:

### For MariaDB on CentOS 7/8:

systemctl start mariadb

 

### For MySQL on CentOS 7/8:

systemctl start mysqld

 

### For MySQL on CentOS 5/6:

service mysqld start

 

9. Check the datadir database location again

Next, try to check the database location whether the datadir location is already in a new folder or not by:

mysql -u root -p -e "SELECT @@datadir;"

 

Please look at the picture below:

The final configuration of the datadir

The datadir location has been changed to the /data/mysql folder.

10. Test the database

You can check the application that connects to the database and try to do many things such as log in, log out, and so on. If there are no problems in the application, we have successfully moved the database to a new folder on the server and the database is ready to use.

Or you try to create a new database on a new database named example by:

mysql -uroot -p -e"create database example;"

 

Please look at the picture below:

Create a new database

 

If you successfully create the new database, then it should be in the folder /data/mysql there will be a folder of the new database like the picture below and that means moving our database from one folder to another folder in the server successfully.

After that, you can delete /var/lib/mysql folder and should partition size / increase.

The final size of the hard drive

 

Note

1. Please make sure when you want to move data to another folder in the server, no application connects to the database to avoid a corrupt database.

2. You should have calculated how many HDDs you need for the database before you install the server so you don’t need to do things like this, so there is a concern that it will create downtime when moving data.

 

References
tecmint.com
orahow.com
blogs.oracle.com
thegeekdiary.com

Print Friendly, PDF & Email

2 thoughts on “How to Move MySQL Data Directory to Another Folder on CentOS?”

Leave a Reply

Your email address will not be published.