Filip Tepper

What happens when you have to compare structures of two databases? Well, first thing - with all the great tools around (watch and learn: ActiveRecord’s Migration) you shouldn’t have let this happen. But since you’re reading this you’re probably already facing this problem.

We have a couple of options: Windows users could try SQLyog MySQL GUI - Enterprise Edition, PHP folks have MySQLdiff, but there’s another thing you might want to try. It’s my favourite since CocoaMySQL and command line client suit me well for every day work and I don’t fell like spending $50 on a piece software that I might (or not) use once a year. I present to you mysqldiff (what a fancy name)!

I’m using OS X 10.5.1 with default Perl installation, so first I had to install the prerequisites - Class-MakeMethods. Download the package and from the command line execute:

tar xzf Class-MakeMethods-*.tar.gz
cd Class-MakeMethods-*
perl Makefile.PL
make test
sudo make install

Now on to the mysqldiff. After downloading we need need to execute the same set of commands:

tar xzf MySQL-Diff-*.tar.gz
cd Class-MakeMethods-*
perl Makefile.PL
make
sudo make install

And you’re done - by default mysqldiff is installed to /Library/Perl/5.8.8/MySQL/mysqldiff.pl.

An example:

mysqldiff.pl --host=host.com --user=user --password=password database_1 database_2

This command will list all the queries required to recreate the exact structure of all the tables of the database_2 in database_1. You can also run mysqldiff.pl with --apply switch, but I always like to check the script before executing it.

That’s it - if you know a better tool - be sure to let me and others know in the comments to this post.