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:

  1. Stop the MySQL/MariaDB service.
    sudo systemctl stop mysql
  2. Create a new data directory.
    sudo mkdir /new-data-directory
  3. Set the ownership and permissions for the new data directory.
    sudo chown -R mysql:mysql /new-data-directory
    sudo chmod 750 /new-data-directory
  4. Copy the current data directory to the new location.
    sudo cp -R /var/lib/mysql/* /new-data-directory/
  5. Edit the MySQL/MariaDB configuration file.
    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
  6. Update the 'datadir' directive to the new data directory.
    datadir = /new-data-directory
  7. Save and close the configuration file.
  8. Edit the AppArmor profile for MySQL/MariaDB.
    sudo nano /etc/apparmor.d/usr.sbin.mysqld
  9. Add the new data directory and its contents to the profile.
    /new-data-directory/ r,
    /new-data-directory/** rwk,
  10. Save and close the AppArmor profile.
  11. Reload AppArmor to apply the changes.
    sudo systemctl reload apparmor
  12. Start the MySQL/MariaDB service.
    sudo systemctl start mysql
  13. Verify that the new data directory is being used.
    sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"

This guide is tested on Ubuntu:

Version Code Name
22.04 LTS Jammy Jellyfish
23.10 Mantic Minotaur
24.04 LTS Noble Numbat
Discuss the article:

Comment anonymously. Login not required.