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.