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
Discussion