Thursday, September 09, 2010

Using NFS mount to sort out a diskspace outage issue on MySQL server

Using NFS mount to sort out a diskspace outage issue on MySQL server

Identify the if the diskspace is full

[root@mysql01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 7.5G 7.1G 0 100% /
/dev/sda1 289M 17M 258M 6% /boot
tmpfs 1006M 0 1006M 0% /dev/shm

This clearly shows that the MySQL server has no diskpace available.

Identify the folder that stores the innodb files

[root@mysql01 ~]# lsof | grep mysql

[root@mysql01 ~]# lsof /var/lib/mysql

[root@mysql01 ~]# du -h ibdata1

[root@mysql01 ~]# lsof -p 10928

[root@mysql01 ~]# lsof -p 10928 | grep /var/lib/mysql

Stop the MySQL service

[root@mysql01 ~]# service mysqld stop

Create a NFS mount on a NFS Server 192.168.yyy.zzz eg. /vol/mysql more than your current MySQL data and mount the same to some local folder in your system.

[root@mysql01 ~]# mkdir -p /tmp/mysql

[root@mysql01 ~]# mount -o tcp,rsize=32768,wsize=32768,hard,intr,timeo=600 192.168.yyy.zzz:/vol/mysql /tmp/mysql

Chage the folder permissions

[root@mysql01 ~]# cd /tmp/

[root@mysql01 ~]# chown -Rf mysql:mysql mysql

Move all the data from the /var/lib/mysql directoru to the temporary folder/tmp/mysql/

[root@mysql01 ~]# cd /var/lib/mysql

[root@mysql01 ~]# mv ibdata1 /tmp/mysql/
[root@mysql01 ~]# mv ib_logfile0 /tmp/mysql/
[root@mysql01 ~]# mv ib_logfile1 /tmp/mysql/
[root@mysql01 ~]# cp -R mysql /tmp/mysql/
[root@mysql01 ~]# rm -rf mysql

Change the directory permissions.

[root@mysql01 ~]# chown -Rf mysql:mysql mysql

Unmount the NFS mount from the temporary location.

[root@mysql01 ~]# umount /tmp/mysql

Mount the NFS mount with the MySQL data as the original location, /var/lib/mysql.

[root@mysql01 ~]# mount -o tcp,rsize=32768,wsize=32768,hard,intr,timeo=600 192.168.yyy.zzz:/vol/mysql /var/lib/mysql

[root@mysql01 ~]# service mysqld start

Add the following line to your /etc/fstab file

[root@mysql01 ~]# vi /etc/fstab

192.168.yyy.zzz:/vol/mysql /var/lib/mysql nfs proto=tcp,rsize=32768,wsize=32768,hard,intr,timeo=600 0 0

Enjoy !

No comments: