Backup/Restore HOSxP database.

backup.sh

#!/bin/bash

# Author: GitHub Copilot (adapted by Satit Rianpit)
# Date: October 2024
# Description: This script performs the dumping of HOSxP database tables.
# Usage: ./backup.sh -f /path/to/config_file
# Parameters:
#   - -f: Specifies the configuration file to use.
# Returns: 0 if successful, 1 if an error occurs.
# Notes: This script requires the 'zip', 'openssl' and 'mysqldump' commands to be installed.

# Get configuration file path from command line argument
# This script processes command-line options using getopts.
# It currently supports the following options:
# -f <file> : Specifies the configuration file to use.
# Parse command line arguments

# Example usage: ./backup.sh -f /path/to/config_file

while getopts ":f:" opt; do
    case $opt in
        f)
            CONFIG_FILE="$OPTARG"
            ;;
        \?)
            echo "Invalid option: -$OPTARG" >&2
            exit 1
            ;;
        :)
            echo "Option -$OPTARG requires an argument." >&2
            exit 1
            ;;
    esac
done

# Check if the configuration file is provided
if [ -z "$CONFIG_FILE" ]; then
    echo "Usage: $0 -f /path/to/config_file"
    exit 1
fi

# Source the configuration file
source "$CONFIG_FILE"

# Configuration variables for the backup script
# 
# DB_HOST: The hostname of the database server. Default is "localhost".
# DB_USER: The username to connect to the database. Default is "sa".
# DB_NAME: The name of the database to back up. Default is "hos".
# DB_PASS: The password to connect to the database. Default is "sa".
# BACKUP_DIR: The directory where backups will be stored. Default is "/tmp/backup".
# SKIP_LOG_TABLES: Whether to skip log tables during backup. Default is "Y".
# THREADS: The number of threads to use for the backup process. Default is 8.
# FAILED_TABLES_FILE: The file to log tables that failed to back up. Default is "failed_tables.log".
# TELEGRAM_BOT_TOKEN: The token for the Telegram bot to send notifications. Default is "xxxxxx".
# TELEGRAM_CHAT_ID: The chat ID for the Telegram bot to send notifications. Default is "-123456".
#
# Example configuration:
# DB_HOST="db.example.com"
# DB_USER="admin"
# DB_NAME="production_db"
# DB_PASS="securepassword"
# BACKUP_DIR="/var/backups"
# SKIP_LOG_TABLES="N"
# THREADS=4
# FAILED_TABLES_FILE="/var/log/failed_tables.log"
# TELEGRAM_BOT_TOKEN="your_telegram_bot_token"
# TELEGRAM_CHAT_ID="your_telegram_chat_id"

: "${DB_HOST:-"localhost"}"
: "${DB_USER:-"sa"}"
: "${DB_NAME:-"hos"}"
: "${DB_PASS:-"sa"}"
: "${BACKUP_DIR:-"/tmp/backup"}"
: "${SKIP_LOG_TABLES:-"Y"}"
: "${THREADS:-8}"
: "${FAILED_TABLES_FILE:-"failed_tables.log"}"
: "${TELEGRAM_BOT_TOKEN:-"xxxxxx"}"
: "${TELEGRAM_CHAT_ID:-"-123456"}"

DATE=$(date +%Y%m%d_%H%M%S)
TARGET_DIR="$BACKUP_DIR/dump_$DATE"

# Generate a random password for the zip file
ZIP_PASSWORD=$(openssl rand -base64 12 | tr -dc 'a-zA-Z0-9' | fold -w 12 | head -n 1)

# Create backup directory
mkdir -p "$TARGET_DIR"
cd "$TARGET_DIR"

# Get list of tables, optionally skipping log tables
if [ "$SKIP_LOG_TABLES" = "Y" ]; then
    mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -N -e "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'" | awk '{print $1}' | grep -vE '_log$|^ksklog$' > tables.txt
else
    mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -N -e "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'" | awk '{print $1}' > tables.txt
fi

