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 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 ./mysqldiff.pl --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.