How to Change a Default MySQL/MariaDB Data Directory in Linux

After installing the components of a LAMP stack on a CentOS/RHEL 7 server, there are a couple of things you may want to do.

Some of them have to do with increasing the security of the Apache and MySQL / MariaDB, while others may be applicable or not according to our setup or needs.

For example, based on the expected use of the database server, we may want to change the default data directory (/var/lib/mysql) to a different location. This is the case when such directory is expected to grow due to high usage.

Otherwise, the filesystem where /var is stored may collapse at one point causing the entire system to fail. Another scenario where changing the default directory is when we have a dedicated network share that we want to use to store our actual data.

For this reason, in this article we will explain how to change the default MySQL / MariaDB data directory to a different path on a CentOS/RHEL 7 server and Ubuntu/Debian distributions.

Although we will use MariaDB, the concepts explained and the steps taken in this article apply both to MySQL and to MariaDB, unless noted otherwise.

Changing the default MySQL/MariaDB Data Directory

Note: We are going to assume that our new data directory is /mnt/mysql-data. It is important to note that this directory should be owned by mysql:mysql.

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

For your convenience, we’ve divided the process into 5 easy-to-follow steps:

Step 1: Identify Current MySQL Data Directory

To begin, it is worthy and well to identify the current data directory using the following command. Do not just assume it is still /var/lib/mysql since it could have been changed in the past.

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

After you enter the MySQL password, the output should be similar to.

Identify MySQL Data Directory

Identify MySQL Data Directory

Step 2: Copy MySQL Data Directory to a New Location

To avoid data corruption, stop the service if it is currently running before proceeding. Use the systemd well-known commands to do so:

------------- On SystemD ------------- 
# systemctl stop mariadb
# systemctl is-active mariadb

------------- On SysVInit ------------- 
# service mysqld stop
# service mysqld status

OR

# service mysql stop
# service mysql status

If the service has been brought down, the output of the last command should be as follows:

Stop MySQL Service

Stop MySQL Service

Then copy recursively the contents of /var/lib/mysql to /mnt/mysql-data preserving original permissions and timestamps:

# cp -R -p /var/lib/mysql/* /mnt/mysql-data
Copy MySQL Data Directory to New Location

Copy MySQL Data Directory to New Location

Step 3: Configure a New MySQL Data Directory

Edit the configuration file (my.cnf) to indicate the new data directory (/mnt/mysql-data in this case).

# vi /etc/my.conf
OR
# vi /etc/mysql/my.conf

Locate the [mysqld] and [client] sections and make the following changes:

Under [mysqld]:
datadir=/mnt/mysql-data
socket=/mnt/mysql-data/mysql.sock

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

Save the changes and then proceed with the next step.

Configure New MySQL Data Directory

Configure New MySQL Data Directory

Step 4: Set SELinux Security Context to Data Directory

This step is only applicable to RHEL/CentOS and its derivatives.

Add the SELinux security context to /mnt/mysql-data before restarting MariaDB.

# semanage fcontext -a -t mysqld_db_t "/mnt/mysql-data(/.*)?"
# restorecon -R /mnt/mysql-data

Next restart the MySQL service.

------------- On SystemD ------------- 
# systemctl stop mariadb
# systemctl is-active mariadb

------------- On SysVInit ------------- 
# service mysqld stop
# service mysqld status

OR

# service mysql stop
# service mysql status

Now, use the same command as in Step 1 to verify the location of the new data directory:

# mysql -u root -p -e "SELECT @@datadir;"
Verify MySQL New Data Directory

Verify MySQL New Data Directory

Step 5: Create MySQL Database to Confirm Data Directory

Login to MariaDB, create a new database, and then check /mnt/mysql-data:

# mysql -u root -p -e "CREATE DATABASE tecmint;"
Check MySQL New Data Directory

Check MySQL New Data Directory

Congratulations! You have successfully changed the data directory for MySQL or MariaDB.

Summary

In this post we have discussed how to change the data directory in a MySQL or MariaDB server running on CentOS/RHEL 7 and Ubuntu/Debian distributions.

Do you have any questions or comments about this article? Feel free to let us know using the form below – we are always glad to hear from you!

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

Support Us

We are thankful for your never ending support.

