One thing that seems to always irritate me when im trying to organize my backups together is how the file sizes bloat over time. You can, and probably should look into optimizing your tables but for those of you who dont have the time or the will power to care to do it in any other method if you place this function at the top of your automated backup script and simply call it appropriately you will be able to automate the process of optimizing all tables that dont meet your threshold and or maximum free space requirements.
You can just set up a cron job to call your PHP scripts and it should take care of things from there.
423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 | $_CONFIG['threshold'] = 0.01; $_CONFIG['maxfreedata'] = 102400; function optimizeDatabase($host, $user, $pass, $dbase) { global $_CONFIG; $mysqli = new mysqli($host, $user, $pass, $dbase); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } if ($result = $mysqli->query('SHOW TABLE STATUS WHERE '. '(Data_free / Data_length) > ' . $_CONFIG['threshold'] . ' AND Data_free > ' . $_CONFIG['maxfreedata'])) { print 'Optimizing Database: ' . $dbase . "\n"; while ($row = $result->fetch_object()) { printf("%-25s %8d %8d\n", $row->Name, $row->Data_free, $row->Data_length); $mysqli->query('OPTIMIZE TABLE ' . $row->Name); } $result->close(); } $mysqli->close(); } optimizeDatabase('localhost', 'User', 'Password', 'Database'); |
Good stuff man. I like this. I’ll have to keep this in mind when I work on PHP projects–really any MySQL project for that matter.