Compare two MySQL databases

Vincent_Ramdhanie_27439 0
Viewed 383867

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:

diff mysql database
21 Answers

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.

Anson_Smith_286850

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.

seanyboy_17260

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

user_27640

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 or migration). 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?

Zac_56300
user_88050
stepancheg_150180

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

Jarod_Elliott_10610
user_88030

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:

W_Craig_Trader_128950
user_11100690

I think Navicat for MySQL will be helpful for this case. It supports Data and Structure Synchronization for MySQL. enter image description here

Admin1

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.

Paul_Tomblin_33330

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

Admin1
Related