How to compare two databases, identify the diferences and generate the necessary SQL to update the structure of one of them

Tool: DBDiff (https://github.com/DBDiff/DBDiff)

Note: It works on Linux or Windows

What do you need

• PHP, GIT and Composer on PATH
• Credentials to access the source and target databases

Quick start

• Download and enter the DBDiff directory

$ git clone https://github.com/DBDiff/DBDiff.git
$ cd DBDiff
$ composer update

• Create a .dbdiff file in DBDiff directory with the connection settings for 2 servers. Add to "tablesToIgnore" a list of tables and views that should be ignored on comparisson.

.dbdiff file:

server1:
    user: root
    password:
    port: 3306
    host: localhost
server2:
    user: AzureDiamond
    password: hunter2
    port: 3306
    host: 192.168.1.1
template: templates/db-migrate.tmpl
type: schema
include: up
nocomments: true
tablesToIgnore:
- view1
- view2
- table5

• Create a db-migrate.tmpl file in templates directory with the content:

-- UP

{{ $up }}

-- END UP

• Execute the command below to generate the database diff file migration.sql

$ php dbdiff server1.yourdatabase:server2.yourdatabase

• Run the commands that are inside the migration.sql using a database client of your preference on server2.yourdatabase to upgrade your database. I recommend review the commands before running them.