# This script sets options for the mysqldump command used in the backup process.
# 
# MYSQLDUMP_OPTS:
# --single-transaction: Ensures a consistent backup by dumping all tables in a single transaction.
# --quick: Fetches rows from the server one at a time, reducing memory usage.
# --lock-tables=false: Prevents locking of tables during the dump process.
# --add-drop-table: Adds a DROP TABLE statement before each CREATE TABLE statement in the dump.
# --skip-events: Excludes events from the dump.
# --skip-triggers: Excludes triggers from the dump.
# --skip-routines: Excludes stored procedures and functions from the dump.
MYSQLDUMP_OPTS="--single-transaction --quick --lock-tables=false --add-drop-table --skip-events --skip-triggers --skip-routines"

# Function to dump and gzip a table
dump_table() {
    local table=$1
    echo "Dumping table: $table"
    # This script performs a MySQL database table backup using mysqldump.
    # It connects to the MySQL server using the provided host, user, and password.
    # The backup is compressed using gzip and saved with the table name as the filename.
    # Variables:
    #   DB_HOST - The hostname of the MySQL server.
    #   DB_USER - The username to connect to the MySQL server.
    #   DB_PASS - The password to connect to the MySQL server.
    #   MYSQLDUMP_OPTS - Additional options for mysqldump.
    #   DB_NAME - The name of the database to back up.
    #   table - The name of the table to back up.
    mysqldump -h$DB_HOST -u$DB_USER -p$DB_PASS $MYSQLDUMP_OPTS $DB_NAME $table | gzip > "$table.sql.gz"
    # This script checks the exit status of the previous command.
    # If the exit status is 0 (indicating success), it prints a success message for the specified table.
    # If the exit status is non-zero (indicating failure), it prints an error message to stderr,
    # logs the failed table name to a specified file, and exits the script with a status of 1.
    if [ $? -eq 0 ]; then
        echo "Successfully dumped $table"
    else
        echo "Error dumping $table" >&2
        echo $table >> "$TARGET_DIR/$FAILED_TABLES_FILE"
        exit 1
    fi
}

# Export the dump_table function and necessary variables for parallel execution
export -f dump_table
export DB_HOST DB_USER DB_PASS DB_NAME TARGET_DIR FAILED_TABLES_FILE MYSQLDUMP_OPTS

# Start time
START_TIME=$(date +%s)
START_TIME_FORMATTED=$(date +"%Y-%m-%d %H:%M:%S")

# This script reads table names from the file 'tables.txt' and processes each table name in parallel.
# The number of parallel processes is determined by the value of the THREADS variable.
# For each table name, the script calls the 'dump_table' function.
# The 'xargs' command is used to handle the parallel processing and argument passing.
# -n1: Use one argument per command line.
# -P$THREADS: Run up to $THREADS processes at a time.
# -I{}: Replace occurrences of {} in the command with the argument read from 'tables.txt'.
cat tables.txt | xargs -n1 -P$THREADS -I{} bash -c 'dump_table "$@"' _ {}

