sqlBackup

sqlBackup is a modern Python-based backup tool for MySQL databases. It supports advanced features such as multiple archiving formats, multi-channel notifications (Telegram, Email, Slack, SMS via Twilio, Viber, etc.), and remote uploads via protocols like SFTP, FTP, or SCP. This project is a significant upgrade from the original BackupSQL shell script.

Features

• MySQL Database Backup:
  Dumps databases using mysqldump with support for routines and events.

• Flexible Archiving:
  Archive your backups in various formats:

  • none (plain SQL dump)
  • gz (gzip-compressed)
  • xz (xz-compressed)
  • tar.xz (tar archive compressed with xz)
  • zip (ZIP archive)
  • rar (RAR archive)

• Multi-Channel Notifications:
  Send notifications via:

  • Telegram
  • Email
  • Slack
  • SMS (via Twilio)
  • Viber
  • Messenger (stub, to be implemented)

• Remote Uploads:
  Optionally upload backups to a remote server using SFTP, FTP, or SCP with configurable scheduling (daily, first day, last day, specific weekday, or a numeric day of the month).

• Wildcard Support for Ignored Databases:
  Use wildcard patterns (e.g., projekti_*) in ignored_databases to skip databases by name pattern.

• Modular & Maintainable:
  Code is organized into multiple modules (configuration, backup logic, notifications, remote upload) for easier maintenance and extensibility.

• Graceful Interruption:
  Handles CTRL+C gracefully, providing a user-friendly exit message.

• Comprehensive Logging:
  Professional logging system with:

  • Colored console output with different levels (DEBUG, INFO, WARNING, ERROR, CRITICAL)
  • File logging with automatic rotation (10MB max, 5 backup files)
  • Detailed logging for debugging and audit trails
  • Configurable log levels and output destinations

• Configuration Validation:
  Robust validation system that:

  • Validates all configuration parameters with detailed error messages
  • Checks file paths, email formats, phone numbers, and URL formats
  • Ensures required sections and fields are present
  • Validates data types and allowed values
  • Provides helpful warnings for potential issues
  • Includes a standalone validation tool for testing configurations

Table of Contents

• Features
• Installation
  • Prerequisites
  • Setup
• Configuration Tutorial
• Usage
• Documentation
• Contributing
• License

Documentation

For Users

• Installation Guide - Quick setup instructions
• Configuration Guide - Complete configuration reference
• Tutorials - Step-by-step tutorials for common tasks
• Configuration Validation - Validate your setup

For Developers

• API Documentation - Complete API reference and code documentation
• Developer Guide - Development setup, architecture, and contribution guidelines
• Code Examples - Practical examples and extension patterns
• Testing Guide - Testing strategies and examples

Installation

Quick Install (Recommended)

The easiest way to get started is using the automated installer:

WSL/Linux:

git clone https://github.com/klevze/sqlBackup.git
cd sqlBackup
chmod +x install.sh
./install.sh

What the installer does:

• Detects your system (apt/dnf/yum/brew) and installs system dependencies
• Creates a Python virtual environment
• Installs all Python requirements and the package in development mode
• Creates config.ini from template with user-writable defaults
• Makes the startup script executable
• Sets up logging directory

Prerequisites

1. Python 3.6+ is required.
2. MySQL or MariaDB client tools (auto-installed by the installer).
3. Build tools for compiling Python packages (auto-installed by the installer).

Manual Installation

1. Install Python 3 and pip

   sudo apt update
   sudo apt install python3 python3-pip

2. Clone or copy the sqlBackup project

   git clone https://github.com/klevze/sqlBackup.git
   cd sqlBackup

3. Install dependencies

   pip3 install -r requirements.txt

4. Copy and edit the configuration file

   cp config.ini.default config.ini
   nano config.ini

   Edit config.ini to match your environment (database credentials, backup paths, notification settings, remote upload, etc.).

   Important: Update any placeholder paths (e.g., [remote].key_file) to real files on your server.

5. (Optional) Install as a package

   • For development:

     pip3 install -e .

   • For production:

     pip3 install .

