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

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”;

Leave a Reply

Your email address will not be published. Required fields are marked *