Deprecated: Function set_magic_quotes_runtime() is deprecated in /home/jovianskye/jovianskye.com/textpattern/lib/txplib_db.php on line 14

Warning: Cannot modify header information - headers already sent by (output started at /home/jovianskye/jovianskye.com/textpattern/lib/txplib_db.php:14) in /home/jovianskye/jovianskye.com/textpattern/lib/txplib_misc.php on line 1240
the Jovian Skye: MySQL Database Checks with PHP
Go to content Go to navigation Go to search

MySQL Database Checks with PHP · 8 February 2006 by Julian

I’m just in the middle of upgrading our web servers (Windows 2003 Enterprise Edition) at the place I work (can’t tell you where sorry, they don’t like blogging – yet ;-) ), & I was wanting to run an automated check of the MySQL database.

I considered running them directly via a Cron type service (prycron is a good one for windows) but that‚??s far too manual for the number of databases we have & lacks the control I wanted. So I looked around the web for some good scripts, but nothing out there was what I wanted, so I tuned to the good old MySQL site & had a nosey around.

Scraping together a few snippets of code from various contributors from the comments section I managed to build a little script to check the MySQL databases.

Essentially all MySQL checks, analyze, optimize, repairs, dumps & log rotations can be performed by simply executing a SQL command. So the idea is just to make up a script in PHP that just executes your required SQL statements.

CHECK TABLE

To perform a ‚??CHECK‚?Ě of a MySQL table the syntax is;
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
e.g.
CHECK TABLE `Customers` CHANGED
(see http://dev.mysql.com/doc/refman/5.0/en/check-table.html)

Simple eh?
The options are: QUICK | FAST | MEDIUM | EXTENDED | CHANGED

I chose to use CHANGED when performing a check on the database during the day for any tables that were updated frequently & did an EXTENDED on all tables at 4am when I did my full backup on all tables. [Of note I found that the hour from 4am until 5am was the quietest so that‚??s when I do my complete database backups & extended checks etc on the databases.]

As well as CHECK I also perform the ANALYZE & OPTIMIZE at the same time. ANALYZE TABLE analyzes and stores the key distribution for a table (see http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html). 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) (see http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html).

ANALYZE & OPTIMIZE have the option of NO_WRITE_TO_BINLOG which is useful in that it won‚??t bloat your bin log files with all these queries

The following uses the PEAR DB libraries as any good PHP programmer would ;)

Please Note: the use of ”= =” in this script should be ”==”. I have had to make changes to the code as Textpattern (my blog software) thinks that this should be some centred text.


/*
$sqlTablesToCheck is an array of database configuration files, for the 20+ databases that you have ;-)
e.g.
$sqlTablesToCheck["druple"] = 'path_to_config_file/druple_db-prams.php';
 */


foreach ($sqlTablesToCheck as $tableName => $configFile) {
  // ---- includes the DB parameters
  require_once($configFile);

  echo "<h2>$tableName</h2>\n";

  if (DB::isError($db)) {
    die($db->getMessage());
  }

  // ---- fetch a list of all tables in the DB
  $sqlAllTables = 'SHOW TABLES';

  $result = $db->query($sqlAllTables);

  if (DB::isError($result)) {
     die($result->getMessage());
  }

  $numberRows = $result->numRows();

  echo "<h3>$sqlAllTables</h3>\n";

  for ($i=0;$i < $numberRows; $i++){
    $row = $result->fetchRow(DB_FETCHMODE_ORDERED,$i);
    echo $row<sup><a href="#fn0">0</a></sup> . ", ";
    $theTablesArr[] = $row<sup><a href="#fn0">0</a></sup>;
   }

  $theTables = implode(',',$theTablesArr);

  // ---- build a list of SQL statements to do the CHECK/ANALYZE/OPTIMIZE
  // ---- perform the full "EXTENDED"  check of the database only at 4am!
  if ($currentTime['hour'] = =  4) {
    $sqlQuerys["CHECK"] = 'CHECK TABLE ' . $theTables . ' EXTENDED';
  } else {
    $sqlQuerys["CHECK"] = 'CHECK TABLE ' . $theTables . ' CHANGED';
  }

  $sqlQuerys["ANALYZE"] = 'ANALYZE NO_WRITE_TO_BINLOG TABLE ' . $theTables;
  $sqlQuerys["OPTIMIZE"] = 'OPTIMIZE NO_WRITE_TO_BINLOG TABLE ' . $theTables;

  // ---- perform the CHECK/ANALYZE/OPTIMIZE on the DB
  foreach ($sqlQuerys as $queryName => $eachQuery) {
    // ---- wait for 4 seconds, don't want to kill the poor server with lots of heavy queries
    usleep($timeWait);

    $result = $db->query($eachQuery);

    if (DB::isError($result)) {
      die($result->getMessage());
    }

    $numberRows = $result->numRows();

    echo "<h3>$queryName</h3>";

    for ($i=0;$i < $numberRows; $i++){
      $row = $result->fetchRow(DB_FETCHMODE_ASSOC,$i);
      echo "{$row['Table']} > {$row['Op']} > {$row['Msg_text']} > {$row['Msg_type']}<br />\n";

      if (($row['Msg_type'] = = "error") || ($row['Msg_type'] = = "warning") || ($row['Msg_type'] = = "info")) {
        $cleanUpErrors[] = "{$row['Table']}   {$row['Op']}   {$row['Msg_text']}   {$row['Msg_type']}";
      }
    }
  }
  // ---- reset the array to be populated again
  unset($theTablesArr);
}


if (!isset($cleanUpErrors)) {
    $cleanUpErrors[] = "No_errors";
}

Of course $cleanUpErrors[] is an array of errors (or warnings) that is generated by MySQL which you could then fir of to an email address

See also:
MySQL Log Rotation with PHP

Search Engine Shootout MySQL Log Rotation with PHP