Gabriel Cánepa

Gabriel Cánepa is a GNU/Linux sysadmin and web developer from Villa Mercedes, San Luis, Argentina. He works for a worldwide leading consumer product company and takes great pleasure in using FOSS tools to increase productivity in all areas of his daily work.

Your name can also be listed here. Got a tip? Submit it here to become an TecMint author.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

13 Responses

  1. Max says:

    Hi

    Thank you for this tutorial, I’m glad to find this, but afterward, I tried to perform:

    $ sudo chown -R mysql:mysql /media/pi/MyDrive/mysql-data
    

    I get the message:

    chown: changing ownership of '/media/pi/MyDrive/mysql-data' : Operation not permitted
    

    the external SSD-USB-drive show’s drwxrwxrwx on mysql-data. It is different, /mnt/MyDrive/mysql-data or /media/pi/MyDrive/mysql-data?

    my system is RPI4 Mod B with Buster, after installing the components and setup of a LAMP-sever

    Thank you for all comments

    • Ravi Saive says:

      @Max,

      All external devices are mounted under /mnt, so that command should be.

      $ sudo chown -R mysql:mysql /mnt/MyDrive/mysql-data
      
      • Max says:

        Hi,

        Ok I have changed this (mnt/MyDrive/mysql-data) after running as user pi:

        $  sudo chown -R mysql:mysql /mnt/Mydrive/mysql-data
        

        I get on all item: Operation not permitted

        mysql is runnig; @@datadir is /var/libmysql/

        for your help

    • Max says:

      Hi, it’s me again

      in the meantime, all is now OK!!! MariaDB Database is no running on my SSD
      (after one week trying… I’m very glad)
      thank you again for this tutorial

      Max

  2. dalu says:

    The new version 10.1.15 of the mariadb /etc/my.cnf does not have the datadir. below the what is in the file? Where to the change?

    #
    # This group is read both both by the client and the server
    # use it for options that affect everything
    #
    [client-server]
    
    #
    # include all files from the config directory
    #
    !includedir /etc/my.cnf.d
    
  3. Artur says:

    Hi, I had the same problem (Job failed to start).

    log: Database MariaDB is not initialized, but the directory /home/data is not empty, so initialization cannot be done.

    I did something like that:

    1. copy all mysql/mariadb datadir into the: /home/data/mysql
    2. mv /var/lib/mysql /var/lib/mysql.bak
    3. mkdir /var/lib/mysql
    4. mount –bind /home/data/mysql /var/lib/mysql (and save/remember it in fstab)

    And It’s working!

  4. Balu says:

    Super job It so helped me And we need to install “yum install policycoreutils-python”
    if we unable to find *semanage* command. It very helped me a lot. Before i done all steps but not getting proper result.
    Because i for got about Selinux group.

  5. Angela Erin says:

    1. Stop MySQL, Before making any changes, first make sure to stop mysql service

    # service mysqld stop
    

    2. Change Data Directory, Now copy default MySQL data directory (/var/lib/mysql) to other location as per your requirement. Also set the required MySQL ownership on new directory location. As per below command, we are relocating data directory to /data/mysql.

    # cp -rap /var/lib/mysql /data/mysql
    # chown mysql.mysql /data/mysql
    

    3. Now edit MySQL default configuration file /etc/my.cnf and update values of datadir and socket variable.

    Change From:
      datadir=/var/lib/mysql
      socket=/var/lib/mysql/mysql.sock
    
    Change To: 
      datadir=/data/mysql
      socket=/data/mysql/mysql.sock
    

    4. Start MySQL, After making all above changes. finally, start MySQL service. Now it will use new data directory path.

    # service mysqld start
    
    • milos says:

      @Angela Erin I did all the steps you described and I get the message: start: Job failed to start

      Any suggestions?
      thanks in advance

      • Gabriel A. Cánepa says:

        Can you please share the output of
        journalctl -xn
        and
        systemctl -l status mariadb
        right after you attempt to restart the service?

        • Max says:

          Hi,

          A previous attempt to move the MySQL database to the external SSD as described above, I get the ERROR 2002 (HY000) can’t connect to local MySQL server through socket!

          I was unable to solve this problem! (new installation!).

Leave a Reply to Artur Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.