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.

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