Change the data directory of MySQL / MariaDB Database Server

Information managed by the MySQL server is stored under a directory known as the data directory.

Data directory subdirectories. Each subdirectory of the data directory is a database directory and corresponds to a database managed by the server.

All MySQL installations have certain standard databases:

The mysql directory corresponds to the MySQL system database, which contains information required by the MySQL server as it runs.

The performance_schema directory corresponds to the Performance Schema, which provides information used to inspect the internal execution of the server at runtime.

The sys directory corresponds to the sys schema, which provides a set of objects to help interpret Performance Schema information more easily.

Other subdirectories correspond to databases created by users or applications.

Find the data directory

mysql -u root -p -e “SELECT @@datadir;”

MariaDB [mysql]> SELECT @@datadir;
+-------------------+
| @@datadir         |
+-------------------+
| /var/lib/mysql/   |
+-------------------+
1 row in set (0.00 sec)

Change the data directory of MySQL or MariaDB

Read here about adding additional EBS volume to your Amazon Linux EC2 Instance for MySQL data directory

Change the owner of the new data directory to mysql

chown -R mysql:mysql /datadir/

First Stop the MySQL / MariaDB Service

systemctl stop mariadb

Then Copy current MySQL Data Directory to a New Location

cp -R -p /var/lib/mysql/* /datadir/

Configure a New MySQL Data Directory in the conf file

sudo nano /etc/my.cnf

[mysqld]
datadir=/datadir/

Start the Service

systemctl start mariadb

Verify the data directory is changed

[ec2-user@ip-172-31-40-130 ~]$ mysql -u root -p -e "SELECT @@datadir;"
Enter password: 
+-----------+
| @@datadir |
+-----------+
| /datadir/ |
+-----------+
[ec2-user@ip-172-31-40-130 ~]$ 

Create a new database and confirm the changes

[ec2-user@ip-172-31-40-130 ~]$ mysql -u root -p -e "CREATE DATABASE asvignesh;"
Enter password: 
[ec2-user@ip-172-31-40-130 ~]$

[ec2-user@ip-172-31-40-130 datadir]$ ls /datadir/
aria_log.00000001  aria_log_control  asvignesh  ibdata1  ib_logfile0  ib_logfile1  lost+found  mysql  performance_schema

---

Also published on Medium.