jueves, septiembre 03, 2009

MySQL schema diff compare - mysqldiff

I've had recently the problem of having to compare schemas between databases.
During summer, some releases to production branch that involve db modification went accumulating.

Since it's not possible to recreate the database due to the critical data stored on the db deployment, it was good to double-check that the databases shared the same schema.
Using JPA as I'm doing on that project, schema is defined on the java code itself, so you may go happily coding and it's possible that on rapid development, some schemas

As well, usage of different JPA implementations may involve some difference on decisions depending on the implementation. As of a shared project, that needs to be handled with care.

Been trying this tool to compare two mysql schemas: mysqldiff. It is a perl-based tool to run over some (remote) databases.

It has a nice feature which is the --table-re=REGEX where you can specify a regex that the table names have to satisfy to enter the comparison. In this case this suits perfectly: only want to compare less than 20 tables from a database with around 100.

Installing it is very easy. You'll need to install first the Class-MakeMethods prerequisite. Following the information in the INSTALL file is enough - supersimple.
Then do the same downloading mysqldiff.

Once you got it started, it goes (in my case, ubuntu hardy) to /usr/local/share/perl/5.8.8/MySQL/mysqldiff.pl. I have created a ln in /usr/bin/mysqldiff entry for niceness :)

An usage example. The options are self-explanatory, but would be nice to show how to use the --table-re option and how easy it is. By the way, I've also find that depending on the characters you use in your password, you better enclose it in single commas!


mysqldiff
-h1=localhost
-h2=localhost
--user1=user1
--user2=user2
--password2=mypass2
--password2=mypass2
--table-re="User|Product"
db:databasename1
db:databasename2
> output.log


The file output.log may show something like:



## mysqldiff 0.30
##
## run on Thu Sep 3 18:24:38 2009
##
## --- db: databasename1 (host=localhost user=user1)
## +++ db: databasename2 (host=localhost user=user2)

DROP TABLE `USER`;
ALTER TABLE `PRODUCT` ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1; # was ENGINE=MyISAM AUTO_INCREMENT=562 DEFAULT CHARSET=latin1



You can also --apply changes, which will patch database1 no match database2. It's NOT the default of course, and well, I like to check the output first to make sure.

PS. Long time with no tech posts...

1 comentario:

Anónimo dijo...

There is also an ability to compare data and schemas using dbForge Studio for MySQL and sync them after that.