6. Run sqlBackup

   • From the project directory:

     python3 -m sql_backup

   • Or, if installed as a package:

     sql-backup

Tip: If you see an error about a missing private key or other file, check your config.ini paths.

Using the Startup Script

The project includes sqlBackup.sh, a convenient startup script that provides comprehensive pre-flight checks and simplified operation:

Features:

• Pre-flight checks: Validates Python version, MySQL tools, package installation, and configuration
• Auto-fallback: Uses python3 -m sql_backup when console scripts aren't on PATH
• Configuration management: Creates config.ini from template if missing
• Multiple operation modes: Install, check, run, or show help/version
• Logging: Tracks startup operations and errors
• Cross-platform: Works in WSL, Linux, and other POSIX environments

Usage Examples:

# Check if everything is ready to run
./sqlBackup.sh --check

# Install the package via pip (if not already installed)
./sqlBackup.sh --install

# Show configuration and log file locations
./sqlBackup.sh --config
./sqlBackup.sh --logs

# Run the backup process (default behavior)
./sqlBackup.sh

# Show help and available options
./sqlBackup.sh --help

Note: The startup script is designed for POSIX systems (WSL/Linux/macOS). On Windows, use PowerShell commands or run the Python module directly.

Platform-Specific Installation

WSL / Windows (recommended for Windows users)

If you are on Windows, use WSL (Windows Subsystem for Linux) for the best compatibility. From your WSL distro (Ubuntu, etc.) run the same steps as Linux above. Example:

# open WSL and switch to your project folder mounted under /mnt
cd /mnt/d/Sites/sqlBackup/sqlBackup

# system packages (Ubuntu example)
sudo apt update
sudo apt install -y python3 python3-venv python3-pip mysql-client build-essential libssl-dev libffi-dev

# create and activate a venv
python3 -m venv venv
. venv/bin/activate

# upgrade packaging tools and install deps
python -m pip install --upgrade pip setuptools wheel
pip install -r requirements.txt

# install package in development/editable mode
pip install -e .

# make startup script executable and create config copy
chmod +x sqlBackup.sh
cp -n config.ini.default config.ini

# run pre-flight checks
./sqlBackup.sh --check

Notes:

• PowerShell and Windows CMD do not reliably support POSIX utilities such as chmod — use WSL to run the shell scripts and the sqlBackup.sh startup helper.
• The project now includes pyproject.toml for modern PEP 517 builds, eliminating pip deprecation warnings.

Windows-native alternative (no WSL)

If you cannot use WSL, you can still run the package using Python on Windows. Use a virtual environment and run the module directly (paths in config.ini must be Windows paths and you must have mysqldump available in PATH):

# in PowerShell (run from project root)
python -m venv venv
venv\Scripts\Activate.ps1
python -m pip install --upgrade pip setuptools wheel
pip install -r requirements.txt
pip install -e .

# copy config and edit
Copy-Item config.ini.default -Destination config.ini -ErrorAction SilentlyContinue
# run module directly
python -m sql_backup

Startup helper and install script

• sqlBackup.sh — a small POSIX startup script added to the repo. It performs pre-flight checks (--check), can install the package (--install), show config/log locations, and run the backup. Use it from within WSL or other POSIX shells.
• install.sh — corrected to install dependencies, perform a development install (pip install -e .) and create config.ini from the template if missing.

Troubleshooting & Tips

• If cryptography or paramiko fail to build during pip install, install the system build deps and retry:

sudo apt install -y build-essential libssl-dev libffi-dev python3-dev
pip install -r requirements.txt

• If the CLI entrypoints (sql-backup, sqlbackup) are not found after install, ensure your virtualenv is activated, or run the module directly with python -m sql_backup.

---

Alternative installation options:

• Quick install script (Linux/macOS):

  chmod +x install.sh
  ./install.sh

• Windows:

  install.bat

Required dependencies:

• requests: For HTTP requests (used in notifications and remote uploads).
• paramiko: For SFTP uploads.
• twilio: For sending SMS notifications.

---

Project Structure:

The project is organized as a modern Python package:

