Steps to Implement CronTab for Daily Backup and DB Restoration

A database backup is a process of having a copy of your database and storing it in a different location from the original. Database backup is essential for data recovery, business continuity, legal compliance, and disaster recovery.

This document explains the steps users must follow to implement CronTab for Daily Backup.

Steps to Implement CronTab for Daily Backup

Please follow the below steps to take database backup periodically using a cron job.

Step#1: Create a sh file using the sudo vi /home/ovaledge/backup.sh command with the below content.

Content:

#!/bin/bash

# Database details & credentials

user="root"

password="<mysql root user password>"

host="<localhost/RDS Endpoint>"

db_name="ovaledgedb"

# Other options

backup_path="<database backup path>"

date=$(date +"%d-%b-%Y")

# taking backup of database in a sql file

mysqldump --user=$user --password=$password --host=$host $db_name > $backup_path/$db_name-$date.sql

And the backup file looks like the below file.

1-Mar-18-2023-05-46-03-5402-AM

Step#2: Configure the crontab in /etc/crontab file using vi /etc/crontab command.

Then, a new file will be opened where users can set up a Cron Job as per the below examples based on the customer’s requirement.

Example:

*/5 * * * * ovaledge sh /home/ovaledge/cronjob/backup.sh

0 19 * * 5 ovaledge sh /home/ovaledge/cronjob/backup.sh

Deleting backup files older than 10 days

(This step is optional)

If the user wishes to delete the backups which are older than 10 days as per the retention policy, then follow the below steps.

Open the backup.sh file and add the below line in the last of the backup.sh.
find "$backup_path" -mindepth 1 -name ovaledgedb* -mmin +2 -exec rm {} \;
The above command will check the date of backups and delete the backup if it is older than 10 days.
After making the above changes the backup.sh file will be as follows:

Writing Logs of Backup to a File

This is an optional step.

If the user wants to store the logs generated by the backup.sh job, please follow the below steps.

Open the backup.sh file and make the change in mysqldump command as shown below:

If Ubuntu:
mysqldump --user=$user --password=$password --host=$host $db_name > $backup_path/$db_name-$date.sql >> $backup_path/backup.log

If Redhat:

mysqldump --user=$user --password=$password --host=$host $db_name > $backup_path/$db_name-$date.sql 2> $backup_path/backup.log
This will write the logs to a file called backup.log.
After making the above changes, the backup.sh file will be as follows.

MySQL Database Restoration from SQL Dump (Database Backups)

In case of Disaster when the application needs to be restored in the DR location or for DR testing follow the below steps (In Sequence)

Navigate to the mysql dump path that is configured for Database backups to be stored.

For the restoration process, the latest copy of the database dump (Based on the date) has to be taken and restored below commands.

Connect to the MySQL using following command
# mysql -u root -p
Create an ovaledge user with password if not created.
CREATE USER 'ovaledge'@'localhost' IDENTIFIED BY '0valEdge!' ;
CREATE USER 'ovaledge'@'%' IDENTIFIED BY '0valEdge!' ;
Create a database as required & grant all privileges on the database to ovaledge users.
CREATE DATABASE ovaledgedbtest;
GRANT ALL ON ovaledgedbtest.* TO 'ovaledge'@'localhost';
GRANT ALL ON ovaledgedbtest.* TO 'ovaledge'@'%';
Use the new database & run the Dumpfile using below queries
use ovaledgedbtest;
source <dumpfile_fullpath>/mysqldump_file.sql
Change the database name in the oasis.properties file using tomcat in extprop directory as shown below.
# vi <path_extprop>/oasis.properties
Restart the tomcat after changes in the oasis.properties file, the OE application will be up & running with a new database.