How to change Mysql data directory location?

Mysql is most famous open source RDBMS (Relational database management system) software used by millions of websites. Most of website with very large Mysql database experience storage problem because /var partition is to small which holds Mysql data files and folder by default. If you are experiencing such a problem then you have to move your Mysql data directory to other linux partition (eg: /home). To change your directory without affecting Mysql configuration. Follow these steps.

• First stop Mysql service

/etc/init.d/mysqld stop

• create a folder ‘mysql’ in /home partition
• copy whole Mysql data directory to ‘mysql’ in preserve mode

cp –pr /var/lib/mysql/ /home/mysql

-p means preserve the specified attributes (default: mode, ownership, timestamps)

-r means copy each directory sub-directory and files

• then create a symbolic link from new data directory to old data directory

ln -s /home/mysql/ /var/lib/mysql/

• Now start Mysql service

/etc/init.d/mysqld start

• If its working properly then delete each file and folder from old data directory

cd /var/lib/mysql rm –rf  *

Note: before changing Mysql data directory please backup all your database