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 Log Rotation with PHP
Go to content Go to navigation Go to search

MySQL Log Rotation with PHP · 15 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 Hacking Sphider to Weigh the Heading Tags