# Check if dumps were successful
if [ $? -eq 0 ]; then
    # Zip the backup folder and password protect it
    zip -r -e -P "$ZIP_PASSWORD" "$DB_NAME-$DATE.zip" .
    if [ $? -eq 0 ]; then
        echo "Successfully zipped and password protected the backup folder"
        rm -f *.sql.gz
    
        # End time
        END_TIME=$(date +%s)
        END_TIME_FORMATTED=$(date +"%Y-%m-%d %H:%M:%S")
        ELAPSED_TIME=$((END_TIME - START_TIME))
        
        # Format elapsed time in HH:MM:SS
        HOURS=$((ELAPSED_TIME / 3600))
        MINUTES=$(( (ELAPSED_TIME % 3600) / 60 ))
        SECONDS=$((ELAPSED_TIME % 60))
        printf "Backup completed successfully in %02d:%02d:%02d\n" $HOURS $MINUTES $SECONDS

        # Send telegram message
        if [ -n "$TELEGRAM_BOT_TOKEN" ] && [ -n "$TELEGRAM_CHAT_ID" ]; then
            # Get the size of the backup file in a human-readable format and store it in the FILE_SIZE variable.
            # The backup file is named using the database name and the current date, followed by a .zip extension.
            # The 'du -sh' command is used to get the size, and 'cut -f1' extracts the size value.
            FILE_SIZE=$(du -sh "$DB_NAME-$DATE.zip" | cut -f1)

            # Construct the message to be sent via Telegram
            # The message includes the start and end times of the backup, the duration, the backup file name,
            # the file size, any failed tables, and the zip password.
            MESSAGE="Backup started at $START_TIME_FORMATTED and finished at $END_TIME_FORMATTED. Completed successfully in $HOURS hours, $MINUTES minutes, and $SECONDS seconds \nBackup file: $DB_NAME-$DATE.zip \nFile size: $FILE_SIZE \nFailed tables: $(cat "$TARGET_DIR/$FAILED_TABLES_FILE" 2>/dev/null) \nZip password: $ZIP_PASSWORD"

            # This script sends a message to a specified Telegram chat using the Telegram Bot API.
            # It makes a POST request to the sendMessage endpoint with the chat ID and message text.
            # The TELEGRAM_BOT_TOKEN environment variable should contain the bot token.
            # The TELEGRAM_CHAT_ID environment variable should contain the chat ID.
            # The MESSAGE environment variable should contain the message text to be sent.
            # The output of the curl command is redirected to /dev/null to suppress any output.
            curl -s -X POST "https://api.telegram.org/bot$TELEGRAM_BOT_TOKEN/sendMessage" \
                 -H "Content-Type: application/json" \
                 -d "{\"chat_id\": \"$TELEGRAM_CHAT_ID\", \"text\": \"$MESSAGE\"}" > /dev/null 2>&1

            # Check the exit status of the previous command.
            # If the exit status is not equal to 0 (indicating an error),
            # write the zip password to a file named "zip_password_<DB_NAME>_<DATE>.txt"
            # in the user's home directory.
            if [ $? -ne 0 ]; then
                echo "Zip password: $ZIP_PASSWORD" > "$HOME/zip_password_${DB_NAME}_$DATE.txt"
            fi
        fi
    else
        echo "Error zipping the backup folder" >&2
        exit 1
    fi
else
    echo "Backup failed" >&2
    exit 1
fi

restore.sh

#!/bin/bash

# Author: GitHub Copilot (adapted by Satit Rianpit)
# Date: October 2024
# Description: This script performs the restoration of HOSxP database backups.
# Usage: ./restore.sh -f /path/to/config_file
# Parameters:
#   - -f: Specifies the configuration file to use.
# Returns: 0 if successful, 1 if an error occurs.
# Notes: This script requires the 'unzip' and 'mysql' commands to be installed.

# Get configuration file path from command line argument
# This script processes command-line options using getopts.
# It currently supports the following options:
# -f <file> : Specifies the configuration file to use.
#
# Usage example:
# ./restore.sh -f config.txt
while getopts ":f:" opt; do
    case $opt in
        f)
            CONFIG_FILE="$OPTARG"
            ;;
        \?)
            echo "Invalid option: -$OPTARG" >&2
            exit 1
            ;;
        :)
            echo "Option -$OPTARG requires an argument." >&2
            exit 1
            ;;
    esac
done

# Check if the configuration file argument is provided
if [ -z "$CONFIG_FILE" ]; then
    echo "Usage: $0 -f /path/to/config_file"
    exit 1
fi

CONFIG_FILE="$1"

# Check if configuration file exists
if [ ! -f "$CONFIG_FILE" ]; then
    echo "Configuration file not found!"
    exit 1
fi

# Source the configuration file
source "$CONFIG_FILE"

