README

A comprehensive, enterprise-grade database backup solution for MySQL databases with multiple storage options, automated scheduling, email notifications, and advanced security features.

⚠️ Important Notice

🔴 DO NOT USE THIS PACKAGE IN PRODUCTION

This package is currently under active development and may contain critical bugs. It is primarily intended for personal use, testing, and development environments. The current version has not undergone rigorous testing and may be unstable.

📋 Requirements

PHP: >= 8.0
MySQL: >= 5.7
Extensions:
- PDO with MySQL driver
- OpenSSL (for encryption)
- ZipArchive (for compression)
- SSH2 (for remote operations)
Composer: For dependency management

Required PHP Packages

{
  "dragonmantank/cron-expression": "^3.0",
  "phpmailer/phpmailer": "^6.0",
  "phpseclib/phpseclib": "^3.0",
  "google/apiclient": "^2.0",
  "aws/aws-sdk-php": "^3.0",
  "monolog/monolog": "^2.0"
}

🔧 Installation

1. Install via Composer

composer require onlyphp/database-backup-manager

2. Database Setup

Import the required database schema from the setup folder:

mysql -u your_username -p your_database < setup/database_schema.sql

3. Directory Permissions

Ensure proper permissions for the backup directories:

chmod 755 /path/to/backup/directory
chmod 755 /path/to/logs/directory

✨ Key Features

🎯 Core Functionality

Automated Database Backups - Schedule backups using cron expressions
Multiple Database Support - Backup multiple MySQL databases simultaneously
Conditional Table Backup - Use WHERE conditions for selective data backup
Table Synchronization - Automatic table and column discovery and sync

💾 Storage Options

Local Storage - Store backups on local filesystem
Google Drive - Upload backups to Google Drive with OAuth2
Amazon S3 - Store backups in AWS S3 buckets
Multiple Destinations - Configure multiple storage locations per backup

🔐 Security & Compression

AES-256 Encryption - Encrypt sensitive configuration data
Password-Protected Archives - Secure backup files with passwords
ZIP Compression - Reduce backup file sizes significantly
Salt-Based Encryption - Enhanced security for stored credentials

📧 Notifications & Monitoring

Email Notifications - SMTP and Mailgun support for backup reports
Comprehensive Logging - Detailed logs with Monolog integration
Backup Statistics - Track success rates, file sizes, and performance
Error Handling - Robust error handling with detailed reporting

🧹 Maintenance

Automatic Cleanup - Remove old backups based on retention policies
Temporary File Management - Clean up temporary files automatically
Job Status Tracking - Monitor backup job progress and status

🚀 Quick Start

Basic Usage

<?php
require 'vendor/autoload.php';

use OnlyPHP\DatabaseBackupManager;

// Initialize the backup manager
$backupManager = new DatabaseBackupManager(
    host: 'localhost',
    database: 'your_app_database',
    username: 'your_username',
    password: 'your_password',
    encryptionKey: 'your-secret-encryption-key'
);

// Run all scheduled backups
$success = $backupManager->run();

if ($success) {
    echo "✅ Backup process completed successfully!\n";
} else {
    echo "❌ Backup process encountered errors.\n";
}

Sync Database Tables

// Synchronize table structures for all active sources
$syncSuccess = $backupManager->syncTableColumn();

if ($syncSuccess) {
    echo "✅ Table synchronization completed!\n";
} else {
    echo "❌ Table synchronization failed.\n";
}

⚙️ Configuration

Database Tables Structure

The backup manager uses several database tables to store configuration:

backup_source - Database connection configurations
backup_run_job - Backup job tracking
backup_table_list - Table configurations and WHERE conditions
config_storage - Storage destination configurations
backup_storage_mapping - Source to storage mappings
backup_email_notifications - Email notification settings

Source Configuration Example

INSERT INTO backup_source (
    source_label,
    source_ip,
    source_port,
    source_username,
    source_password,
    source_salt,
    source_schema,
    source_charset,
    source_cron_schedule,
    source_compress_password,
    source_status
) VALUES (
    'Production Database',
    '192.168.1.100',
    3306,
    'backup_user',
    'encrypted_password_here',
    'random_salt_here',
    'production_db',
    'utf8mb4',
    '0 2 * * *',  -- Daily at 2 AM
    'encrypted_zip_password',
    1
);

💾 Storage Options

1. Local Storage

{
  "path": "/var/backups/database",
  "permissions": 0755
}

2. Google Drive

{
  "client_id": "your-google-client-id",
  "client_secret": "your-google-client-secret",
  "refresh_token": "your-refresh-token",
  "folder_id": "google-drive-folder-id"
}

3. Amazon S3

