Gecko [ v3.cesa.co.za ]

Name	Size	Permission	Date
__pycache__	[ DIR ]	drwxr-sr-x	2025-12-29 08:35
app	[ DIR ]	drwxr-sr-x	2025-10-28 07:05
venv	[ DIR ]	drwxr-sr-x	2025-12-29 09:06
.env	479 B	-rw-r--r--	2025-12-29 08:31
.htaccess	44 B	-rw-r--r--	2025-12-29 08:31
check-ssl.py	1.36 KB	-rw-r--r--	2025-12-29 08:31
config.py	5.79 KB	-rw-r--r--	2025-12-29 08:31
db-bck.py	10.9 KB	-rw-r--r--	2025-12-29 09:24
db-transfer-no-longer-needed.py	3.94 KB	-rw-r--r--	2025-12-29 08:31
env.example	5.5 KB	-rw-r--r--	2025-12-29 08:31
requirements.txt	266 B	-rw-r--r--	2025-12-29 08:59
run-backup.sh	529 B	-rw-r--r--	2025-12-29 09:24
send-mail.py	1.3 KB	-rw-r--r--	2025-12-29 08:31
server-checks.py	2.93 KB	-rw-r--r--	2025-12-29 08:31
setup-env.sh	3.03 KB	-rw-r--r--	2025-12-29 09:06

Rename

#!/usr/bin/env python3
"""
MySQL Database Backup Script with Cloud Storage Upload

This script:
1. Creates MySQL backups for configured databases
2. Zips all backups from the current day
3. Uploads the zip file to Azure Blob Storage (optional)
4. Uploads the zip file to Google Cloud Storage (optional)

Configuration:
- Set environment variables to enable cloud storage uploads:
  - Azure: AZURE_BLOB_ENABLED=true, AZURE_BLOB_CONNECTION_STRING=..., AZURE_BLOB_CONTAINER=...
  - GCS: GCS_ENABLED=true, GCS_CREDENTIALS_FILE=..., GCS_BUCKET_NAME=..., GCS_PROJECT_ID=...

Dependencies:
- pip install azure-storage-blob  # For Azure uploads
- pip install google-cloud-storage  # For GCS uploads
"""

import os
import subprocess
import datetime
import glob
import sys

# Try to import cloud storage libraries (optional dependencies)
try:
    from azure.storage.blob import BlobServiceClient
    AZURE_AVAILABLE = True
except ImportError:
    AZURE_AVAILABLE = False
    print("Warning: azure-storage-blob not installed. Azure uploads will be skipped.")

try:
    from google.cloud import storage
    GCS_AVAILABLE = True
except ImportError:
    GCS_AVAILABLE = False
    print("Warning: google-cloud-storage not installed. Google Cloud Storage uploads will be skipped.")

# Cloud Storage Configuration
# Set these to None or empty string to disable uploads
CLOUD_STORAGE_CONFIG = {
    # Azure Blob Storage Configuration
    "azure": {
        "enabled": os.getenv("AZURE_BLOB_ENABLED", "true").lower() == "true",
        "connection_string": os.getenv("AZURE_BLOB_CONNECTION_STRING", "DefaultEndpointsProtocol=https;AccountName=webstorage01;AccountKey=O/g/Vdh45ulJLxZfhcuut03dTq0dcuUSRUSCL6N7D3IdZ9w/FtmeoBCscH8qnJVt0dLd8LVp6I8J+AStW2Cndw==;EndpointSuffix=core.windows.net"),
        "container_name": os.getenv("AZURE_BLOB_CONTAINER", "cesa-www-mysql-backups"),
    },
    # Google Cloud Storage Configuration
    "gcs": {
        "enabled": os.getenv("GCS_ENABLED", "true").lower() == "true",
        "credentials_file": os.getenv("GCS_CREDENTIALS_FILE", "../cfg/google-keyfile-storage-admin.json"),  # Path to service account JSON
        "bucket_name": os.getenv("GCS_BUCKET_NAME", "cesa-www-mysql-backups"),
        "project_id": os.getenv("GCS_PROJECT_ID", "agile-bonbon-386208"),
    }
}

