MySQL Backup PHP

Modern, efficient MySQL database backup and restore utility for PHP 7.4+

Features

• Modern PHP: Built with PHP 7.4+ features (type hints, PDO, PSR-4 autoloading)
• Memory Efficient: Batch processing to handle large databases without exhausting memory
• Compression Support: Optional gzip compression to save disk space
• Backup & Restore: Full backup and restore functionality
• Progress Tracking: Real-time logging and progress information
• Flexible Table Selection: Backup all tables or select specific ones
• CLI & Web Compatible: Works in both command-line and web environments
• Zero Dependencies: Core functionality requires only PHP and PDO extension

Requirements

• PHP >= 7.4
• PDO MySQL extension
• MySQL/MariaDB database

Installation

Using Composer (Recommended)

composer require varienos/mysql-backup

Manual Installation

1. Clone or download this repository:

   git clone https://github.com/varienos/mysql-backup.git
   cd mysql-backup

2. Install dependencies:

   composer install

3. Verify installation:

   php test-autoload.php

Quick Install with Make

If you have make installed:

make install      # Install dependencies
make check        # Validate installation
make test         # Run tests

Quick Start

Creating a Backup

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

use Varienos\MySQLBackup\MySQLBackup;

$backup = new MySQLBackup(
    'localhost',      // host
    'username',       // username
    'password',       // password
    'database_name'   // database
);

// Backup all tables
$backup->backup('*');

// Get backup file path
echo "Backup saved to: " . $backup->getLastBackupFile();

Restoring from Backup

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

use Varienos\MySQLBackup\MySQLBackup;

$backup = new MySQLBackup(
    'localhost',
    'username',
    'password',
    'database_name'
);

// Restore from a backup file
$backup->restore('sqlbackup/varienos-sql-backup-mydb-20241010_143000.sql.gz');

Configuration Options

$options = [
    'backup_dir' => 'sqlbackup',              // Backup directory
    'charset' => 'utf8mb4',                   // Database charset
    'gzip' => true,                           // Enable gzip compression
    'batch_size' => 2000,                     // Rows per batch
    'disable_foreign_key_checks' => true,     // Disable FK checks during restore
];

$backup = new MySQLBackup($host, $user, $pass, $db, $options);

Advanced Usage

Backup Specific Tables

// Backup specific tables (array)
$backup->backup(['users', 'posts', 'comments']);

// Backup specific tables (comma-separated string)
$backup->backup('users, posts, comments');

List Available Backups

$backups = $backup->listBackups();

foreach ($backups as $file) {
    echo "{$file['file']} ({$file['size_formatted']}) - {$file['created_formatted']}\n";
}

Access Logs

$backup->backup('*');

// Get all log entries
$logs = $backup->getLogs();
foreach ($logs as $log) {
    echo $log . "\n";
}

Examples

See the example-backup.php and example-restore.php files for complete working examples.

Running Examples

1. Ensure dependencies are installed:

   composer install

2. Copy .env.example to .env and configure your database credentials:

   cp .env.example .env
   # Edit .env with your database credentials

3. Run the backup example:

   php example-backup.php

4. Run the restore example:

   php example-restore.php

Available Commands

Composer Commands

# Install dependencies
composer install

# Update dependencies
composer update

# Run tests
composer test

# Run tests with coverage
composer test-coverage

# Run specific test
composer test-filter testBackupWithData

# Validate composer.json
composer validate

# Optimize autoloader
composer dump-autoload --optimize

Make Commands

make help           # Show all available commands
make install        # Install composer dependencies
make update         # Update composer dependencies
make autoload       # Regenerate optimized autoloader
make test           # Run PHPUnit tests
make test-coverage  # Run tests with coverage report
make check          # Validate composer.json and test autoload
make clean          # Clean vendor and cache files

Performance Tips

• Batch Size: Adjust batch_size based on your available memory. Larger batches are faster but use more memory.
• Compression: Enable gzip compression to save disk space (typically 70-90% reduction).
• Foreign Key Checks: Disable during restore for faster imports.

Testing

This package includes a comprehensive test suite using PHPUnit.

Setup Test Environment

1. Install PHPUnit (if not already installed):

   composer install --dev

2. Configure test database credentials in phpunit.xml:

   <php>
       <env name="DB_HOST" value="localhost"/>
       <env name="DB_USER" value="root"/>
       <env name="DB_PASSWORD" value=""/>
       <env name="DB_NAME" value="test_backup_db"/>
   </php>

   Or copy .env.testing.example to .env.testing and configure your test credentials.

3. Ensure test database user has permissions:

   CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'test_password';
   GRANT ALL PRIVILEGES ON test_backup_db.* TO 'test_user'@'localhost';
   FLUSH PRIVILEGES;

Running Tests

Run all tests:

./vendor/bin/phpunit

Run specific test class:

./vendor/bin/phpunit tests/MySQLBackupTest.php

Run with coverage report:

./vendor/bin/phpunit --coverage-html coverage-report

Run specific test method:

./vendor/bin/phpunit --filter testBackupWithData

Test Coverage

The test suite covers:

• ✅ Constructor and initialization with valid/invalid credentials
• ✅ Backup all tables (empty and with data)
• ✅ Backup specific tables (array and string format)
• ✅ Restore from compressed and uncompressed backups
• ✅ Gzip compression/decompression
• ✅ Batch processing with large datasets
• ✅ List backups with metadata
• ✅ Foreign key constraint handling
• ✅ Special character escaping in data
• ✅ Error handling and exceptions
• ✅ Logging system

Writing Custom Tests

Extend the base TestCase class for database test utilities:

<?php

namespace Varienos\MySQLBackup\Tests;

use Varienos\MySQLBackup\MySQLBackup;

class MyCustomTest extends TestCase
{
    public function testMyFeature(): void
    {
        // Create test database and tables
        $this->createTestDatabaseConnection();
        $this->createSampleTables();

        // Insert sample data
        $this->insertSampleData(10, 5);

        // Your test code here
        $backup = new MySQLBackup(
            $this->testDbHost,
            $this->testDbUser,
            $this->testDbPassword,
            $this->testDbName
        );

        $this->assertTrue($backup->backup('*'));
    }
}

Security Notes

• Never commit .env files or hardcode credentials in version control
• Store backup files in a secure location with appropriate permissions
• Regularly test your backup and restore procedures
• Consider encrypting sensitive backup files

License

MIT License

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

For security reports or general questions, reach out at hello@varien.dev.