Calculating MySQL Database sizes in Linux

Checking regularly if you still have space left on your hard drive(s) is a mandatory procedure and can be painful if this procedure is done manually. To create an automated script that sends email (for example) to someone telling how much space the web folder, how big is the database or how much space left on the hard drive can be very useful.

Run this as sudo or any user that has access to all the folder listed

MySQL Disk Usage:

List of Databases disk usage (without the table definition - so data and index data) assuming the path of the MySQL data folder is: /var/lib/mysql

MyDatabase=`ls -l /var/lib/mysql/mydb/ | grep -v '.frm' | awk 'BEGIN { sum = 0 } { sum += $5 } END { print sum }'`
MyDatabase2=`ls -l /var/lib/mysql/mydb2/ | grep -v '.frm' | awk 'BEGIN { sum = 0 } { sum += $5 } END { print sum }'`
# etc...

You can omit the "grep -v '.frm'" if you want full database sizes (table definition, indexes and data)

Now the total:

let sumDB=$MyDatabase+$MyDatabase2

or (if you include the .frm file)

sumDB=`du -sPb /var/lib/mysql/ | awk '{print $1}'`

WWW Data Disk Usage:

List of the WWw data folder assuming the path of the WWW folder is: /var/www/

sumWWW=`du -sPb /var/www/ | awk '{print $1}'`

Total Disk Usage:

let sum=$TotalMysql+$sumWWW

Space left on the disk:

SpaceLeft=`df | egrep '^/dev/sda1' | awk '{print $4}'`

Build the report

Message="\nDisk usage (in bytes) @ `date`\nMySQL database - MyDatabase : $MyDatabase\nTotal MySQL: $TotalMysql\n\nWWW data: $sumWWW\n\nTotal: $sum\n\nSpace Left: $SpaceLeft"

Send it

echo -e $Message | mail -s "Disk Usage" "admin@domain.com"

Here's the full script:

#!/bin/bash

MyDatabase=`ls -l /var/lib/mysql/mydb/ | grep -v '.frm' | awk 'BEGIN { sum = 0 } { sum += $5 } END { print sum }'`
MyDatabase2=`ls -l /var/lib/mysql/mydb2/ | grep -v '.frm' | awk 'BEGIN { sum = 0 } { sum += $5 } END { print sum }'`

let sumDB=$MyDatabase+$MyDatabase2
#or
#sumDB=`du -sPb /var/lib/mysql/ | awk '{print $1}'`

sumWWW=`du -sPb /var/www/ | awk '{print $1}'`
let sum=$TotalMysql+$sumWWW

SpaceLeft=`df | egrep '^/dev/sda1' | awk '{print $4}'`

Message="\nDisk usage (in bytes) @ `date`\nMySQL database - MyDatabase : $MyDatabase\nTotal MySQL: $TotalMysql\n\nWWW data: $sumWWW\n\nTotal: $sum\n\nSpace Left: $SpaceLeft"
echo -e $Message | mail -s "Disk Usage" "admin@domain.com"

Make sure the script is executable, and you are done!