Diamond Notes

Today I scripted a couple of tasks. Now I get to take a little more time sipping some coffee. It was a good introduction to my “Automate Everything” goal for the year. Some of you might find it laughable. That’s OK. Someone might find it useful.

As a DBA it is important to periodically run ‘CHECK TABLE tablename’ and it’s a real good thing to run ‘OPTIMIZE TABLE tablename’ also. I just cron’ed two little code snippets on my slaves to do just that.

The check table problem is very trivial. Here is the crontab entry:

# cat check_tables
MAILTO=”my email address”

0 8 * * sat root /usr/local/mysql/bin/mysqlcheck –all-databases

#
This simply runs mysqlcheck on all databases at 8:00 am Saturday mornings. It emails me the output. I could have it auto-repair the tables, but I would rather do that by hand.

The optimization problem is a little more difficult. I ended up using a php script I found online (with a decent amount of changes). I won’t show the crontab entry here as it it very similar to the previous one.

Here is the script:

#!/usr/bin/php
<?php

//
// code based on this: http://snippets.dzone.com/posts/show/2526
//

set_time_limit( 100 );

$time = microtime();
$time = explode(’ ‘, $time);
$time = $time[1] + $time[0];
$start = $time;

//Connection variables :
$host = ‘localhost’;
$user = ‘username’;
$password = ‘password’;

$db_link = mysql_connect($host,$user,$password);
if (!db_link) {
die (’Could not connect: ‘ . mysql_error());
}
$results = mysql_query(’SHOW DATABASES’) or die(’Could not connect: ‘ . mysql_er ror());
echo ‘Found ‘. mysql_num_rows( $results ) . ‘ databases’ . “\n”;
$dbs = array();
while ( $rec = mysql_fetch_array($results) )
{
$dbs [] = $rec [0];
}

foreach ( $dbs as $db_name )
{
echo “Database : $db_name \n\n”;
$results = mysql_query(”SHOW TABLE STATUS FROM `” . $db_name . “`”) or die(’Quer y : ‘ . mysql_error());
$to_optimize = array();
while ( $rec = mysql_fetch_array($results) )
{
if ( $rec[’Data_free’] > 0 )
{
$to_optimize [] = $rec[’Name’];
echo $rec[’Name’] . ‘ needs optimization’ . “\n”;
}
}
if ( count ( $to_optimize ) > 0 )
{
foreach ( $to_optimize as $table )
{
mysql_db_query($db_name, “OPTIMIZE TABLE `” . $table .”`”, $db_link );
}
}
}

$time = microtime();
$time = explode(’ ‘, $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
echo ‘Parsed in ‘ . $total_time . ‘ secs’ . “\n\n”;
?>