How to Create a Database Backup Scheduler Script

Our goal in this article is to configure a script that will keep only the last seven days of database backup files.

Let's get started.

First, create a bash script file backupexecutor.sh where we will write all our backup scripts.

Then, write an expect script file backup.exp where we will execute backupexecutor.sh and provide a password to execute backupexecutor.sh. We also write our output log script in our backup.exp file.

To do our database backup, we need only two files:

1. backupexecutor.sh

2. backup.exp

So, your Linux system must have expect installed.

To check if expect installed, type:

$ which expect

This will give an output like this:

/usr/bin/expect

If your system can't recognize expect, then install it in your system by typing:

apt-get install expect

Or if you're using Amazon Linux, it's CentOS-based (which is RedHat-based), so type:

yum install expect

Now, you are ready to write your backup script.

backupexecutor.sh:

#First get current date and time for logging
#and to use as suffix in our Database backup file name
#get date yyyy-MM-dd
NOW_DATE=$(date +%F)

#get time hh:mm:ss
NOW_TIME=$(date +%T)

#current date time
NOW="$NOW_DATE($NOW_TIME)"

#To keep last few days backup files only
#backup date
BACKUP_DATE=7

#Location of your database backup file.
#In this location your database backup file will generate
#backup location
LOCATION=/home/user/DB_BACKUP/BACKUP_FILES

#Database user name
DB_USER_NAME=root

#Database Name
DB_NAME=sampledb

#Your database backup file name
#Example: If database backup executed on this date time 2017-11-08(20:30:01)
#Database backup file name will be myDatabase_2017-11-08(20:30:01).backup
#file name
FILE_NAME=myDatabase_$NOW.backup

echo "==================================== Start $NOW  ===================================="
echo "#Starting to take Database backup......"
#For postgresql
pg_dump -U $DB_USER_NAME -h 127.0.0.1 -W -F t $DB_NAME > $LOCATION/$FILE_NAME
#For mysql (uncomment below)
#mysqldump -u $DB_USER_NAME -p $DB_NAME > $LOCATION/$FILE_NAME
echo "#Deleting Older than $BACKUP_DATE day(s) backup......"
sudo find $LOCATION -type f -mtime +$BACKUP_DATE -name '*.backup' -execdir rm -- '{}' \;
echo "====================================  End $NOW   ===================================="

backup.exp: 

#!/usr/bin/expect

set homeDirectory "/home/user/DB_BACKUP"
set dbPassword "db123"

# Start logging
log_file $homeDirectory/LOG/db_backup.log;

# Execute database backup script
spawn $homeDirectory/./backupexecutor.sh

# Sending password to execute this command
expect "*asswor*"
send -- "$dbPassword\r"

expect eof

Now, make those files executable for all:

$ chmod a+x backupexecutor.sh

$ chmod a+x backup.exp

Now, we need to set up a scheduler in Linux to execute backup.exp.  We are using crontab to schedule database backup. crontab has five fields to schedule a script/command

{minute} {hour} {day of month} {month} {day of week [0-6] Sunday = 0, Saturday = 7}

So, to execute every day at 8 PM, our crontab command will be:

0 20 * * *

Here, * means anything.

Now, open crontab in edit mode:

$ crontab -e

And write there:

0 20 * * * /file/path/to/backup.exp

Save the file.

Now, every day at 8 PM, backup.exp will execute!

Log file:

Database backup location:

And that's it!