# Ensure required variables are set or use default values
# This script restores a database from a backup file.
# It uses several environment variables to configure the restore process.
#
# Environment Variables:
#   DB_HOST: The database host (default: "localhost").
#   DB_USER: The database user (default: "sa").
#   DB_NAME: The database name (default: "hos").
#   DB_PASS: The database password (default: "sa").
#   BACKUP_FILE: The path to the backup file (default: "/tmp/restored/backup.zip").
#   ZIP_PASSWORD: The password for the zip file (default: "123456").
#   TARGET_DIR: The directory where the backup will be extracted (default: "/tmp/restored").
#   THREADS: The number of threads to use for the restore process (default: 6).
#   FAILED_RESTORE_FILE: The file to log failed restore attempts (default: "failed_restore.log").
#   TELEGRAM_BOT_TOKEN: The token for the Telegram bot to send notifications (default: "xxxxxx").
#   TELEGRAM_CHAT_ID: The chat ID for the Telegram bot to send notifications (default: "123456").
#
# Example configuration:
# DB_HOST="localhost"
# DB_USER="sa"
# DB_NAME="hos"
# DB_PASS="sa"
# BACKUP_FILE="/tmp/restored/backup.zip"
# ZIP_PASSWORD="123456"
# TARGET_DIR="/tmp/restored"
# THREADS=6
# FAILED_RESTORE_FILE="failed_restore.log"
# TELEGRAM_BOT_TOKEN="xxxxxx"
# TELEGRAM_CHAT_ID="123456"

: "${DB_HOST:-"localhost"}"
: "${DB_USER:-"sa"}"
: "${DB_NAME:-"hos"}"
: "${DB_PASS:-"sa"}"
: "${BACKUP_FILE:-"/tmp/restored/backup.zip"}"
: "${ZIP_PASSWORD:-"123456"}"
: "${TARGET_DIR:-"/tmp/restored"}"
: "${THREADS:-6}"
: "${FAILED_RESTORE_FILE:-"failed_restore.log"}"
: "${TELEGRAM_BOT_TOKEN:-"xxxxxx"}"
: "${TELEGRAM_CHAT_ID:-"123456"}"

# Define the directory where the extracted files will be extracted.
# This directory is set to a subdirectory named 'extracted' within the target directory.
EXTRACT_DIR="$TARGET_DIR/extracted"

# Remove old path if it exists
rm -rf "$EXTRACT_DIR"

# Create target directory
mkdir -p "$EXTRACT_DIR"

# Unzip the backup file without creating a folder structure
unzip -j -P "$ZIP_PASSWORD" "$BACKUP_FILE" -d "$EXTRACT_DIR"

# Function to restore a table from a compressed SQL file.
# Arguments:
#   $1 - The path to the compressed SQL file (.sql.gz) to be restored.
# Environment Variables:
#   DB_HOST - The hostname of the MySQL server.
#   DB_USER - The username to connect to the MySQL server.
#   DB_PASS - The password to connect to the MySQL server.
#   DB_NAME - The name of the database to restore the table into.
#   EXTRACT_DIR - The directory where the failed restore log file is located.
#   FAILED_RESTORE_FILE - The name of the file to log failed restores.
# Usage:
#   restore_table /path/to/table.sql.gz
restore_table() {
    local table_file=$1
    local table_name=$(basename "$table_file" .sql.gz)
    echo "Restoring table: $table_name"
    # This script restores a MySQL database from a gzipped SQL dump file.
    # It uses gunzip to decompress the file and pipes the output directly into the MySQL client.
    # Variables:
    #   $table_file - The path to the gzipped SQL dump file.
    #   $DB_HOST - The hostname of the MySQL server.
    #   $DB_USER - The username to connect to the MySQL server.
    #   $DB_PASS - The password to connect to the MySQL server.
    #   $DB_NAME - The name of the database to restore.
    gunzip < "$table_file" | mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME
    if [ $? -eq 0 ]; then
        echo "Successfully restored $table_name"
    else
        # Log the error and the table name to the failed restore file
        echo "Error restoring $table_name" >&2
        echo $table_name >> "$EXTRACT_DIR/$FAILED_RESTORE_FILE"
        exit 1
    fi
}

# Export function for parallel execution
export -f restore_table
# This script sets environment variables for database restoration.
# 
# Environment Variables:
# - DB_HOST: The hostname of the database server.
# - DB_USER: The username for the database.
# - DB_PASS: The password for the database user.
# - DB_NAME: The name of the database to restore.
# - EXTRACT_DIR: The directory where the backup files are extracted.
# - FAILED_RESTORE_FILE: The file to log failed restore attempts.
export DB_HOST DB_USER DB_PASS DB_NAME EXTRACT_DIR FAILED_RESTORE_FILE

