Updating MySQL Data Directory (dataDir) on Ubuntu

To move the MySQL data folder from the default path to a new location, such as /mnt/r-data/mysql, in MySQL 8 on Ubuntu 22.04, follow these steps:

1. Stop the MySQL service

Before making any changes, make sure to stop the MySQL service

sudo systemctl stop mysql

2. Create the new location for the data

Ensure the new path exists and that the permissions are correct.

sudo mkdir -p /mnt/r-data/mysql

3. Move the data to the new location

Copy all files and folders from the current data directory (/var/lib/mysql) to the new location.

sudo rsync -av /var/lib/mysql/ /mnt/r-data/mysql/

4. Change the ownership of the folder

Ensure that the files in the new location are owned by the mysql user and group.

sudo chown -R mysql:mysql /mnt/r-data/mysql

5. Configure MySQL to use the new path

Edit the MySQL configuration file to point to the new data location.

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

Find the line that begins with datadir and change it to point to the new path:

datadir = /mnt/r-data/mysql

6. Update AppArmor (if enabled)

AppArmor is an access control system that might be enabled on Ubuntu. If AppArmor is configured to restrict MySQL, you will need to update the configuration to allow access to the new path.

Edit the AppArmor profile file for MySQL:

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

Find the lines containing the old path and change them to point to the new path:

/mnt/r-data/mysql/ r,
/mnt/r-data/mysql/** rwk,

Then, reload the AppArmor profile:

sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

7. Start MySQL:

Once the changes are made, start MySQL again.

sudo systemctl start mysql

8. Verify the status of MySQL

Make sure that MySQL is running properly and is using the new data directory.

sudo systemctl status mysql

9. Clean up the old location (optional).

If everything is working correctly, you can delete the old data folder to free up disk space:

sudo rm -rf /var/lib/mysql

Remember, this step is optional and should only be done once you’re sure that the database is functioning correctly from the new location.

Conclusion

By following these steps, you will have successfully moved the MySQL data directory to the new location /mnt/r-data/mysql.

Yuniel Alvarez