Re: Keeping MySQL Databases Heathly

[Marc Prewitt]

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, $erro

Re: Keeping MySQL Databases Heathly

[Marc Prewitt]

We run a CHECK TABLE nightly on every table (see:
http://www.mysql.com/doc/C/H/CHECK_TABLE.html) and I would HIGHLY
recommend doing so.  If the check table fails, we run a REPAIR TABLE.  We
also used to do a REPAIR TABLE EXTENDED on weekends to optimize indexes
until our db got too big for that to finish in a reasonable amount of
time.

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.
> --
> <->
>  Alex Pilson
>  FlagShip Interactive, Inc.
>  [EMAIL PROTECTED]
>  404.728.4417
>  404.642.8225 CELL
> 
> // Web Design
> // Lasso Application Development
> // Filemaker Pro / SQL Development
> // Sonic Solutions Creator Authoring
> // Apple DVD Studio Pro Authoring
> // Macromedia Director/Flash Authoring
> <->
> 
> -
> 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

-
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

RE: Keeping MySQL Databases Heathly

[sean . odonnell]

MySQLfront (www.mysqlfront.de) displays all the output from show status in
the host tab when you connect.

-Original Message-
From: Alex Pilson [mailto:[EMAIL PROTECTED]]
Sent: 04 April 2002 07:15
To: John Klein
Cc: [EMAIL PROTECTED]
Subject: Re: Keeping MySQL Databases Heathly


At 10:09 AM -0500 4/4/02, John Klein wrote:
>[EMAIL PROTECTED] 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?
>
>I actually find it helpful to periodically run the 'SHOW STATUS' command
>on the database and dump the results to a file. Examining this can give
>all sorts of information on how much use the database is getting, whether
>queries are optimized, etc. Documentation on the command is here:
><http://www.mysql.com/doc/S/H/SHOW_STATUS.html>. I'm actually running this
>every half-hour, because I'm a freak.
>
>The script I use isn't exactly production quality (one of those ten-minute
>jobs). I keep intending to fix it someday or just use some internal MySQL
>statement that does the same thing, but I'm sure you know how it is. The
>script takes one parameter (a filename to dump results to). If called
>without one, it prints to STDOUT. Here's my code:

Cool. I will give it whirl to see what information it reveals.
-- 
<->
 Alex Pilson
 FlagShip Interactive, Inc.
 [EMAIL PROTECTED]
 404.728.4417
 404.642.8225 CELL

// Web Design
// Lasso Application Development
// Filemaker Pro / SQL Development
// Sonic Solutions Creator Authoring
// Apple DVD Studio Pro Authoring
// Macromedia Director/Flash Authoring
<->

-
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


-
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

Re: Keeping MySQL Databases Heathly

[Alex Pilson]

At 10:09 AM -0500 4/4/02, John Klein wrote:
>[EMAIL PROTECTED] 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?
>
>I actually find it helpful to periodically run the 'SHOW STATUS' command
>on the database and dump the results to a file. Examining this can give
>all sorts of information on how much use the database is getting, whether
>queries are optimized, etc. Documentation on the command is here:
>. I'm actually running this
>every half-hour, because I'm a freak.
>
>The script I use isn't exactly production quality (one of those ten-minute
>jobs). I keep intending to fix it someday or just use some internal MySQL
>statement that does the same thing, but I'm sure you know how it is. The
>script takes one parameter (a filename to dump results to). If called
>without one, it prints to STDOUT. Here's my code:

Cool. I will give it whirl to see what information it reveals.
-- 
<->
 Alex Pilson
 FlagShip Interactive, Inc.
 [EMAIL PROTECTED]
 404.728.4417
 404.642.8225 CELL

// Web Design
// Lasso Application Development
// Filemaker Pro / SQL Development
// Sonic Solutions Creator Authoring
// Apple DVD Studio Pro Authoring
// Macromedia Director/Flash Authoring
<->

-
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

Re: Keeping MySQL Databases Heathly

[John Klein]

[EMAIL PROTECTED] 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?

I actually find it helpful to periodically run the 'SHOW STATUS' command
on the database and dump the results to a file. Examining this can give
all sorts of information on how much use the database is getting, whether
queries are optimized, etc. Documentation on the command is here:
. I'm actually running this
every half-hour, because I'm a freak.

The script I use isn't exactly production quality (one of those ten-minute
jobs). I keep intending to fix it someday or just use some internal MySQL
statement that does the same thing, but I'm sure you know how it is. The
script takes one parameter (a filename to dump results to). If called
without one, it prints to STDOUT. Here's my code:

#!/usr/local/bin/perl

use strict;
use DBI;

my $filename = shift;
my @time = localtime();
my $dbtype = 'mysql';
my $database = '';  # Insert your database's name here
my $port = '';  # Insert your port here, or use a socket below
my $dbuser = '';# Some correctly set-up username
my $dbpassword = '';# The password for said name

# Alternately, you could just hardcode everything into the connect
# statement here. I have it broken up to make it easier for non-Perl
# users to reconfigure the script.

my $dbh =
DBI->connect("DBI:$dbtype:database=$database;host=127.0.0.1;port=$port", 
$dbuser, $dbpassword);

if (! $filename) {
  open (STATFILE, '>&STDOUT');
  print STATFILE &makeheader();
} elsif (-f $filename) {
  open (STATFILE, ">>$filename");
} else {
  open (STATFILE, ">$filename");
  print STATFILE &makeheader();
}

printf STATFILE ('"%04d","%02d","%02d","%02d","%02d"', $time[5] + 1900,
$time[4] + 1, $time[3], $time[2], $time[1]);
my $sth = $dbh->prepare('show status');
$sth->execute;
while (my $row = $sth->fetchrow_arrayref) {
  print STATFILE ",\"$row->[1]\"";
}
print STATFILE "\n";

close (STATFILE);
$dbh->disconnect;

## END

sub makeheader {
  my $outstring = '"Year","Month","Day","Hour","Minute"';
  my $sth = $dbh->prepare('show status');
  $sth->execute;
  while (my $row = $sth->fetchrow_arrayref) {
$outstring .= ",\"$row->[0]\"";
  }
  $outstring .= "\n";
  return ($outstring);
}

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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 
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Keeping MySQL Databases Heathly

[Alex Pilson]

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.
-- 
<->
 Alex Pilson
 FlagShip Interactive, Inc.
 [EMAIL PROTECTED]
 404.728.4417
 404.642.8225 CELL

// Web Design
// Lasso Application Development
// Filemaker Pro / SQL Development
// Sonic Solutions Creator Authoring
// Apple DVD Studio Pro Authoring
// Macromedia Director/Flash Authoring
<->

-
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