Sunday, November 29, 2009

Poor man's schema comparison


poor man

Comparing database schemas is one of those DBA tasks that occur all the time.
If you can afford the luxury of having a GUI, then MySQL Workbench is the tool for you. It will help you compare structures and eventually synchronizing the differences.
If the only thing at your disposal is a command line interface, without the possibility of installing anything (a common occurrence when you are consulting), then this Poor man's schema comparison tool may come handy.

It's a Perl script that doesn't require module installations beyond the most common ones, and produces a simple output, fit for further examination with the tools that you have at your disposal when working at the command line.
By default, it invokes vimdiff, to let you know visually what the differences are. If you change the command to diff, you get a simple output that can tell you at a glance what differences you are dealing with.
For example, after executing
schema_diff mysql://root:mysql@127.0.0.1:3306/crosstab \
mysql://msandbox:msandbox@127.0.0.1:5139/crosstab
I got the difference (a column that I introduced in the first database but not in the second one) clearly visible on screen.
vimdiff
I will be talking about this and other resources for the DBA during my session on schema maintenance at CodeBits in Lisbon (December 3-5, 2009).

1 comment:

Log Buffer said...

"[...]Giuseppe Maxia writes, Comparing database schemas is one of those DBA tasks that occur all the time.'[...]"

Log Buffer #171

Vote on Planet MySQL