PHPackages amitdugar/db-tools - 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. amitdugar/db-tools ActiveLibrary[Database & ORM](/categories/database) amitdugar/db-tools ================== Database backup/restore/export/verify/pitr/collation console toolkit 2.0.1(2mo ago)0165↓50%MITPHPPHP ^8.2CI passing Since Dec 12Pushed 2mo agoCompare [ Source](https://github.com/amitdugar/db-tools)[ Packagist](https://packagist.org/packages/amitdugar/db-tools)[ Docs](https://github.com/amitdugar/db-tools)[ RSS](/packages/amitdugar-db-tools/feed)WikiDiscussions master Synced 1mo ago READMEChangelogDependencies (5)Versions (21)Used By (0) db-tools ======== [](#db-tools) MySQL/MariaDB backup, restore, and recovery toolkit. Installation ------------ [](#installation) ``` composer require amitdugar/db-tools ``` Quick Start ----------- [](#quick-start) ``` # See all available commands vendor/bin/db-tools # Get help for any command vendor/bin/db-tools backup --help vendor/bin/db-tools collation --help ``` Setup ----- [](#setup) ### If your project already has a `.env` file (Laravel, Symfony, etc.) [](#if-your-project-already-has-a-env-file-laravel-symfony-etc) **You're done!** db-tools automatically reads `DB_*` variables from your `.env` file. ``` # Just works - no additional config needed vendor/bin/db-tools backup --output-dir=/backups ``` If your `.env` doesn't have database variables yet, run setup: ``` vendor/bin/db-tools setup ``` This will prompt for credentials and add them to your `.env` file. --- ### If you're starting fresh [](#if-youre-starting-fresh) ``` vendor/bin/db-tools setup ``` Choose where to save config: - **`.env` file** (recommended) - adds `DB_HOST`, `DB_DATABASE`, etc. - **Profile file** - saves to `~/.config/db-tools/profiles.php` --- ### Verify your setup [](#verify-your-setup) ``` # Test database connectivity vendor/bin/db-tools db:test # Show current configuration vendor/bin/db-tools config:show --validate ``` Commands -------- [](#commands) ### Backup & Restore [](#backup--restore) ``` # Create compressed backup (zstd/pigz/gzip) vendor/bin/db-tools backup --output-dir=/backups vendor/bin/db-tools backup --output-dir=/backups --note=before-deploy vendor/bin/db-tools backup --output-dir=/backups --encrypt # GPG encrypted vendor/bin/db-tools backup --output-dir=/backups --compression=zstd # specific compression # Restore from backup vendor/bin/db-tools restore /backups/mydb-20240501-0100.sql.zst vendor/bin/db-tools restore /backups/mydb.sql.zst.gpg # auto-decrypts # Import SQL file (supports .sql, .gz, .zst, .zip, .gpg) vendor/bin/db-tools import /path/to/dump.sql.zst vendor/bin/db-tools import /path/to/dump.sql.gpg -e secretpassword # Verify archive integrity vendor/bin/db-tools verify /backups/mydb-20240501-0100.sql.zst # Export plain SQL (no compression) vendor/bin/db-tools export mydb /backups/mydb.sql ``` ### Backup Management [](#backup-management) ``` # List backup files in directory vendor/bin/db-tools show --output-dir=/backups # Show database/table sizes vendor/bin/db-tools size # all databases vendor/bin/db-tools size mydb # specific database vendor/bin/db-tools size mydb --tables # show table breakdown # Clean old backups (keep last N) vendor/bin/db-tools clean --output-dir=/backups --retention=7 # Clean backups older than N days vendor/bin/db-tools clean --output-dir=/backups --days=30 ``` ### Database Maintenance [](#database-maintenance) ``` # Fix collation (convert to utf8mb4) vendor/bin/db-tools collation mydb # convert all tables vendor/bin/db-tools collation mydb --dry-run # preview changes vendor/bin/db-tools collation mydb --table=users # specific table only vendor/bin/db-tools collation mydb --skip-columns # tables only, skip columns vendor/bin/db-tools collation mydb --collation=utf8mb4_unicode_ci # specific collation # Run mysqlcheck operations vendor/bin/db-tools mysqlcheck mydb # check tables vendor/bin/db-tools mysqlcheck mydb --analyze # update index statistics vendor/bin/db-tools mysqlcheck mydb --optimize # optimize tables vendor/bin/db-tools mysqlcheck mydb --repair # repair tables # Test database connectivity vendor/bin/db-tools db:test ``` ### Point-in-Time Recovery [](#point-in-time-recovery) ``` # View available recovery points vendor/bin/db-tools pitr-info --meta=/backups/mydb.meta.json # Restore to specific point in time vendor/bin/db-tools pitr-restore --meta=/backups/mydb.meta.json --to="2024-05-10 12:00:00" # Purge old binary logs vendor/bin/db-tools purge-binlogs --before="2024-05-01" ``` ### Configuration [](#configuration) ``` # List available profiles vendor/bin/db-tools config:list # Show current configuration vendor/bin/db-tools config:show vendor/bin/db-tools config:show --validate # test connection # Interactive setup vendor/bin/db-tools setup # Non-interactive setup (for deploy scripts) vendor/bin/db-tools setup --no-prompt --database=mydb --user=root --password=secret vendor/bin/db-tools setup --no-prompt --database=mydb --user=root -o config # output to db-tools.php ``` Profiles (Multiple Databases) ----------------------------- [](#profiles-multiple-databases) If you manage multiple databases, use **profiles**. Each profile is a named database configuration that you can switch between. ### When to use profiles [](#when-to-use-profiles) - You have separate databases (e.g., main app + analytics) - You need different backup settings per database - You want to manage multiple projects from one machine ### Quick setup with profiles [](#quick-setup-with-profiles) ``` # Add your main database (uses "default" profile) vendor/bin/db-tools setup --no-prompt --database=myapp --user=root --password=secret # Add an analytics database as a separate profile vendor/bin/db-tools setup --no-prompt --profile=analytics --database=analytics_db --user=root --password=secret # Add a legacy database vendor/bin/db-tools setup --no-prompt --profile=legacy --database=old_app --user=root --password=secret ``` ### Using profiles [](#using-profiles) ``` # Commands use "default" profile by default vendor/bin/db-tools backup --output-dir=/backups # Specify a profile with --profile vendor/bin/db-tools backup --profile=analytics --output-dir=/backups vendor/bin/db-tools restore --profile=legacy /backups/old_app.sql.zst vendor/bin/db-tools size --profile=analytics # List all configured profiles vendor/bin/db-tools config:list ``` ### How profiles are stored [](#how-profiles-are-stored) **In `.env` file** (default) - profiles use prefixed variable names: ``` # Default profile DB_HOST=localhost DB_DATABASE=myapp DB_USERNAME=root DB_PASSWORD=secret # Analytics profile (note the _ANALYTICS_ prefix) DB_ANALYTICS_HOST=localhost DB_ANALYTICS_DATABASE=analytics_db DB_ANALYTICS_USERNAME=root DB_ANALYTICS_PASSWORD=secret ``` **In `db-tools.php`** - profiles are array keys: ```