How to change MySQL data directory?

When you need to change the MySQL data directory to a new location, follow these steps:

1. Stop the MySQL Service

First, stop the running MySQL service to prevent data corruption or loss during the directory change. This can be done by running the following command:

bash
sudo systemctl stop mysql

2. Copy the Existing Data Directory

Next, copy the existing data directory to the new location. This step is crucial as it ensures all data and directory structure are preserved in the new location. Use rsync to maintain data consistency and integrity:

bash
sudo rsync -av /var/lib/mysql /new/path/mysql

Here /var/lib/mysql is the default MySQL data directory, and /new/path/mysql is the new data directory path.

3. Update the Configuration File

After copying the data, update the MySQL configuration file (typically /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf) to point to the new data directory:

ini
[mysqld]
datadir=/new/path/mysql

Ensure the datadir value in the configuration file is updated.

4. Adjust Permissions

After changing the data directory, ensure the MySQL user has access to the new directory:

bash
sudo chown -R mysql:mysql /new/path/mysql

This command reassigns ownership and group of the new directory to the MySQL user and group.

5. Adjust AppArmor/Selinux Settings (if applicable)

If your system uses AppArmor (e.g., Ubuntu) or SELinux (e.g., CentOS), you may need to update the relevant security policies to allow MySQL access to the new data directory.

For AppArmor, edit /etc/apparmor.d/usr.sbin.mysqld and add lines for the new directory:

plaintext
/new/path/mysql/ r,
/new/path/mysql/** rwk,

Then reload the AppArmor configuration:

bash
sudo systemctl reload apparmor

6. Restart the MySQL Service

After all configurations are complete, restart the MySQL service:

bash
sudo systemctl start mysql

7. Verify the Changes

Finally, verify that MySQL is running correctly and using the new data directory. Log in to MySQL and use the following command to check:

sql
SHOW VARIABLES LIKE 'datadir';

These steps should help you successfully change the MySQL data directory to a new location. It is strongly recommended to test these steps in a non-production environment first to ensure all steps meet your system requirements and prevent data loss.