# Database Backup Automation

This utility automates the backup of a MySQL or MariaDB database. It uses the `subprocess` module to execute the system's `mysqldump` command and pipes the output directly into a gzip-compressed file, creating a timestamped archive.

**Modules Used:**
*   [[programming/python/modules/subprocess-module|subprocess]]: To execute the `mysqldump` command.
*   `gzip`: To compress the SQL dump on the fly.
*   [[programming/python/modules/argparse-module|argparse]]: To handle command-line arguments.
*   `datetime`: To generate unique filenames based on the current time.

## Prerequisites

1.  **MySQL/MariaDB Server**: You must have a database server running.
2.  **mysqldump**: The `mysqldump` command-line utility must be installed and available in your system's PATH.

## The Code

Save this as `db_backup.py`.

```python
import subprocess
import gzip
import argparse
import os
from datetime import datetime
import sys

def backup_database(host, user, password, database, output_dir):
    # Ensure output directory exists
    if not os.path.exists(output_dir):
        try:
            os.makedirs(output_dir)
        except OSError as e:
            print(f"Error creating directory: {e}")
            return

    # Generate filename: dbname_YYYY-MM-DD_HH-MM-SS.sql.gz
    timestamp = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")
    filename = f"{database}_{timestamp}.sql.gz"
    filepath = os.path.join(output_dir, filename)

    print(f"Backing up database '{database}' to '{filepath}'...")

    # Construct command
    # Note: -p{password} must not have a space
    cmd = [
        'mysqldump',
        f'-h{host}',
        f'-u{user}',
        f'-p{password}',
        database
    ]

    try:
        # Open the output file in binary write mode for gzip
        with gzip.open(filepath, 'wb') as f:
            # Run mysqldump and pipe stdout to the gzip file
            # stderr is captured to print errors if they occur
            process = subprocess.Popen(cmd, stdout=f, stderr=subprocess.PIPE)
            
            # Wait for process to finish
            _, stderr = process.communicate()

            if process.returncode != 0:
                print(f"Error during backup: {stderr.decode('utf-8')}")
            else:
                size_mb = os.path.getsize(filepath) / (1024 * 1024)
                print(f"Backup successful! Size: {size_mb:.2f} MB")

    except FileNotFoundError:
        print("Error: 'mysqldump' command not found. Is MySQL installed and in your PATH?")
    except Exception as e:
        print(f"An error occurred: {e}")

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="MySQL Database Backup Tool")
    parser.add_argument("database", help="Database name")
    parser.add_argument("-u", "--user", required=True, help="MySQL Username")
    parser.add_argument("-p", "--password", required=True, help="MySQL Password")
    parser.add_argument("-h", "--host", default="localhost", help="MySQL Host (default: localhost)")
    parser.add_argument("-o", "--output", default="./backups", help="Output directory (default: ./backups)")

    args = parser.parse_args()

    backup_database(args.host, args.user, args.password, args.database, args.output)
```

## Usage

```bash
# Backup 'my_app_db' using root user
python db_backup.py my_app_db -u root -p mysecretpassword

# Backup to a specific folder
python db_backup.py my_app_db -u root -p mysecretpassword -o /var/backups/sql
```

[[programming/python/python]]