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:
- 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
Нужно создать LOG каталог в операционной системе (log_file $homeDirectory/LOG/db_backup.log;)
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!
https://dzone.com/articles/linux-database-backup-scheduler-script-postgresql
|