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 liked this article, then do subscribe to email alerts for Linux tutorials. If you have any questions or doubts? do ask for help in the comments section.

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.

16 thoughts on “How to Change a Default MySQL/MariaDB Data Directory in Linux”

  1. Interesting: followed all the steps and received a message that MariaDB “can’t create a test file” in the new directory. Something with directory permissions?

    Reply
  2. Thanks a lot for this tutorial.

    I actually ran out of space and was trying to move the data directory to a new drive. I browsed through many blogs and followed the steps but was getting the below error “couldn’t find aria_log_control“.

    The step: Setting the SELinux security context to the new data directory solved my problem.

    Reply
  3. Hi, Thanks for a really useful tutorial. The moving the DB part worked really well, but when I try and move the socket there seems to be a problem accepting client connections. phpMyAdmin returns “Cannot Logon to the MYSQL server” – mysqli_real_connect(): (HY000/2002): No such file or directory.

    If I leave the socket in the default dir “/run/mysql/mysql.sock” I get no problems.

    I have the following in my.cnf:

    [client]
    port       = 3306
    socket     = /mnt/HD2TB/serverdata/mysqlsock/mysql.sock
    [mysqld]
    datadir = /mnt/HD2TB/serverdata/mysql
    socket     = /mnt/HD2TB/serverdata/mysqlsock/mysql.sock
    port       = 3306
    

    MYSQL starts OK and I can run sql from the terminal for example :

    [email protected]:~> mysql -u root -p -e "SELECT @@socket;"
    +--------------------------------------------+
    | @@socket                                   |
    +--------------------------------------------+
    | /mnt/HD2TB/serverdata/mysqlsock/mysql.sock |
    +--------------------------------------------+
    

    I don’t think ownership permissions is the problem:

    [email protected]:/mnt/HD2TB/serverdata> ls -al
    total 16
    drwxr-xr-x 1hidrusr users  28 Jan 17 13:35 .
    drwxr-xr-x 1 hidrusr users  74 Jan 15 20:46 ..
    drwx------ 1 mysql  root  372 Jan 17 13:59 mysql
    drwxr-xr-x 1 mysql  root   20 Jan 17 13:59 mysqlsock
    

    I also tried configuring it to be in “/mnt/HD2TB/serverdata/mysql/mysql.sock” and I get the same problem. I get the same problem if I chown to mysql:mysql (I’m not sure what the difference is.

    Each time I test I do perform: systemctl stop mysql and then start MySQL. I am running on OpenSuse Leap 15.1

    Reply
  4. 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

    Reply
    • @Max,

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

      $ sudo chown -R mysql:mysql /mnt/MyDrive/mysql-data
      
      Reply
      • 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

        Reply
    • 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

      Reply
  5. 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
    
    Reply
  6. 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!

    Reply
  7. 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.

    Reply
  8. 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
    
    Reply
    • @Angela Erin I did all the steps you described and I get the message: start: Job failed to start

      Any suggestions?
      thanks in advance

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

        Reply
        • 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!).

          Reply

Leave a Reply to Angela Erin Cancel reply

Have a question or suggestion? Please leave a comment to start the discussion. Please keep in mind that all comments are moderated and your email address will NOT be published.

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