README

A Laravel package for database backup, restore, and synchronisation between environments with SSH tunnel support, a built-in Web UI, and merge-mode syncing that never deletes data.

Features

• Web UI — Backup, restore, delete, and sync from a browser at /db-sync
• Sync from Production — Pull production data to local with a field-level diff preview before applying
• Sync to Production — Push local data to production with the same diff preview
• SSH Tunnel Support — Connects to remote databases securely; auto-clears stale tunnels
• Environment-Prefixed Backups — production-*.zip, local-*.zip, pre-sync-from-production-*.zip, pre-sync-to-production-*.zip
• Merge Mode — Updates existing records, inserts new ones, never deletes
• Conflict Resolution — Multiple strategies (last-write-wins, production-wins, etc.)
• User ID Mapping — Remaps foreign keys by matching users via email
• Auto Pre-Sync Backup — Automatically snapshots the target DB before any sync and cleans it up on success
• Non-standard mysqldump paths — Configure the binary path for tools like Laravel Herd

Installation

composer require banditconsult/laravel-db-sync

Publish Configuration & Migrations

php artisan vendor:publish --tag=db-sync-config
php artisan vendor:publish --tag=db-sync-migrations
php artisan migrate

Publish Views (optional — only if you want to customise them)

php artisan vendor:publish --tag=db-sync-views

Configuration

.env Variables

# Binary path — only needed if mysqldump is not on your $PATH (e.g. Laravel Herd)
DB_SYNC_MYSQLDUMP_PATH="/path/to/mysqldump"

# Production database credentials
DB_SYNC_PRODUCTION_HOST=127.0.0.1
DB_SYNC_PRODUCTION_PORT=3306
DB_SYNC_PRODUCTION_DATABASE=your_production_db
DB_SYNC_PRODUCTION_USERNAME=db_user
DB_SYNC_PRODUCTION_PASSWORD=db_password

# SSH tunnel (required when the production DB is behind a firewall)
DB_SYNC_SSH_ENABLED=true
DB_SYNC_SSH_HOST=your-server.com
DB_SYNC_SSH_USER=deploy
DB_SYNC_SSH_PORT=22

# SSH key names to try, comma-separated, resolved from ~/.ssh/
DB_SYNC_SSH_KEYS=deploy_key,id_rsa

config/db-sync.php Reference

Key	Description	Default
mysqldump_path	Path to mysqldump binary	mysqldump
backup_path	Directory to store .zip backups	storage/db-backups
backup_before_sync	Auto-backup target before any sync	true
backup_retention_days	Days to keep old backups	7
conflict_strategy	Default conflict resolution	last-write-wins
tables.exclude	Tables always skipped	migrations, sessions, etc.
tables.include	Allowlist (empty = all non-excluded)	[]
chunk_size	Records per batch	1000
ssh.local_port	Local port for SSH tunnel	33066
ui.enabled	Enable the web UI	true
ui.route_prefix	URL prefix for the UI	db-sync
ui.middleware	Middleware applied to all UI routes	['web', 'auth']
ui.layout	Blade layout to extend (overrides the built-in one)	null

Web UI

Navigate to /db-sync in your browser after installing and migrating.

Main Page (/db-sync)

Lists all backup files with environment badges and actions:

Badge colour	Environment
Violet	production
Indigo	local
Teal	pre-sync-from-production
Orange	pre-sync-to-production
Gray	Unknown

Header buttons:

• Backup Local — Dumps the local DB to local-{timestamp}.zip
• Backup Production — Dumps production via SSH tunnel to production-{timestamp}.zip
• Sync from Production — Opens the diff preview before pulling production data locally
• Sync to Production — Opens the diff preview before pushing local data to production

Row actions:

• Restore — Restores that backup file to the appropriate database (confirm modal)
• Delete — Permanently deletes the zip file (confirm modal)

Diff Preview (/db-sync/sync/preview and /db-sync/sync/preview-to-production)

Before any sync is applied you see:

• Summary cards: tables affected, records to insert (green), records to update (amber)
• Per-table expandable rows showing:
  • New records — full row table, up to 15 rows sampled
  • Changed records — field-by-field old → new comparison, only differing fields shown
• Apply Sync / Push to Production button — applies the sync
• Cancel link — returns to the main page without changes

The preview uses an offline diff: it imports the relevant backup into a temporary database and compares it against the target without any live network connection to production.

Using a Custom Layout

If you want the UI inside your app's existing navigation, set ui.layout in config/db-sync.php:

'ui' => [
    'layout' => 'layouts.app', // your app's Blade layout
],

Your layout must have a @yield('content') section.

CLI Commands

db:sync — Sync Database

Merge mode: updates existing, inserts new, never deletes.

# Sync from production to local (default)
php artisan db:sync

# Specify source
php artisan db:sync --from=production

