How to Create a Database Backup Scheduler Script
Learn how to configure a script that will keep only the last seven days of database backup files in this quick tutorial.
Join the DZone community and get the full member experience.
Join For FreeOur 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:
backupexecutor.sh
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!
Opinions expressed by DZone contributors are their own.
Trending
-
Top Six React Development Tools
-
Decoding eBPF Observability: How eBPF Transforms Observability as We Know It
-
How AI Will Change Agile Project Management
-
13 Impressive Ways To Improve the Developer’s Experience by Using AI
Comments