脚本如何实现定时备份MySQL数据库

本文目录导读：

1. Shell脚本备份方案
2. 设置定时任务（Crontab）
3. Python脚本方案
4. 使用脚本权限设置
5. 安全注意事项
6. 验证和测试
7. 注意事项

我来介绍几种常见的定时备份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

注意事项

1. 备份性能：对大型数据库，考虑使用 --single-transaction 选项
2. 磁盘空间：监控备份目录的磁盘使用情况
3. 网络备份：可考虑将备份文件传输到远程服务器或云存储
4. 测试恢复：定期测试备份文件的完整性
5. 日志记录：保持详细日志以便故障排查
6. 邮件通知：可添加邮件通知功能

选择哪种方案取决于你的具体需求、技术栈和环境要求，最简单的方案是直接使用crontab + shell脚本，而Python脚本提供了更丰富的功能和更好的错误处理。