A bash script to automate MySQL database dumps with customizable filename prefixes and timestamp patterns. This script dynamically generates the dump file name using the format: <prefix>_<env>_YYYYMMDDHHmmss_TIMEZONE.sql

README.md

MySQL Database Backup Script

A bash script to automate MySQL database dumps with customizable filename prefixes and timestamp patterns. This script dynamically generates the dump file name using the format:

<prefix>_<env>_YYYYMMDDHHmmss_TIMEZONE.sql

Features:

• Customizable filename prefix (PREFIX) to identify your app or backup purpose.
• Timestamp format includes the current date, time, and system timezone.
• Measures the time taken to create the backup.
• Outputs the file size of the dump.
• Securely prompts for the MySQL password (no password hardcoding).

Usage:

• Replace <host>, <user>, <db_name>, <env>, and <app_name> with your own values.
• Run the script to create a timestamped SQL dump of your MySQL database.

backup_mysql_db.sh

#!/bin/bash

# Set environment variables
HOST="<host>"         # Replace with your host
USER="<user>"         # Replace with your MySQL user
DB_NAME="<db_name>"   # Replace with your database name
ENV="<env>"           # Replace with your environment (e.g., prd, dev, etc.)
PREFIX="<app_name>"       # Define your customizable prefix (e.g., app name, backup, etc.)

# Get the current date and time in UTC (you can change this to the desired timezone if needed)
TIMESTAMP=$(date +"%Y%m%d%H%M%S")
TIMEZONE=$(date +"%Z%z")  # Fetches the system's timezone

# Define the output file name
FILE_NAME="${PREFIX}_${ENV}_${TIMESTAMP}_${TIMEZONE}.sql"

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

# Run the MySQL dump command (will prompt for password)
mysqldump -h $HOST -u $USER -p $DB_NAME > $FILE_NAME

# End time
END_TIME=$(date +%s)

# Calculate time taken in seconds
TIME_TAKEN=$((END_TIME - START_TIME))

# Get the size of the dump file
FILE_SIZE=$(du -h "$FILE_NAME" | cut -f1)

# Check if the dump was successful
if [ $? -eq 0 ]; then
    echo "Database dump created successfully: $FILE_NAME"
    echo "Time taken: $TIME_TAKEN seconds"
    echo "File size: $FILE_SIZE"
else
    echo "Error occurred during database dump."
    exit 1
fi