How to compare MySQL database schemas

Question

I am looking for a tool that will allow me to compare schemas of MySQL databases.
Wich is the best tool to do that?

Answer 1

The best thing to do is to try out some performance benchmarks that are already out there. It's always better to use tried-and-tested benchmarks, unless you're thoroughly convinced that your data and database loading is going to be significantly different to the traditional usage patterns (but, then, what are you using a database for?). I'm going to steal my own answer from ServerFault:

There are a good number of benchmarks out there for different MySQL database engines. There's a decent one comparing MyISAM, InnoDB and Falcon on the Percona MySQL Performance Blog, see here.

Another thing to consider between the two aforementioned engines (MyISAM and InnoDB) are their approaches to locking. MyISAM performs table-locking, whilst InnoDB performs row-locking. There are a variety of things to consider, not only downright performance figures.

Answer 2

Navicat is able to do that for you. It will also synchronize schema and/or data between two mysql database instances. I've used it with success in the past.

http://www.navicat.com/html/en/products/navicat_mysql/mysql_overview.html

There is a screenshot of the data and structure synchronization tool here:

http://www.navicat.com/html/products/navicat_mysql/mysql_detail_mac.html#6

Answer 3

I use SqlYog:

http://www.webyog.com/en/

It isn't free but is a very good tool and has saved the cost of it's license many many times over. I'm in no way affiliated with the company, just someone who has used a number of MySQL tools.

Answer 4

Perhaps a bit late to the party, but I've just written a simple tool in PHP to compare MySQL database schemas:

PHP script to compare MySQL database schemas

It exports the schema and serialises it before doing the comparison. This is so that databases can be compared that reside on different hosts (where both hosts may not be accessible by the PHP script).

Answer 5

I know its an old post.. but theres a new third party tool for Schema and Data compare for MySQL, its called Nob Hill Compare.