Tutorial

How To Change a MariaDB Data Directory to a New Location on CentOS 7

How To Change a MariaDB Data Directory to a New Location on CentOS 7

Introduction

Databases grow over time, sometimes outgrowing the space on the file system. You can also run into I/O contention when they’re located on the same partition as the rest of the operating system. RAID, network block storage, and other devices can offer redundancy and other desirable features. Whether you’re adding more space, evaluating ways to optimize performance, or looking to take advantage of other storage features, this tutorial will guide you through relocating MariaDB’s data directory.

Prerequisites

To complete this guide, you will need:

In this example, we’re moving the data to a block storage device mounted at /mnt/volume-nyc1-01. You can learn how to set one up in the How To Use Block Storage on DigitalOcean guide.

No matter what underlying storage you use, this guide can help you move the data directory to a new location.

Step 1 — Moving the MariaDB Data Directory

To prepare for moving MariaDB’s data directory, let’s verify the current location by starting an interactive session using the administrative credentials.

  1. mysql -u root -p

When prompted, supply the MariaDB root password. Then from the mysql prompt, select the data directory:

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

This output confirms that MariaDB is configured to use the default data directory, /var/lib/mysql/, so that’s the directory we need to move. Once you’ve confirmed this, type exit and press “ENTER” to leave the monitor:

  1. exit

To ensure the integrity of the data, we’ll shut down MariaDB before we actually make changes to the data directory:

  1. sudo systemctl stop mariadb

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:

  1. sudo systemctl status mariadb

You can be sure it’s shut down if the final line of the output tells you the server is stopped:

Output
. . . Dec 16 18:29:26 mysql systemd[1]: Stopped MariaDB database server.

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

Note: Be 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:

  1. sudo rsync -av /var/lib/mysql /mnt/volume-nyc1-01

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:

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

Now we’re ready to turn our attention to configuration.

Step 2 — Pointing to the New Data Location

MySQL has several ways to override configuration values. By default, the datadir is set to /var/lib/mysql in the /etc/my.cnf file. Edit this file to reflect the new data directory:

  1. sudo vi /etc/my.cnf

Find the line that begins with datadir= and change the path which follows to reflect the new location. In addition, we since the socket was previously located in the data directory, we’ll need to update it to the new location:

/etc/my.cnf
[mysqld]
. . .
datadir=/mnt/volume-nyc1-01/mysql
socket=/mnt/volume-nyc1-01/mysql/mysql.sock
. . .

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 won’t split up directives in the [mysqld] block and above the include line:

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

!includedir /etc/my.cnf.d

When you’re done, hit ESCAPE, then type :wq! to save and exit the file.

Step 3 — Restarting MariaDB

Now that we’ve updated the configuration to use the new location, we’re ready to start MariaDB and verify our work.

  1. sudo systemctl start mariadb
  2. sudo systemctl status mariadb

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

  1. mysql -u root -p

Look at the value for the data directory again:

  1. select @@datadir;
Output
+----------------------------+ | @@datadir | +----------------------------+ | /mnt/volume-nyc1-01/mysql/ | +----------------------------+ 1 row in set (0.01 sec)

Type exit to leave the monitor.

Now that you’ve restarted MariaDB 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 MariaDB’s data directory to a new location. Although we were using a Block Storage device, the instructions here should be suitable for redefining the location of the data directory regardless of the underlying technology.

Since MariaDB is designed as a drop-in replacement for MySQL, you can learn more about managing data directories from these sections in the official MySQL documentation:

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
6 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

I had a friend who was trying to move the dir into the /home/mysql (i don’t know why) but he found that there’s a setting that prevent’s the user read from there.

I’ll paste the solution here in case that someone else is looking for it:

The problem is the systemd service file mariadb.service which has set

ProtectHome = true

This setting prevents the service from accessing /home, /root and /run/user directories.

So either you mount your mysql data to a different location or you set this feature to false.

To disable this feature, best would be to use systemctl edit mariadb which opens the editor defined in the environment variable EDITOR. Just put:

[Service]
ProtectHome = false

Save and close and an override file /etc/systemd/system/mariadb.service.d/override.conf will be created. After that a systemctl daemon-reload is needed to re-read the setting. Start your service.

Thanks. I followed the steps bit I had to add one more step ie, create symlink

**ln -s /data/mysql /var/lib/mysql **

or

keep the socket location as such ie /var/lib/msql/mysql.sock

It doesn’t work on Mariadb 10.2 :(

Also, it may be necesary to update the socket direction in the php configuration. To do so:

Open the php.ini with sudo vi /etc/php.ini and find this line:

mysql.default_socket

And make it:

mysql.default_socket = /path/to/mysql.sock

If you are using PDO, there is a similar line to change. After that do:

sudo systemctl reload php-fpm sudo systemctl reload httpd

Everything should work after that.

Dont forget many installations will need the SELinux context changed on the new files.

sudo chcon -R system_u:object_r:mysqld_db_t:s0 /mnt/volume-nyc1-01/mysql

This comment has been deleted

    Try DigitalOcean for free

    Click below to sign up and get $200 of credit to try our products over 60 days!

    Sign up

    Join the Tech Talk
    Success! Thank you! Please check your email for further details.

    Please complete your information!

    Get our biweekly newsletter

    Sign up for Infrastructure as a Newsletter.

    Hollie's Hub for Good

    Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

    Become a contributor

    Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

    Welcome to the developer cloud

    DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

    Learn more
    DigitalOcean Cloud Control Panel