[How-To] Change the Default Data Directory for MariaDB Server

Purpose

This documentation will focus on the process of changing the default data directory from its mysql default to a desired location. This will not focus on how to create the remote directory or how to mount it to the db server.

Prerequisites

List of prerequisites:

Root user or sudo user

MariaDB Server

MariaDB admin creds

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.

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:

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

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.cnf
OR
# vi /etc/mysql/my.cnf

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.

Step 4: Start the MariaDB Service

# systemctl start mariadb
# systemctl is-active mariadb

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;"

Step 5: Create MySQL Database to Confirm Data Directory

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

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

Documentation derived from: How to Change a Default MySQL/MariaDB Data Directory in Linux (tecmint.com)