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++;
}
?>

Annoyed from Caps Lock?

EVERYONE KNOW’S IT… hups… entering a password doesn’t work and so on. Why? Caps Lock is activated.

Have a look at CAPSoff.org I agree, no one needs this useless and annoying key.

There is a good workaround from heise.de available to disable this key. Using this direct download link from heise.de you can download a Windows registry key which disables your caps lock key. To install double click on the CapsLockOff.reg and choose “yes”. Afterwards reboot your PC and caps lock will be disabled, how nice!

Free iPhone Game Solitaire City

I could survive several years now without playing Solitaire, good luck. But after obtaining a iPhone things got worse and I’m addicted again…

A must for Solitaire addicts! Many options, nice graphics and cute sound effects.

The lite version is free and fulfills my whishes absolutely. Get it from SolitaireCity or directly from your iTunes shop under free applications.

Update: The latest lite version includes less games, unfortunalty my favorite is missing now. Therefore I’ve purchased the full version now. According to feedback in the iTunes store the new version is a little bit unstable. I can confirm that the app does sometimes terminate when choosing games, but playing itself seems stable. For me still worth paying for the app.

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 {} ";"

Search and Replace over Multiple Files

Sometimes you need to search and replace strings over multiple files. This can easily be done using perl. I found this tip from www.liamdelahunty.com/tips

perl -pi -w -e 's/search/replace/g;' *.php
-e means execute the following line of code.
-i means edit in-place
-w write warnings
-p loop