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

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