Automating Full Database Backups

Automating full database backups ensures data is preserved in case of failure, corruption, or accidental deletion. This guide provides a detailed bash script to automate database backups, examples, and technical explanations of each step.

Key Features of the Script

Database Support:
- Works with MySQL/MariaDB and PostgreSQL.
Backup Management:
- Compresses backups to save space.
- Automatically deletes old backups based on retention policy.
Customizable Settings:
- Support for scheduling backups with cron.
Icons for Visual Feedback:
- ✔ (Success), ⚠️ (Warning), and ✘ (Error).
Error Handling:
- Verifies backup success and logs errors.

The Script

#!/bin/bash

# Full Database Backup Automation Script
# Author: [Your Name]
# Version: 1.0

# Icons for feedback
CHECK="\u2714" # ✔
WARNING="\u26A0" # ⚠️
CROSS="\u274C" # ✘

# Configuration
BACKUP_DIR="/path/to/backup"          # Directory to store backups
RETENTION_DAYS=7                      # Retain backups for this many days
LOG_FILE="/var/log/db_backup.log"     # Log file for backup process

# Database-specific settings
DB_TYPE="mysql"                       # Supported: mysql, postgres
DB_HOST="localhost"                   # Database host
DB_PORT=3306                          # Database port (3306 for MySQL, 5432 for PostgreSQL)
DB_USER="backup_user"                 # Database username
DB_PASSWORD="password123"             # Database password
DB_NAME="my_database"                 # Database name (leave empty to back up all databases)

# Ensure the backup directory exists
mkdir -p "$BACKUP_DIR"

# Function: Log messages
log_message() {
    echo -e "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Function: MySQL Backup
backup_mysql() {
    local backup_file="${BACKUP_DIR}/mysql_backup_$(date '+%Y%m%d%H%M%S').sql.gz"

    log_message "Starting MySQL backup..."
    if [[ -z "$DB_NAME" ]]; then
        mysqldump --host="$DB_HOST" --port="$DB_PORT" --user="$DB_USER" --password="$DB_PASSWORD" --all-databases | gzip > "$backup_file"
    else
        mysqldump --host="$DB_HOST" --port="$DB_PORT" --user="$DB_USER" --password="$DB_PASSWORD" "$DB_NAME" | gzip > "$backup_file"
    fi

    if [[ $? -eq 0 ]]; then
        log_message "${CHECK} MySQL backup completed: $backup_file"
    else
        log_message "${CROSS} MySQL backup failed."
        exit 1
    fi
}

# Function: PostgreSQL Backup
backup_postgres() {
    local backup_file="${BACKUP_DIR}/postgres_backup_$(date '+%Y%m%d%H%M%S').sql.gz"

    log_message "Starting PostgreSQL backup..."
    if [[ -z "$DB_NAME" ]]; then
        PGPASSWORD="$DB_PASSWORD" pg_dumpall --host="$DB_HOST" --port="$DB_PORT" --username="$DB_USER" | gzip > "$backup_file"
    else
        PGPASSWORD="$DB_PASSWORD" pg_dump --host="$DB_HOST" --port="$DB_PORT" --username="$DB_USER" --dbname="$DB_NAME" | gzip > "$backup_file"
    fi

    if [[ $? -eq 0 ]]; then
        log_message "${CHECK} PostgreSQL backup completed: $backup_file"
    else
        log_message "${CROSS} PostgreSQL backup failed."
        exit 1
    fi
}

# Function: Retention Policy
cleanup_old_backups() {
    log_message "Cleaning up backups older than $RETENTION_DAYS days..."
    find "$BACKUP_DIR" -type f -name "*.gz" -mtime +$RETENTION_DAYS -exec rm -f {} \;

    if [[ $? -eq 0 ]]; then
        log_message "${CHECK} Old backups cleaned successfully."
    else
        log_message "${WARNING} Failed to clean old backups."
    fi
}

# Main Script Execution
log_message "=== Database Backup Automation Script ==="

case "$DB_TYPE" in
    mysql)
        backup_mysql
        ;;
    postgres)
        backup_postgres
        ;;
    *)
        log_message "${CROSS} Unsupported database type: $DB_TYPE"
        exit 1
        ;;
