Database backup script

Linux

MySQL

To set up a cron job for daily database backup follow the steps mentioned below

1. Create a backup.sh file using command - vi backup.sh

2. On the vim, editor screen enter the command for database backup and to delete files older than 30 days

   #! /bin/bash
   
   EMAIL_ID= #<EMAIL_ID_SEND_AN_EMAIL>
   EMAIL_PASS= #<EMAIL_ID_PASSWORD>
   DB_NAME= #<DATABASE_NAME>
   DB_USER= #<DATABASE_USER>
   DB_PASS= #<DATABASE_USER_PASSWORD>
   DB_BACKUP_PATH= #<ABSOLUTE_PATH_TO_BACKUP_DIR>
   RCPT_EMAIL_ID= #<EMAIL_ID_TO_RECIEVE_AN_EMAIL>
   CLIENT_NAME_AND_ENVIRONMENT= #<CLIENT_NAME_AND_ENVIRONMENT_NAME>
   EMAIL_FILE=backup.txt
   BACKUP_LOG=db_backup.log
   
   backupStartTime() {
       echo "-----------------------------------------------------" &>> $BACKUP_LOG
       echo "DB backup start time:" `date +%x-%r` &>> $BACKUP_LOG
   }
   
   backupEndTime() {
       echo "DB backup end time:" `date +%x-%r` &>> $BACKUP_LOG
       echo "-----------------------------------------------------" &>> $BACKUP_LOG
   }
   backupFailedTime() {
       echo "DB backup fail time:" `date +%x-%r` &>> $BACKUP_LOG
       echo "-----------------------------------------------------" &>> $BACKUP_LOG
   }
   
   backupStartTime
   
   cat > $EMAIL_FILE << EOF
   Subject: [ IMPORTATNT ALERT ] : '$CLIENT_NAME_AND_ENVIRONMENT' : DB Backup Script Failed!
   
   Hello Buid Team,
   
   Please check the below errors
   
   ==================================================================================================================
   
   EOF
   
   set -o pipefail
   
   mysqldump -u$DB_USER -p$DB_PASS --single-transaction --skip-lock-tables --routines $DB_NAME --verbose &>> $EMAIL_FILE | gzip > $DB_BACKUP_PATH/OPENSPECIMEN_`date +\%d-\%m-\%Y`.SQL.gz
   DUMP_EXIT_CODE=$?
   
   cat >> $EMAIL_FILE << EOF
   
   ===================================================================================================================
   
   Thanks,
   Backup Monitor
   EOF
   
   if [[ $DUMP_EXIT_CODE -ne 0 ]]
   then
     sed -n '6,14p' $EMAIL_FILE &>> $BACKUP_LOG
     curl --ssl-reqd --url 'smtps://smtp.gmail.com:465' -u $EMAIL_ID:$EMAIL_PASS --mail-from $EMAIL_ID --mail-rcpt $RCPT_EMAIL_ID --upload-file $EMAIL_FILE
     backupFailedTime
   else
     backupEndTime
   fi
   
   rm backup.txt
   
   find $DB_BACKUP_PATH -mtime +30 -exec rm {} \;

   
   

3. Create a cron job with the command:  crontab -e

4. This will open a crontab (cron configuration file) and the format of the job should be as follows.

   minute hour day-of-month month day-of-week <path to the backup.sh file>

   This is an example file to take database backup nightly every day at 11:59
   

   59 23 * * * /home/backup.sh

Windows

MySQL

Need to create the task scheduler to run the backup script i.e bat file.

It will take the backup of database daily and also delete files older than 30 days

database-backup.bat

@echo off

echo Exporting OpenSpecimen database dump...

set backupFilename=OPENSPECIMEN_%DATE:~7,2%-%DATE:~4,2%-%DATE:~10,4%.SQL

mysqldump -uroot -py4txhySc --routines --result-file="path of database backup directory\%backupFilename%" <name of the databse>

set backupfldr=path of database backup directory

set retaindays=30

Forfiles -p %backupfldr% -s -m *.SQL -d -%retaindays% -c "cmd /c del /q @path"

echo Done!