Go to content Go to navigation Go to search

MySQL Log Rotation with PHP · 14 February 2006 by Julian

If you want to rotate your MySQL log files (i.e. you have enabled the ”—log-bin” option in the command line or used MySQL Administrator to enable Binary Log Files) via PHP, here a little script to enable you to do it.

Using the PEAR DB libraries, here’s how you would do it:


//---- this is the time of the last log file that will be kept
$purgeTime['hour'] = 23;
$purgeTime['minute'] = 59;
$purgeTime['second'] = 50;
//---- date adjustment for the purging of the log files,
//---- in this case it is set to 2 because I want to keep
//---- 24 hours worth & my purge will take place after
//---- midnight
$purgeDateAdjustment['days'] = 2;
$purgeDateAdjustment['months'] = 0;
$purgeDateAdjustment['years'] = 0;
//---- includes PEAR DB classes
require_once("DB.php");
//---- includes the DB parameters
require_once("config/admin_db-prams.php");

if (DB::isError($db)) { die($db->getMessage());
}
$purgeEvent = date("Y-m-d H:i:s",mktime($purgeTime['hour'], $purgeTime['minute'], $purgeTime['second'], date("m")-$purgeDateAdjustment['months'], date("d")-$purgeDateAdjustment['days'], date("Y")-$purgeDateAdjustment['years']));
$queryPurge = "PURGE BINARY LOGS before '$purgeEvent'";
$result = $db->query($queryPurge);
echo $queryPurge . "<br />\n";
//---- fetch a resultset of all the binary logs, this part is
//---- not necessary but show you what you have done
$queryShowMaster = 'SHOW MASTER LOGS';
$result = $db->query($queryShowMaster);
if (DB::isError($result)) { die(showErrorPage($result->getMessage()));
}
$numrows = $result->numRows();
$counter = 1;
for ($i = 0; $i < $numrows; $i++){ $row = $result->fetchRow(DB_FETCHMODE_ASSOC, $i); if ($counter == 1) { $oldest_kept_log = $row['Log_name']; } $counter++;
}
$reportMessage = "The MySQL binary logs have been rotated. The oldest log is '$oldest_kept_log'";

See also:
MySQL Database Checks with PHP

MySQL Database Checks with PHP · 7 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