Perl script for checking MySQL database disk space usage (in GB)

#!/usr/bin/perl

# database size verification
 use DBI;
 use Getopt::Long;

#Commandline arguments
 #my @cmdline = @ARGV;
 #my $counts =@cmdline;

sub usage {
 print STDERR @_, “n” if @_;
 die <<EOF;
 Usage: [ options ]
 Options:
 -?,  –help             show this help
 -h,  –host=…         connect to host
 -u,  –usr=…          user for login if not current user
 -p,  –password=…     password to use when connecting to server
 -s,  –socket=…       socket to use when connecting to server
 -d,  –database=…     Database which is having the table
 -t,  –table=…        Table name
 EOF
 }

my %opts = ();
 GetOptions(%opts, “help|?”, “table|t=s”,
 “host|h=s”, “usr|u=s”, “password|p=s”,
 “socket|s=s”,”database|d=s”
 );
 if ($opts{help}) {
 usage();
 exit 1;
 }

my $dbh = DBI->connect(“DBI:mysql:database=information_schema:host=$opts{host}”,$opts{usr},$opts{password})
 or die “Use <file name> –help for more optionsn”;
 my $sth_engine = $dbh->prepare (“SELECT IFNULL(B.engine,’Total’) ‘storage_engine’, CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),’,',”),17,’ ‘),’ ‘,SUBSTR(‘ KMGTP’,pw+1,1),’B') ‘data_size’, CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),’,',”),17,’ ‘),’ ‘,SUBSTR(‘ KMGTP’,pw+1,1),’B') ‘index_size’, CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),’,',”),17,’ ‘),’ ‘,SUBSTR(‘ KMGTP’,pw+1,1),’B') ‘table_size’ FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN (‘mysql’,'information_schema’) AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize”);

my $sth_db = $dbh->prepare (“SELECT dbname,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,’ ‘),’ ‘,SUBSTR(‘ KMGTP’,pw+1,1),’B') ‘data_size’,CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,’ ‘),’ ‘,SUBSTR(‘ KMGTP’,pw+1,1),’B') ‘index_size’,CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,’ ‘),’ ‘,SUBSTR(‘ KMGTP’,pw+1,1),’B') ‘total_size’ FROM (SELECT IFNULL(DB,’All Databases’) DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,index_length XSize,data_length+index_length TSize FROM information_schema.tables WHERE table_schema NOT IN (‘mysql’,'information_schema’)) AAA GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize)”);

$sth_engine->execute();

# Taking the table values into an array
 print “nn”;
 print sprintf (“%-40.35s %-20.35s %-30.35s %-35.35sn”,”Storage Engine”,”Data Size”,”Index Size”,”Table Size”);
 print  “-” x 103, “n”;

while(($storage_engine,$data_size,$index_size,$table_size) = $sth_engine->fetchrow_array){
 printf  (“%-30.35s %15s %-30.35s %15sn”,$storage_engine,$data_size,$index_size,$table_size);
 } #end of while
 print  “-” x 103, “n”;
 print “nnn”;
 $sth_db->execute();
 print sprintf (“%-40.35s %-20.35s %-30.35s %-35.35sn”,”Database Name”,”Data Size”,”Index Size”,”Total Size”);
 print  “-” x 103, “n”;

while(($dbname,$data_size,$index_size,$total_size) = $sth_db->fetchrow_array){
 printf  (“%-30.35s %15s %-30.35s %15sn”,$dbname,$data_size,$index_size,$total_size);
 } #end of while
 print  “-” x 103, “n”;
 print “nnn”;