# Skip pre-sync backup
php artisan db:sync --no-backup

# Skip confirmation prompt
php artisan db:sync --force

# Filter tables
php artisan db:sync --tables=users,posts
php artisan db:sync --exclude=sessions,cache

# Choose conflict strategy
php artisan db:sync --conflicts=production-wins

Conflict Resolution Strategies

Strategy	Description
last-write-wins	Record with newer updated_at wins (default)
production-wins	Production always takes precedence
local-wins	Local always takes precedence
source-wins	Source connection always wins
target-wins	Target connection always wins

db:backup — Create Backup

# Auto-detect from APP_ENV
php artisan db:backup

# Backup production database (via SSH tunnel)
php artisan db:backup --production

# Backup local database
php artisan db:backup --local

# List all available backups
php artisan db:backup --list

db:restore — Restore from Backup

# Interactive — selects from available backups
php artisan db:restore

# Restore from production backups
php artisan db:restore --production

# Restore from local backups
php artisan db:restore --local

# Restore a specific file
php artisan db:restore --backup=production-2024-01-15-10-30-00.zip

# Skip confirmation
php artisan db:restore --force

db:backup:cleanup — Remove Old Backups

# Remove backups older than configured retention (default: 7 days)
php artisan db:backup:cleanup

# Custom retention
php artisan db:backup:cleanup --days=14

# Skip confirmation
php artisan db:backup:cleanup --force

Background Queue

All heavy operations (backup, restore, sync, rollback) are dispatched as Laravel jobs so they run in the background — no PHP timeout, no browser waiting.

Requirements

Set your queue connection to anything other than sync in .env:

QUEUE_CONNECTION=database   # or redis, etc.

