Determine Sql Usage For Cpanel Accounts


This quick and dirty script (cleaner code will come as I work on it more) was needed to email users who were using over 900MB on their MySQL databases. The information_schema database was lacking in the more accurate sizes, so "stat" was used instead.

As usual, I'm not responsible for the misuse of this script or any kind of damage it may cause.

#!/bin/bash if [[ $UID != 0 ]]; then echo "You need to be root to run this script!"; exit 1; fi if [[ $1 == "recall" ]]; then rawFile=$2 file=$(stat --printf="%n:%s" $rawFile) IFS=":" fileArray=($file) fileSize=${fileArray[1]} humanReadable=$(awk 'BEGIN{printf("%0.0f", '$fileSize' / (1024 * 1024))}'); fileName=${fileArray[0]##*/} fileNameTrim=${fileName%.*} IFS="/" pathArray=($fileArray[0]) userName=${pathArray[4]} if [[ $humanReadable -ge 900 ]]; then sql1=$(mysql -D WHMCS_DATABASE -e "SELECT userid FROM tblhosting WHERE username = '"$userName"';" --silent | grep -v userid) sql2=$(mysql -D WHMCS_DATABASE -e "SELECT email FROM tblclients WHERE id = '"$sql1"';" --silent | grep -v email ) if [[ -z $sql1 || -z $sql2 ]]; then echo -e $fileNameTrim"\t"$humanReadable"MB \t "$userName"\t"$sql1"\t"$sql2"\tCouldn't determine email address; email not sent!" exit 0 else echo -e $fileNameTrim"\t"$humanReadable"MB \t "$userName"\t"$sql1"\t"$sql2"\tEmail sent!" echo -e "Hello!\n\nYou appear to have a very large database! The database name is "$fileNameTrim" on the hosting account "$userName" and it is currently taking up "$humanReadable"MB. You'll need to trim this database down a bit!\n\nIf you have any issues, please open a support ticket." | /bin/mail -s "Large database discovered!" "$sql2" exit 0 fi fi else find /backup/cpbackup/daily/*/mysql/*.sql -exec ./ recall '{}' \; fi