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