2009-11-27

How to move your MySQL Database

I recently upgraded a server and installed a RAID5 set just to house the database running on that server. After a little research I found that it was really easy to move a MySQL database. Here is how you do it.

First off since I run CentOS this is going to be Red Hat/CentOS specific. However, you can use these methods on any distribution.

After you configure your RAID set or formatted your hard drive you want to move your MySQL database to you will need to mount it, lets say you mount it to /database and you already updated fstab.

If MySQL is running make sure you shut it down:
#/etc/rc.d/init.d/mysql stop
OR
#service mysql stop

Now unless you want to put your database in the root of /database you will want to create a sub-directory:
#mkdir /database/mysql

Copy your database files:
#cp -R /var/lib/mysql/ /database/mysql

Setup the correct permissions:
#chown -R mysql.mysql /database/mysql

Rename your old directory (or you can delete it):
#mv /var/lib/mysql/ /var/lib/mysql_OLD
OR
#rm -rf /var/lib/mysql

Update your my.cnf file with the path you moved your old database files to. You can either comment out the old lines or remove them:
[mysqld]
#datadir=/var/lib/mysql
datadir=/database/mysql
#socket=/var/lib/mysql/mysql.sock
socket=/database/mysql/mysql.sock

If your running version 4 or older of mysql then you will also need to update your basedir, otherwise don't worry about this step:
#basedir=/var/lib
basedir=/database

Now all you need to do is start MySQL and your done. If for some reason it doesn't start check your /var/log/mysqld.log for the reason.

On a side note if you have any programs that are particular of the MySQL path, then you will probably want to setup a soft link BEFORE you start-up mysqld.
#ln -s /database/mysql/ /var/lib/mysql

And set the appropriate permissions for the link:
#chown mysql.mysql /var/lib/mysql

No comments:

Post a Comment