# Ngauge SQL Backup Extension for Yii2

Database table backup and restore extension with table-level granularity, compression, and restore point management.

## Features

- **Table-level backups**: Backup individual tables or multiple selected tables
- **Compressed storage**: Automatic gzip compression to save disk space
- **Restore points**: Organized backups with table name prefixes for easy version tracking
- **Multiple interfaces**:
  - Console commands for CLI automation
  - Backend UI for visual table selection
  - Programmatic API for custom integration
- **Queue support**: Async backup execution via Yii2 queue
- **Backup history**: Track all backup/restore operations
- **Automatic rotation**: Configurable retention policy to manage old backups

## Installation

Add to your `composer.json`:

```bash
composer require portalpeople/ngauge-backup-sql
```

Or manually add to `require` section:

```json
{
    "require": {
        "portalpeople/ngauge-backup-sql": "*"
    }
}
```

Run migrations:

```bash
./yii migrate --migrationPath=@PortalPeople/NgaugeBackupSql/migrations
```

## Configuration

Add to your console configuration (`console/config/main.php`):

```php
return [
    'bootstrap' => ['db-backup'],
    'modules' => [
        'db-backup' => [
            'class' => 'PortalPeople\NgaugeBackupSql\Module',
            'backupPath' => '@backups/sql',           // Backup storage location
            'retentionDays' => 30,                     // Keep backups for 30 days
            'compressBackups' => true,                 // Use gzip compression
            'excludeTables' => ['log', 'queue'],       // Tables to exclude from listings
            'dbComponent' => 'db',                     // Database component name
        ],
    ],
];
```

For backend UI access, also add to `backend/config/main.php`:

```php
return [
    'modules' => [
        'db-backup' => [
            'class' => 'PortalPeople\NgaugeBackupSql\Module',
        ],
    ],
];
```

## Usage

### Console Commands

**Create a backup:**

```bash
# Interactive table selection
./yii db-backup/create

# Backup specific table
./yii db-backup/create --table=users

# Backup without compression
./yii db-backup/create --table=users --compress=0

# Backup with custom filename
./yii db-backup/create --table=pricing --filename=pricing-before-update.sql.gz
```

**List available backups:**

```bash
# List all backups
./yii db-backup/list

# List backups for specific table
./yii db-backup/list --table=users
```

**Restore from backup:**

```bash
# Interactive restore point selection
./yii db-backup/restore

# Restore specific backup
./yii db-backup/restore --file=users_20260210_1430.sql.gz

# Force restore without confirmation
./yii db-backup/restore --file=users_20260210_1430.sql.gz --force
```

**Cleanup old backups:**

```bash
# Remove backups older than retention period
./yii db-backup/cleanup

# Remove backups older than 7 days
./yii db-backup/cleanup --days=7
```

### Programmatic API

Use the static `Backup` facade in your code:

```php
use PortalPeople\NgaugeBackupSql\components\Backup;

// Backup a single table
$backupFile = Backup::SQLTable('pricing');
// Returns: /backups/sql/pricing_20260210_1430.sql.gz

// Backup with custom filename
$backupFile = Backup::SQLTable('pricing', 'pricing-ADP.zip');
// Returns: /backups/sql/pricing-ADP.zip

// List restore points for a table
$restorePoints = Backup::listRestorePoints('pricing');
// Returns: ['pricing_20260210_1430.sql.gz', 'pricing_20260209_0900.sql.gz', ...]

// Restore from a backup file
Backup::restore('pricing_20260210_1430.sql.gz');
```

### Queue Integration

Schedule backups using Yii2 queue:

```php
use PortalPeople\NgaugeBackupSql\jobs\DatabaseBackupJob;

// Queue a backup job
Yii::$app->queue->push(new DatabaseBackupJob([
    'tables' => ['users', 'pricing'],
    'compress' => true,
]));

// Schedule daily backup
Yii::$app->queue->push(new DatabaseBackupJob([
    'tables' => ['*'], // All tables
    'compress' => true,
]));
```

### Backend UI

Access the backup interface at:

```
/db-backup/default/index
```

Features:
- Visual table selection with checkboxes
- One-click backup creation
- Browse restore points by table
- Download backup files
- Restore with confirmation dialog

## Backup File Naming

Backups follow this naming convention:

```
{tablename}_{YYYYMMDD}_{HHMM}.sql.gz
```

Examples:
- `users_20260210_1430.sql.gz`
- `pricing_20260209_0900.sql.gz`
- `orders_20260208_1600.sql.gz`

Custom filenames are also supported when using the programmatic API.

## Security Considerations

1. **File Permissions**: Backup files are created with 0600 permissions (owner read/write only)
2. **Database Credentials**: Never exposed in backup files or logs
3. **Path Validation**: All file paths are validated to prevent traversal attacks
4. **Restore Confirmation**: Interactive restore requires confirmation unless `--force` is used

## Requirements

- PHP >= 8.2
- Yii2 >= 2.0
- MySQL/MariaDB with `mysqldump` and `mysql` CLI tools available
- Write permissions on backup directory

## Troubleshooting

**Command not found: mysqldump**

Ensure MySQL client tools are installed:
```bash
# Ubuntu/Debian
sudo apt-get install mysql-client

# CentOS/RHEL
sudo yum install mysql
```

**Permission denied on backup directory**

Ensure the backup directory exists and is writable:
```bash
mkdir -p /var/www/vhosts/your-domain/backups/sql
chmod 755 /var/www/vhosts/your-domain/backups/sql
```

**Backup fails for large tables**

For tables larger than 1GB, consider increasing PHP memory limit and execution time in `php.ini`:
```ini
memory_limit = 512M
max_execution_time = 300
```

## License

MIT License - see LICENSE file for details.

## Author

PortalPeople - a.pickstock@PortalPeople.uk