esac

cleanup_old_backups
log_message "Backup process completed successfully."

Step-by-Step Explanation

1. Configuration

Set variables for:

Backup Directory: Location to store backups.
Retention Policy: Number of days to keep old backups.
Database Connection Details:
- Host (DB_HOST)
- Port (DB_PORT)
- Credentials (DB_USER, DB_PASSWORD)
- Database name (DB_NAME).

Example:

BACKUP_DIR="/backup"
DB_TYPE="mysql"
DB_NAME="my_database"

2. MySQL Backup

Command Used:

mysqldump: Exports databases to SQL files.
gzip: Compresses the output to save space.

If DB_NAME is empty, all databases are backed up:

mysqldump --all-databases | gzip > backup.sql.gz

If a specific database is provided:

mysqldump --database my_database | gzip > backup.sql.gz

Output Example:

[2025-01-09 10:00:00] ✔ MySQL backup completed: /backup/mysql_backup_20250109100000.sql.gz

3. PostgreSQL Backup

Command Used:

pg_dump for single databases:

pg_dump --dbname=my_database | gzip > backup.sql.gz

pg_dumpall for all databases:
```
pg_dumpall | gzip > backup.sql.gz
```

Output Example:

[2025-01-09 10:30:00] ✔ PostgreSQL backup completed: /backup/postgres_backup_20250109103000.sql.gz

4. Retention Policy

The find command deletes old backups:

find "$BACKUP_DIR" -type f -name "*.gz" -mtime +$RETENTION_DAYS -exec rm -f {} \;

Output Example:

[2025-01-09 11:00:00] ✔ Old backups cleaned successfully.

Usage Examples

1. Full Backup of MySQL

Update the configuration:
```
DB_TYPE="mysql"
DB_NAME="my_database"
```
Run the script:
```
bash db_backup.sh
```

2. Full Backup of PostgreSQL

Update the configuration:

DB_TYPE="postgres"
DB_NAME="my_database"

Run the script:
```
bash db_backup.sh
```

Automating Backups with `cron`

Schedule the script to run daily:

Edit the crontab:
```
crontab -e
```

Add the schedule:

0 2 * * * /path/to/db_backup.sh >> /var/log/db_backup.log 2>&1

This runs the backup script daily at 2 AM.

Advanced Features

1. Notification on Backup Completion

Send email alerts using mail:

echo "Backup completed successfully!" | mail -s "Backup Notification" [email protected]

2. Remote Backup Storage

Sync backups to a remote server using rsync:

rsync -avz /backup/ user@remote-server:/remote-backup/

3. Incremental Backups

Modify the script for incremental backups:

For MySQL:

mysqlbinlog --read-from-remote-server > incremental_backup.sql

For PostgreSQL:
```
pg_basebackup -D /backup/incremental/
```

Implementation Scenarios

Scenario 1: Single Database Backup

Configure DB_NAME with the database name.
Backups will only include the specified database.

Scenario 2: Full Database Backup

Leave DB_NAME empty to back up all databases.

Scenario 3: Cloud Integration

Upload backups to cloud storage:

aws s3 cp /backup/ s3://my-backup-bucket/ --recursive

Best Practices

Use Dedicated Backup Users:

Create a user with minimal privileges for backups:

GRANT SELECT, SHOW VIEW, LOCK TABLES, EVENT ON *.* TO 'backup_user'@'localhost';

Encrypt Backups:
- Encrypt backups using gpg:
```
gpg -c backup.sql.gz
```
Test Restorations:
- Regularly test restoring backups to verify integrity.
Monitor Backup Status:
- Integrate monitoring tools like Prometheus or Nagios for backup status.

Conclusion

This automated database backup script provides a reliable way to manage and secure your database backups for both MySQL and PostgreSQL. With features like retention policies, logging, and easy scheduling, it ensures that your critical data is safe and recoverable.