本文目录导读：
我来分享几种常见的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
使用建议
-
权限设置：
chmod 600 /root/.my.cnf chmod +x backup_script.sh
-
定时任务设置（crontab）：
# 每天凌晨3点执行备份 0 3 * * * /path/to/backup_script.sh
-
主要参数说明：
--single-transaction: 保证数据一致性（InnoDB）--routines: 备份存储过程和函数--triggers: 备份触发器--events: 备份事件--lock-tables: 锁定表（MyISAM）
-
验证备份：
gunzip < backup.sql.gz | mysql -u root -p test_restore
这些脚本可以根据实际需求进行调整和组合使用。
