Tag Archives: perl

11 Jan

Simple Shell Script to Monitoring Disk Space on a Linux Machine

This article explain a way to get a mail as soon as the disk usage reaches to its critical level to avoid issues later. To set a simple monitor on Linux / Unix, I have two simple scripts:

  • DSAlert.sh : Shell script for retrieving disk space percentages and put in to a cron job.
  • DiskSpace_Alert.pl : Perl script for sending an email.

Following is the disk monitor shell script which will execute the perl file for sending alert email.

#DSAlert.sh
#!/bin/sh
#Retrive disk space info
df=`df -Pl  | grep "^/dev" | awk '{print $5, $6}' | sed "s/%//"`
#Reference: df
echo “$df” | while read percent fs
do
#If Disk Usage increases above 90% send an email.
if [ $percent -ge 90 ] ; then
#Calling a perl file with parameters for sending an email.
`/opt/lampp/bin/perl DiskSpace_Alert.pl $fs is $percent percent full`
fi
done

Perl Script for sending an email:

# DiskSpace_Alert.pl
#!perl
use MIME::Lite;

#Reference : Mime-Lite

# set up email
$to = “my-mail-id\@domain.com”;
$from = “Diskmonitor\@ServerName.com”;
$subject = “Disk_Alert”;
$message = “Disk Space issue.\nActions Required:\n”.”@ARGV”;
# send email
email($to, $from, $subject, $message, $file);

# email function
sub email
{
# get incoming parameters
local ($to, $from, $subject, $message, $file) = @_;
# create a new message
$msg = MIME::Lite->new(
From => $from,
To => $to,
Subject => $subject,
Data => $message);
# send the email

MIME::Lite->send(‘smtp’, ‘localhost’, Timeout => 60);
$msg->send();
}
Setting up a crontab for monitoring:

Crontab –e
* * * * * sh  full/path/to/DSAlert.sh

And we’re done with the Linux/Unix Disk Monitoring alerts.

Instructions for setting up timing for crontab:

+----------------> minute (0 - 59)
|  +-------------> hour (0 - 23)
|  |  +----------> day of month (1 - 31)
|  |  |  +-------> month (1 - 12)
|  |  |  |  +----> day of week (0 - 6) (Sunday=0 or 7)
|  |  |  |  |
*  *  *  *  *

01 Jan

Perl Script for Analyze – Optimize – Repair Mysql Databases

The perl script is mainly created to avoid manual Mysql Server Maintenance. The script uses Perl module DBI. You need to provide access credentials and database name(optional). Regarding Analyse, Optimize and Repair you may ofcourse refer dev.mysql.com.

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk –recover tbl_name. REPAIR TABLE works for MyISAM and for ARCHIVE tables.

ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM and BDB. For InnoDB the table is locked with a write lock. This statement works with MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk –analyze.

The perl script is mainly created to avoid manual Mysql Server Maintenance. The script uses Perl module DBI. You need to provide access credentials and database name(optional). Regarding Analyse, Optimize and Repair you may ofcourse refer dev.mysql.com.
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.
REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk –recover tbl_name. REPAIR TABLE works for MyISAM and for ARCHIVE tables.
ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM and BDB. For InnoDB the table is locked with a write lock. This statement works with MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk –analyze.

use DBI;
my $username;
my $password;
my $hostname;
my $dbName;
my $databasez;
my $withRepair = 0; #1=enabled ##Also checks and repairs the table if required.
$username='USERNAME';
$password='PASSWORD';
$hostname='HOSTNAME';
$dbName=''; #(Optional) considers all if left blank
$databasez='';
$dbName ='';
my $port = 3307;
my $dbh;

$dbh = DBI->connect("dbi:mysql:database=$dbName;host=$hostname;$port", trim($username), trim($password)) or die "$DBI::errstr";

if(length(trim($databasez)) == 0)
{
my $db_SQL="SHOW DATABASES;";
$sth=$dbh->prepare($db_SQL);
$sth->execute();

while($databasez = $sth->fetchrow_array())
{
if ((trim($databasez) eq "information_schema") or (trim($databasez) eq "mysql")) {
next;
}
tablesCount($databasez);
}
}
else {
tablesCount($databasez);
}

sub trim($)
{
my $string = shift;
$string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string;
}

sub execute($)
{
my $sth_analyze_table = $dbh->prepare(shift);
$sth_analyze_table->execute() or $dbh->errstr;
my $status2 = $sth_analyze_table->fetchrow_array();
printf "%35s %10s %10s\n",$tablez,$status,$status2;
}

sub tablesCount($)
{
my $Line = "-----------------";
printf "\n%20s\n",$Line;
printf "\nDatabase:%35s\n",$databasez;
printf "\n%35s %10s %10s\n","Table Name" ,"Status", "Repair Status";
printf "\n%20s\n",$Line;
my $tables_SQL = "SHOW TABLES FROM $databasez;";
my $sth_table=$dbh->prepare($tables_SQL);
$sth_table->execute();
while($tablez = $sth_table->fetchrow_array())
{
my $analyze_tables_SQL = "ANALYZE TABLE $databasez.$tablez;";
my $optimize_table_SQL = "OPTIMIZE TABLE $databasez.$tablez;";
my $check_tables_SQL = "repair table $databasez.$tablez;";

execute($analyze_tables_SQL);
execute($optimize_table_SQL);

if ($withRepair == 1)
{
my $check_tables_SQL = "check table $databasez.$tablez;";
my $sth_check_table=$dbh->prepare($check_tables_SQL);
$sth_check_table->execute() or $dbh->errstr;
my $status = $sth_check_table->fetchrow_array();

if(trim($status) ne 'OK')
{
print "Attempting Repair: $databasez.$tablez \n";
my $check_tables_SQL = "repair table $databasez.$tablez;";
execute($check_tables_SQL);
my $status2 = $sth_check_table->fetchrow_array();
printf "%35s %10s %10s\n",$tablez,$status,$status2;
}
else
{
printf "%35s %10s %10s\n",$tablez,$status,"NA";
}
}
}
}

Download mysql-maintenance.pl

-- Kedar Vaijanapurkar --
↑