@@ -0,0 +1,211 @@
#! /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:" ; 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) $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
siteslave revised this gist