How to Change MySQL Data Directory to a New Location on Linux

In this article we will explain how to change the default MySQL data directory to a different mount point or location on a CentOS/RHEL 7 server.

 Sometimes there is a requirement to change the default mysql data directory (/var/lib/mysql) to a new location based on the expected use of the database server. The default /var location might not be feasible to hold the incoming data and over some period of time databases can run into I/O contention or space crunch. 

Thus it is a good practice to relocate default MySQL’s data directory to the new location.

In this example, we are moving the data directory to the mount point /DBdata. You can also create and use a separate directory for this purpose.

Check the mount point free space and create new data directory
In this example we are considering /DBdata as new directory location.

[orahow@orahowdb ~]$ df -h
Filesystem                      Size  Used Avail Use% Mounted on
/dev/mapper/cl_clunode3-DBdata  500G   33M  500G   1% /DBdata

# mkdir directory_name
# chown -R mysql:mysql directory_name

STEP 1:  Check the current MySQL Data Directory Location

To identify the current data directory, login into the mysql server and fire the below command.

[orahow@orahowdb ~]$ mysql -u root -p
Enter password:


mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+

1 row in set (0.00 sec)

You can also fire the below command to get the current directory location.

STEP 2: Check the Current Status of MySQL Server:

[orahow@orahowdb ~]$ service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2018-05-23 19:33:55 IST; 764ms ago
  Process: 13660 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
  Process: 13644 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 13659 (mysqld_safe)
    Tasks: 12
   CGroup: /system.slice/mysqld.service
           ├─13659 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─13825 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --pid-file=/var/run/...

STEP 3: To ensure data integrity, shut down the MySQL server before making changes to the data directory.
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:

[orahow@orahowdb ~]$ sudo systemctl stop mysqld
[orahow@orahowdb ~]$ service mysql status
[orahow@orahowdb ~]$ sudo systemctl status mysqld
.......
.......
May 23 19:34:11 orahow.oradb systemd[1]: Stopping MySQL Community Server...
May 23 19:35:34 orahow.oradb systemd[1]: Stopped MySQL Community Server.

STEP 4: Copy and Synchronize Default Directory (/var/lib/mysql) to New Location.
To copy existing database directory to new location, we will use sync (Remote Sync) command which is the most commonly used command for copying and synchronizing files and directories remotely as well as locally in Linux/Unix systems.

[orahow@orahowdb ~]$ cd /var/lib/mysql
[orahow@orahowdb mysql]$ ls -lrt
total 116804
drwx------. 2 mysql mysql     4096 May 17 10:35 mysql
drwx------. 2 mysql mysql     4096 May 17 10:35 performance_schema
-rw-rw----. 1 mysql mysql       52 May 17 10:35 aria_log_control
-rw-rw----. 1 mysql mysql    16384 May 17 10:35 aria_log.00000001
-rw-r--r--  1 mysql mysql      276 May 23 15:33 RPM_UPGRADE_MARKER-LAST
-rw-r--r--  1 root  root       276 May 23 15:33 RPM_UPGRADE_HISTORY
-rw-rw----  1 mysql mysql 50331648 May 23 15:54 ib_logfile1
-rw-rw----  1 mysql mysql       56 May 23 15:54 auto.cnf
srwxrwxrwx  1 mysql mysql        0 May 23 16:01 mysql.sock
-rw-rw----. 1 mysql mysql 18874368 May 23 16:01 ibdata1
-rw-rw----  1 mysql mysql 50331648 May 23 16:01 ib_logfile0
-rw-r-----  1 mysql mysql        0 May 23 16:13 binlog.index

We have already shut down the MySQL server, now we will copy the existing database directory to the new location with rsync command. For this we will use -a flag which will preserves the permissions and other directory properties, while -v provides verbose output so that you can follow the progress.

Note: Please don't use trailing slash on the directory, otherwise rsync can dump the contents of the directory into the mount point instead of transferring into a containing mysql directory.

[orahow@orahowdb DBdata]$ sudo rsync -av /var/lib/mysql /DBdata
sending incremental file list
mysql/
mysql/RPM_UPGRADE_HISTORY
mysql/RPM_UPGRADE_MARKER-LAST
mysql/aria_log.00000001
mysql/aria_log_control
mysql/auto.cnf
mysql/performance_schema/threads.frm

sent 120,636,427 bytes  received 1,962 bytes  80,425,592.67 bytes/sec
total size is 120,600,480  speedup is 1.00

Once the synchronization is complete, rename the current folder with a .bak extension and keep it until we’ve confirmed the move was successful.

[orahow@orahowdb ~]$ sudo mv /var/lib/mysql /var/lib/mysql_23rd_may_2018.bak

STEP 5: Configure and Point the Existing Data Directory to the New Location
By default, the datadir is set to /var/lib/mysql in the /etc/my.cnf file. Edit this configuration file to change the new data directory:
We have marked the necessary changes below. Please make an entry of client block[client], if it doesn't exist and point socket file to the new location.

[orahow@orahowdb ~]$ sudo vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
datadir=/DBdata/mysql
socket=/DBdata/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
port=3306
socket=/DBdata/mysql/mysql.sock

Once done, save the configuration file using  escape :wq! and exit.

STEP 6 — Finally Restart the MySQL server 

We have updated the configuration file to use new location, now we are ready to start the MySQL server and verify the new directory location.

[orahow@orahowdb ~]$ sudo systemctl start mysqld
[orahow@orahowdb ~]$ sudo systemctl status mysqld

To make sure that the new data directory is indeed in use, start the MySQL monitor. Look at the value for the data directory again:

[orahow@orahowdb ~]$ mysql -u root -p
Enter password:
mysql> select @@datadir;
Output
+----------------------------+
| @@datadir                   |
+----------------------------+
| /DBdata/mysql/             |
+----------------------------+

1 row in set (0.01 sec)

Now that you’ve restarted MySQL and confirmed that it’s using the new location, take the opportunity to ensure that your database is fully functional. Once you’ve verified the integrity of any existing data, you can remove the backup data directory with sudo rm -Rf /var/lib/mysql.bak.

Conclusion:

In this tutorial, we’ve moved and changed the MySQL’s data directory to the new location. Although we have updated and restarted the services, sometimes their might be a port conflict. So check if MySQL port is already in use or not. If port is already in use then check the process who is listening the port, kill that process and restart the MySQL server again.