Shell脚本如何备份MySQL数据库

本文目录导读：

1. 基础备份脚本
2. 包含多个数据库的备份
3. 带日志和邮件通知的完整脚本
4. 增量备份脚本
5. 使用配置文件的安全方式
6. 使用建议

我来分享几种常见的MySQL数据库备份Shell脚本方法：

基础备份脚本

#!/bin/bash
# MySQL数据库备份脚本
# 数据库配置
DB_USER="root"
DB_PASSWORD="your_password"
DB_NAME="your_database"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
# 创建备份目录
mkdir -p $BACKUP_DIR
# 执行备份
mysqldump -u$DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql
# 压缩备份文件
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql
# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"

包含多个数据库的备份

#!/bin/bash
# 多数据库备份
DB_USER="root"
DB_PASSWORD="your_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
# 获取所有数据库列表
DATABASES=$(mysql -u$DB_USER -p$DB_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")
for db in $DATABASES; do
    echo "Backing up database: $db"
    mysqldump -u$DB_USER -p$DB_PASSWORD --databases $db | gzip > $BACKUP_DIR/${db}_${DATE}.sql.gz
done
# 可选：备份所有数据库
# mysqldump -u$DB_USER -p$DB_PASSWORD --all-databases | gzip > $BACKUP_DIR/all_databases_${DATE}.sql.gz

带日志和邮件通知的完整脚本

#!/bin/bash
# 配置信息
DB_USER="root"
DB_PASSWORD="your_password"
DB_HOST="localhost"
BACKUP_DIR="/backup/mysql"
LOG_FILE="/var/log/mysql_backup.log"
# 邮件配置（可选）
ADMIN_EMAIL="admin@example.com"
# 创建备份和日志目录
mkdir -p $BACKUP_DIR
touch $LOG_FILE
# 日志函数
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}
# 开始备份
log_message "Starting MySQL backup..."
# 获取当前日期
DATE=$(date +%Y%m%d_%H%M%S)
# 测试数据库连接
if ! mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD -e "SELECT 1" >/dev/null 2>&1; then
    log_message "ERROR: Cannot connect to MySQL server"
    echo "MySQL backup failed - connection error" | mail -s "Backup Failed" $ADMIN_EMAIL
    exit 1
fi
# 执行备份
backup_db() {
    local db_name=$1
    local backup_file="${BACKUP_DIR}/${db_name}_${DATE}.sql"
    log_message "Backing up database: $db_name"
    # 执行mysqldump
    if mysqldump -h$DB_HOST -u$DB_USER -p$DB_PASSWORD \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        $db_name > $backup_file 2>> $LOG_FILE; then
        # 压缩文件
        gzip $backup_file
        log_message "Successfully backed up: ${db_name}_${DATE}.sql.gz"
        return 0
    else
        log_message "ERROR: Failed to backup database: $db_name"
        return 1
    fi
}
# 备份指定数据库
backup_db "your_database"
# 或者备份所有数据库
#备份所有数据库
#mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD -e "SHOW DATABASES;" | while read db; do
#    if [[ ! "$db" =~ ^(information_schema|performance_schema|mysql|sys|Database)$ ]]; then
#        backup_db "$db"
#    fi
#done
# 清理旧备份（保留30天）
log_message "Cleaning old backups..."
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete
# 获取备份文件大小
BACKUP_SIZE=$(du -sh $BACKUP_DIR | cut -f1)
# 完成通知
log_message "Backup completed successfully. Total backup size: $BACKUP_SIZE"
# 可选：发送邮件通知
if [ -n "$ADMIN_EMAIL" ]; then
    echo "MySQL backup completed at $(date). 
    Backup directory: $BACKUP_DIR
    Total size: $BACKUP_SIZE" | mail -s "MySQL Backup Completed" $ADMIN_EMAIL
fi

增量备份脚本

#!/bin/bash
# 增量备份配置
DB_USER="root"
DB_PASSWORD="your_password"
BACKUP_DIR="/backup/mysql_incremental"
FULL_BACKUP_DIR="/backup/mysql_full"
DATE=$(date +%Y%m%d_%H%M%S)
# 每周日做全量备份
DAY_OF_WEEK=$(date +%u)
if [ "$DAY_OF_WEEK" -eq 7 ]; then
    # 全量备份
    echo "Performing full backup..."
    mysqldump -u$DB_USER -p$DB_PASSWORD --all-databases | gzip > ${FULL_BACKUP_DIR}/full_backup_${DATE}.sql.gz
else
    # 增量备份（使用二进制日志）
    echo "Performing incremental backup..."
    mysqladmin -u$DB_USER -p$DB_PASSWORD flush-logs
    # 复制二进制日志文件
    cp /var/lib/mysql/mysql-bin.* ${BACKUP_DIR}/
fi

使用配置文件的安全方式

创建 /root/.my.cnf 配置文件：

[mysqldump]
user=root
password=your_password
host=localhost

对应的脚本：

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
# 不需要在脚本中硬编码密码
mysqldump your_database | gzip > ${BACKUP_DIR}/backup_${DATE}.sql.gz

使用建议

1. 权限设置：

   chmod 600 /root/.my.cnf
   chmod +x backup_script.sh

2. 定时任务设置（crontab）：

   # 每天凌晨3点执行备份
   0 3 * * * /path/to/backup_script.sh

3. 主要参数说明：

   • --single-transaction: 保证数据一致性（InnoDB）
   • --routines: 备份存储过程和函数
   • --triggers: 备份触发器
   • --events: 备份事件
   • --lock-tables: 锁定表（MyISAM）

4. 验证备份：

   gunzip < backup.sql.gz | mysql -u root -p test_restore

这些脚本可以根据实际需求进行调整和组合使用。