Easy way to compare MySQL Databases

compare MySQL Databases

Experience:
We started working on a project that already had live and production environment setup. The live version was using Master branch and production was using develop branch. There were many features in production which was yet to be tested and move to live. We worked on adding many features to production and got it ready to move to live.

Before moving it to live we prepared the complete log of all code base changes and database changes. Updating code base was very simple as GitHub was used and simply needed merge from develop to master branch. The main concern came with database changes. In fact if migrations would have been used then everything would have been straightforward. Without migration we need to manually update all database changes. So we searched for a tool which can give us the different between live and production databases. And Thanks to MySQL community, they already have mysqldbcompare utility ready for us.

mysqldbcompare:
mysqldbcompare is a Free command-line Utility tool which compares the objects and data from two databases to find differences. It is very easy to install and use this tool. You may already have it installed in your system, simply type ‘mysqldbcompare’ in command line to check if it is installed. If not installed, follow the steps mentioned in the official website for installing in different OS.

It has many options to exclude different checks performed while finding the difference,

• Check database definitions,
• Check existence of objects in both databases,
• Compare object definitions and many more.

And the good part about this tool is, it also has options to show differences in different formats. So you can choose SQL format, for example, to get difference between them in the form of SQL queries or a CSV format to get difference in CSV format.

Run the command:

and get a diff.txt file having the difference between the database in SQL format. But, there is a Catch, it will also list all the difference between indexes, so have a look at diff.txt, filter it manually and get the exact SQL needed to be run on live environment.

So, I too ran the command, and got the SQL needed to execute in live environment.

Checkout the official documentation to explore more about options present in this tool.

And yes, don’t forget to checkout other MySQL utilities which can help in many different ways in time.

Feel free to share if you have much easier way to do this.

Thank you 🙂