How To: using bash script to backup MySql

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.

Leave a Reply

Your email address will not be published.