How to move your MySQL database directory

How to move your MySQL database directory

If you have a dedicated database server and the partition with the MySQL database fills up (or starts getting low), you can move your MySQL databases to a different location.

Let’s say you want to move the database to /home/mysql

1. The first step is to stop MySQL so that all your data
gets copied correctly.
$ /etc/rc.d/init.d/mysql stop

2. Create the new database directory in the new location
$ mkdir /home2/mysql

(At this point many people would use the move command (mv) instead of the copy command (cp). I ALWAYS copy the files… so that I can go back and undo my changes later!)

3. Copy the database files from the first HDD to the second HDD
$ cp -R /var/lib/mysql/ /home/mysql

4. Set the correct owner and group, permissions of the new database directory
$ chown -R mysql.mysql /home/mysql/

5. Rename your old database directory
$ mv /var/lib/mysql/ /var/lib/mysql_old

6. Create a symbolic link from the old database directory to the new one for any programs that rely on the default location
$ ln -s /home/mysql/ /var/lib/mysql

7. Set the correct owner and group on the symbolic link
$ chown mysql.mysql /var/lib/mysql

8. Edit the configuration file (/etc/my.cnf) to update the changes

Comment out the old settings and add a line for the new one as you can see below

[mysqld]
#datadir=/var/lib/mysql
datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock

save my.cnf and exit your text editor

9. Check on the new mysql directory path /home/mysql/mysql the “host.MDI” files.

10. Check the file permissions

11. Restart MySQL
$ /etc/rc.d/init.d/mysql start

12. Update the AppArmor configuration file (if necessary).

13. If MySQL refuses to start look at your mysql error log for the reason

==============================================================>

It’s works for me 🙂

Reference http://www.eth0.us/mysql-dir

Advertisements

One thought on “How to move your MySQL database directory

  1. Hi,

    If I’m not wrong this procedure is suitable to move the whole MySQL RDBMS server from one FS to another. Is there any way or similar procedure to just copy an individual MySQL DB instance from one FS to another without the need of relocating the whole DB engine?

    Thanks and regards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s