How to change the data directory of MySQL or MariaDB in Ubuntu/Debian

In this guide, we will provide you with step-by-step instructions on how to change the data directory of MySQL or MariaDB in Ubuntu/Debian. Changing the data directory may be necessary if you need to move your databases to a new location, such as when you are running out of disk space or when you want to store your data on a separate partition for better performance or security reasons.

To achieve this, we will need to modify the MySQL/MariaDB configuration file, update the AppArmor profile to allow access to the new data directory, and then restart the MySQL or MariaDB service to apply the changes.

Keep in mind that the process outlined below requires administrative privileges and assumes you have already installed MySQL or MariaDB on your Ubuntu/Debian system. Proceed with caution, as any misconfiguration might lead to database errors or data loss.

Steps to change the data directory of MySQL or MariaDB in Ubuntu/Debian:

Stop the MySQL/MariaDB service.
```
sudo systemctl stop mysql
```
Create a new data directory.
```
sudo mkdir /new-data-directory
```

Set the ownership and permissions for the new data directory.

sudo chown -R mysql:mysql /new-data-directory
sudo chmod 750 /new-data-directory

Copy the current data directory to the new location.
```
sudo cp -R /var/lib/mysql/* /new-data-directory/
```

Edit the MySQL/MariaDB configuration file.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Update the 'datadir' directive to the new data directory.
```
datadir = /new-data-directory
```
Save and close the configuration file.
Edit the AppArmor profile for MySQL/MariaDB.
```
sudo nano /etc/apparmor.d/usr.sbin.mysqld
```
Add the new data directory and its contents to the profile.
```
/new-data-directory/ r,
/new-data-directory/** rwk,
```
Save and close the AppArmor profile.
Reload AppArmor to apply the changes.
```
sudo systemctl reload apparmor
```
Start the MySQL/MariaDB service.
```
sudo systemctl start mysql
```

Verify that the new data directory is being used.

sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is an experienced cloud architect with a strong development and open-source advocacy background. He boasts multiple certifications in AWS, Red Hat, VMware, ITIL, and Linux, underscoring his expertise in cloud architecture and system administration.

Discuss the article:

Comment anonymously. Login not required.