{
  "key": "your-access-key",
  "secret": "your-secret-key",
  "url": "your-url",
  "bucket": "your-backup-bucket",
  "region": "us-east-1",
  "endpoint": ""
}

📧 Email Notifications

SMTP Configuration

{
  "noti_host": "smtp.gmail.com",
  "noti_port": 587,
  "noti_username": "your-email@gmail.com",
  "noti_password": "your-app-password",
  "noti_encryption": "tls",
  "noti_email": "backups@yourcompany.com",
  "noti_from_name": "Database Backup System"
}

Mailgun Configuration

{
  "noti_secret_key": "your-mailgun-api-key",
  "noti_domain": "your-domain.com",
  "noti_email": "backups@your-domain.com",
  "noti_from_name": "Database Backup System"
}

Email Recipients

[
  "admin@company.com",
  "dba@company.com",
]

⏰ Scheduling

The backup manager uses cron expressions for flexible scheduling:

// Examples of cron schedules
'0 2 * * *'     // Daily at 2:00 AM
'0 2 * * 0'     // Weekly on Sunday at 2:00 AM
'0 2 1 * *'     // Monthly on 1st day at 2:00 AM
'0 */6 * * *'   // Every 6 hours
'*/30 * * * *'  // Every 30 minutes

Setting Up Cron Job

Add this to your crontab to run the backup manager:

# Run every minute to check for scheduled backups
* * * * * /usr/bin/php /path/to/your/backup-script.php >> /var/log/backup-cron.log 2>&1

🔐 Security Features

Encryption

All sensitive data is encrypted using AES-256-CBC:

Database passwords
Storage credentials
Archive passwords
API keys

Salt-Based Security

Each source has a unique salt for enhanced encryption security:

// Example of secure password storage
$encryptedPassword = $backupManager->encrypt($password, $uniqueSalt);

📖 Usage Examples

Advanced Backup Configuration

<?php
use OnlyPHP\DatabaseBackupManager;

try {
    $manager = new DatabaseBackupManager(
        host: 'localhost',
        database: 'backup_manager',
        username: 'root',
        password: 'password',
        encryptionKey: 'super-secret-key-32-characters!'
    );

    // Run scheduled backups
    if ($manager->run()) {
        echo "✅ All scheduled backups completed successfully\n";

        // Sync table structures
        if ($manager->syncTableColumn()) {
            echo "✅ Table synchronization completed\n";
        }
    }

} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    error_log("Backup Error: " . $e->getMessage());
}

Table-Specific Backup with WHERE Conditions

-- Configure specific table backup with conditions
INSERT INTO backup_table_list (
    source_id,
    table_name,
    table_where_condition,
    table_column_list,
    table_status
) VALUES (
    1,
    'user_activities',
    'created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)',
    '["id","user_id","activity_type","created_at"]',
    1
);

Multiple Storage Destinations

-- Map source to multiple storage destinations
INSERT INTO config_storage (storage_label, storage_code, storage_config, storage_status) VALUES
('Local Storage', 'local', '{"path": "/backup/mysql", "permissions": "0755"}', 1),  -- Local storage
('AWS S3', 's3', '{"bucket": "", "region": "us-east-1", "key": "", "secret": "", "url": "", "endpoint": null}', 1), -- Amazon S3
('Google Drive', 'gdrive', '{"client_id": "", "client_secret": "", "refresh_token": "", "folder_id": ""}', 1); -- Google Drive

🗄️ Database Schema

Core Tables

`backup_source`

Stores database source configurations

Column	Type	Description
`id`	INT	Primary key
`source_label`	VARCHAR(255)	Friendly name
`source_ip`	VARCHAR(255)	Database host
`source_port`	INT	Database port
`source_username`	VARCHAR(255)	Database username
`source_password`	TEXT	Encrypted password
`source_salt`	VARCHAR(255)	Encryption salt
`source_schema`	VARCHAR(255)	Database name
`source_cron_schedule`	VARCHAR(100)	Cron expression
`source_status`	TINYINT	Active status

`backup_run_job`

Tracks backup job execution

Column	Type	Description
`id`	INT	Primary key
`source_id`	INT	Source reference
`batch_start_date`	DATETIME	Job start time
`batch_end_date`	DATETIME	Job end time
`batch_total_status`	TINYINT	Job status
`batch_total_table`	INT	Total tables
`batch_total_success`	INT	Successful tables
`batch_total_failed`	INT	Failed tables

📚 API Reference

Main Methods

`run(): bool`

Executes all scheduled backup jobs

Returns: true on success, false on failure
Throws: Exception on critical errors

`syncTableColumn(): bool`

Synchronizes table structures for all active sources

Returns: true on success, false on failure
Throws: Exception on database errors

