How to Change Mysql Data Directory

Here I am going to move my default mysql data directory (/var/lib/mysql on RHEL5) to a new directory /mysqldata.

First, we must stop the mysql service before doing next step.

# /etc/init.d/mysql stop

we need to create a directory that will be the new databases location.

# mkdir /mysqldata

Now copy the existing data directory (/var/www/mysql ) to /mysqldata.

# cp –pvr /var/www/mysql/* /mysqldata/

Change the ownership of /mysqldata recursivly to mysql user and group.

# chown -R mysql:mysql /mysqldata

Now we need to make the following changes in /etc/my.cnf so that it can make point to new data directory.

# vim /etc/my.cnf

datadir = /mysqldata

socket = /mysqldata/mysql.sock

Finally start the mysql server

#/etc/init.d/mysql start

If mysql server start without any error or warning your data directory has been moved successfully. Now all the database files will be managed under new data directory, here is some steps to verify the same :-

Login to mysql server

#mysql –u root –pmysqlpassword

mysql> create database newdb;

mysql> exit;

Now list all the databases under /mysqldata directory.Mysql should store newly created datrabase under /mysqldata.

#ls -ltr /mysqldata

rw-rw---- 1 mysql mysql 10485760 2010-04-22 10:05 ibdata1

-rw-rw---- 1 mysql mysql 5242880 2010-04-22 10:05 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 2010-04-22 09:49 ib_logfile1

drwx------ 2 mysql mysql 4096 2010-04-22 09:49 mysql

srwxrwxrwx 1 mysql mysql 0 2010-04-22 10:05 mysql.sock

drwx------ 2 mysql mysql 4096 2010-04-22 09:49 test

drwx------ 2 mysql mysql 4096 2010-05-03 11:39 newdb (newly created database)