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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>