the Jovian Skye: MySQL Log Rotation with PHP

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'";

MySQL Database Checks with PHP Hacking Sphider to Weigh the Heading Tags

the Jovian Skye

Blogroll

Categories

Search

MySQL Log Rotation with PHP · 14 February 2006 by Julian