Database sync or migration tool [closed]

Question

It is really a pain keeping production and development databases in sync manually.

Is there any tool that allows one to keep the two databases in sync? Something like the amazing Laravel framework's migrations thing?

I am using MySQL and PHP. I searched here and there but wasn't able to spot the right tool for the job.

Obliging

Have a look at Liquibase or Flyway. They use Java in the background but are simple commandline tools to get the job done. – Melgar

Answer 1

You should definitely have a look at phinx. Here's an example of a complex database migration step I performed using MySQL and PHP: https://coderwall.com/p/v3qg2q

Massarelli

Answer 2

If I am not wrong you need a tool to create database version control to keep track of schema changes. You may use Cygnite Migration commands. It is simple to use and make your job easier.

For your reference-

Migration & Seeding Video Tutorial

Database migration tool

Ingaborg

Answer 3

Check out Schematic, It's designed to work with schema files in YAML or JSON, and it can be installed globally:

http://andrefigueira.github.io/Schematic/

And it's designed to be committed with your VCS so your database versions are stored with your project commits.

Duley

Answer 4

It's not clear exactly what you want ...

• one way from prod to dev?
• data or ddl or both?

take a look at this thread: How to synchronize development and production database

Varner

Sync means keeping both in sync :) I mean both way – Obliging

in more casual usage, "sync" can be taken to mean mirroring ... as in one-way sync. you still have not answered: ddl or data or both ? i would assume ddl ... if so, see the link provided. – Varner

Answer 5

Try to use rubyrep http://www.rubyrep.org/. It can continuously replicate between two databases.

Cupping

Answer 6

1 . make your remote or production database accept remote connection using a wild card or special Ip address ! through some sort of cpanel or config file !

2 . you can extend the db artisan command like "db:sync".

3 . command code (not tested yet) :

$db_local = Config::get('database.'.env('DB_CONNECTION', 'db_local')); 
$dump = "tmp.db";
exec("mysqldump --user={$db_local['username']} --password='{$db_local['password']}' --host={$db_local['host']} {$db_local['database']} --ignore-table={$db['database']}.some_table > $dump");
$db_remote = Config::get('database.'.env('DB_CONNECTION', 'db_remote')); 
exec("mysql --user={$db_remote['username']} --password='{$db_remote['password']}' --host={$db_remote['host']} {$db_remote['database']} < $dump");

I did no add any function check as usually it's intended to work.

You can for example instead of a command, add some automation using events, cron job, listeners ... there's many options, but the main logic part trick is : Defining environment variable names for your 2 db connections, and in the app config database.php, define your connections credentials etc, and finally make use of the exec, mysqldump & mysql.

Good luck

Romaine

Answer 7

If you just want the two DBs to be identical, why not use MySQL replication with your production as the master and the dev server as the slave.

MySQL Replication Docs

Degression

Any updates made on the slave are going to confuse the hell out of and/or break replication. Read-only slaves don't make good dev databases. – Pines

Yeah agreed, guess I didn't think too deep into what exactly he needs the dev DB to be doing. – Degression