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


Published on October 23, 2013 by Sunny Verma

Liked It? Get Free updates in your Email

Delivered by feedburner

comments powered by Disqus