Change MySQL default Data Directory to New Location in Linux

When we install MySQL on Linux the default MySQL database created under /var/lib/mysql directory. If your database is small this might be OK because it does not take much space. But for larger database I will suggest to change the default data directory of MySQL to different locations or you can also use separate hard disk for database. In this tutorial I am going to explain how we can change MySQL default data directory to new location.

Follow the below steps to change MySQL default data directory.

Stop MySQL Service

Before doing anything make sure MySQL service is stopped follow the below command to stop MySQL service

# service mysqld stop
OR
# /etc/init.d/mysqld stop

Take Current MySQL Database Backup

After stopping the MySQL service, I will suggest take a backup of your current MySQL database.You can find the current MySQL database under /var/lib/mysql directory. For backup copy this directory to safe location.

# cp -r /var/lib/mysql /opt/backup/mysql

Or, you can also use mysqldump to take MySQL database backup.

# mysqldump -u root -ppassword --all-databases > /opt/backup/all-database-bkp.sql

Change MySQL Data Directory to New Location

In my case there are no much space in my root partition that’s why I have added another hard drive and I have /mysql_data partiton on /dev/sdb1 disk which has lot of space.

Now I am going to move MySQL database from / partition to /mysq_data partition.

# mkdir -p /mysql_data/var/lib

# cd /var/lib

# mv mysql /mysql_data/var/lib

If you have copied MySQL data directory instead of move, you will need to change the ownership on new directory. Follow the below command to change ownership.

# chown -R mysql:mysql /mysql_data

Update my.cnf Default Configuration File

Open the MySQL /etc/my.cnf default configurations and modify datadir and socket variable like below.

# vim /etc/my.cnf

#Change From:
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#Change To:
datadir=/mysql_data/var/lib/mysql
socket=/mysql_data/var/lib/mysql/mysql.sock

Start MySQL Service

# service mysqld start
Starting mysqld: [ OK ]

If you are facing any issue during MySQL service startup check its log file /var/log/mysqld.log for error.

I hope this article will be helpful to change MySQL default data directory to new location. Read our another article Check and Update max_connections value to fix MySQL Error: Too many connections and Types of Mysql Backup. If you have any queries and problem please comment in comment section or you can also ask your question.