Sync structure of 2 mySQL databases

Maintaining apps with database backends often involves creating new tables or extending new fields in your existing tables. I have always kept a record of all CREATE and ALTER TABLE etc statements to apply them as a whole when going live with new features.

I found a useful tool to do a diff of the mysql structure of 2 databases (before and after)


mysqldiff requires Perl 5.

Install file says:

MySQL-Diff-0.30# perl Makefile.PL
MySQL-Diff-0.30# make
MySQL-Diff-0.30# make test
MySQL-Diff-0.30# make install

Which did not work since I’m not a Perl-guy and have nothing perl related installed on my boxes…

So you also need to install another Perl module using the CPAN shell interface:

MySQL-Diff-0.30# perl -MCPAN -e 'install Class::MakeMethods::Template'

Afterwards you have the ‘mysqldiff’-command ready to be used.

I use it this way having both databases to be compared on my dev-box:

MySQL-Diff-0.30# perl ./ --user=root --password=xxx before_dbname after_dbname > my_diff.sql

Be careful to generate the diff in the right direction.

You can then use the SQL-statements in my_diff.sql to update your database ‘before_dbname’. If not needed, delete unnecessary statements like AUTO_INCREMENTS etc manually.