I often want to sync a production database into a local database and manually exporting and importing, so I wrote a package to automate it!
A word of warning you should only sync a remote database into a local database if you have permission to do so within your organisation's policies. I'm syncing during early phases of development where the data is largely test data and not actual customer data.
I wrote a package called Laravel DB Sync
Install the package:
composer require dcblogdev/laravel-db-sync
Publish the config file
php artisan vendor:publish --provider="Dcblogdev\DbSync\DbSyncServiceProvider" --tag="config"
Set the remote database credentials in your .env file
When using SSH Add:
REMOTE_USE_SSH=true
REMOTE_SSH_PORT=22
REMOTE_SSH_USERNAME=
REMOTE_DATABASE_HOST=
REMOTE_DATABASE_USERNAME=
REMOTE_DATABASE_NAME=
REMOTE_DATABASE_PASSWORD=
REMOTE_DATABASE_IGNORE_TABLES=''
REMOTE_REMOVE_FILE_AFTER_IMPORT=true
REMOTE_IMPORT_FILE=true
For only MySQL remote connections:
REMOTE_DATABASE_HOST=
REMOTE_DATABASE_USERNAME=
REMOTE_DATABASE_NAME=
REMOTE_DATABASE_PASSWORD=
REMOTE_DATABASE_IGNORE_TABLES=''
REMOTE_REMOVE_FILE_AFTER_IMPORT=true
REMOTE_IMPORT_FILE=true
if you want to exclude certain tables you can add them to REMOTE_DATABASE_IGNORE_TABLES for example to ignore users and jobs being exported
REMOTE_DATABASE_IGNORE_TABLES='users,jobs'
now when you want to export the remote database into the local database run:
php artisan db:production-sync