# Settings list where each entry is the details of one web site/ database
all_settings = [
    {
        "db_host": "cesa-main-db.local",
        "db_port": 3306,
        "db_name": "cesa",
        "db_user": "cesa",
        "db_pass": "]u9yU[ui92.6V0vN",
        "backup_dir": "/var/backups",
        "retention_days": 7,
        # "db_restore_host": "cesa-main-db.local",
        # "db_restore_port": 3306,
        # "db_restore_name": "cesa",
        # "db_restore_user": "cesa",
        # "db_restore_pass": "MAC4SCg9268c_MCd",
        "storage_engine": "innodb"
    },
    {
        "db_host": "cesa-main-db.local",
        "db_port": 3306,
        "db_name": "cesa_mail",
        "db_user": "cesa_mail",
        "db_pass": "c1yN3zU6ZN/BeXk1",
        "backup_dir": "/var/backups",
        "retention_days": 7,
        # "db_restore_host": "cesa-main-db.local",
        # "db_restore_port": 3306,
        # "db_restore_name": "cesa_mail",
        # "db_restore_user": "cesa_mail",
        # "db_restore_pass": "DIo60ZDXK7A9iSwG",
        "storage_engine": "innodb"
    },
    {
        "db_host": "cesa-main-db.local",
        "db_port": 3306,
        "db_name": "cesa_wp",
        "db_user": "cesa_wp",
        "db_pass": "Ji)YGPcY5Km8uNsy",
        "backup_dir": "/var/backups",
        "retention_days": 7,
        # "db_restore_host": "cesa-main-db.local",
        # "db_restore_port": 3306,
        # "db_restore_name": "cesa_wp",
        # "db_restore_user": "cesa_wp",
        # "db_restore_pass": "r0jYdp5/h)iLA/N5",
        "storage_engine": "myisam"
    }
]

def upload_to_azure(backup_file_path, date_str):
    """Upload backup file to Azure Blob Storage"""
    try:
        config = CLOUD_STORAGE_CONFIG["azure"]
        if not config["connection_string"]:
            print("Azure connection string not configured, skipping upload")
            return
        
        print(f"Uploading {backup_file_path} to Azure Blob Storage...")
        blob_service_client = BlobServiceClient.from_connection_string(config["connection_string"])
        container_client = blob_service_client.get_container_client(config["container_name"])
        
        # Create container if it doesn't exist
        try:
            container_client.create_container()
            print(f"Created container: {config['container_name']}")
        except Exception:
            # Container likely already exists
            pass
        
        # Upload blob using the filename
        blob_name = f"{date_str}/{os.path.basename(backup_file_path)}"
        with open(backup_file_path, "rb") as data:
            blob_client = container_client.upload_blob(
                name=blob_name,
                data=data,
                overwrite=True
            )
        
        print(f"Successfully uploaded to Azure: {blob_name}")
        
    except Exception as e:
        print(f"Error uploading to Azure Blob Storage: {e}", file=sys.stderr)

def upload_to_gcs(backup_file_path, date_str):
    """Upload backup file to Google Cloud Storage"""
    try:
        config = CLOUD_STORAGE_CONFIG["gcs"]
        credentials_file = config["credentials_file"]
        
        # Handle relative paths - resolve relative to script directory
        if credentials_file and not os.path.isabs(credentials_file):
            script_dir = os.path.dirname(os.path.abspath(__file__))
            credentials_file = os.path.join(script_dir, credentials_file)
        
        if not credentials_file or not os.path.exists(credentials_file):
            print(f"GCS credentials file not found or not configured: {credentials_file}, skipping upload")
            return
        
        if not config["bucket_name"]:
            print("GCS bucket name not configured, skipping upload")
            return
        
        print(f"Uploading {backup_file_path} to Google Cloud Storage...")
        
        # Initialize GCS client with credentials
        storage_client = storage.Client.from_service_account_json(
            credentials_file,
            project=config["project_id"] if config["project_id"] else None
        )
        
        bucket = storage_client.bucket(config["bucket_name"])
        blob_name = f"{date_str}/{os.path.basename(backup_file_path)}"
        blob = bucket.blob(blob_name)
        
        # Upload file
        blob.upload_from_filename(backup_file_path)
        
        print(f"Successfully uploaded to GCS: gs://{config['bucket_name']}/{blob_name}")
        
    except Exception as e:
        print(f"Error uploading to Google Cloud Storage: {e}", file=sys.stderr)

for index, settings in enumerate(all_settings):