Private Methods

`getActiveBackupSources(): array`

Retrieves sources scheduled to run now

`processBackupSource(array $source): bool`

Processes a single backup source

`uploadToStorage(int $sourceId, string $backupFile): array`

Uploads backup to configured storage destinations

`sendNotifications(int $sourceId, array $results, array $stats): bool`

Sends email notifications about backup results

🔧 Troubleshooting

Common Issues

"Permission Denied" Errors

# Fix directory permissions
chmod 755 /path/to/backup/directory
chown www-data:www-data /path/to/backup/directory

"Connection Refused" Database Errors

Verify database credentials
Check firewall settings
Ensure MySQL service is running
Verify network connectivity

"Encryption/Decryption Failed"

// Verify encryption key length (should be 32+ characters)
$encryptionKey = 'your-32-character-encryption-key-here';

Google Drive Upload Failures

Check OAuth2 credentials
Verify refresh token validity
Ensure proper API permissions
Check quota limits

S3 Upload Failures

Verify AWS credentials
Check bucket permissions
Verify region settings
Check network connectivity

Debug Mode

Enable detailed logging for troubleshooting:

// Enable debug logging
$logger = new \Monolog\Logger('backup-debug');
$logger->pushHandler(new \Monolog\Handler\StreamHandler('debug.log', \Monolog\Logger::DEBUG));

Log Files

Check these log files for detailed information:

logs/backup.log - General backup operations
logs/error.log - Error messages and stack traces
logs/debug.log - Detailed debug information

🤝 Contributing

We welcome contributions! Please follow these guidelines:

Development Setup

# Clone the repository
git clone https://github.com/onlyphp/database-backup-manager.git

# Install dependencies
composer install

# Set up test database
mysql -u root -p < setup/database_schema.sql

# Run tests
./vendor/bin/phpunit

Coding Standards

Follow PSR-12 coding standards
Add comprehensive docblocks
Include unit tests for new features
Update documentation for changes

Pull Request Process

Fork the repository
Create a feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add amazing feature')
Push to the branch (git push origin feature/amazing-feature)
Open a Pull Request

📄 License

This project is licensed under the MIT License - see the license.md file for details.

🆘 Support

If you encounter any issues or need help:

Check the Documentation - Most common issues are covered here
Search Issues - Look through existing GitHub issues
Create an Issue - If you can't find a solution, create a new issue
Community Support - Join our community discussions

🙏 Acknowledgments

PHPMailer - Email functionality
Monolog - Logging system
phpseclib - SSH connectivity
Google APIs - Google Drive integration
AWS SDK - Amazon S3 integration
Cron Expression Parser - Scheduling functionality

onlyphp / database-backup-manager

Maintainers

Details

README

⚠️ Important Notice

📋 Table of Contents

📋 Requirements

Required PHP Packages

🔧 Installation

1. Install via Composer

2. Database Setup

3. Directory Permissions

✨ Key Features

🎯 Core Functionality

💾 Storage Options

🔐 Security & Compression

📧 Notifications & Monitoring

🧹 Maintenance

🚀 Quick Start

Basic Usage

Sync Database Tables

⚙️ Configuration

Database Tables Structure

Source Configuration Example

💾 Storage Options

1. Local Storage

2. Google Drive

3. Amazon S3

📧 Email Notifications

SMTP Configuration

Mailgun Configuration

Email Recipients

⏰ Scheduling

Setting Up Cron Job

🔐 Security Features

Encryption

Salt-Based Security

📖 Usage Examples

Advanced Backup Configuration

Table-Specific Backup with WHERE Conditions

Multiple Storage Destinations

🗄️ Database Schema

Core Tables

backup_source

backup_run_job

📚 API Reference

Main Methods

run(): bool

syncTableColumn(): bool

Private Methods

getActiveBackupSources(): array

processBackupSource(array $source): bool

uploadToStorage(int $sourceId, string $backupFile): array

sendNotifications(int $sourceId, array $results, array $stats): bool

🔧 Troubleshooting

Common Issues

"Permission Denied" Errors

"Connection Refused" Database Errors

"Encryption/Decryption Failed"

Google Drive Upload Failures

S3 Upload Failures

Debug Mode

Log Files

🤝 Contributing

Development Setup

Coding Standards

Pull Request Process

📄 License

🆘 Support

🙏 Acknowledgments

`backup_source`

`backup_run_job`

`run(): bool`

`syncTableColumn(): bool`

`getActiveBackupSources(): array`

`processBackupSource(array $source): bool`

`uploadToStorage(int $sourceId, string $backupFile): array`

`sendNotifications(int $sourceId, array $results, array $stats): bool`