It is almost an everyday task for developers to ensure the 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. When this happens, there must be a reliable solution where it should work in any complex 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 schema and then synchronize them.

Solution: 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(Ex: 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) Schema Sync- a MySQL Schema Versioning and Migration Utility.
Syntax:

schemasync [options] <source> <target>

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) mysql-diff- tool to compare MySQL database structures.
Syntax:

mysql-diff <from_file|from_jdbc_url> <to_file|to_jdbc_url>

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.

3. Using a GUI tool: SQLyog

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.

Using SQLyog is well suited for large databases because SQLyog hardly takes 1-2 minutes to compare ~2000 tables.

Advantages of using Schema synchronization:

• Side-by-side comparison which makes it easy to find 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.

Conclusion:

There are several ways to compare your database schema and sync the changes. Doing it manually or using command line would not be easy to pinpoint all the differences easily and quickly. It involves risk and is also complicated. But SQLyog provides an efficient and simple way of comparing the schema visually and lets you execute only the objects you want to be synchronized.
If you found SQLyog interesting, here is a blog post: All you wanted to know about SQLyog’s Schema Comparison tool
So, How do you sync your database schema?

Tweet