本文目录导读：
我来介绍几种常见的定时备份MySQL数据库的脚本实现方法：
Shell脚本备份方案
基础备份脚本
#!/bin/bash
# 数据库配置
DB_USER="your_username"
DB_PASSWORD="your_password"
DB_NAME="your_database"
BACKUP_DIR="/path/to/backup"
DATE=$(date +"%Y%m%d_%H%M%S")
# 创建备份目录
mkdir -p $BACKUP_DIR
# 执行备份
mysqldump -u$DB_USER -p$DB_PASSWORD $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz
# 删除30天前的备份
find $BACKUP_DIR -name "*.sql.gz" -type f -mtime +30 -exec rm {} \;
echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"
增强版备份脚本
#!/bin/bash
# 配置变量
DB_USER="root"
DB_PASSWORD="your_password"
DB_HOST="localhost"
DB_PORT="3306"
BACKUP_DIR="/var/backup/mysql"
RETENTION_DAYS=30
LOG_FILE="/var/log/mysql_backup.log"
# 日志函数
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}
# 获取数据库列表（排除系统数据库）
get_databases() {
mysql -u$DB_USER -p$DB_PASSWORD -h$DB_HOST -P$DB_PORT \
-e "SHOW DATABASES;" | grep -v "Database\|information_schema\|performance_schema\|mysql\|sys"
}
# 备份单个数据库
backup_database() {
local db=$1
local backup_file="${BACKUP_DIR}/${db}_$(date +%Y%m%d_%H%M%S).sql.gz"
log "Starting backup of database: $db"
if mysqldump -u$DB_USER -p$DB_PASSWORD -h$DB_HOST -P$DB_PORT \
--single-transaction --routines --triggers --events \
$db | gzip > $backup_file; then
log "Successfully backed up $db to $backup_file"
return 0
else
log "ERROR: Failed to backup $db"
return 1
fi
}
# 主函数
main() {
# 创建备份目录
mkdir -p $BACKUP_DIR
log "Starting MySQL backup process"
# 备份所有数据库
for db in $(get_databases); do
backup_database $db
done
# 清理过期备份
log "Cleaning backups older than $RETENTION_DAYS days"
find $BACKUP_DIR -name "*.sql.gz" -type f -mtime +$RETENTION_DAYS -delete
log "Backup process completed"
}
main
设置定时任务（Crontab）
编辑crontab
crontab -e
常用的定时规则
# 每天凌晨2点执行 0 2 * * * /path/to/backup_script.sh # 每天凌晨2点和下午2点执行 0 2,14 * * * /path/to/backup_script.sh # 每周日凌晨3点执行 0 3 * * 0 /path/to/backup_script.sh # 每小时执行一次 0 * * * * /path/to/backup_script.sh # 每隔30分钟执行 */30 * * * * /path/to/backup_script.sh
Python脚本方案
#!/usr/bin/env python3
import os
import sys
import subprocess
import datetime
import logging
from pathlib import Path
class MySQLBackup:
def __init__(self, config):
self.config = config
self.setup_logging()
def setup_logging(self):
logging.basicConfig(
level=logging.INFO,
format='%(asctime)s - %(levelname)s - %(message)s',
handlers=[
logging.FileHandler(self.config['log_file']),
logging.StreamHandler()
]
)
def backup_database(self, database):
"""备份单个数据库"""
timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
backup_file = Path(self.config['backup_dir']) / f"{database}_{timestamp}.sql.gz"
cmd = [
'mysqldump',
f'-u{self.config["user"]}',
f'-p{self.config["password"]}',
f'-h{self.config["host"]}',
f'-P{self.config["port"]}',
'--single-transaction',
'--routines',
'--triggers',
'--events',
database,
'|',
'gzip'
]
try:
with open(backup_file, 'wb') as f:
process = subprocess.Popen(
' '.join(cmd[:-2]), # 排除gzip部分
shell=True,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE
)
gzip_process = subprocess.Popen(
'gzip',
shell=True,
stdin=process.stdout,
stdout=f,
stderr=subprocess.PIPE
)
process.stdout.close()
gzip_process.communicate()
logging.info(f"Successfully backed up {database} to {backup_file}")
return True
except Exception as e:
logging.error(f"Failed to backup {database}: {e}")
return False
def cleanup_old_backups(self):
"""清理过期备份"""
retention_days = self.config.get('retention_days', 30)
backup_dir = Path(self.config['backup_dir'])
cutoff_date = datetime.datetime.now() - datetime.timedelta(days=retention_days)
for backup_file in backup_dir.glob("*.sql.gz"):
if datetime.datetime.fromtimestamp(backup_file.stat().st_mtime) < cutoff_date:
backup_file.unlink()
logging.info(f"Deleted old backup: {backup_file}")
def run(self):
"""执行备份"""
try:
# 确保备份目录存在
Path(self.config['backup_dir']).mkdir(parents=True, exist_ok=True)
# 获取数据库列表
cmd = f'mysql -u{self.config["user"]} -p{self.config["password"]} -h{self.config["host"]} -P{self.config["port"]} -e "SHOW DATABASES;"'
result = subprocess.run(cmd, shell=True, capture_output=True, text=True)
databases = [db.strip() for db in result.stdout.split('\n')[1:] if db.strip() and db not in ['information_schema', 'performance_schema', 'mysql', 'sys']]
logging.info(f"Starting backup of {len(databases)} databases")
# 备份每个数据库
for db in databases:
self.backup_database(db)
# 清理旧备份
self.cleanup_old_backups()
logging.info("Backup process completed successfully")
except Exception as e:
logging.error(f"Backup process failed: {e}")
sys.exit(1)
if __name__ == "__main__":
config = {
'user': 'your_username',
'password': 'your_password',
'host': 'localhost',
'port': '3306',
'backup_dir': '/var/backup/mysql',
'log_file': '/var/log/mysql_backup.log',
'retention_days': 30
}
backup = MySQLBackup(config)
backup.run()
使用脚本权限设置
# 给脚本执行权限 chmod +x /path/to/backup_script.sh # 确保备份目录存在且有写入权限 mkdir -p /var/backup/mysql chmod 755 /var/backup/mysql
安全注意事项
密码安全配置
# 创建密码配置文件 cat > ~/.my.cnf << EOF [mysqldump] user=your_username password=your_password host=localhost [mysql] user=your_username password=your_password host=localhost EOF # 设置权限 chmod 600 ~/.my.cnf
使用环境变量
#!/bin/bash export MYSQL_PWD='your_password' # 这样就不需要在命令行中传递密码 mysqldump -u$DB_USER $DB_NAME > backup.sql unset MYSQL_PWD
验证和测试
# 手动测试脚本 bash /path/to/backup_script.sh # 检查备份文件 ls -lh /var/backup/mysql/ # 验证备份文件完整性 gunzip -t /var/backup/mysql/database_20240101_020000.sql.gz # 查看定时任务 crontab -l # 检查cron日志 grep backup /var/log/syslog # 或 journalctl -u cron | grep backup
注意事项
- 备份性能：对大型数据库，考虑使用
--single-transaction选项 - 磁盘空间：监控备份目录的磁盘使用情况
- 网络备份：可考虑将备份文件传输到远程服务器或云存储
- 测试恢复：定期测试备份文件的完整性
- 日志记录：保持详细日志以便故障排查
- 邮件通知：可添加邮件通知功能
选择哪种方案取决于你的具体需求、技术栈和环境要求，最简单的方案是直接使用crontab + shell脚本，而Python脚本提供了更丰富的功能和更好的错误处理。
