HINTS

Check and repair database
mysqlcheck -p --auto-repair --check --databases [databasename]
MySQL Backup
mysqldump -u [dbusername] -p [databasename] > [/path/to/backupname.dump]
MySQL Restore
mysql -u [dbusername] -p [databasename] < [/path/to/backupname.dump]
Set Mysql Passwort
/usr/bin/mysqladmin -u root password ‘secret’

Backup Script

This script backups MySQL Database, zip it, and removes old backup files.

  #! /bin/bash
  #
  # MySQL backup rotation script
  #
  # (c) 2010 Philipp Schellworth
  # All rights reserved
  # www.schellworth.de
  #
 
  ACCEPTABLE_DAYS="+7"
  TARGET="/mnt/dumps"
  SQLPASSWD="secret"
 
  DATE=$(date +"%Y%m%d")
  mysqldump -p"$SQLPASSWD" db_bak | gzip > "/tmp/db_bak_"$DATE".dump.gz"
  #gzip "/tmp/db_bak_"$DATE".dump"
  mv -f "/tmp/db_bulls_"$DATE".dump.gz" "$TARGET"/.
 
  find "$TARGET"/db_bulls* -mtime $ACCEPTABLE_DAYS -exec rm -f {} \;

Remove old Table entries

If you have a growing (log) table in a database, you can use this script to get rid of old entries and keep your database slim (important for a good performance).

  #!/bin/bash
  #
  # This script removes old Log entries from a DB table
  #
  # (c) 2009 Philipp Schellworth
  # All rights reserved
  # www.schellworth.de
  #
 
 
  acceptable_days=60
 
 
 
  date2stamp () {
      date --utc --date "$1" +%s
  }
 
  stamp2date (){
      date --utc --date "1970-01-01 $1 sec" "+%Y-%m-%d %T"
  }
 
  today=$(date '+%s')
  echo today: $(stamp2date $today)
 
  DATE=$(( $today-($acceptable_days*24*60*60) ))
  echo Deleting all Log entries from $(stamp2date $today) to $(stamp2date $DATE)
 
 
  result=`mysql -e "delete FROM logs WHERE date < $DATE" -D DATABASE -u root --password="secret" -h localhost` 
  #For testing use
  #result=`mysql -e "select count(*) FROM logs WHERE date < $DATE" -D DATABASE -u root --password="secret" -h localhost`
 
  echo $result
Enter your comment. Wiki syntax is allowed:
   _  __   ___   _____   ___   _____
  | |/_/  / _ ) / ___/  / _ \ / ___/
 _>  <   / _  |/ (_ /  / , _// (_ / 
/_/|_|  /____/ \___/  /_/|_| \___/