PHPackages ziffmedia/laravel-mysql-snapshots - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. ziffmedia/laravel-mysql-snapshots ActiveLibrary[Database & ORM](/categories/database) ziffmedia/laravel-mysql-snapshots ================================= A set of commands that will create and load MySQL snapshots with the mysql-client command line tools v1.1.1(3mo ago)3150.8kโ†“10.8%2[1 PRs](https://github.com/ziffmedia/laravel-mysql-snapshots/pulls)MITPHPPHP >=8.2CI failing Since Jan 5Pushed 2mo ago12 watchersCompare [ Source](https://github.com/ziffmedia/laravel-mysql-snapshots)[ Packagist](https://packagist.org/packages/ziffmedia/laravel-mysql-snapshots)[ RSS](/packages/ziffmedia-laravel-mysql-snapshots/feed)WikiDiscussions master Synced 1mo ago READMEChangelog (1)Dependencies (6)Versions (27)Used By (0) [![Laravel MySQL Snapshots](art/logo.svg)](art/logo.svg) **Create, manage, and load MySQL database snapshots with ease** [![Latest Version on Packagist](https://camo.githubusercontent.com/8c5a683eed5834e59e1b1bcbf4c82c7f4541feea01dcc2eab18a02f7442a098e/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f7a6966666d656469612f6c61726176656c2d6d7973716c2d736e617073686f74732e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/ziffmedia/laravel-mysql-snapshots) [![Total Downloads](https://camo.githubusercontent.com/5bc8325899ea0d304fbfe46906a5a1e063d4d6dde076097186c91d4a174a160c/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f7a6966666d656469612f6c61726176656c2d6d7973716c2d736e617073686f74732e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/ziffmedia/laravel-mysql-snapshots) [![GitHub Tests Action Status](https://camo.githubusercontent.com/350ae2bce56bcdee309d1ff5f286bed95761d8ebcf172e5b0b951a382f253b78/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f7a6966666d656469612f6c61726176656c2d6d7973716c2d736e617073686f74732f72756e2d74657374732e796d6c3f6272616e63683d6d6173746572267374796c653d666c61742d737175617265)](https://github.com/ziffmedia/laravel-mysql-snapshots/actions) --- Overview -------- [](#overview) Laravel MySQL Snapshots is a powerful package that streamlines the process of creating, managing, and loading MySQL database snapshots in your Laravel applications. Perfect for syncing production data to local development environments, creating test fixtures, or maintaining database backups across different storage systems. Features -------- [](#features) - **๐Ÿ“ธ Flexible Snapshot Plans** - Define multiple snapshot configurations with custom naming, tables, and options - **โ˜๏ธ Cloud Storage Integration** - Seamlessly store and retrieve snapshots from any Laravel filesystem disk - **๐Ÿ“Š Enhanced List Display** - View snapshots in formatted tables with file sizes and timestamps - **โšก Smart Caching** - Automatic timestamp-based cache validation for faster subsequent loads - **๐Ÿ“ˆ Progress Indicators** - Visual feedback for large snapshot downloads - **๐Ÿ”ง Post-Load SQL Commands** - Execute custom SQL commands automatically after loading snapshots - **๐Ÿ‘ฅ Plan Groups** - Batch operations on related plans with automatic detection - **๐Ÿ”’ Environment Locks** - Restrict snapshot creation/loading to specific environments - **๐Ÿ—‚๏ธ Partial Snapshots** - Include/exclude specific tables or use schema-only dumps - **๐Ÿงน Automatic Cleanup** - Keep only the most recent N snapshots per plan Installation ------------ [](#installation) Install the package via Composer: ``` composer require ziffmedia/laravel-mysql-snapshots ``` Publish the configuration file: ``` php artisan vendor:publish --provider='ZiffMedia\LaravelMysqlSnapshots\MysqlSnapshotsServiceProvider' ``` This will create a `config/mysql-snapshots.php` file in your application. Configuration ------------- [](#configuration) The configuration file allows you to define snapshot plans, storage locations, and behavior. Here's an overview of the key configuration options: ### Basic Configuration Structure [](#basic-configuration-structure) ``` return [ 'cache_by_default' => false, // Enable smart caching 'filesystem' => [ 'local_disk' => 'local', // Local disk for caching 'local_path' => 'mysql-snapshots', 'archive_disk' => 'cloud', // Cloud disk for storage 'archive_path' => 'mysql-snapshots', ], // Global SQL commands to run after ANY snapshot load 'post_load_sqls' => [ // 'UPDATE users SET environment = "local"', ], // Plan groups: Named groups of plans for batch operations 'plan_groups' => [ // 'daily' => [ // 'plans' => ['daily-base', 'daily-extra'], // ], ], 'plans' => [ 'daily' => [ 'connection' => null, // Database connection (null = default) 'file_template' => 'mysql-snapshot-daily-{date:Ymd}', 'mysqldump_options' => '--single-transaction --no-tablespaces', 'tables' => [], // Empty = all tables 'ignore_tables' => [], 'schema_only_tables' => ['failed_jobs'], // Only dump structure 'keep_last' => 7, // Keep last N snapshots 'environment_locks' => [ 'create' => 'production', // Only create in production 'load' => 'local', // Only load in local ], 'post_load_sqls' => [ // Plan-specific SQL commands ], ], ], 'utilities' => [ 'mysqldump' => 'mysqldump', 'mysql' => 'mysql', 'zcat' => 'zcat', 'gzip' => 'gzip', ], ]; ``` ### Configuration Options Explained [](#configuration-options-explained) #### Global Options [](#global-options) - `cache_by_default` - Enable automatic timestamp-based cache validation #### Filesystem [](#filesystem) - `local_disk` - Laravel disk for local caching (default: `local`) - `local_path` - Path on local disk for cached snapshots - `archive_disk` - Laravel disk for archived snapshots (typically cloud storage) - `archive_path` - Path on archive disk #### Plans [](#plans) Each plan can have the following options: - `connection` - Database connection name (null for default) - `file_template` - Snapshot filename template (supports `{date:format}` placeholder) - `mysqldump_options` - Additional options passed to mysqldump - `tables` - Array of specific tables to include (empty = all tables) - `ignore_tables` - Array of tables to exclude - `schema_only_tables` - Array of tables to dump structure only (no data) - `keep_last` - Number of snapshots to retain (older ones are deleted) - `environment_locks` - Restrict operations to specific environments - `create` - Environment(s) where snapshots can be created - `load` - Environment(s) where snapshots can be loaded - `post_load_sqls` - Array of SQL commands to execute after loading Usage ----- [](#usage) ### List Snapshots [](#list-snapshots) View all available snapshots with file sizes and timestamps: ``` php artisan mysql-snapshots:list ``` View snapshots for a specific plan: ``` php artisan mysql-snapshots:list daily ``` Example output: ``` Plan: daily โ”Œโ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ” โ”‚ # โ”‚ Filename โ”‚ Created โ”‚ Size โ”‚ โ”œโ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค โ”‚ 1 โ”‚ mysql-snapshot-daily-20250115.gz โ”‚ 2025-01-15 10:30:00 โ”‚ 125.4 MB โ”‚ โ”‚ 2 โ”‚ mysql-snapshot-daily-20250114.gz โ”‚ 2025-01-14 10:30:00 โ”‚ 123.8 MB โ”‚ โ””โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜ ``` ### Create Snapshots [](#create-snapshots) Create a snapshot using the specified plan: ``` php artisan mysql-snapshots:create daily ``` Create a snapshot and automatically cleanup old ones: ``` php artisan mysql-snapshots:create daily --cleanup ``` Create snapshots for all plans in a plan group: ``` php artisan mysql-snapshots:create daily-group ``` ### Load Snapshots [](#load-snapshots) Load the newest snapshot from the first available plan: ``` php artisan mysql-snapshots:load ``` Load a specific plan: ``` php artisan mysql-snapshots:load daily ``` Load with caching (keeps local copy for faster subsequent loads): ``` php artisan mysql-snapshots:load daily --cached ``` Download fresh snapshot and keep it cached: ``` php artisan mysql-snapshots:load daily --recached ``` Load without dropping existing tables: ``` php artisan mysql-snapshots:load daily --no-drop ``` Skip post-load SQL commands: ``` php artisan mysql-snapshots:load daily --skip-post-commands ``` Load all plans in a plan group sequentially: ``` php artisan mysql-snapshots:load daily-group ``` Advanced Features ----------------- [](#advanced-features) ### Smart Caching [](#smart-caching) Enable smart caching to automatically validate cached snapshots based on timestamps: ``` 'cache_by_default' => true, ``` When enabled, the system stores metadata (`.meta.json` files) alongside cached snapshots. On subsequent loads, it checks if the archive file is newer than the cached version and automatically refreshes if needed. ### Post-Load SQL Commands [](#post-load-sql-commands) Execute SQL commands automatically after loading snapshots. Useful for environment-specific adjustments. Commands execute in this order: 1. **Global commands** - Run after each individual plan loads 2. **Plan-specific commands** - Run after the specific plan loads 3. **Plan group commands** - Run after all plans in a group have loaded **Global commands** (run after any snapshot load): ``` 'post_load_sqls' => [ 'UPDATE users SET email = CONCAT("user+", id, "@example.test") WHERE is_admin = 0', 'ANALYZE TABLE users, orders, products', ], ``` **Plan-specific commands** (run after loading specific plan): ``` 'plans' => [ 'daily' => [ // ... 'post_load_sqls' => [ 'UPDATE settings SET environment = "local"', 'DELETE FROM cache WHERE expires_at < NOW()', ], ], ], ``` **Plan group commands** (run after all plans in the group have loaded): ``` 'plan_groups' => [ 'daily' => [ 'plans' => ['daily-base', 'daily-extra'], 'post_load_sqls' => [ 'ANALYZE TABLE users, orders', // Run after both plans are loaded 'OPTIMIZE TABLE products', ], ], ], ``` ### Plan Groups [](#plan-groups) Group related plans for batch operations: ``` 'plan_groups' => [ 'daily' => [ 'plans' => ['daily-base', 'daily-savings-partial'], 'post_load_sqls' => [ // Optional: SQL commands to run after ALL plans in group are loaded 'ANALYZE TABLE users', ], ], ], ``` Then operate on all plans in the group: ``` # System automatically detects "daily" is a plan group php artisan mysql-snapshots:create daily php artisan mysql-snapshots:load daily ``` ### Progress Indicators [](#progress-indicators) Large snapshot downloads automatically display progress bars with download speed and percentage: ``` Loading mysql-snapshot-daily-20250115.gz... 125 MB/250 MB [โ–“โ–“โ–“โ–“โ–“โ–“โ–“โ–“โ–“โ–“โ–“โ–“โ–“โ–“โ–‘โ–‘โ–‘โ–‘โ–‘โ–‘โ–‘โ–‘โ–‘โ–‘โ–‘โ–‘] 50% 5.2 MB/s ``` ### MariaDB Support [](#mariadb-support) If you're using MariaDB instead of MySQL, you'll need to adjust your `mysqldump_options` since MariaDB's `mysqldump` doesn't support certain MySQL-specific flags. **MySQL 8.0+ recommended options:** ``` 'mysqldump_options' => '--single-transaction --no-tablespaces --set-gtid-purged=OFF --column-statistics=0', ``` **MariaDB recommended options:** ``` 'mysqldump_options' => '--single-transaction --no-tablespaces', ``` Use Cases & Examples ------------------------ [](#use-cases--examples) ### Use Case 1: Simple Daily Production Sync [](#use-case-1-simple-daily-production-sync) **Scenario:** Sync production database to local development daily. ``` 'plans' => [ 'daily' => [ 'connection' => null, 'file_template' => 'mysql-snapshot-daily-{date:Ymd}', 'mysqldump_options' => '--single-transaction --no-tablespaces --set-gtid-purged=OFF --column-statistics=0', 'schema_only_tables' => ['failed_jobs'], 'keep_last' => 7, 'environment_locks' => [ 'create' => 'production', 'load' => 'local', ], 'post_load_sqls' => [ 'UPDATE users SET email = CONCAT("user+", id, "@test.local")', ], ], ], ``` **Workflow:** ``` # On production (automated via cron) php artisan mysql-snapshots:create daily --cleanup # On local php artisan mysql-snapshots:load daily --cached ``` ### Use Case 2: Split Large Database [](#use-case-2-split-large-database) **Scenario:** Production database is too large. Split into base data and a filtered subset of large table. ``` 'plan_groups' => [ 'daily' => [ 'plans' => ['daily-base', 'daily-transactions-partial'], ], ], 'plans' => [ 'daily-base' => [ 'connection' => null, 'file_template' => 'mysql-snapshot-daily-base-{date:Ymd}', 'mysqldump_options' => '--single-transaction --no-tablespaces --set-gtid-purged=OFF --column-statistics=0 --skip-lock-tables', 'ignore_tables' => ['transactions'], // Exclude large table 'keep_last' => 1, 'environment_locks' => [ 'create' => 'production', 'load' => 'local', ], ], 'daily-transactions-partial' => [ 'connection' => null, 'file_template' => 'mysql-snapshot-daily-transactions-partial-{date:Ymd}', 'mysqldump_options' => '--single-transaction --no-tablespaces --set-gtid-purged=OFF --column-statistics=0 --skip-lock-tables --where="created_at >= \'2025-01-01\'"', 'tables' => ['transactions'], // Only this table 'keep_last' => 1, 'environment_locks' => [ 'create' => 'production', 'load' => 'local', ], ], ], ``` **Workflow:** ``` # On production (automated) php artisan mysql-snapshots:create daily # Creates both plans # On local (system auto-detects plan group and loads both) php artisan mysql-snapshots:load daily --cached ``` ### Use Case 3: Multiple Environments with Different Data [](#use-case-3-multiple-environments-with-different-data) **Scenario:** Maintain separate snapshots for staging and production, with environment-specific post-load adjustments. ``` 'plans' => [ 'production-daily' => [ 'connection' => null, 'file_template' => 'mysql-snapshot-production-{date:Ymd}', 'mysqldump_options' => '--single-transaction --no-tablespaces', 'keep_last' => 7, 'environment_locks' => [ 'create' => 'production', 'load' => ['local', 'testing'], ], 'post_load_sqls' => [ 'UPDATE settings SET app_env = "local"', 'UPDATE users SET email = CONCAT("user+", id, "@test.local") WHERE role != "admin"', 'TRUNCATE TABLE sessions', ], ], 'staging-daily' => [ 'connection' => null, 'file_template' => 'mysql-snapshot-staging-{date:Ymd}', 'mysqldump_options' => '--single-transaction --no-tablespaces', 'keep_last' => 3, 'environment_locks' => [ 'create' => 'staging', 'load' => ['local', 'testing'], ], ], ], ``` ### Use Case 4: Testing with Specific Fixtures [](#use-case-4-testing-with-specific-fixtures) **Scenario:** Create specialized snapshots for different test scenarios. ``` 'plans' => [ 'test-base' => [ 'connection' => 'testing', 'file_template' => 'test-base-{date:Ymd}', 'mysqldump_options' => '--single-transaction', 'keep_last' => 1, 'environment_locks' => [ 'create' => 'local', 'load' => ['local', 'testing'], ], ], 'test-with-orders' => [ 'connection' => 'testing', 'file_template' => 'test-orders-{date:Ymd}', 'tables' => ['users', 'orders', 'order_items', 'products'], 'keep_last' => 1, 'environment_locks' => [ 'create' => 'local', 'load' => ['local', 'testing'], ], ], ], ``` ### Use Case 5: Optimized Performance Snapshots [](#use-case-5-optimized-performance-snapshots) **Scenario:** Large database with optimizations for faster dumps and loads. ``` 'plans' => [ 'daily-full' => [ 'connection' => null, 'file_template' => 'mysql-snapshot-daily-{date:Ymd}', 'mysqldump_options' => '--single-transaction --no-tablespaces --set-gtid-purged=OFF --column-statistics=0', 'schema_only_tables' => ['failed_jobs', 'telescope_entries', 'activity_log'], 'ignore_tables' => ['sessions', 'cache'], 'keep_last' => 1, 'environment_locks' => [ 'create' => 'production', 'load' => 'local', ], 'post_load_sqls' => [ 'ANALYZE TABLE users', 'ANALYZE TABLE orders', 'ANALYZE TABLE products', ], ], ], 'cache_by_default' => true, // Enable smart caching ``` **Workflow:** ``` # First load (downloads from cloud) php artisan mysql-snapshots:load daily-full --cached # Subsequent loads (uses cached copy, very fast) php artisan mysql-snapshots:load daily-full --cached # When new snapshot available (automatically detects and refreshes) php artisan mysql-snapshots:load daily-full --cached ``` Real-World Configuration Example -------------------------------- [](#real-world-configuration-example) Here's a complete configuration from a production application with a large database: ```