# Start time
START_TIME=$(date +%s)

# Change to backup directory
cd "$EXTRACT_DIR"

# This script restores SQL tables from compressed .sql.gz files in parallel.
# It lists all .sql.gz files in the current directory and uses xargs to 
# process each file with the restore_table function.
# 
# -n1: Passes one argument at a time to the restore_table function.
# -P$THREADS: Runs up to $THREADS processes in parallel.
# -I{}: Replaces {} with the current file name being processed.
# The restore_table function is expected to handle the actual restoration process.
ls *.sql.gz | xargs -n1 -P$THREADS -I{} bash -c 'restore_table "$@"' _ {}

# Check if restores were successful
if [ ! -f "$EXTRACT_DIR/$FAILED_RESTORE_FILE" ]; then
    # End time
    END_TIME=$(date +%s)
    ELAPSED_TIME=$((END_TIME - START_TIME))
    
    # Format elapsed time in HH:MM:SS
    HOURS=$((ELAPSED_TIME / 3600))
    MINUTES=$(( (ELAPSED_TIME % 3600) / 60 ))
    SECONDS=$((ELAPSED_TIME % 60))
    echo "Restore completed successfully in $(printf "%02d:%02d:%02d" $HOURS $MINUTES $SECONDS)"

    if [ -n "$TELEGRAM_BOT_TOKEN" ] && [ -n "$TELEGRAM_CHAT_ID" ]; then
        FILE_SIZE=$(du -sh "$BACKUP_FILE" | cut -f1)
        # This script constructs a message indicating the successful completion of a restore operation.
        # The message includes the duration of the restore process in hours, minutes, and seconds,
        # as well as the name and size of the backup file.
        MESSAGE="Restore completed successfully in $HOURS hours, $MINUTES minutes, and $SECONDS seconds\nFile name: $BACKUP_FILE\nFile size: $FILE_SIZE"
        # This script sends a message to a specified Telegram chat using the Telegram Bot API.
        # It uses the curl command to make a POST request to the Telegram API endpoint.
        # The following environment variables must be set before running this script:
        # - TELEGRAM_BOT_TOKEN: The token for the Telegram bot.
        # - TELEGRAM_CHAT_ID: The ID of the chat where the message will be sent.
        # - MESSAGE: The message text to be sent.
        curl -s -X POST "https://api.telegram.org/bot$TELEGRAM_BOT_TOKEN/sendMessage" \
        -H "Content-Type: application/json" \
        -d "{\"chat_id\": \"$TELEGRAM_CHAT_ID\", \"text\": \"$MESSAGE\"}"
    fi

else
    echo "Restore failed" >&2
    exit 1
fi

How to:

Chage mode

chmod +x backup.sh
chmod +x restore.sh

Create configure file

backup.conf

DB_HOST="localhost"
DB_USER="sa"
DB_NAME="hos"
DB_PASS="sa"
BACKUP_DIR="/var/backups"
SKIP_LOG_TABLES="N"
THREADS=8
FAILED_TABLES_FILE="/var/log/failed_tables.log"
TELEGRAM_BOT_TOKEN="your_telegram_bot_token"
TELEGRAM_CHAT_ID="your_telegram_chat_id"

restore.conf

DB_HOST="localhost"
DB_USER="sa"
DB_NAME="hos"
DB_PASS="sa"
BACKUP_FILE="/tmp/restored/backup.zip"
ZIP_PASSWORD="123456"
TARGET_DIR="/tmp/restored"
THREADS=6
FAILED_RESTORE_FILE="failed_restore.log"
TELEGRAM_BOT_TOKEN="your_telegram_bot_token"
TELEGRAM_CHAT_ID="your_telegram_chat_id"

Backup

./backup.sh -f backup.conf

Restore

./restore.sh -f restore.conf

Crontab.

crontab -e

0 0 * * * /home/mybackup/backup.sh -f /home/mybackup/backup.conf >> /home/mybackup/backup_$(date +\%Y\%m\%d_\%H\%M\%S).log 2>&1

Verify job.

crontab -l