Shell Script To Backup MySQL Database

Taking regular backup is as important as writing good code, and it is always recommended to take database backup every day or week depending on the size of the database and the site users count. As a developer sometimes we forget to take database backup, so the best thing is to automate this and let your server deal with this whole you can concentrate on other important things.
So first of all we have to create a Shell (.sh)script which will hold the logic to grab the data and store it in a compressed filed.

Shell Script: database_backup.sh

#!/bin/sh
# Step 1: set up all the variables
###Set the file path where you want to store the backup file and set the name of the file.
FILE=/path/to/your/backup_dir/my_db_file.sql.$(date +'%Y%m%d')
###Database Details:
DBSERVER=db_host
DATABASE=db_name
USER=db_user
PASS=db_password

# Step 2: If you are running this script more then one time then delete the previous copy of db file.
rm -f "$FILE" "$FILE.gz"
# Step 3: Take a MySQL backup.
mysqldump -opt -user=${USER} -password=${PASS} ${DATABASE} > ${FILE}
# Step 4: gzip/compress the MySQL database dump file.
gzip $FILE

Now that we have created the shell script now upload it in to your file server where all your website files reside. If you have the database host name other then localhost then you have to update the above script line like below.

mysqldump -opt -user=${USER} -password=${PASS} ${DATABASE} > ${FILE}
# replace above line with this
mysqldump -opt -user=${USER} -password=${PASS} -host=${DBSERVER} ${DATABASE} > ${FILE}

Now you have to add a cron to your crontab, which will run the above script on desired time.

So let say we want to take a database backup after every 6 hour, so you need to use below command to edit cron.

$ crontab -e

and over there write below code.

0 */6 * * * sh  /path/to/your/backup_dir/database_backup.sh

Every day in the backup_dir directory a new file will be generated namely my_db_file.sql.(date).