Back

Backup MySQL using Python

---

Author: Admin 11/04/2026
Language: Python
Tags: python mysql backup

---

Description:

Here is a small script i put together using Python to backup a MySQL database and delete old files in the backup directory that are older than a specific number of days and match a particular file extension.

Article:

Main.py

#!/usr/bin/env python3
from mysql import backup_mysql_database
from deleteoldbackups import delete_old_backups

if __name__ == "__main__":
    # --- MySql backup configuration ---
    db_host = "127.0.0.1"
    db_user = "username"
    db_password = "password"
    db_name = "my_sql_db_name"
    backup_dir = "/home/admin/Desktop/MySQLBackups/"
    backup_mysql_database(db_host, db_user, db_password, db_name, backup_dir)

    # --- Delete old backups ---
    days_threshold = 30
    file_extension = ".sql"  # Include the dot (e.g., '.log', '.csv', '.pdf')
    delete_old_backups(backup_dir, days_threshold, file_extension)

Mysql.py

import os
import subprocess
from datetime import datetime

def backup_mysql_database(db_host, db_user, db_password, db_name, backup_dir):
    # Create backup directory if it doesn't exist
    if not os.path.exists(backup_dir):
        os.makedirs(backup_dir)

    # Generate timestamped filename
    timestamp = datetime.now().strftime("%Y%m%d-%H%M%S")
    backup_filename = f"{db_name}_{timestamp}.sql"
    backup_filepath = os.path.join(backup_dir, backup_filename)

    # --- Formulate the mariadb-dump command ---
    # --single-transaction allows online backups of InnoDB tables without locking
    dump_command = [
        "mariadb-dump",
        f"-h{db_host}",
        f"-u{db_user}",
        f"-p{db_password}",
        "--single-transaction",
        "--quick",
        "--lock-tables=false",
        "--ssl-verify-server-cert=OFF",
        db_name
    ]

    print(f"Starting backup for database: {db_name}...")

    try:
        # Open the target file and pipe the output directly into it
        with open(backup_filepath, "w", encoding="utf-8") as output_file:
            result = subprocess.run(
                dump_command,
                stdout=output_file,
                stderr=subprocess.PIPE,
                text=True,
                check=True
            )

        print(f"✅ Success! Backup saved to: {backup_filepath}")

    except subprocess.CalledProcessError as e:
        print(f"❌ Error occurred during backup: {e.stderr}")
        # Clean up empty/corrupted file if it was created
        if os.path.exists(backup_filepath):
            os.remove(backup_filepath)

Deleteoldbackups.py

from datetime import datetime, timedelta, timezone
from pathlib import Path

def delete_old_backups(folder_path: str, days: int, extension: str):
    """
    Deletes files in the target directory that are older than a specific number
    of days and match a particular file extension.
    """
    target_dir = Path(folder_path)

    # Ensure the directory exists
    if not target_dir.exists() or not target_dir.is_dir():
        print(f"Error: {folder_path} is not a valid directory.")
        return

    # Calculate the threshold date
    # Using timezone-aware UTC dates is best practice to avoid system clock errors
    cutoff_time = datetime.now(timezone.utc) - timedelta(days=days)
    print(f"Looking for {extension} files modified before: {cutoff_time.strftime('%Y-%m-%d %H:%M:%S')} UTC\n")

    # Counter for deleted files
    deleted_count = 0

    # Use rglob to search recursively through subfolders.
    # Use target_dir.glob() if you only want to check the top-level folder.
    search_pattern = f"*{extension}"

    for file_path in target_dir.rglob(search_pattern):
        if file_path.is_file():
            # Get the file's last modified time (converted to a UTC datetime object)
            file_modified_time = datetime.fromtimestamp(file_path.stat().st_mtime, tz=timezone.utc)

            if file_modified_time < cutoff_time:
                try:
                    # Dry run safety mechanism: Comment out 'file_path.unlink()' to test safely
                    file_path.unlink()
                    print(f"Deleted: {file_path.name} (Modified: {file_modified_time.strftime('%Y-%m-%d')})")
                    deleted_count += 1
                except Exception as e:
                    print(f"Failed to delete {file_path.name}: {e}")

    print(f"\nCleanup complete. Total files deleted: {deleted_count}")

---

Back