Automated MySQL Database Backup Script with Parameterized Configuration and Retention Management

This script supports parameterized configuration of database connection information, backup storage path, and expiration file cleanup strategy, implementing core functions such as full backup, expiration cleanup, detailed logging, and dynamic adjustment via command‑line arguments or a configuration file.

#!/bin/bash # 自动化数据库备份脚本 v1.0 # 最后更新：2025-04-17 ############################ ### 参数配置区（支持命令行覆盖）### ############################ # 默认配置 DB_HOST="localhost" DB_PORT="3306" DB_USER="root" DB_PASSWORD="123456" DB_NAME="production_db" BACKUP_DIR="/data/backups" RETENTION_DAYS=7 LOG_FILE="/var/log/db_backup.log" ######################## ### 函数定义区 ### ######################## # 日志记录函数 log() { local timestamp=$(date "+%Y-%m-%d %H:%M:%S") echo "[${timestamp}] $1" | tee -a ${LOG_FILE} } # 参数解析函数 parse_args() { while getopts "h:P:u:p:d:r:l:" opt; do case $opt in h) DB_HOST="$OPTARG" ;; P) DB_PORT="$OPTARG" ;; u) DB_USER="$OPTARG" ;; p) DB_PASSWORD="$OPTARG" ;; d) DB_NAME="$OPTARG" ;; r) RETENTION_DAYS="$OPTARG" ;; l) BACKUP_DIR="$OPTARG" ;; *) echo "用法: $0 [-h 主机] [-P 端口] [-u 用户] [-p 密码] [-d 数据库] [-r 保留天数] [-l 备份路径]" >&2; exit 1 ;; esac done } # 备份执行函数 perform_backup() { local timestamp=$(date "+%Y%m%d_%H%M%S") local backup_file="${BACKUP_DIR}/${DB_NAME}_full_${timestamp}.sql.gz" log "开始备份数据库: ${DB_NAME}" mysqldump -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} \ --single-transaction --routines --triggers ${DB_NAME} | gzip > ${backup_file} if [ $? -eq 0 ]; then log "备份成功 ➜ ${backup_file}" else log "备份失败! 错误码: $?" exit 1 fi } # 清理旧备份函数 clean_old_backups() { log "清理过期备份（保留天数: ${RETENTION_DAYS}）" find ${BACKUP_DIR} -name "${DB_NAME}_full_*.sql.gz" -mtime +${RETENTION_DAYS} -exec rm -fv {} \; | tee -a ${LOG_FILE} } ###################### ### 主程序流程 ### ###################### main() { parse_args "$@" mkdir -p ${BACKUP_DIR} perform_backup clean_old_backups log "备份任务完成" } main "$@"

The script defines a log function for timestamped messages, a parse_args function to handle command‑line overrides, perform_backup to run mysqldump with gzip compression, and clean_old_backups that removes files older than the configured retention period.

Parameterization can be done either by passing arguments such as ./backup.sh -h 192.168.1.100 -u admin -p securepass -d order_db -r 30 -l /mnt/backups or by editing the default variable values directly in the script.

Backup strategy optimizations include using gzip to reduce storage size, the --single-transaction flag to ensure transactional consistency, and preserving triggers and routines metadata.

Logging records both real‑time output and file entries with timestamps and operation status; an example log shows start time, success message with the backup file path, and timestamps.

Security measures advise passing the password via the command line to avoid plain‑text storage and recommend setting restrictive permissions on the backup directory ( chmod 700 /data/backups ) and log file ( chmod 600 /var/log/db_backup.log ).

Deployment steps include installing the MySQL client ( sudo apt-get install mysql-client coreutils -y for Debian/Ubuntu or sudo yum install mysql-community-client coreutils -y for CentOS/RHEL) and configuring a daily cron job, e.g., (crontab -l ; echo "0 2 * * * /usr/local/bin/backup.sh -p $(cat /etc/db_pass.txt)") | crontab - .

A troubleshooting table lists common error symptoms, possible causes, and solutions such as checking firewall rules, fixing directory permissions, or ensuring the log path is writable.

Disclaimer: The script has been tested on CentOS 7/8 and Ubuntu 20.04 LTS; it is recommended to perform full validation in production environments and refer to related documentation for any issues.