sqlBackup/
├── setup.py              # Package installation and metadata
├── pyproject.toml         # Modern build system configuration
├── requirements.txt      # Runtime dependencies
├── requirements-dev.txt  # Development dependencies
├── config.ini.default    # Default configuration template
├── install.sh            # Automated first-time installer
├── sqlBackup.sh          # Startup script with pre-flight checks
├── example.py            # Usage examples
├── validate_config.py    # Standalone validation tool
├── sql_backup/           # Main package
│   ├── __init__.py       # Package initialization
│   ├── __main__.py       # Module execution support
│   ├── main.py           # Application entry point
│   ├── backup.py         # Database backup functionality
│   ├── config.py         # Configuration management
│   ├── config_validator.py # Configuration validation
│   ├── logger.py         # Logging system
│   ├── notifications.py  # Multi-channel notifications
│   └── remote_upload.py  # Remote upload capabilities
├── tests/                # Comprehensive test suite
│   └── test_*.py         # Individual test modules
├── docs/                 # Complete documentation
│   ├── API.md            # API reference
│   ├── DEVELOPER_GUIDE.md # Development guide
│   ├── EXAMPLES.md       # Code examples
│   └── TUTORIALS.md      # User tutorials
└── logs/                 # Application logs (created at runtime)

Quick Start After Installation

1. Activate the virtual environment:

   . venv/bin/activate

2. Edit the configuration:

   nano config.ini
   # Set your MySQL credentials and backup directory

3. Run pre-flight checks:

   ./sqlBackup.sh --check

4. Perform your first backup:

   ./sqlBackup.sh

Troubleshooting

Common Issues

Permission denied creating backup directory:

• Edit config.ini and set backup_dir to a writable path
• Or create the directory with proper permissions: sudo mkdir -p /path/to/backup && sudo chown $USER /path/to/backup

mysqldump/mysql not found:

• Install MySQL client tools: sudo apt install mysql-client (Ubuntu/Debian)
• Or update mysql_path and mysqldump_path in config.ini to correct paths

Console scripts not found (sql-backup, sqlbackup):

• Ensure virtual environment is activated: . venv/bin/activate
• Or use the module directly: python3 -m sql_backup
• The startup script automatically falls back to python3 -m sql_backup

Configuration validation errors:

• Run the standalone validator: python validate_config.py --verbose
• Check file paths exist and are readable
• Ensure placeholder values are replaced with real configuration

cryptography/paramiko build failures:

• Install build dependencies: sudo apt install build-essential libssl-dev libffi-dev python3-dev

• Then retry: pip install -r requirements.txt

• Install MySQL client tools: sudo apt install mysql-client (Ubuntu/Debian)

• Or update mysql_path and mysqldump_path in config.ini

Package not found after installation:

• Ensure the virtual environment is activated: . venv/bin/activate
• Or run the module directly: python3 -m sql_backup

Build failures during pip install (cryptography/paramiko):

• Install build dependencies: sudo apt install build-essential libssl-dev libffi-dev python3-dev

Console scripts not found:

• The startup script automatically falls back to python3 -m sql_backup
• Or activate the virtual environment where the package was installed

Logging

The application uses a comprehensive logging system that provides:

• Console Logging: Colored output with different levels (DEBUG, INFO, WARNING, ERROR, CRITICAL)
• File Logging: Automatic log rotation with 10MB max file size and 5 backup files
• Configurable Levels: Control logging verbosity via configuration

Log files are stored in the logs/ directory by default and include detailed information for debugging and audit trails.

Configuration Validation

The tool includes a standalone configuration validator that checks your configuration before running backups:

# Validate the default config file
python validate_config.py

# Validate a specific config file
python validate_config.py --config path/to/your/config.ini

# Verbose output showing all checks
python validate_config.py --verbose

The validator will check:

• Required sections and parameters are present
• Data types are correct (integers, booleans, emails, URLs)
• File and directory paths exist and are accessible
• Email formats are valid
• Phone number formats are correct for SMS notifications
• URL formats are valid for webhooks
• Cross-dependencies between configuration options

