How to Change Default MySQL Data Directory

When you install MySQL on your Linux system, it creates a folder /var/lib/mysql to store your data and configuration in it. But sometimes database administrators want to change the default MySQL data directory because they expect this folder to grow large due to high usage. In such cases, if you don’t use a different folder, the filesystem under /var directory will collapse and cause your system to crash. In this article, we will learn how to change default MySQL data directory in Linux. You can also use the same steps for Mariadb.

How to Change Default MySQL Data Directory

Let us say you want to /mnt/mysql-data to be our new data directory for MySQL.

1. Create Directory

First we create the new directory in our system. Open terminal and run the following commands to do it.

# mkdir /mnt/mysql-data

To make a certain directory as default data directory of MySQL, you need to set mysql as its owner. Run the following command to do so.

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

2. Locate Current Data Directory

Next, we find out the existing MySQL data directory. Although, we have mentioned the default location of data directory earlier, it is better check it using proper commands. We run the following command to logi to MySQL database and run the ‘SELECT @@datadir’ SQL query directly from terminal.

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

After you enter password, you will see the following output

@@datadir
----------
/var/lib/mysql/

3. Copy MySQL Data Directory

Next, we stop our MySQL server to make sure that no data is written or updated in our database, when we copy its data to another location.

# service mysqld stop
# service mysqld status

OR

# service mysql stop
# service mysql status

Next, run the following command to copy files from your present data directory to its new location.

# cp -R -p /var/lib/mysql/* /mnt/mysql-data

4. Configure New MySQL Data Directory

The MySQL configuration file contains the location of its default data directory. We need to update it to point to the new location of data directory.

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

Locate [mysqld] and [client] sections and make the following changes highlighted in bold. Its present value will be something like

Under [mysqld]:
datadir=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock

Under [client]:
port=3306
socket=/var/lib/mysql/mysql.sock

Change it to the following.

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

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

Save and close the file.

5. Set SELinux Security Context (for RHEL/CentOS)

If you are using RHEL/CentOS Linux system, you need to run the following command to add SELinux Security Context to the new folder location.

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

6. Restart MySQL Server

Next, start MySQL server to apply changes.

# service mysqld start
# service mysqld status

OR

# service mysql start
# service mysql status

Now if you run the following command to check the location of data directory.

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

On entering password, you will see the following output, indicating that the data folder has been changed.

@@datadir
---------
/mnt/mysql-data/

You can also run the following command to create a new database in your system, to see if things are working properly.

# mysql -u root -p -e "CREATE DATABASE test123;"

If you see no errors, it means MySQL has started writing to your new data folder /mnt/mysql-data.

In this article, we have learnt how to change default MySQL data directory.

How to Change Default MySQL Data Directory