dcblogdev / laravel-db-sync Goto Github PK

View Code? Open in Web Editor NEW

297.0 3.0 25.0 55 KB

Sync a remote DB to a local DB

License: Other

PHP 100.00%

laravel-db-sync's Introduction

Community

There is a Discord community. https://discord.gg/VYau8hgwrm For quick help, ask questions in the appropriate channel.

Laravel DB Sync

Introduction

Sync remote database to a local database

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.

Connection can be made over SSH or using a remote MySQL connection.

Install

Install the package.

composer require dcblogdev/laravel-db-sync

Config

You can publish the config file with:

php artisan vendor:publish --provider="Dcblogdev\DbSync\DbSyncServiceProvider" --tag="config"

.env

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_PORT=3306
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_PORT=3306
REMOTE_DATABASE_NAME=
REMOTE_DATABASE_PASSWORD=
REMOTE_DATABASE_IGNORE_TABLES=''

REMOTE_REMOVE_FILE_AFTER_IMPORT=true
REMOTE_IMPORT_FILE=true

Set a comma seperate list of tables NOT to export in REMOTE_DATABASE_IGNORE_TABLES

To generate a SQL with a custom file name REMOTE_DEFAULT_FILE_NAME

To specify a different local database connection:

LOCAL_TARGET_CONNECTION=different_mysql_connection

Set the mysql command path:

LOCAL_MYSQL_PATH=/usr/bin/mysql

For only mysqldump:

REMOTE_MYSQLDUMP_SKIP_TZ_UTC=true

Usage

To export a remote database to OVERRIDE your local database by running:

php artisan db:production-sync

Provide a filename for export on the fly by passing the option --filename, remember to provide .sql

php artisan db:production-sync --filename=other.sql

Run the command without attempting to export:

php artisan db:production-sync --test

Aliases

There are shortcuts that can be used:

-T = will use --test F = will use --filename

Alternative name

When connecting to a none production database, say a staging database you can choose to use this alternative name:

db:remote-sync may be used instead of db:production-sync

laravel-db-sync's People

Contributors

Stargazers

Watchers

laravel-db-sync's Issues

Getting Allowed memory

Hello,

I have a big DB, when I try the sync I get error something like: Allowed memory size of 536870912 bytes exhausted (tried to allocate 477174084 bytes)

Syncing only specific tables?

Do you have any plan to add syncing of a few selected tables only, instead of a complete database?
Thanks

Connecting via SSH

First, congrats, I think that this package it's super useful! 🎉

While trying to make it work with my production database, I notice that I need to open and SSH server to connect to my production database because it's protected.

With something similar to this, it could be possible:

// Open a tunnel from remote host with remote user and bind it in localhost:3307
shell_exec("ssh -f -L 3307:127.0.0.1:3306 $remoteUser@$remoteHost sleep 60 >> logfile");

// Then connect to localhost 3307
exec("mysqldump -h 127.0.0.1 -P 3307 -u $username -p$password $database --column-statistics=0 $ignoreString > file.sql",
                $output);

What do you think?

Error handling

If there can be feedback when a connection cannot be established that would be a better experiance.

remote to remote

I have 2 scripts running on different servers I want to copy data from 1 to server 2 every 30 mins

On Laravel Vapor Application

@dcblogdev Have you ever tested the package for Laravel Vapor?

Thanks

It didn't work, but I don't know how to debug it

It didn't work — my local database was left unmodified.
Here's the console output:

$ php artisan db:production-sync
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump. 
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data. 

DB Synced

My dump ~5gb right now.

Thanks.