Cron Tab for Daily Backup and DB Restoration

This article provides a step-by-step guide to automate MySQL database backups and perform database restoration on a Linux-based system.

The backup process utilizes a scheduled Cron job to ensure consistent and timely backups with minimal manual intervention.

Automation enhances data reliability, minimizes the risk of data loss, and facilitates recovery in the event of failures.

Prerequisites

• A Linux-based system with root or sudo access

• Sufficient storage space for backup files

• Basic understanding of CronTab and shell scripting

• (Optional) NFS mount if backup files must be stored on a network location

• Installed MySQL client utilities (e.g., mysqldump)

MySQL Database Backup

1. Create a Backup Script

   • Log in to the MySQL VM.

   • Create a directory for storing backup scripts and files:

   mkdir -p /home/ovaledge
   cd /home/ovaledge

   • Create a script file named mysql_backup.sh and add the following content:

     #!/bin/bash
     
     # Database credentials
     user="root"
     password="<mysql root user password>"
     host="<localhost or RDS endpoint>"
     db_name="ovaledgedb"
     
     # Backup storage path
     backup_path="<full path where backup should be stored>"
     
     # Date format for filename
     date=$(date +"%d-%b-%Y")
     
     # Log file path
     log_file="$backup_path/backup.log"
     
     # Create backup directory if it doesn't exist
     mkdir -p "$backup_path"
     
     # Perform mysqldump
     echo "[$(date)] Starting backup for $db_name" >> "$log_file"
     mysqldump --user="$user" --password="$password" --host="$host" "$db_name" > "$backup_path/${db_name}-${date}.sql" 2>>"$log_file"
     
     if [ $? -eq 0 ]; then
         echo "[$(date)] Backup successful: ${db_name}-${date}.sql" >> "$log_file"
     else
         echo "[$(date)] Backup failed for $db_name" >> "$log_file"
     fi
     
     # Delete backups older than 10 days
     find "$backup_path" -name "${db_name}-*.sql" -type f -mtime +10 -exec rm -f {} \; >> "$log_file" 2>&1
     echo "[$(date)] Old backups (older than 10 days) cleaned up." >> "$log_file"

     Update the values for MySQL username, password, host, and backup path as per the environment.

2. Assign Execution Permission

   • Make the script executable:

     chmod +x mysql_backup.sh

3. Test the Backup Script

   • Run the script manually to validate functionality:

     • If successful, the MySQL dump file (*.sql) is created in the backup directory.

     • Log details are stored in backup.log.

     ./mysql_backup.sh

4. Automate Backup with Cron

   • Open the CronTab editor:

     crontab -e

     Add the following entry to schedule a daily backup at 2:00 AM:

     0 2 * * * /home/ovaledge/mysql_backup.sh >> /home/ovaledge/backup.log 2>&1

   • Save and exit.

     This ensures the backup script runs automatically every day at 2:00 AM.

MySQL Database Restoration

1. Connect to MySQL

   • Log in to the MySQL VM and start the MySQL terminal:

     mysql -u root -p

2. Create Database and User

   • Run the following SQL commands:

     CREATE DATABASE ovaledgedb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
     
     CREATE USER IF NOT EXISTS 'ovaledge'@'localhost' IDENTIFIED BY '<password>';
     CREATE USER IF NOT EXISTS 'ovaledge'@'%' IDENTIFIED BY '<password>';
     
     GRANT ALL ON ovaledgedb.* TO 'ovaledge'@'localhost';
     GRANT ALL ON ovaledgedb.* TO 'ovaledge'@'%';

     Replace <password> with a strong user password.

   • At this stage, the ovaledgedb database is created, and access permissions are granted.

3. Restore from Backup

   • Inside the MySQL terminal, execute:

     source /home/ovaledge/ovaledgedb-22-May-2025.sql;

     Update the file path with the actual backup file to restore.

4. Update Application Configuration

   • Modify the oasis.properties file with the correct database connection details.

   • Restart the Tomcat service to apply changes:

     sudo systemctl start tomcat

   • Wait 3–5 minutes, then verify the application in a web browser.

Error Handling and Recovery

• If backup script fails:

  • Review backup.log for error messages.

  • Ensure mysqldump is installed and accessible in the system PATH.

• If restoration fails:

  • Confirm the backup file exists and is readable.

  • Check MySQL user privileges for the restore operation.

---

Copyright © 2025, OvalEdge LLC, Peachtree Corners, GA, USA.