There are two ways to relocate MySQL data directory: use symbolic link or change datadir parameter from the configuration file. In this tutorial, I will show you how to relocate MySQL data directory using datadir parameter.

Change datadir parameter

The datadir parameter is set in /etc/mysql/mariadb.conf.d/50-server.cnf. So, open it and update with your directory path. For example,

datadir                 = /media/sql/mysql-data

If you don't want to change the original configuration file, /etc/mysql/mariadb.conf.d/50-server.cnf, then you can add a separate cnf file in /etc/mysql/mariadb.conf.d/ directory. However, ensure that your cnf filename is listed below 50-server.cnf in a lexical order. An example of the filename would be 99-datadir.cnf.

In 99-datadir.cnf, add the followings:

# Change datadir to match your's and 
#   ensure that it has the same permission as /var/lib/mysql/.
[mysqld]
datadir=/media/sql/mysql-data

Give permissions

Ensure that MySQL has permission to access the new data directory path. If you are running Debian, you can run the following commands.

# Yes, give permission to the parent directory 
#   of the data directory.
chown -R mysql  /media/sql
chgrp -R mysql  /media/sql
chmod 755       /media/sql

Restart MySQL server

After you updated datadir parameter in /etc/mysql/mariadb.conf.d/50-server.cnf, you have to restart MySQL server.

service mysql stop
service mysql start

Test the changes

Check whether the new datadir path has changed by running the following command.

mysql -e 'select @@datadir;'

It should show the new path.

>mysql -e 'select @@datadir;'
+------------------------+
| @@datadir              |
+------------------------+
| /media/sql/mysql-data/ |
+------------------------+