Compare two MySQL databases [closed]

Question

I'm currently developing an application using a MySQL database.

The database-structure is still in flux and changes while development progresses (I change my local copy, leaving the one on the test-server alone).

Is there a way to compare the two instances of the database to see if there were any changes?

While currently simply discarding the previous test server database is fine, as testing starts entering test data it could get a bit tricky.
The same though more so will happen again later in production...

Is there an easy way to incrementally make changes to the production database, preferably by automatically creating a script to modify it?

---

Tools mentioned in the answers:

• Red-Gate's MySQL Schema & Data Compare (Commercial)
• Maatkit (now Percona)
• liquibase
• Toad
• Nob Hill Database Compare (Commercial)
• MySQL Diff
• SQL EDT (Commercial)

Answer 1

check: http://schemasync.org/ the schemasync tool works for me, it is a command line tool works easily in linux command line

Answer 2

I use a piece of software called Navicat to :

• Sync Live databases to my test databases.
• Show differences between the two databases.

It costs money, it's windows and mac only, and it's got a whacky UI, but I like it.

Answer 3

For the first part of the question, I just do a dump of both and diff them. Not sure about mysql, but postgres pg_dump has a command to just dump the schema without the table contents, so you can see if you've changed the schema any.

Answer 4

Toad for MySQL has data and schema compare features, and I believe it will even create a synchronization script. Best of all, it's freeware.

Answer 5

dbSolo, it is paid but this feature might be the one you are looking for http://www.dbsolo.com/help/compare.html

It works with Oracle, Microsoft SQL Server, Sybase, DB2, Solid, PostgreSQL, H2 and MySQL

Answer 6

For myself, I'd start with dumping both databases and diffing the dumps, but if you want automatically generated merge scripts, you're going to want to get a real tool.

A simple Google search turned up the following tools:

• MySQL Workbench, available in Community (OSS) and Commercial variants.
• Nob Hill database compare, available for free for MySQL.
• A listing of other SQL comparison tools.