Compare two MySQL databases

Question

I have a database in MySQL that I am currently developing. I have a copy of this database on my development machine which I modify as fast as I develop and a copy on a test server. My question is:

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

Its not a real problem to simply re-deploy the database on the test server but as the end user's start entering test data it could be a bit tricky. A related question is also relevant a little later on in production...

Is there an easy way to incrementally make changes to the production database? That is, suppose I add one new table and modified one existing table, are there tools that will detect the changes and generate scripts that will safely perform the updates on the production copy of the database?

EDIT: Tools mentioned in the answers:

• Maatkit.
• red-gate's SQL Compare (Commercial)
• liquibase
• Toad
• Nob Hill Database Compare
• MySQL Diff

Answer 1

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 2

There is a useful tool written using perl called Maatkit. It has several database comparison and syncing tools among other things.

Answer 3

Have a look at http://www.liquibase.org/

Answer 4

Toad for MySql has data and schema compare features, and I believe it will even create a synchronization script.

Answer 5

From the feature comparison list... MySQL Workbench offers Schema Diff and Schema Synchronization in their community edition.

Answer 6

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 7

SQL Compare by RedGate http://www.red-gate.com/products/SQL_Compare/index.htm

DBDeploy to help with database change management in an automated fashion http://dbdeploy.com/

Answer 8

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.

Answer 9

If you only need to compare schemas (not data), and have access to Perl, mysqldiff might work. I've used it because it lets you compare local databases to remote databases (via SSH), so you don't need to bother dumping any data.

http://adamspiers.org/computing/mysqldiff/

It will attempt to generate SQL queries to synchronize two databases, but I don't trust it (or any tool, actually). As far as I know, there's no 100% reliable way to reverse-engineer the changes needed to convert one database schema to another, especially when multiple changes have been made.

For example, if you change only a column's type, an automated tool can easily guess how to recreate that. But if you also move the column, rename it, and add or remove other columns, the best any software package can do is guess at what probably happened. And you may end up losing data.

I'd suggest keeping track of any schema changes you make to the development server, then running those statements by hand on the live server (or rolling them into an upgrade script). It's more tedious, but it'll keep your data safe. And by the time you start allowing end users access to your site, are you really going to be making constant heavy database changes?

Answer 10

Hi guys. I'm working with Nob Hill's Marketing team, I wanted to tell you I'll be happy to hear your questions, suggestion or anything else, please feel free to contact me.

We originally decided to create our tool from scratch because while there are other such products on the market, none of them do the job right. It’s quite easy to show you the differences between databases. It’s quite another to actually make one database like the other. Smooth migration, both of schema and data, has always been a challenge. Well, we have achieved it here.
We are so confident that it could provide you a smooth migration, than if it doesn’t – if the migration scripts it generates are not readable enough or won’t work for you, and we can’t fix it in five business days – you will get your own free copy!

http://www.nobhillsoft.com/NHDBCompare.aspx

Answer 11

There is another open source command-line mysql-diff tool:

http://bitbucket.org/stepancheg/mysql-diff/

Answer 12

Zidsoft CompareData is cross-DBMS and works with MySQL and will let you compare and synchronize MySQL data, but for metadata you have to generate sync script yourself.

Answer 13

Take a look at dbForge Data Compare for MySQL. It's a shareware with 30-days free trial period. It's a fast MySQL GUI tool for data comparison and synchronization, management of data differences, and customizable synchronization.

Answer 14

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 15

There is a new Open Source project that should be able to handle all of your database comparison requirements, both data and schema. It will not however do the incremental changes for you-- it's read only.

http://www.diffkit.org