Changing Location of MySql DataStore

On one of our projects, we had to store the MySql Data at a location different from the default location. This was needed because the server was on an instance-store Amazon instance which meant that the data would get lost if the instance had to be rebooted for some reason. After going through the tutorials and articles on AWS, we came across this excellent piece on how to setup MySql Data store on an EBS volume.

Later on, I tried out the steps on the local machine and see if we could move the MySql data store to some other location on our system. As it turned out, we could and here are the steps I followed.

Stop mysql server running on your system with the command

sudo /etc/init.d/mysql stop

Now, create folders named etc, lib and log at any location where you wish to save your mysql data. Let us keep them inside a folder called “/mysqldata”

mkdir etc lib log

Now, back-up your mysql data by copying the contents of /etc/mysql, /var/lib/mysql and /var/log/mysql to another location. Then, move the folders /etc/mysql, /var/lib/mysql and /var/log/mysql to the “/mysqldata”

mv /etc/mysql /mysqldata/etc
mv /var/lib/mysql /mysqldata/lib
mv /var/log/mysql /mysqldata/log

Let us now create new folders /etc/mysql, /var/lib/mysql, /var/log/mysql

mkdir /etc/mysql
mkdir /var/lib/mysql
mkdir /var/log/mysql

Now these new directories need to be mapped with the ones we moved to “/mysqldata”
This is done by setting the mount point for each of these directories to the ones which we created.
To do so, appending the following entries to “/etc/fstab”

mkdir /etc/mysql
/mysqldata/etc/mysql /etc/mysql none bind
/mysqldata/lib/mysql /var/lib/mysql none bind
/mysqldata/log/mysql /var/log/mysql none bind

With this done, let us mount all the entries by issuing the command

mount -a

MySql data store is now at another location.

Hope this helps