paul Posted July 12, 2006 Report Share Posted July 12, 2006 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) Quote Link to comment Share on other sites More sharing options...
Guest nina Posted November 15, 2006 Report Share Posted November 15, 2006 I wish I read this before... Oh well... Now I know ;) Thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.