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

Changing the data directory of MySQL or MariaDB on Ubuntu or Debian is essential when you need to move the database files to another location. This can be necessary if your current directory is running out of space or if you want to improve performance by using a different partition. It can also enhance data management by organizing the files on a different drive or location.

To perform this task, you will need to update the database’s configuration files and adjust the security settings. Both MySQL and MariaDB store their data in a specific directory defined by the datadir parameter in their configuration. Changing this requires creating a new data directory, copying the current data, and ensuring that the database can access it. Proper ownership and permissions must be applied to avoid startup failures or access issues.

Additionally, systems like Ubuntu and Debian often use AppArmor to control the access of applications to system resources. You will need to modify the AppArmor profile for MySQL or MariaDB to allow it to use the new data directory. Once the profile is updated and the service restarted, the database will run from the new directory. This requires administrative access and precision to avoid errors or data loss.

1. Stop the MySQL or MariaDB service.

   $ sudo systemctl stop mysql

   This stops the database service before making any changes. Replace mysql with mariadb if you are using MariaDB.

2. Create a new data directory.

   $ sudo mkdir /new-data-directory

   Choose an appropriate path for the new data directory.

3. Set ownership and permissions for the new directory.

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

   This sets the correct ownership and access permissions for MySQL/MariaDB.

4. Copy the contents of the current data directory to the new one.

   $ sudo cp -R /var/lib/mysql/* /new-data-directory/

   Ensure all files and subdirectories are copied to the new directory. Use /var/lib/mysql/ for MySQL or /var/lib/mariadb/ for MariaDB as the source directory.

5. Modify the MySQL or MariaDB configuration file to set the new datadir.

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

   Set datadir directive to the new location.

     datadir = /new-data-directory

6. Save and close the file after updating the datadir value.
7. Update the AppArmor profile to include the new data directory path.

   $ sudo nano /etc/apparmor.d/usr.sbin.mysqld

   Add the following lines:

     /new-data-directory/ r,
     /new-data-directory/// rwk,

   This ensures MySQL or MariaDB can access the new directory under AppArmor's restrictions.

8. Reload the AppArmor service.

   $ sudo systemctl reload apparmor

9. Start the MySQL or MariaDB service.

   $ sudo systemctl start mysql

   Replace mysql with mariadb if using MariaDB.

10. Verify that the database is using the new data directory.

    $ sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"
      +---------------+------------------------+
      | Variable_name | Value                  |
      +---------------+------------------------+
      | datadir       | /new-data-directory/   |
      +---------------+------------------------+

    Ensure that the datadir reflects the new path.