Move MySQL Directory to Another Location

Lets say your MySQL data has fully utilized the disk space and you have another new hard disk specifically mounted for this purpose, you will need to migrate the data over to this new location. By default, MySQL will occupied following locations in the server:

    Data -/var/lib/mysql
    Config – /etc
    Log -/var/log

We will not migrate data in /etc or /log because it usually not required much space. I will use following variables:

    Server OS: CentOS 5.6 64bit
    Old MySQL data: /var/lib/mysql
    New MySQL data: /mnt/disk1/mysql

Warning: You MUST able to afford downtime on MySQL service to do this!

1. First of all, we need to make sure the service is stopped (if you are running cPanel, please disable MySQL monitoring under WHM > Service Manager > Untick ‘monitor’ for MySQL):

[root@centos ~]# service mysql stop

2. Make sure there is no MySQL services running on the server:

[root@centos ~]# ps aux | grep mysql | egrep -v 'grep'

3. Lets copy over the data directory. We are using rsync command so we can see the copy progress with estimate time remaining (this is really good for monitoring), and also preserve the permission and ownership of the MySQL directory:

[root@centos ~]# rsync -avpP /var/lib/mysql /mnt/disk1/mysql

4. Now we need to tell MySQL to look at new data directory. Open /etc/my.cnf via text editor and change following server options:

[mysqld]
......
datadir=/mnt/disk1/mysql
socket=/mnt/disk1/mysql/mysql.sock
......

5. Now lets start back the MySQL with this new settings:

[root@centos ~]# service mysql start

If everything are running back as expected, you can then delete the old /var/lib/mysql directory so you can save more free space.

Source : http://blog.secaserver.com/2011/07/move-mysql-directory-to-another-location/

====================XXXXXXXXXXXXXXXX========================

How to symlink /var/lib/mysql if /var mount becomes full

---

Most of you may already know how to do this, so forgive if this is too simple, but this may be helpful for some dedicated server owners whose /var mount (some hosts use separate partitions for /usr, /var, /home etc) has become full, which is usually do to large databases in /var/lib/mysql. There is usually plenty of room in /the /home partition. This will work on cPanel servers as well.

This simple tutorial lays out the steps involved to copy contents of /var/lib/mysql to /home and then symlink /var/lib/mysql to /home/mysql. Of course adjust accordingly if moving/symlinking to a different mount or directory.

1) Go to webpage that uses mysql to verify working pre-copy

2) Stop mysql service (I will usually also stop cpanel & http service just to be safe).

/etc/init.d/mysql stop
/etc/init.d/httpd stop
/etc/init.d/cpanel stop

3) Do the copy:

rsync -av /var/lib/mysql /home/

4) Verify copied folder same size as source with du:

du -sh /var/lib/mysql
du -sh /home/mysql

5) Move /var/lib/mysql to /home/mysql-bak (essentially removing /var/lib/mysql because the folder must not exist when symlink is created)

mkdir /home/mysql-bak
mv /var/lib/mysql /home/mysql-bak/

6) Create symlink:

ln -s /home/mysql /var/lib/mysql

7) Verify the symlink is correct when listing the contents of new symlinked /var/lib/mysql (you can refer to the rsync copy to make sure the paths are the same - they should be)

8) Restart mysql, httpd, cpanel

9) Verify mysql-reliant webpage is loading without issue

10) You should now have freed up space on /var. Verify with 'df -h'

 

Source : http://www.webhostingtalk.com/showthread.php?t=1086919