How to Relocate a MySQL Data Directory on an Ubuntu 20.04 Server

Here are the steps to provide a step-by-step guide for relocating the MySQL data directory to a new location on an Ubuntu 20.04 server. This process is useful for scenarios where the existing data directory has space limitations or when optimizing performance by utilizing alternative storage solutions, such as network block storage.

Prerequisites

Before proceeding with the MySQL data directory relocation, ensure you have the following prerequisites in place:

• An Ubuntu 20.04 server with a non-root user having sudo privileges and an active firewall. Refer to the “Initial Server Setup with Ubuntu 20.04” guide for user setup.
• A MySQL server installed on the Ubuntu server. If MySQL is not installed, consult the guide on “How to Install MySQL on Ubuntu 20.04.”

Step 1: Identifying the Current Data Directory

*Note: If you’ve configured the root MySQL user to use a password, connect using the following command:

1.2. Supply the MySQL user password when prompted, and then execute the following SQL statement to retrieve the active data directory:

1.3. The output will confirm the current data directory location, typically found in MySQL’s `datadir` variable. For example:

1.4. Exit the MySQL prompt by typing:

1.5. To maintain data integrity, stop the MySQL service:

1.6. Verify the MySQL service is inactive by using:

Ensure the “Active” line states “inactive (dead),” indicating that MySQL has shut down successfully.

Step 2: Moving the MySQL Data Directory

2.1. Now that MySQL is stopped, copy the existing database directory (`/var/lib/mysql`) to the new destination (`/mnt/volume-nyc1-01`) using `rsync`. The `-a` flag preserves permissions and other properties, while `-v` provides verbose output for progress monitoring:

2.2. After the `rsync` command completes, rename the current data directory with a `.bak` extension to avoid confusion:

Step 3: Updating MySQL Configuration

3.1. MySQL’s data directory is specified in the `/etc/mysql/mysql.conf.d/mysqld.cnf` configuration file. Edit this file to reflect the new data directory using a text editor of your choice (e.g., `nano`):

3.2. Locate the line that starts with `datadir=` and uncomment it by removing the ‘#’ symbol. Update the path to match the new data directory, like so:

3.3. Save and exit the file (for `nano`, press `CTRL + X`, then `Y`, and finally `ENTER`).

Step 4: Configuring AppArmor Access Control Rules

4.1. To allow MySQL to write to the new directory, establish an alias between the default directory and the new location by editing the AppArmor alias file:

4.2. At the end of the file, uncomment the following line and add the alias rule:

4.3. Save and exit the file.

4.4. Apply the AppArmor changes by restarting AppArmor:

*Note: If you skipped the AppArmor configuration step, you will receive an error message when attempting to start MySQL.

Step 5: Restarting MySQL

5.1. Start MySQL once again. You may encounter an error related to `mysql-systemd-start`, a script that manages MySQL through systemd. Inspect this script:

5.2. The script checks for the existence of either a directory or symbolic link matching the default data directory path. To address this, create the necessary directory structure:

5.3. Start MySQL:

5.4. Confirm MySQL’s status:

Step 6: Verification

6.1. Verify the new data directory is in use by starting the MySQL monitor:

6.2. Query for the value of the data directory:

6.3. Confirm that MySQL is using the new location.

6.4. Exit the MySQL monitor and return to the command prompt:

6.5. After verifying data integrity, remove the backup data directory:

6.6. Restart MySQL one final time and confirm it’s working as expected:

If the “Active” line states “active (running),” this confirms that MySQL is operational.

Conclusion

The MySQL data directory has been successfully relocated to a new location. This process ensures data integrity and provides the flexibility to manage data storage efficiently.