DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations

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

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
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Create a Database Backup Scheduler Script

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.

Md Nasir Uddin Bhuiyan user avatar by
Md Nasir Uddin Bhuiyan
·
Dec. 29, 17 · Tutorial
Like (1)
Save
Tweet
Share
15.69K Views

Join the DZone community and get the full member experience.

Join For Free

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:

Image title

Database backup location:

Image title

And that's it!

Database Backup

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

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: