/var partition got full ? mysql data increasing ? move it out.

Today as usual i'm logon to my server and put some command to monitor my server status. i try to lookup the hdd used for each partition and saw :

Filesystem Size Used Avail Use% Mounted on
/dev/sda5 2.0G 1.5G 441M 77% /
/dev/sda1 99M 14M 80M 15% /boot
none 489M 0 489M 0% /dev/shm
/dev/sda8 205G 43G 152G 22% /home
/dev/sda7 2.0G 40M 1.9G 3% /tmp
/dev/sda2 15G 5.6G 8.2G 41% /usr
/dev/sda3 2.5G 2.3G 0 100% /var
/tmp 2.0G 40M 1.9G 3% /var/tmp

Huh~ my /var just got full.

I just remember that my mysql data was in there /var/lib/mysql. If i let it go the mysql will get write error and the daemon should not working properly.

First i try to use easy way to copy all the data to other partition into /home/mysql/

then i edit the /etc/my.cnf and put datadir = /home/mysql

then i restart the mysql.

Suddenly all goes wrong. My mysqld does not work anymore and could not be start.
It's about half and hour i struggle to settle this problem and i found the best solution to make this happen smoothly.


First edit the my.cnf:

pico -w /etc/my.cnf

Now in the mysqld section add the following:

pid-file = /home/mysql/mysqld.pid
socket = /var/lib/mysql/mysql.sock

datadir = /home/mysql
basedir = /home/mysql


Now we are going to copy all of the data to the new partition. Notice that we do the copy TWICE, that is because moving 1gb++ of data can take some time and the tables may have changed. When we run it the second time we hopefully get it so that when the switch over happens there is very little, if any, lost data. If you can afford the downtime simply shut down mysql before running this command. If you cannot though running it twice then quickly copy/pasting the other commands is a valid substitute.

rsync -vrplogDtH /var/lib/mysql/ /home/mysql/
rsync -vrplogDtH /var/lib/mysql/ /home/mysql/

Now we need to setup the mysql.sock so that it operates correctly:
ln -s /home/mysql/mysql.sock /var/lib/mysql/mysql.sock
rm -rf /tmp/mysql.sock
ln -s /home/mysql/mysql.sock /tmp/mysql.sock

Restart mysql so it is on the new parition:
killall -9 mysqld
service mysql start

*Note* I do not show you deleting the /var/lib/mysql directory, go ahead and do that a few days after the move if you do not have good backups incase something went wrong. Make sure when you delete the /var/lib/mysql directory you recreate it so that the mysql.sock file can be created in the directory. Do the following to remove the old data and get the mysql.sock correctly set back up.

rm -rf /var/lib/mysql
mkdir /var/lib/mysql
chown mysql /var/lib/mysql
service mysql restart
ln -s /home/mysql/mysql.sock /var/lib/mysql/mysql.sock
rm -rf /tmp/mysql.sock
ln -s /home/mysql/mysql.sock /tmp/mysql.sock

Thats it, you are all done with moving mysql!