How to change MySQL Data Directory on Ubuntu

#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.

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 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 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. I hope will this your helpful. Thank you for reading the DevopsRoles page!