And here's a script that will do the check/repair for you. It has some
custom logging libs in it that you'll have to remove to make it work
(Admutil and Getpassword), but you'll get the general idea.
Alex Pilson wrote:
>
> Do MySQL server administrators recommend running a cron script daily
> to isamchk?
>
> Is there any other things that should be run daily to keep MySQL
> running top-notch? If so, does anyone have any pointers to a script
> that has already been developed?
>
> Thanks.
> --
#!/usr/local/bin/perl
#
# $Id: 60mysql,v 1.30 2002-04-07 09:55:24-04 mprewitt Exp $
# $Source: /usr/local/src/daily/0.1/RCS/60mysql,v $
# $Locker: $
# Copyright 2002 (c) Chelsea Networks, All rights reserved
use strict;
use Admutil;
use File::Basename;
use GetPassword (); # import no exports
use Getopt::Long;
my $opt_extended_check;
GetOptions(
'extended-check!' => \$opt_extended_check,
) || print STDERR "Usage: 60mysql [--extended-check]\n";
my $script = new Admutil;
my $script_name = basename( $0 );
$script->StartLog( -logdir=>"/var/adm/daily/$script_name", -private=>1,
-days=>14 );
$script->ErrMailTo("mail-oncall");
$script->StartSyslog(-facility=>"local7");
#
# End standard Admutil header here. Begin regular script.
#
# Look at comments in /usr/local/httpd/conf/wusage.conf.template for how
to
# configure things.
#
use DBI();
use Sys::Hostname;
my $hostname = hostname;
my $dir = "/export/DB/mysqldb";
unless ( -d $dir ) {
print "$script_name does't run on $hostname because it is not a mysql
server\n";
exit;
}
my $password = GetPassword::GetPassword( -context=>'mysql', -user=>'mysql'
) or
$script->Error( -level=>"CRIT",
-text=>"Unable to get password for mysql" );
my $username = "mysql";
#
# Database checks first.
#
my $day = (localtime)[6];
my $check_type = "FAST";
# extended check takes too long. run manually.
# if ($day == 0){
# #EXTENDED Do a full key lookup for all keys for each row.
# print "running extended check\n";
# $check_type = "EXTENDED";
# } else {
# #FAST Only check tables which haven't been closed properly.
# $check_type = "FAST";
# }
if ($opt_extended_check){
#EXTENDED Do a full key lookup for all keys for each row.
print "running extended check\n";
$check_type = "EXTENDED";
}
#
# First, connect to the dataserver.
#
my $dbh = DBI->connect( "DBI:mysql:mysql", $username, $password,
{'RaiseError' => 0 } )
||
$script->Error( -level=>"CRIT",
-text=>"Unable to connect to mysql [$DBI::errstr]" );
my $sth = $dbh->prepare( "SHOW DATABASES" );
$sth->execute ||
$script->Error( -level=>"CRIT",
-text=>"Unable to obtain list of databases
[$DBI::errstr]" );
while( my $db = ( $sth->fetchrow_array )[0] ) {
next if ( $db eq "binlogs" || $db eq "RCS" || $db eq "lost+found"
|| $db eq "hash" || $db eq "OUTPUT" );
print "Checking database $db\n";
my $sth = $dbh->do( "use $db" ) ||
$script->Error( -level=>"WARN",
-text=>"Unable to access $db for checking
[$DBI::errstr]" );
my $sth = $dbh->prepare('show tables')
|| $script->Error( -level=>"WARN",
-text=> "show tables prepare failed
[$DBI::errstr]" );
$sth->execute
|| $script->Error( -level=>"WARN",
-text=> "show tables execute failed
[$DBI::errstr]" );
my @tables = map $_->[0], @{ $sth->fetchall_arrayref };
if ( @tables ) {
foreach my $table ( @tables ) {
#
# if CHECK fails REPAIR TABLE should be run(look in sub
statement below)
#
my $sql_check = "CHECK TABLE $table $check_type";
my $sql_analyze = "ANALYZE TABLE $table";
my $sql_repair = "REPAIR TABLE $table";
print " table $table\n";
if( statement($sql_check) ) {
#
# Analyze it.
#
# statement($sql_analyze);
} else {
#
# Uh-oh...better repair it.
#
statement($sql_repair);
}
}
} else {
if ( $sth->errstr ) {
$script->Error(-level=>"WARN",
-text=> "Unable to enumerate tables from
database $db");
}
}
}
#
# Did the slave die?
#
sub check_slave {
if ( my $sth = $dbh->prepare('show slave status')) {
if ( $sth->execute ) {
return ( $sth->fetchrow_array )[0,4,5,6,10];
} else {
return $script->Error(-level=>"WARN",
-text=> "Unable to execute show slave
status
[$DBI::errstr]" );
}
} else {
return $script->Error(-level=>"WARN",
-text=> "Unable to prepare show slave status
[$DBI::errstr]" );
}
}
my( $host, $file, $pos1, $status, $error ) = check_slave;
if ( $host && $file && $status eq "No" ) {
$dbh->do( "SLAVE START" ) ||
$script->Error(-level=>"WARN",
-text=> "Unable to restart slave [$DBI::errstr]" );
#
# Make sure it started
#
sleep 120;
my( $host, $file, $pos2, $status, $error ) = check_slave;
if ( $host && $file && $status eq "No" ) {
$script->Error(-level=>"WARN",
-text=> "Slave start failed [$error]" );
}
if ( $pos1 != $pos2 ) {
$script->Error(-level=>"WARN",
-text=> "Slave started, but not incrementing
[$error]" );
}
}
$dbh->disconnect();
#
# Logfile rotation and aging
#
my ($updatecheck, $debugcheck, $binlogcheck, $slowcheck, $errcheck);
my $debug = "debug.out";
my $update = "update.out";
my $slow = "slow.out";
my $savedir = "$dir/OUTPUT";
unless ( -d $savedir ) {
mkdir( $savedir, 0750 ) ||
$script->Error( -level=>"CRIT",
-text=>"Failed to make $savedir [$!]" );
chown((getpwnam( "mysql" ))[2,3], $savedir ) ||
$script->Error( -level=>"CRIT",
-text=>"chown of $savedir failed [$!]" );
}
foreach my $file ( $debug, $update, $slow ) {
if ( -e "$dir/$file" ) {
rename( "$dir/$file", "$savedir/$file" ) ||
$script->Error( -level=>"WARN",
-text=>"Rename of $dir/$file into $savedir
failed [$!]" );
}
}
#
# Logs are aged, do the flush.
#
unless ( $dbh->do( "FLUSH LOGS" ) ) {
$script->Error( -level=>"WARN",
-text=>"Unable to flush mysql logs [$!]." );
}
#
# Now, age gracefully.
#
foreach my $file ( $debug, $update, $slow ) {
if ( -e "$savedir/$file" ) {
$script->AgeLog( -files=>"$savedir/$file",
-save=>30,
-notouch=>1 ) ||
$script->Error("Unable to age mysql
$savedir/$file file
[$!].");
}
}
sub statement {
my $statement = shift;
my $sth;
my $return_type;
my $result;
$sth = $dbh->prepare($statement) ||
return $script->Error( -level=>"WARN",
-text=>"$statement failed [$DBI::errstr]"
);
my( $return_type, $return_text );
if ( $sth->execute ) {
#
# Find the status of $statement
#
while( my $ref = $sth->fetchrow_hashref ) {
$return_type = $ref->{'Msg_type'};
$return_text = $ref->{'Msg_text'};
}
if ($return_type eq "status") {
if ($return_text eq "OK" or $return_text eq "Table is already
up to date") {
return $return_text;
}
} else {
return $script->Error( -level=>"WARN",
-text=> "$statement returned
$return_text" );
}
if ( $sth->finish ) {
return "OK";
} else {
return $script->Error( -level=>"WARN",
-text=> "$statement finish failed
[$DBI::errstr]" );
}
} else {
return $script->Error( -level=>"WARN",
-text=> "$statement prepare failed
[$DBI::errstr]" );
}
}
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
