DEV Community

Lawrence Lagerlof
Lawrence Lagerlof

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • Create a db-migrate.tmpl file in templates directory with the content:
-- UP

{{ $up }}

-- END UP

Enter fullscreen mode Exit fullscreen mode
  • Execute the command below to generate the database diff file migration.sql
$ php dbdiff server1.yourdatabase:server2.yourdatabase
Enter fullscreen mode Exit fullscreen mode
  • 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.

Discussion (1)