How To Move a MySQL Data Directory to a New Location on Ubuntu 20.04?

Introduction:

Moving a MySQL data directory to a new location can be necessary when the original storage is limited, or we want to optimize database performance by using faster storage.

Precautions:

1. Before making any changes, back up MySQL data to avoid accidental loss or corruption.

Step 1:

To prevent data corruption, stop the MySQL service before moving files

sudo systemctl stop mysql

Step 2:

Assume the current MySQL data directory is located at /var/lib/mysql, and want to move it to /new/mysql_data. Use the following command to copy all data to the new location.

rsync -av /var/lib/mysql /new/mysql_data

Step 3:

Next, configure MySQL to use the new data directory by editing the MySQL configuration file.

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

Locate the datadir parameter, which by default points to /var/lib/mysql

datadir = /var/lib/mysql

Change it to new data directory path

datadir = /new/mysql_data

Step 4:

If we are on a system with AppArmor (common on Debian and Ubuntu), need to update AppArmor’s settings to allow MySQL access to the new directory.

Edit the AppArmor configuration file for MySQL

sudo vi /etc/apparmor.d/usr.sbin.mysqld

Add the new path so AppArmor permits MySQL to read/write there

/new/mysql_data/ r, /new/mysql_data/** rwk,

Save and reload AppArmor to apply the changes

sudo systemctl restart apparmor

Step 5:

Ensure that the new MySQL directory has the right ownership and permissions so that MySQL can access it properly

sudo chown -R mysql:mysql /new/mysql_data

Step 6:

Once the above steps are completed, restart the MySQL service to apply the changes

sudo systemctl start mysql

Step 7:

Log into MySQL to confirm that it is using the new data directory. Run the following command

mysql -u root -p SHOW VARIABLES LIKE ‘datadir’;

Conclusion:

Relocating the MySQL data directory on Ubuntu 20.04 can improve performance and free up space in the system’s root directory.