How to change MySQL Data Directory on Ubuntu: A Step-by-Step Guide

Introduction

In this tutorial, How To Move a MySQL Data Directory to a New Location on Ubuntu. I will change MySQL Data Directory on Ubuntu. Managing databases efficiently is crucial for any application, and sometimes this involves changing the data directory for MySQL. Whether you need to move your MySQL data to a different partition for better performance or to manage storage, this task can seem daunting.

This guide will walk you through the process of changing the MySQL data directory on an Ubuntu system, ensuring your database remains secure and operational. Let’s dive in and make this transition smooth and straightforward.

Change MySQL Data Directory on Ubuntu

Identify Current MySQL Data Directory

mysql -u username -p -e “SELECT @@datadir”

For example, the current data directory is ‘/var/lib/mysql‘

Stop MySQL service

service mysql stop

Backup existing MySQL data directory or copy recursively the contents of ‘/var/lib/mysql’ to ‘/data/mysql-data’

tar -cvf mysql.tar /var/lib/mysql
#or
cp -rap /var/lib/mysql/* /data/mysql-data

Create a new data directory with proper permissions

mkdir -p /data/mysql
chown mysql:mysql /data/mysql

Using the Rsync command Migrate existing data into the new location

nohup rsync -avp /var/lib/mysql/ /data/mysql

Configure the new MySQL Data Directory

Edit the MySQL default configuration file /etc/my.cnf

datadir = /data/mysql

Change the AppArmor data directory in file /etc/apparmor.d/usr.sbin.mysqld

# Replace the lines beginning with /var/lib/mysql into /data/mysql
:%s/\/var\/lib\/mysql/\/data\/mysql/g

Start MySQL service

service mysql start

Verify the location change of the new data directory as the command follows

mysql -u username -p -e “SELECT @@datadir”

Checking issue during MySQL startup check MySQL log file /var/log/mysqld.log for any errors.

Conclusion

You have change MySQL Data Directory on Ubuntu. By following this guide, you have learned how to safely relocate your MySQL data, ensuring your system’s efficiency and reliability. Regular maintenance and updates, along with proper data management, are key to sustaining the performance and security of your MySQL databases. Keep these practices in mind to maintain a robust and scalable database environment. I hope will this your helpful. Thank you for reading the DevopsRoles page!