Here's a comprehensive solution to create a Docker container that backs up databases and pushes to GitHub on a schedule.

Solution Overview

We'll create a Docker container that can:

1. Connect to various database types (MySQL, PostgreSQL, MongoDB, etc.)
2. Create backups on a schedule (daily or every 2 days)
3. Push backups to a GitHub repository using SSH authentication

Option 1: Python-based Solution (Recommended)

Dockerfile

dockerfile

Copy

FROM python:3.9-slim

# Install dependencies
RUN apt-get update && apt-get install -y \
    git \
    openssh-client \
    && rm -rf /var/lib/apt/lists/*

# Install database clients (customize based on your needs)
RUN apt-get update && apt-get install -y \
    mysql-client \
    postgresql-client \
    mongodb-tools \
    && rm -rf /var/lib/apt/lists/*

WORKDIR /app

# Copy backup script and requirements
COPY requirements.txt backup.py /app/

# Install Python dependencies
RUN pip install --no-cache-dir -r requirements.txt

# Set up SSH for GitHub
RUN mkdir -p /root/.ssh && \
    chmod 700 /root/.ssh && \
    ssh-keyscan github.com >> /root/.ssh/known_hosts

# Entrypoint
CMD ["python", "backup.py"]

requirements.txt

Copy

PyMySQL==1.0.2
psycopg2-binary==2.9.3
pymongo==4.1.1
python-dotenv==0.19.2

backup.py

python

Copy

import os
import subprocess
import time
from datetime import datetime
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

# Configuration
BACKUP_DIR = "/backups"
GIT_REPO = os.getenv('GIT_REPO')  # e.g., git@github.com:username/backups.git
DB_TYPE = os.getenv('DB_TYPE')     # mysql, postgres, mongodb
DB_HOST = os.getenv('DB_HOST')
DB_PORT = os.getenv('DB_PORT')
DB_NAME = os.getenv('DB_NAME')
DB_USER = os.getenv('DB_USER')
DB_PASS = os.getenv('DB_PASS')
SSH_KEY_PATH = os.getenv('SSH_KEY_PATH', '/root/.ssh/id_ed25519')

def run_command(cmd):
    try:
        subprocess.run(cmd, check=True, shell=True)
    except subprocess.CalledProcessError as e:
        print(f"Command failed: {e}")

def init_git_repo():
    if not os.path.exists(BACKUP_DIR):
        os.makedirs(BACKUP_DIR)
    
    if not os.path.exists(os.path.join(BACKUP_DIR, '.git')):
        run_command(f"git clone {GIT_REPO} {BACKUP_DIR}")
    
    os.chdir(BACKUP_DIR)
    run_command("git config --local user.email 'backup-bot@example.com'")
    run_command("git config --local user.name 'Backup Bot'")

def create_backup():
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    backup_file = os.path.join(BACKUP_DIR, f"{DB_NAME}_{timestamp}.sql")
    
    if DB_TYPE == 'mysql':
        cmd = f"mysqldump -h {DB_HOST} -P {DB_PORT} -u {DB_USER} -p{DB_PASS} {DB_NAME} > {backup_file}"
    elif DB_TYPE == 'postgres':
        cmd = f"PGPASSWORD={DB_PASS} pg_dump -h {DB_HOST} -p {DB_PORT} -U {DB_USER} {DB_NAME} > {backup_file}"
    elif DB_TYPE == 'mongodb':
        backup_file = backup_file.replace('.sql', '.gz')
        cmd = f"mongodump --uri mongodb://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME} --archive={backup_file} --gzip"
    else:
        raise ValueError(f"Unsupported database type: {DB_TYPE}")
    
    run_command(cmd)
    return backup_file

def git_push():
    os.chdir(BACKUP_DIR)
    run_command("git add .")
    run_command('git commit -m "Automated backup" || echo "No changes to commit"')
    run_command("git push origin main")

def main():
    # Set up SSH key
    if os.path.exists(SSH_KEY_PATH):
        run_command(f"chmod 600 {SSH_KEY_PATH}")
        run_command(f"eval $(ssh-agent -s) && ssh-add {SSH_KEY_PATH}")
    
    init_git_repo()
    
    while True:
        try:
            backup_file = create_backup()
            print(f"Created backup: {backup_file}")
            git_push()
            print("Pushed to GitHub")
        except Exception as e:
            print(f"Backup failed: {e}")
        
        # Sleep for 2 days (172800 seconds) or 1 day (86400)
        time.sleep(172800 if os.getenv('BACKUP_INTERVAL') == '2' else 86400)

if __name__ == "__main__":
    main()

docker-compose.yml

yaml

Copy

version: '3'

services:
  backup:
    build: .
    environment:
      - GIT_REPO=git@github.com:username/backups.git
      - DB_TYPE=mysql
      - DB_HOST=db_host
      - DB_PORT=3306
      - DB_NAME=my_database
      - DB_USER=root
      - DB_PASS=password
      - BACKUP_INTERVAL=2  # 1 for daily, 2 for every 2 days
    volumes:
      - ./ssh:/root/.ssh  # Mount your SSH key
      - ./backups:/backups
    restart: unless-stopped

Option 2: Bash-based Solution (Simpler)

Dockerfile

dockerfile

Copy

FROM alpine:latest

# Install dependencies
RUN apk add --no-cache \
    bash \
    git \
    openssh-client \
    mysql-client \
    postgresql-client \
    mongodb-tools \
    tzdata

# Create backup directory
RUN mkdir -p /backups

# Copy backup script
COPY backup.sh /backup.sh
RUN chmod +x /backup.sh

# Set up SSH for GitHub
RUN mkdir -p /root/.ssh && \
    chmod 700 /root/.ssh && \
    ssh-keyscan github.com >> /root/.ssh/known_hosts

# Entrypoint
CMD ["/backup.sh"]

backup.sh

bash

Copy

#!/bin/bash

# Configuration
BACKUP_DIR="/backups"
GIT_REPO="git@github.com:username/backups.git"
DB_TYPE="mysql"  # Change as needed
DB_HOST="db_host"
DB_PORT="3306"
DB_NAME="my_database"
DB_USER="root"
DB_PASS="password"
SSH_KEY_PATH="/root/.ssh/id_ed25519"
BACKUP_INTERVAL="2"  # 1 for daily, 2 for every 2 days

# Initialize git repo
init_git_repo() {
    if [ ! -d "$BACKUP_DIR/.git" ]; then
        git clone "$GIT_REPO" "$BACKUP_DIR"
    fi
    cd "$BACKUP_DIR" || exit 1
    git config --local user.email "backup-bot@example.com"
    git config --local user.name "Backup Bot"
}

# Create database backup
create_backup() {
    TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
    BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}"
    
    case "$DB_TYPE" in
        mysql)
            BACKUP_FILE="${BACKUP_FILE}.sql"
            mysqldump -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" > "$BACKUP_FILE"
            ;;
        postgres)
            BACKUP_FILE="${BACKUP_FILE}.sql"
            PGPASSWORD="$DB_PASS" pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" "$DB_NAME" > "$BACKUP_FILE"
            ;;
        mongodb)
            BACKUP_FILE="${BACKUP_FILE}.gz"
            mongodump --uri "mongodb://$DB_USER:$DB_PASS@$DB_HOST:$DB_PORT/$DB_NAME" --archive="$BACKUP_FILE" --gzip
            ;;
        *)
            echo "Unsupported database type: $DB_TYPE"
            exit 1
            ;;
    esac
    
    echo "$BACKUP_FILE"
}

# Push to GitHub
git_push() {
    cd "$BACKUP_DIR" || exit 1
    git add .
    git commit -m "Automated backup" || echo "No changes to commit"
    git push origin main
}

# Main function
main() {
    # Set up SSH key
    if [ -f "$SSH_KEY_PATH" ]; then
        chmod 600 "$SSH_KEY_PATH"
        eval "$(ssh-agent -s)"
        ssh-add "$SSH_KEY_PATH"
    fi
    
    init_git_repo
    
    while true; do
        echo "Starting backup process..."
        if BACKUP_FILE=$(create_backup); then
            echo "Created backup: $BACKUP_FILE"
            if git_push; then
                echo "Pushed to GitHub"
            else
                echo "Failed to push to GitHub"
            fi
        else
            echo "Backup failed"
        fi
        
        # Sleep for 2 days (172800 seconds) or 1 day (86400)
        sleep $([ "$BACKUP_INTERVAL" = "2" ] && echo "172800" || echo "86400")
    done
}

main

Deployment Instructions

1. Set up your GitHub repository:

• Create a private repository for backups
• Add your SSH public key to GitHub account

1. Prepare SSH key:

• Place your private key in ./ssh/id_ed25519 (or whatever key name you use)
• Ensure permissions are correct (chmod 600)

1. Configure environment:

• Update the environment variables in either the Python or Bash solution
• For Python, you can use a .env file

1. Build and run:
2. bash
3. Copy

docker-compose up -d --build

Notes

1. Security Considerations:

• Never commit your actual database credentials to source control
• Use environment variables or secrets management
• Keep your SSH key secure

1. Customization:

• Adjust the backup commands based on your database type
• Add compression if needed
• Implement backup rotation if repository size becomes an issue

1. Alternative Scheduling:

• Instead of sleeping in the script, you could use cron inside the container
• Or use an external scheduler like Kubernetes CronJob

1. Multiple Databases:

• The Python solution can be easily extended to back up multiple databases