it pays to flush when you're finished

[paul]

I learnt a valuable lesson today .. and thought it might be worth sharing

 

When working with large mysql databases (like the MUB database)

mysql has a habit of loggin every thing .. GREAT !! especially if you go and make big mistakes, you can roll back your databases, and all is well again.

 

so let say you had 60mb of data that every hour you wanted to replace with 60mb of other data.

 

soon enough your mysql logging becomes a pain, so much so, that BANG! no hard drive space left :)

 

fix:

flush logs !!!!

so as the google kiddie I am (I guess) I googled together a quick script that will flush them and keep them "stable"

written in php (and must be chmod 755 at least)

#!/usr/bin/php -q
<?
define('KEEPNUM', 2);
define('HOST', 'localhost');
define('USER', 'root');
define('PASS', '********');

$dbh = new mysqli(HOST, USER, PASS);
if(mysqli_connect_errno()) die(mysqli_connect_error());

if($result = $dbh->query("SHOW MASTER LOGS"))
{
$lognames = array();
while($row = $result->fetch_array()) $lognames[] = $row[0];
$oldest_kept_log = $lognames[count($lognames) - KEEPNUM];
if(!$dbh->query("PURGE MASTER LOGS TO '$oldest_kept_log'")) die($dbh->error);
print "The MySQL binary logs have been rotated. The oldest log is $oldest_kept_log\n";
}
$dbh->close();
?>

 

I've chucked this in a cronjob .. ad now I shouldn't have to worry about mysql logs ballooning (lets hope)

[Guest nina]

I wish I read this before...

Oh well...

Now I know ;)

 

Thanks