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.
sudo systemctl stop mysql
sudo mkdir /new-data-directory
sudo chown -R mysql:mysql /new-data-directory sudo chmod 750 /new-data-directory
sudo cp -R /var/lib/mysql/* /new-data-directory/
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
datadir = /new-data-directory
sudo nano /etc/apparmor.d/usr.sbin.mysqld
/new-data-directory/ r, /new-data-directory/** rwk,
sudo systemctl reload apparmor
sudo systemctl start mysql
sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"
Comment anonymously. Login not required.