Database

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

Change MySQL Data Directory to New Location on CentOS 7
mm
Written by Santosh Prasad

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.

Make sure there is no trailing slash on the directory, which may be added if you use tab completion. When there’s a trailing slash, rsync will dump the contents of the directory into the mount point instead of transferring it into a containing mysql directory.
# 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/.

Thank you! for visiting Look Linux.

If you find this tutorial helpful please share with your friends to keep it alive. For more helpful topic browse my website www.looklinux.com. To become an author at Look Linux Submit Article. Stay connected to Facebook.

About the author

mm

Santosh Prasad

Hi! I'm Santosh and I'm here to post some cool article for you. If you have any query and suggestion please comment in comment section.

1 Comment

  • Followed this on a new Fedora 28 installation, but got this errorÖ
    # systemctl start mysqld
    Job for mysqld.service failed because the control process exited with error code.
    See “systemctl status mysqld.service” and “journalctl -xe” for details.
    [[email protected] ~]# systemctl status mysqld.service
    ● mysqld.service – MySQL Server
    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
    Active: failed (Result: exit-code) since Mon 2018-06-18 21:29:21 CEST; 7s ago
    Docs: man:mysqld(8)
    http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 13948 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
    Process: 13931 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
    Main PID: 13948 (code=exited, status=1/FAILURE)
    Status: “SERVER_BOOTING”
    Error: 13 (Permission denied)

    Jun 18 21:29:21 leia2.thommym.st systemd[1]: Starting MySQL Server…
    Jun 18 21:29:21 leia2.thommym.st systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
    Jun 18 21:29:21 leia2.thommym.st systemd[1]: mysqld.service: Failed with result ‘exit-code’.
    Jun 18 21:29:21 leia2.thommym.st systemd[1]: Failed to start MySQL Server.

    # systemctl status mysqld
    ● mysqld.service – MySQL Server
    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
    Active: failed (Result: exit-code) since Mon 2018-06-18 21:29:21 CEST; 1min 48s ago
    Docs: man:mysqld(8)
    http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 13948 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
    Process: 13931 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
    Main PID: 13948 (code=exited, status=1/FAILURE)
    Status: “SERVER_BOOTING”
    Error: 13 (Permission denied)

    Jun 18 21:29:21 leia2.thommym.st systemd[1]: Starting MySQL Server…
    Jun 18 21:29:21 leia2.thommym.st systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
    Jun 18 21:29:21 leia2.thommym.st systemd[1]: mysqld.service: Failed with result ‘exit-code’.
    Jun 18 21:29:21 leia2.thommym.st systemd[1]: Failed to start MySQL Server.

Leave a Comment