Configuration Parameters

The config.ini file is the central configuration file for sqlBackup. It is divided into several sections:

[backup]

• backup_dir: Directory where backup files will be stored.

• backup_retention_days: Number of days to retain backups.

• archive_format: Archive format to use. Options: none, gz, xz, tar.xz, zip, rar.

[mysql]

• user, password, host: MySQL credentials.

• mysql_path: Path to the MySQL client.

• mysqldump_path: Path to the mysqldump utility.

• ignored_databases: Comma-separated list of databases to skip.

  • Now supports wildcards: e.g. sys, mysql, projekti_*. Any database name matching projekti_* will be ignored (e.g., projekti_alpha, projekti_1).

[telegram]

• enabled: Enable or disable Telegram notifications.

• telegram_token: Your Telegram Bot API token.

• telegram_chatid: Chat ID for notifications.

• telegram_serverid: A friendly name for your server (used in messages).

[email]

• enabled: Enable or disable email notifications.

• smtp_server, smtp_port: SMTP server details.

• username, password: SMTP credentials.

• from_address: Sender email address.

• to_addresses: Comma-separated recipient email addresses.

[slack]

• enabled: Enable or disable Slack notifications.

• webhook_url: Slack webhook URL for notifications.

[sms]

• enabled: Enable or disable SMS notifications.

• provider: Currently supports "twilio".

• account_sid, auth_token: Twilio credentials.

• from_number: Twilio phone number.

• to_numbers: Comma-separated list of recipient phone numbers.

[viber]

• enabled: Enable or disable Viber notifications.

• auth_token: Your Viber bot authentication token.

• receiver_id: Viber receiver ID (the user ID to send messages to).

• sender_name: (Optional) Sender name; defaults to "BackupBot" if not provided.

[messenger]

• enabled: Enable or disable Messenger notifications.

• page_access_token, recipient_id: Messenger API credentials (currently not implemented).

[notification]

• channels: Comma-separated list of notification channels to use (e.g., telegram, email, slack, sms, viber).

[export]

• include_routines: Include stored procedures and functions.

• include_events: Include scheduled events.

• column_statistics: If set to false, the script adds --column-statistics=0 to the dump command (helpful for older servers).

[logging]

• level: Logging level (DEBUG, INFO, WARNING, ERROR, CRITICAL).

• enable_console: Enable or disable colored console output.

• enable_file_logging: Enable or disable file logging with automatic rotation.

• log_directory: Directory where log files will be stored (created automatically).

[remote]

• upload_enabled: Enable or disable remote upload of backups.
• protocol: Upload protocol (sftp, ftp, or scp).
• host, port: Remote server details.
• username, password: Remote server credentials.
• remote_directory: Remote directory where backups will be stored.
• upload_schedule: When to perform the upload (e.g., daily, first_day, last_day, weekday, or a specific day).
• key_file, key_passphrase: (Optional) For SFTP public key authentication.

Usage

To run sqlBackup, you have several options depending on how you installed it:

Console Script (After pip install)

# Primary command
sql-backup

# Alternative name (backward compatibility)
sqlbackup

Module Execution

# Run as Python module
python -m sql_backup

Development/Direct Execution

# From source directory (development)
python -m sql_backup

# Standalone validation tool
python validate_config.py config.ini

Programmatic Usage

# Import and use programmatically
from sql_backup import main
from sql_backup.backup import MySQLBackup
from sql_backup.config import load_config

# Run complete backup process
main()

# Or use individual components
config = load_config()
backup = MySQLBackup(config)

How It Works

The script will:

• Connect to MySQL and dump databases (skipping those in ignored_databases, including wildcards)
• Archive each dump according to the specified format
• Display a summary table with database name, backup status, elapsed time, dump size, and archive size
• Send notifications via the enabled channels
• Upload backups to a remote server if enabled and if the schedule condition is met

Contributing

Contributions are welcome! Feel free to fork the repository, open issues, and submit pull requests. Please follow the existing code style and include tests for new features.

License

This project is licensed under the MIT License. See the LICENSE file for details.