The jobs table must exist (php artisan queue:table && php artisan migrate if it doesn't).

How the worker is managed

The package manages a queue worker (php artisan queue:work) for the db-sync queue. You control it from the status bar at the top of /db-sync:

Control	What it does
Start Worker	Spawns a persistent worker that stays running until you click Stop. Green dot stays green.
Stop Worker	Sends SIGTERM — worker exits cleanly after finishing any in-progress job.
Clear Queue	Appears when there are pending jobs. Deletes all waiting (unprocessed) jobs from the queue immediately.

The worker is also auto-started when you click Backup, Restore, or Sync without a worker running. In that case it starts with --stop-when-empty and exits automatically once the queue is empty. The page reloads automatically when the job finishes so new files appear in the list.

Queue status bar

The status bar at the top of /db-sync polls every 3 seconds and shows:

• Green dot = worker running (persistent), grey dot = worker stopped
• Spinning indicator + count = jobs waiting to be processed — click to expand the list
• Expanded job list — each waiting job shows its name, status (waiting / processing), and queued time. Waiting jobs have a × button to cancel them individually without affecting jobs already being processed
• Recent activity row — last 8 job events from the worker log:
  • ✓ green = job completed successfully
  • ● purple (pulsing) = job was running at that moment
  • ✗ red = job failed
  • Clear link — truncates the worker log and removes all activity pills instantly
• Red badge = failed jobs — click to expand error details with the job name, error message, and timestamp

Cancelling queued jobs

Individual jobs can be cancelled while they are still waiting (not yet picked up by the worker):

• Click the N jobs queued badge to expand the pending jobs list
• Click the × on any waiting job to remove it from the queue
• Jobs already processing cannot be cancelled this way — use Stop Worker instead

Clear Queue (visible in the top-right of the status bar when jobs are pending) removes all waiting jobs at once.

Worker logs

Worker output is appended to storage/logs/db-sync-worker.log.

Queue config

// config/db-sync.php
'queue' => [
    'connection' => env('DB_SYNC_QUEUE_CONNECTION', env('QUEUE_CONNECTION', 'database')),
    'name'       => env('DB_SYNC_QUEUE_NAME', 'db-sync'),
],

You can isolate the package onto its own connection if needed:

DB_SYNC_QUEUE_CONNECTION=redis
DB_SYNC_QUEUE_NAME=db-sync

Rollback

Every sync automatically saves a rollback snapshot before making any changes. If a sync produces unexpected results you can undo it in one click from the UI.

Sync direction	Rollback snapshot	Restores
Sync from Production	pre-sync-from-production-{ts}.zip — local state before pull	Local DB
Sync to Production	pre-push-to-production-{ts}.zip — production state before push	Production DB (via SSH)

How to rollback:

1. Go to /db-sync
2. Find the rollback snapshot (teal badge = local rollback, amber badge = production rollback)
3. Click ↺ Rollback and confirm
4. The snapshot is applied and then deleted

Only the most recent rollback snapshot per direction is kept — old ones are replaced automatically when you run another sync.

How Syncing Works

Sync from Production (production → local)

1. Any existing pre-sync-from-production-*.zip is replaced with a fresh local snapshot (the rollback point)
2. SSH tunnel opens to production server
3. mysqldump exports production DB through the tunnel; tunnel closes
4. Production dump is imported into a temporary database
5. Users are matched by email to build a production_id → local_id mapping
6. Tables are merged using INSERT … ON DUPLICATE KEY UPDATE
7. user_id foreign keys are remapped via the user mapping
8. Temporary database and temp files are dropped
9. pre-sync-from-production-{ts}.zip is kept for rollback

Sync to Production (local → production)

1. Any existing pre-push-to-production-*.zip is replaced with a fresh production snapshot (the rollback point)
2. An SSH tunnel opens to take the production snapshot; tunnel closes
3. Local DB is dumped to local-{ts}.zip
4. An SSH tunnel opens and the local dump is imported into production; tunnel closes
5. pre-push-to-production-{ts}.zip is kept for rollback

The diff preview (for both directions) is computed entirely offline using backup files and a temp database — no live production connection is needed during the preview step.

Backup File Naming

production-2024-01-15-10-30-00.zip              # production snapshot (manual)
local-2024-01-15-10-30-00.zip                   # local snapshot (manual or push payload)
pre-sync-from-production-2024-01-15-10-30.zip   # rollback point: local before pulling from prod
pre-push-to-production-2024-01-15-10-30.zip     # rollback point: production before local was pushed

Stored in storage/db-backups/ (configurable via backup_path).

Rollback snapshots (pre-sync-from-production-* and pre-push-to-production-*) are kept after each sync so you can undo it. The previous snapshot for that direction is replaced automatically when you run a new sync. After a rollback is applied the snapshot is deleted.

SSH Tunnel Details

When DB_SYNC_SSH_ENABLED=true, the package:

1. Reads SSH key names from DB_SYNC_SSH_KEYS (comma-separated)
2. Looks for each key in ~/.ssh/
3. Kills any stale SSH tunnel on the configured local port before opening a new one
4. Creates tunnel: localhost:33066 → production_db:3306
5. Runs mysqldump / mysql through the tunnel
6. Closes the tunnel in a finally block when done

Key Fallback

# Tries ~/.ssh/deploy_key first, then ~/.ssh/id_rsa
DB_SYNC_SSH_KEYS=deploy_key,id_rsa

Programmatic Usage

use BanditConsult\DbSync\Services\SyncEngine;
use BanditConsult\DbSync\Services\BackupManager;

// Sync from production
$engine = app(SyncEngine::class);
$engine->setLogger(fn($msg) => logger()->info($msg));
$engine->setConflictStrategy('last-write-wins');
$stats = $engine->sync('production', 'local');
// Returns: ['tables_synced' => 5, 'records_inserted' => 100, ...]

// Backup local
$manager = app(BackupManager::class);
$zipPath = $manager->backup('local', 'local');

// Backup production (via SSH tunnel)
$zipPath = $manager->backup('production', 'production');

// Restore to local
$manager->restore($zipPath);

// Push a local backup to production (via SSH tunnel)
$manager->restoreToProduction($zipPath);

// List backups
$backups = $manager->getBackups('production');

// Cleanup old backups
$deleted = $manager->cleanup(7);

Requirements

• PHP: >= 8.1
• Laravel: >= 10.0 (including 11 and 12)
• Database: MySQL 5.7+ / MariaDB
• CLI Tools: mysqldump, mysql (must be on $PATH or configured via DB_SYNC_MYSQLDUMP_PATH)
• SSH Client: Required for remote production backups

Troubleshooting

"Database dump failed or returned empty"

If mysqldump is not on your system $PATH (common with Laravel Herd), set the full path:

DB_SYNC_MYSQLDUMP_PATH="/Users/you/Library/Application Support/Herd/bin/mysqldump"

"bind: Address already in use" (SSH tunnel)

A previous request may have timed out, leaving an SSH tunnel process alive on port 33066. The package now auto-kills stale tunnels before creating a new one. If it still fails, run:

pkill -f "ssh.*33066"

"SSH key not found"

Ensure your SSH keys are in ~/.ssh/ and listed in DB_SYNC_SSH_KEYS:

ls -la ~/.ssh/

Backup or sync times out (web request)

The web request PHP time limit may be too low for large databases. The sync-to-production route calls set_time_limit(600) automatically. For other routes, increase the limit in your PHP config or run the operation via CLI:

php artisan db:backup --production
php artisan db:sync --from=production --force

"Table doesn't exist in temp database"

The package automatically strips mysqldump: warnings, USE database, and CREATE DATABASE statements from SQL files before importing. If you still hit this, check that mysql on your system can read the dump file manually:

mysql -u root -p temp_db < dump.sql

License

MIT License

Contributing

Pull requests are welcome. Please ensure syntax is valid (php -l) before submitting.