Sometime we want to backup our databases for a reasons, like prevent the database deleted, move database to another server, etc. its very bored if we do it every day manually and sometime we forget to backup the database. so what we want is backup the database with something automatically with the time we have set, but how ? there is a lot of trick to do this, one of the is use shell script and cron.

ok, lets do it

1. Create Shell Script

$ vim backup.sh

in vim editor put this script

#!/bin/bash
#directory backup
DIR_NAME="/directory/backup/"
#file name we will be used
FILE_NAME="mysqlbackup_`date +%Y%m%d%H%M%S`.sql"
#Variable NUM is used for counted total of existing backup file
NUM=`ls $DIR_NAME -R -1 | grep mysqlbackup | wc -l`
#maximal file backup
MAX=2
#email notification
MAIL="mail@domain.com"
#if reach maximum backup
if [ $NUM -ge $MAX ]; then
#find out the first file in backup directory
for i in $( ls $DIR_NAME -R -1 | grep mysqlbackup  ); do
#deleting first file that we found
rm -rf $DIR_NAME$i
#get out from the loop
break
done
fi
#backup all database
mysqldump -uusername -ppassword --all-databases > $DIR_NAME$FILE_NAME
#compressed dump file
gzip -rf $DIR_NAME$FILE_NAME
#create notification file
echo "New Backup file created : $DIR_NAME$FILE_NAME.gz" > /tmp/mydbbackup.log
#send notification to email
mail -s "New Backup file created" $MAIL < /tmp/mydbbackup.log

save file “esc” “:wq”

change the permission of file to execute file

$ chmod a+x backup.sh

and test

$  ./backup.sh

if this work you willl get a notification email, or for see the log see here :

$ cat /tmp/mydbbackup.log

next step is set the cron

$ sudo vim /etc/crontab

for example

# m h dom mon dow user  command
00 03 * * * username sh /directory/script/backup.sh #yourscript here

descriptions :

m = Minute
h = Hour
dom = Day of Month
mon = Month
dow = Day of Week
user = User
command = command that will execute

in conditions above 00 03 * * * …. this script will be running every day at 3 AM.