mysql database diff tool

It is almost an everyday task for developers to ensure the database schema are in sync in their test/development/production databases. No doubt that everyone who works with MySQL servers will encounter situations where schema should be synchronized but isn’t. When this happens, there must be a reliable solution where it should work in any intricate situation.

Challenge:

You have made numerous MySQL schema changes to your databases during development of a new feature. Now you want to sync development and production schemas so that they match when you push a new release. DBAs often spend significant time creating complex synchronization scripts, even though the update itself might be a simple one. You need a solution that will automatically compare and identify the object differences between two MySQL database schemas and then synchronize them.

1. Manual

a) If you are working with small databases running mysqldump on both databases with the –no-data option to generate SQL scripts, then running file comparison tool(Example: Araxis merge) on the SQL scripts works pretty well.

mysqldump -h localhost -u root -p -no-data dbname1>file1 mysqldump -h localhost -u root -p -no-data dbname2>file2

But this becomes very tedious to carry out this process for large databases.

b) Always record those changes and finally create a hand-crafted SQL file with ALTER TABLE. This is good if you are changing only few database schemas, but risky because it is not done automatically.

2. Command line tools

These command line tools can compare the schema of two databases:
a) – a MySQL Schema Versioning and Migration Utility.
Syntax:

schemasync [options]

This actually generates two scripts. One is Patch script- SQL to migrate the schema of a source DB to a target DB. And, the other one is Revert script- SQL to undo changes after you apply them. This product is good as it produces proper patch and revert SQL scripts. But it requires python to be installed in advance.

b) – tool to compare MySQL database structures.
Syntax:

mysql-diff

The file name will point to the SQL scripts. It can compare two SQL scripts and can also connect to MySQL servers through jdbc, compare and generate the changes. mysql-diff doesn’t support Views/Functions/Stored procedures/Triggers comparison. It requires JDK to run.

If you want to a color-coded side-by-side comparison which makes it easy to pinpoint at a glance, similarities or differences between two databases then SQLyog is what you are looking for.

Select the databases you want to compare and press the compare button. That is all you need to do. Once your database schema are compared, you can view the object differences and generate only necessary synchronization script to update the destination database.

You can select which object or the number of objects you want to sync and press the button ‘Execute All’ to apply the sync changes. There is also an option to ‘Save to file’, with which you can sync at a later point in time.

Advantages of using Schema synchronization:

• Side-by-side comparison which makes it easy to locate the differences.
• Filter to eliminate comparing all database objects.
• Useful before doing SQLyog’s Database synchronization(To sync only the data, schema must be same).
• Visually represent the differences between MySQL databases with details and modification scripts for the different objects.
• Synchronization of columns, indexes, constraints, views, stored procedures and functions.

• SQL database tools
• Database diff tool
• MySQL database admin tool