README

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

Clone or download this repository:

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

Install dependencies:
```
composer install
```
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

Ensure dependencies are installed:
```
composer install
```

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

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

Run the backup example:
```
php example-backup.php
```
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

Install PHPUnit (if not already installed):
```
composer install --dev
```

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.

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.

varienos / mysql-backup

Maintainers

Package info

Statistics

Security