MySQL Database Backup Python Script

backup.py

#!/usr/bin/env python3

import subprocess
import shutil
from datetime import datetime, timedelta
from pathlib import Path

# Configuration
BASE_DIR = Path("/backup/db")
HOURLY_DIR = BASE_DIR / "hourly"
DAILY_DIR = BASE_DIR / "daily"

# Multiple databases can be put here also
DB = "db1 db2 db3 db4"

# Retention
HOURLY_RETENTION_DAYS = 7
DAILY_RETENTION_DAYS = 90

def mariabackup(args):
    subprocess.run(["mariabackup"] + args, check=True)

def create_backup(backup_dir):
    # Create and prepare backup
    mariabackup(["--backup", f"--target-dir={backup_dir}", f"--databases={DB}"])
    mariabackup(["--prepare", f"--target-dir={backup_dir}"])

def hourly_backup():
    now = datetime.now()
    backup_name = f"backup_{now.strftime('%Y-%m-%d@%H%M')}"
    backup_dir = HOURLY_DIR / backup_name
    
    # Create backup
    create_backup(backup_dir)
    
    # Zip the backup
    shutil.make_archive(str(backup_dir), 'zip', backup_dir)
    shutil.rmtree(backup_dir)
    
    return HOURLY_DIR / f"{backup_name}.zip"

def daily_backup(hourly_zip):
    now = datetime.now()
    daily_name = f"backup_{now.strftime('%Y-%m-%d')}"
    daily_zip = DAILY_DIR / f"{daily_name}.zip"
    
    # Copy to daily folder (will overwrite if exists)
    shutil.copy2(hourly_zip, daily_zip)

def cleanup_hourly():
    cutoff = (datetime.now() - timedelta(days=HOURLY_RETENTION_DAYS)).date()
    for backup in HOURLY_DIR.glob("backup_*.zip"):
        backup_date = datetime.strptime(backup.stem[7:17], '%Y-%m-%d').date()
        if backup_date < cutoff:
            backup.unlink()

def cleanup_daily():
    cutoff = (datetime.now() - timedelta(days=DAILY_RETENTION_DAYS)).date()
    for backup in DAILY_DIR.glob("backup_*.zip"):
        backup_date = datetime.strptime(backup.stem[7:17], '%Y-%m-%d').date()
        if backup_date < cutoff:
            backup.unlink()

def main():
    # Create directories
    HOURLY_DIR.mkdir(parents=True, exist_ok=True)
    DAILY_DIR.mkdir(parents=True, exist_ok=True)
    
    # Create hourly backup
    hourly_zip = hourly_backup()
    
    # Copy to daily
    daily_backup(hourly_zip)
    
    # Cleanup old backups
    cleanup_hourly()
    cleanup_daily()

if __name__ == "__main__":
    main()

Multiple databases can be specified by just putting the names in the variable
DB = "db1 db2 db3 db4"
Like this