How to change database directory in MySQL server

Sometime, we need to change MySQL data directory due to low space on the file system, save the actual data on any dedicated partition or any network drives and enhance MySQL database performance etc. Mostly in Linux based servers, MySQL uses /var/lib/mysql directory as default data directory. Before do anything, take a cold backup or use MySQL dump to take backup for databases. To change database location please follow these steps:

Stop MySQL using the following command:

[root@linuxcnf ~]# /etc/init.d/mysqld stop

Copy the existing data directory /var/lib/mysql to the new path using the following command:

[root@linuxcnf ~]# cp -p /var/lib/mysql /data/mysqldata/ #chown –R mysql.mysql /data/mysqldata/

Edit the MySQL configuration file with the following command:

[root@linuxcnf ~]# vi /etc/ my.cnf

Find the entry for datadir, and change the path from default path to /var/lib/mysql to the new data directory path.

[mysqld] #datadir=/var/lib/mysql datadir=/data/mysqldata/mysql #socket=/var/lib/mysql/mysql.sock socket=/data/mysqldata/mysql/mysql.sock

Start MySQL service with the following command:

[root@linuxcnf ~]# /etc/init.d/mysql restart

Now login to MySQL server and check the data!!!