TechniTip.Net

MySQL Optimize Script

A nice small PHP script which simple connects to a given MySQL server and optimizes all databases and included tables. Only the internal table “information_schema” is skipped because it will show an error.

Working with MySQL version 5.0.32 and PHP version 5.2.0 on Debian Etch.

Script is designed to run from command line “php optimize.php” maybe from a cron job. Be careful: avoid running this script during your server “rush hour”, it may slow down your server.

<?php
/***********************************************************
 optimimze.php - optimizes all databases and tables of the
                 given mysql host.
 2008 - technitip.net
 ***********************************************************/
$mysqlhost = "localhost"; // enter MySQL host
$mysqluser = "user";      // enter MySQL user
$mysqlpwd  = "password";  // enter password
###########################################
$connection = mysql_connect($mysqlhost, $mysqluser, $mysqlpwd);
if (mysql_error())
{
  echo "Could not connect to database server! " . mysql_error() . "\n";
  exit;
}
$db_list = mysql_list_dbs();
$i = 0;
$cnt = mysql_num_rows($db_list);
while ($i < $cnt)
{
  $db = mysql_db_name($db_list, $i);
  ###########################################
  mysql_select_db($db, $connection);
  $result = mysql_list_tables($db);
  while ($row = mysql_fetch_row($result))
  {
    if ( $db == "information_schema" )
      continue;
    echo $db . " : `" . $row[0] . "`";
    $sql = "OPTIMIZE TABLE `".$row[0]."`";
    $erg = mysql_query($sql, $connection) or die(mysql_error());
    $data= mysql_fetch_array($erg, MYSQL_ASSOC);
    if($data)
    {
      echo " - " . $data['Msg_text'] . "\n";
    }
  }
  ###########################################
  $i++;
}
?>

Simple MySql Backup Script

The following example scripts performs a simple backup of all MySQL databases. The resulting .sql file is automatically zipped. Using “find” backups older than 3 days are deleted, so you will get complete backups of the last 3 days.

This script is intended to be called periodically (e.g. every day) from cron:

2 2       * * *   root /root/scripts/mysql_backup.sh

#/bin/sh
now=`date "+%Y-%m-%d"`
user="mysql_user"
password="mysql_password"
path="/home/backup/"
cd $path
mysqldump -u $user -p$password  --all-databases | gzip -c > backup_all_$now.sql.gz
# delete files older than 3 days
find . -name "*.gz" -type f -mtime +3 -exec rm {} ";"