backup_dir = settings["backup_dir"]
    retention_days = settings["retention_days"]
    db_host = settings["db_host"]
    db_port = settings["db_port"]
    db_name = settings["db_name"]
    db_user = settings["db_user"]
    db_pass = settings["db_pass"]

# Ensure backup directory exists
    os.makedirs(backup_dir, exist_ok=True)

# Today's date
    today = datetime.date.today().isoformat()
    backup_file = os.path.join(backup_dir, f"{db_name}-{today}.sql.gz")
    all_settings[index]["backup_file"] = backup_file

print(f"Backing up {db_name} to {backup_file}")
    # Run mysqldump using environment variables to avoid shell interpretation issues
    env = os.environ.copy()
    env['MYSQL_PWD'] = db_pass
    
    # Build mysqldump command without password in command line
    mysqldump_args = [
        "mysqldump",
        "-f",
        "--skip-triggers", 
        "--add-locks",
        "--hex-blob",
        "--single-transaction",
        "-u", db_user,
        "-h", db_host,
        "-P", str(db_port),
        db_name
    ]
    
    # Run mysqldump and pipe through sed commands
    dump_cmd = " ".join(mysqldump_args) + " | sed '1i SET FOREIGN_KEY_CHECKS=0;' | sed '$a SET FOREIGN_KEY_CHECKS=1;' | sed 's/DEFINER[ ]*=[ ]*[^*]*\\*/\\*/' | gzip > " + backup_file
    subprocess.run(dump_cmd, shell=True, check=True, env=env)

for settings in all_settings:
    
    #Check if db_restore_pass is defined
    if "db_restore_pass" in settings:

db_name = settings["db_name"]
        db_restore_host = settings["db_restore_host"]
        db_restore_port = settings["db_restore_port"]
        db_restore_name = settings["db_restore_name"]
        db_restore_user = settings["db_restore_user"]
        db_restore_pass = settings["db_restore_pass"]
        backup_file = settings["backup_file"]

# Restore the database from zipped backup file
        print(f"Restoring {db_name} from {backup_file}")
        
        # Use environment variables to avoid shell interpretation issues
        restore_env = os.environ.copy()
        restore_env['MYSQL_PWD'] = db_restore_pass
        
        restore_cmd = (
            f"gunzip -c {backup_file} | "
            f"mysql -u {db_restore_user} -h {db_restore_host} "
            f"-P {db_restore_port} {db_restore_name}"
        )
        subprocess.run(restore_cmd, shell=True, check=True, env=restore_env)

# Upload individual backup files to cloud storage
today = datetime.date.today().isoformat()
backup_files_today = [settings["backup_file"] for settings in all_settings if "backup_file" in settings and os.path.exists(settings["backup_file"])]

if backup_files_today:
    print(f"Uploading {len(backup_files_today)} backup file(s) to cloud storage...")
    
    for backup_file in backup_files_today:
        if os.path.exists(backup_file):
            file_size = os.path.getsize(backup_file)
            print(f"Processing {os.path.basename(backup_file)} ({file_size / (1024*1024):.2f} MB)")
            
            # Upload to Azure Blob Storage
            if CLOUD_STORAGE_CONFIG["azure"]["enabled"] and AZURE_AVAILABLE:
                upload_to_azure(backup_file, today)
            elif CLOUD_STORAGE_CONFIG["azure"]["enabled"]:
                print("Azure Blob Storage upload skipped (library not available)")
            
            # Upload to Google Cloud Storage
            if CLOUD_STORAGE_CONFIG["gcs"]["enabled"] and GCS_AVAILABLE:
                upload_to_gcs(backup_file, today)
            elif CLOUD_STORAGE_CONFIG["gcs"]["enabled"]:
                print("Google Cloud Storage upload skipped (library not available)")
else:
    print("No backup files to upload")

for index, settings in enumerate(all_settings):

db_name = settings["db_name"]
    retention_days = settings["retention_days"]
    backup_dir = settings["backup_dir"]

print(f"Deleting old backups of {db_name} older than {retention_days} days")
    # Delete old backups
    cutoff = datetime.datetime.now() - datetime.timedelta(days=retention_days)
    for f in glob.glob(os.path.join(backup_dir, f"{db_name}-*.sql.gz")):
        file_time = datetime.datetime.fromtimestamp(os.path.getmtime(f))
        if file_time < cutoff:
            os.remove(f)