Automatic MySQL Backup Script

[Lease]

I've used another sort of backup script, not this one, but I have to stress implementing some sort of backup cronjob on a set time (Daily would be the best choice), is a must have for every server. You never know when something goes wrong.. mod installation? Upgrades? Changing servers?

I'll post another topic on how to backup your files and folders.

Got the extra cash? Purchase another server to transfer your backups, or use something such as bqbackup.com. $5/10GB of storage, per month? Cheap + Worth it.
--------

How to install this script:

Here is how to use it if you are a newbie:
 
1.download it to your computer and then upload it to your server 
2.unpack the archive:
 
tar -xzf apmysqlbackup.0.11.tar.gz
 
3.chmod the file to 700
 
chmod 700 apmysqlbackup.sh
 
4.edit the script configuration area accordind to your needs. PLEASE BE CAREFUL IF USING ROTATION. Read the notes about it in configuration area
 
pico -w apmysqlbackup.sh
 
5.Create directory where you will save your backups (according to the backuppath that you used in script configuration)
for example:
 
mkdir /home/databasebackup
 
6. ruh the script and see if it works for you.
./apmysqlbackup.sh
 
You may need to put your username and password into the script (It is ususlly not needed if you run script manualy). 
 
If you would like that script runs automaticaly every day you can put something like this in your crontab:
1 3 * * * root /pathtothescript/apmysqlbackup.sh
it will run the script in 3 am every day
Note tha you will have to have root alowed to log into mysql if you want tu run the script from crontab. In my case that was not needed when I ran the script manualy.
 
If you would like to get mail report from the script you can use it like this:
 
/pathtothescript/apmysqlbackup.sh | mail -s "apmysqlbackup.sh report" [EMAIL="yourmail@domain.com"]yourmail@domain.com[/EMAIL]

The Script - Copy and paste, then save it.

#!/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/databasebackup/"
 
#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=""
password=""
#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="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="on"
 
#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="eximstats test leechprotect mysql horde"
 
#usebzip2 means if you want to bzip2 created .sql files 
#use "on" or "off"
usebzip="on"
 
#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="off"
 
#rotation_days means number of days to keep backup before deleting it
#it is relevant only if you set use_rotation="on"
rotation_days=7 
 
#######################################################################################
###################### 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" = "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
#####################################################################################################