Create a file backup_db.sh, and paste the following contents:
#get the first parameter as the database name
DATABASE=$1
#if no database specified, then you can set the default one
if [ -z $DATABASE ]; then
DATABASE=default_database_name_here
fi
#mysql user and password to backup the database.
MYSQLUSER=mysql_user
MYSQLPWD=mysql_password
#path to backup
ARCHIVEPATH=~/backup/db_backup
DATE=`date +%Y%m%d`
YEAR=`date +%Y`
MONTH=`date +%m`
FOLDER_MONTH=$ARCHIVEPATH/$YEAR$MONTH
if [ ! -d $FOLDER_MONTH ]; then
echo "mkdir $FOLDER_MONTH"
mkdir $FOLDER_MONTH
fi
# Backup
echo "mysqldump -u$MYSQLUSER -p$MYSQLPWD $DATABASE | gzip $FOLDER_MONTH/$DATABASE-$DATE.sql.gz"
mysqldump -u$MYSQLUSER -p$MYSQLPWD $DATABASE | gzip $FOLDER_MONTH/$DATABASE-$DATE.sql.gz
and you can add the script to cron job under *nix and schedule under windows:
*nix:
Save the following text in file: db_backup.at
10 * * * * ~/backup/backup_db.sh databasename
and call
crontab db_backup.at
You need to change the period to run the script for your business, e.g. each day, each week etc.