# 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:

   ```bash
   mkdir -p /home/ovaledge
   cd /home/ovaledge
   ```

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

     ```bash
     #!/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"
     ```

     <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p>Update the values for MySQL username, password, host, and backup path as per the environment.</p></div>

2. **Assign Execution Permission**
   * Make the script executable:

     ```bash
     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`.

     ```bash
     ./mysql_backup.sh
     ```

4. **Automate Backup with Cron**
   * Open the CronTab editor:

     ```bash
     crontab -e
     ```

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

     ```bash
     0 2 * * * /home/ovaledge/mysql_backup.sh >> /home/ovaledge/backup.log 2>&1
     ```
   * Save and exit.<br>

     <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p>This ensures the backup script runs automatically every day at 2:00 AM.</p></div>

## MySQL Database Restoration

1. **Connect to MySQL**
   * Log in to the MySQL VM and start the MySQL terminal:

     ```bash
     mysql -u root -p
     ```

2. **Create Database and User**

   * Run the following SQL commands:

     ```sql
     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'@'%';
     ```

     <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p>Replace <code>&#x3C;password></code> with a strong user password.</p></div>
   * At this stage, the `ovaledgedb` database is created, and access permissions are granted.

3. **Restore from Backup**
   * Inside the MySQL terminal, execute:

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

     <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p>Update the file path with the actual backup file to restore.</p></div>

4. **Update Application Configuration**
   * Modify the `oasis.properties` file with the correct database connection details.
   * Restart the Tomcat service to apply changes:

     ```bash
     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.