mysql-backup.sh

#!/bin/sh
######################################################################################
######################################################################################
############### apmysqlbackup.sh ver. 0.11 ##########################
############### ########################## 
############### made by Andrej Polic in April 2005 ##########################
############### You can use this script free ##########################
############### and freely distribute it. ##########################
############### This script is provided "as is" and ##########################
############### without any warranties. ##########################
############### You use this script at your own risk. ##########################
############### It was my intention to make mysql datbase ##########################
############### backups easy and simple. ##########################
############### This script is ment to be used by server ##########################
############### administrators who have root access. ##########################
######################################################################################
######################################################################################
 
######################################################################################
############### CONFIGURATION AREA ###################
############### You will have to fill your configuration data here ###################
######################################################################################
#backuppath is directory where you want your backups to be stored
#example backuppath="/backup/databasebackup/"
#The directory MUST be empty if you want to use backup rotation (deleting old backups) 
#I STRONGLY suggest you to create new directory
#the directory should allready exist before you run this script
#dont forget to put slash "/" at the end of the path
backuppath="/home/irony/mysql-backup/"
 
#username and password
#note that this is usually not needed if you run script manualy as root, so yo can leave it empty 
#user must be user that can run mysqlshow command on the server
#username will almost always be root, but you can change it if you wish
 
username="root"
password="vamonodejaleo"
#mysqldumpoption is the option that you want to use when you do mysqldump 
#example mysqldumpoption="--opt" , it will give you mysqldump --opt when backing up database
mysqldumpoption="--opt"
 
#backup_every_database_individualy means if you want every database you have to be backed up in separate .sql file
#use "on" or "off"
backup_every_database_individualy="on"
# triyng to fix a bug with this
backup_every_database_individualy_no_skip="off"
 
#backup_all_databases_in_one_file means if you want to make backup of all databases in one .sql file 
#it uses mysqldump --A option to backup databases
#use "on" or "off"
backup_all_databases_in_one_file="off"
 
#backup_selected_databases_individualy means if you want every database you have, except ones in skip list, to be
#backed up in separate .sql file
#use "on" or "off"
backup_selected_databases_individualy="on"
 
#backup_selected_databases_in_one_file creates backup of all databases, except ones in skip_list, into one .sql file
#it uses mysqldump --databases and dumps all databases except ones in skip_list
#use "on" or "off"
backup_selected_databases_in_one_file="off"
 
#skip_list is the list where you can put databases you want to be skipped in selected databse backups
#example skip_list="test mysql some_database"
skip_list="syslog eximstats test leechprotect mysql horde"
 
#usebzip2 means if you want to bzip2 created .sql files 
#use "on" or "off"
usebzip="off"
 
#use_rotation means if you want to delete old backups
#if you enable it it will delete backups older then value in rotation_days
#use "on" or "off"
## IMPORTANT ### IF ENABLED IT WILL DELETE ALL FILES IN backuppath OLDER THEN NUMBER OF DAYS GIVEN IN rotation_days###### 
use_rotation="on"
 
#rotation_days means number of days to keep backup before deleting it
#it is relevant only if you set use_rotation="on"
rotation_days=3
 
#######################################################################################
###################### END OF CONFIGURATION AREA ###########################
#######################################################################################
 
##############################################################
################ BEGINING OF THE SCRIPT ######################
##############################################################
if [ "$username" != "" ]; then
userandpass="--user=$username --password=$password"
mysqldumpoption="$mysqldumpoption $userandpass"
fi
if [ "$use_rotation" = "on" ]; then
echo
echo "You choosed to use backup rotation, which means that all files in" $backuppath " older than " $rotation_days " days" 
echo "WILL BE DELETED"
echo
echo "DELETING FILES ============>"
echo "`find "$backuppath" -type f -mtime +"$rotation_days"`"
rm -f `find "$backuppath" -type f -mtime +"$rotation_days"` 
fi
databases=`mysqlshow $userandpass | tr -d ' |'|grep -v -E '^Databases$|^\+\-\-\-'`
if [ "$usebzip" = "on" ]; then
echo
echo "You choosed to bzip-2 .sql files after creating them"
fi
####### backup up every database individualy ############### 
if [ "$backup_every_database_individualy_no_skip" = "on" ]; then 
echo
echo "We will be backing up all databases on your sistem in separate .sql files" 
for database in $databases; do 
echo "Processing database: " $database
mysqldump $mysqldumpoption $database > $backuppath$database"_"$(date +%m%d%Y).sql 
if [ "$usebzip" = "on" ]; then
bzip2 -9 -f $backuppath$database"_"$(date +%m%d%Y).sql
fi
done
fi
###############################################################
 
###### backup all databases in one file ##################
if [ "$backup_all_databases_in_one_file" = "on" ]; then
echo
echo "We will be backing up all databases on your sistem in one .sql file"
echo "Processing databases: "
echo "$databases" 
mysqldump $mysqldumpoption -A > $backuppath"alldatabases_"$(date +%m%d%Y).sql 
if [ "$usebzip" = "on" ]; then
bzip2 -9 -f $backuppath"alldatabases_"$(date +%m%d%Y).sql
fi
fi
##########################################################
 
##### backup all databases except for the ones in skip_list individualy #########################
if [ "$backup_selected_databases_individualy" = "on" ]; then
selecteddatabases=$databases
for examined1 in $skip_list; do
selecteddatabases=`echo "$selecteddatabases" | grep -x -v -E "$examined1"`
done
echo
echo "We will be backing up all databases on your sistem, except for the ones in skip_list, in separate .sql files"
for database2 in $selecteddatabases; do
echo "Processing database: " $database2
mysqldump $mysqldumpoption $database2 > $backuppath$database2"_"$(date +%m%d%Y).sql
if [ "$usebzip" = "on" ]; then 
bzip2 -9 -f $backuppath$database2"_"$(date +%m%d%Y).sql
fi
done
fi
###################################################################################################
 
##### backing up all databases except for the ones in skip_list in one file #########################
if [ "$backup_selected_databases_in_one_file" = "on" ]; then
selecteddatabases=$databases
for examined2 in $skip_list; do
selecteddatabases=`echo "$selecteddatabases" | grep -x -v -E "$examined2"`
done
echo
echo "We will be backing up all databases on your sistem, except for the ones in skip_list, in one .sql file"
echo "Processing databases: "
echo "$selecteddatabases"
mysqldump $mysqldumpoption --databases $selecteddatabases > $backuppath"selecteddatabases_"$(date +%m%d%Y).sql
if [ "$usebzip" = "on" ]; then
bzip2 -9 -f $backuppath"selecteddatabases_"$(date +%m%d%Y).sql
fi
fi
###########################################################################################