BASH
Automating Daily Database Backups for Web Applications
Secure your web application data by scheduling automatic daily database backups using bash scripts for MySQL or PostgreSQL, ensuring reliable data recovery and disaster preparedness.
#!/bin/bash
# Configuration
DB_TYPE="mysql" # or "postgresql"
DB_HOST="localhost"
DB_USER="your_db_user"
DB_PASSWORD="your_db_password"
DB_NAME="your_database_name"
BACKUP_DIR="/var/backups/databases"
RETENTION_DAYS=7 # Number of days to keep backups
# --- DO NOT EDIT BELOW THIS LINE ---
DATE=$(date +%Y%m%d%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"
# Create backup directory if it doesn't exist
mkdir -p "${BACKUP_DIR}"
echo "Starting database backup for '${DB_NAME}' (${DB_TYPE})..."
if [ "${DB_TYPE}" == "mysql" ]; then
mysqldump --host="${DB_HOST}" --user="${DB_USER}" --password="${DB_PASSWORD}" \
"${DB_NAME}" | gzip > "${BACKUP_FILE}"
elif [ "${DB_TYPE}" == "postgresql" ]; then
PGPASSWORD="${DB_PASSWORD}" pg_dump --host="${DB_HOST}" --username="${DB_USER}" \
"${DB_NAME}" | gzip > "${BACKUP_FILE}"
else
echo "Error: Unsupported DB_TYPE specified. Use 'mysql' or 'postgresql'." >&2
exit 1
fi
if [ $? -eq 0 ]; then
echo "Backup successful: '${BACKUP_FILE}'"
else
echo "Error: Database backup failed!" >&2
rm -f "${BACKUP_FILE}" # Remove incomplete backup file
exit 1
fi
# Clean up old backups
echo "Cleaning up old backups (older than ${RETENTION_DAYS} days)..."
find "${BACKUP_DIR}" -type f -name "${DB_NAME}_*.sql.gz" -mtime +${RETENTION_DAYS} -delete
echo "Backup and cleanup complete."
How it works: This script automates the process of creating compressed backups for MySQL or PostgreSQL databases. It's configured with database credentials, host, name, and a backup directory. Based on the `DB_TYPE` variable, it uses either `mysqldump` or `pg_dump` to export the database content, which is then piped to `gzip` for compression, creating a timestamped `.sql.gz` file. The script also includes a cleanup mechanism using `find` to automatically delete backups older than a specified number of days, helping manage disk space and adhere to data retention policies. This is vital for disaster recovery plans in web development.