#!/bin/bash

#Created by Henry Saptono <henry[at]nurulfikri.com>, April 2007
#This script will check database quota and if quota excedeed then disable privileges insert & create for those database
#Use cron to check quota every 5 mninutes
#*/5 * * * * checkquotaMysqlDb.sh 
#Lisensi GPL
MYSQL_ROOT='root'
MYSQL_ROOT_PW='password'
MYSQL_DB='mysql'
MYSQL_DIR='/var/lib/mysql/'
#MYSQL_QUOTA_DAT is variable of file data dbquota.dat
#the dbquota.dat containing data with form like:
# database-name:size-of-database
# example entry in /etc/dbquota.dat below:
#  <dbname>:<quota size in byte>
#  dbdps:10000000
#  dbrojulman:5000000
#  dbsdm:40000000
MYSQL_QUOTA_DAT='/etc/dbquota.dat'
MYSQL_LOG_DBQUOTA='/var/log/mysql-dbquota.log'
MYSQL_LOCK_DBSTATUS='/var/run/mysql_lock_status'
softquota=95
hardquota=100

if [ ! -d "$MYSQL_LOCK_DBSTATUS" ];
then
      echo "`date` :  Directory $MYSQL_LOCK_DBSTATUS doesn't exist" >> $MYSQL_LOG_DBQUOTA
      echo "`date` :  Create Directory $MYSQL_LOCK_DBSTATUS" >> $MYSQL_LOG_DBQUOTA
      mkdir -p  $MYSQL_LOCK_DBSTATUS

fi
if [ -f "$MYSQL_QUOTA_DAT" ];
then

      for x in `cat /etc/dbquota.dat|grep -v ^#`;
      do
	if [ -n "$x" ];
	then
		dbname=`echo $x|cut -f1 -d:`
		dbsize=`echo $x|cut -f2 -d:`
		cursize=`du -sb $MYSQL_DIR$dbname  |cut -f1 -d"/"`
                quotasize=`expr $cursize '*' 100 '/' $dbsize`
		echo "DBname: $dbname, quota : $dbsize byte, $quotasize %";
		#echo "du -sb $MYSQL_DIR$dbname"
                #softquota="expr $cursize '*' 100 '/' $dbsize"
                #echo "$quotasize %"
		if [ $quotasize -lt $softquota ];
		then
			
			if [  -f "$MYSQL_LOCK_DBSTATUS/$dbname.lock" ];
			then
			 	rm -rf $MYSQL_LOCK_DBSTATUS/$dbname.lock
				mysql -u $MYSQL_ROOT -p$MYSQL_ROOT_PW $MYSQL_DB -e "update db set  Insert_priv='Y', Create_priv='Y' where DB='$dbname'; flush privileges"	
			fi	
		fi

		if [ $quotasize -gt $softquota ] && [ $quotasize -lt $hardquota ];
		then
			echo "`date` : Warning: Size of $dbname Database   now >= $softquota % of quota" >> $MYSQL_LOG_DBQUOTA ;
		fi
		if [ $quotasize -gt $hardquota ];
		then
			echo "`date` :Fatal: Size of $dbname Database excedeed $hardquota % of quota " >>  $MYSQL_LOG_DBQUOTA ;
			if [ ! -f "$MYSQL_LOCK_DBSTATUS/$dbname.lock" ];
			then
			 	touch $MYSQL_LOCK_DBSTATUS/$dbname.lock
				mysql -u $MYSQL_ROOT -p$MYSQL_ROOT_PW $MYSQL_DB -e "update db set  Insert_priv='N', Create_priv='N' where DB='$dbname'; flush privileges"	
			fi	
		fi
	
	else
		echo "NO Entri in /etc/dbquota.dat";
	fi
      done

else
   echo "ERROR : Mysql Data quota file doesn't exist";
   exit 1;
fi