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 !
