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)

Impellent

I believe RedGate's tools are for SQL Server only. – Pfennig

Red Gate now has a MySQL version as well, currently free as it's in extended early access: red-gate.com/products/MySQL_Compare/index.htm – Subset

It is a real problem. I deploy from dev to production machine and it ALWAYS breaks something. Thank you for this informative post – Glowing

The MySQL tool from Redgate is now $70/user. Even at that price I'll evaluate and post comments here. – Indiraindirect

Also needed this just now, had to increase the size of a field. Didn't want to just increase it and suspect everything was okay. @Jared suggested exactly what I used. – Johnathan

Another Windows tool: Database Workbench, works on Linux via Wine and supports other database systems as well. This tool has a "Schema Compare" included, not free, but not expensive either. – Kharif

I like to use SQL Architect. Is has a nice compare feature. Here: sqlpower.ca/page/architect_download_os – Spies

A fantastic alternative for Windows, Linux or Mac is the mysqldbcompare tool developed by MySQL themselves. Please see my answer here for further information: stackoverflow.com/a/26192873/1365289 ! This took me a LONG time to find and it was worth the effort! – Twinberry

This one doesn't require a database connection, provides an API and a web interface: blog.geneticcoder.com/2015/11/12/… – Dol

Answer 1

If you're working with small databases I've found running mysqldump on both databases with the --skip-comments and --skip-extended-insert options to generate SQL scripts, then running diff on the SQL scripts works pretty well.

By skipping comments you avoid meaningless differences such as the time you ran the mysqldump command. By using the --skip-extended-insert command you ensure each row is inserted with its own insert statement. This eliminates the situation where a single new or modified record can cause a chain reaction in all future insert statements. Running with these options produces larger dumps with no comments so this is probably not something you want to do in production use but for development it should be fine. I've put examples of the commands I use below:

mysqldump --skip-comments --skip-extended-insert -u root -p dbName1>file1.sql
mysqldump --skip-comments --skip-extended-insert -u root -p dbName2>file2.sql
diff file1.sql file2.sql

Barrick

Double-plus upvotes for command-line literacy!!! – Jinni

To compare data, use this instead; still be some MySQL4+ comments about character sets, etc. mysqldump --opt --compact --skip-extended-insert -u user -p db table > file.sql – Eberta

-d, --no-data may be of interest to those needing production use but only care about the schema – Murrelet

A better tool to use would be the mysqldbcompare utility developed by MySQL themselves which you can use on Windows, Linux or Mac - it can also output SQL statements for both data AND schema changes and does a lot more tests than a simple command line diff could determine. – Twinberry

@jasdeepkhalsa The link is broken, may you please provide a new one? – Intellectualism

--all-databases is also useful if you wish to compare the entire server schema instead of a specific database. – Gronseth

For a nice diff with colors try vimdiff – Aryn

@PHPst Unfortunately, the entire question was removed for "moderation", but I'm in the process of writing my own open source diff tool so will post back details once I have them! – Twinberry

@PHPst Try out github.com/DBDiff/DBDiff – Twinberry

Brilliant answer! But yeah, -d is needed to skip data. – Voussoir

-iEwB could be useful if you don't care about whitespaces, tabs and case. – Penumbra

Answer 2

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.

Percussive

All the tools mentioned look good. I am selecting Toad arbitrarily for now until I could conduct some more research. – Impellent

MySQL Workbench just provides a report of the changes, doesn't generate the update script (or I haven't found how to do so) if you compare two scripts. It thens ask you for updating the database. In my case I'm only interested in the update script. – Opacity

Toad is very unhandy :( you cannot just compare two different sql dumps – Average

I got all excited about this tool until I realized that it runs in windows, not linux. Back to searching... – Waldo

Worked fantastic for me. Did everything I needed it to do and the highlighted cells for changed records made it easy to see what changed. – Despoliation

@Waldo et al - since the advent of ESXi, OS isn't a massive inhibition (who can't find an old 2000 or XP license that came with an old PC?) – Eberta

mysqldbcompare with --run-all-tests --difftype sql --disable-binary-logging options can do almost the same job(except the output is mixed with comment, and special characters in string are not escaped). – Faught

My only complaint about Toad is that is does not always generate ALTER commands in a smart way, instead it will rename a table, create a new table (with new columns), then move your data from the old to the new. This takes so much more time than a simple alter would – Dyestuff

@Anson Smith Can you tell me the alternative for linux? – Disraeli

This tool is Windows only, rendering it useless for me. I think the answer should mention this. – Brevier

it is now eclipse plugin, still this plugin is quite buggy. still better then windows. – Cellist

Today Toad is no longer freeware. It is free trail and you have to buy to use full function. – Ironlike

@jdias, Get a VM then, Settled. – Ronen

Answer 3

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.

Sandhog

It does runs on Linux. I have it open on another desktop at the moment. The structure sync feature to push schema changes from dev->test->live is worth the licence fee alone. – Meagher

Nice catch, I didn't even know it had those features. It's the best thing on the mac so far. – Disject

It seems to only compare databases that live on servers, not native sql files – Potassium

@seanyboy, Why do you like the whacky UI? – Ronen

Answer 4

There is a Schema Synchronization Tool in SQLyog (commercial) which generates SQL for synchronizing two databases.

Strophic

yep this is the best solution to this so far for me, provides fine SQL sync queries so that you can update it anytime, anywhere.. – Pushup

over-costly and heavy, not good for after-the-fact patchings – Eberta

Very slow, and for some reason it drops and recreates a lot of foreign keys even when it's not needed. No way to follow progress. – Insult

Answer 5

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

Zehe

Works great! And it's free, thanks. For those who couldn't find it (Like me). It's here: Database -> Reverse Engineer -> In MySQL Model or EER Diagram -> Database -> Synchronize with any source – Pomeranian

It does work well. However you can only compare databases of the same name. I have multiple (multi-tenant client) databases I want to sync from a "master" version on the same host. So I have to to rename the master to match the each client db before syncing. Otherwise nice! – Attested

Additional info on this can be found on this link – Dogy

Answer 6

There are many ways certainly, but in my case I prefer the dump and diff command. So here is an script based on Jared's comment:

#!/bin/sh

echo "Usage: dbdiff [user1:pass1@dbname1] [user2:pass2@dbname2] [ignore_table1:ignore_table2...]"

dump () {
  up=${1%%@*}; user=${up%%:*}; pass=${up##*:}; dbname=${1##*@};
  mysqldump --opt --compact --skip-extended-insert -u $user -p$pass $dbname $table > $2
}

rm -f /tmp/db.diff

# Compare
up=${1%%@*}; user=${up%%:*}; pass=${up##*:}; dbname=${1##*@};
for table in `mysql -u $user -p$pass $dbname -N -e "show tables" --batch`; do
  if [ "`echo $3 | grep $table`" = "" ]; then
    echo "Comparing '$table'..."
    dump $1 /tmp/file1.sql
    dump $2 /tmp/file2.sql
    diff -up /tmp/file1.sql /tmp/file2.sql >> /tmp/db.diff
  else
    echo "Ignored '$table'..."
  fi
done
less /tmp/db.diff
rm -f /tmp/file1.sql /tmp/file2.sql

Feedback is welcome :)

Abeu

Answer 7

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

Bartizan

Answer 8

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?

Execution

Don't forget to provide both --hostN and --userN or it will fail silently. – Hatred

I had trouble with Oracle's mysqldbcompare tools generating bugs on indexes, and altering fields that were equivalent. The mysqldiff tool worked flawlessly and saved a good bit of time. – Arlinearlington

Answer 9

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

Frivolity

Answer 10

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

Hest

If you are having trouble installing this on a mac, I was only able to by installing mysql and python using homebrew, with macports of no avail. – Unwished

Answer 11

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

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

Obvolute

This project is no longer in development. – Girder

Answer 12

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

Hexastyle

I did not know about this project! Thanks, it looks like it has quite a few tools that would be extremely useful. – Impellent

I haven't found schema comparison tools in Maatkit. – Obvolute

Me neither - where in the tools might we find this? – Trace

I don't think there is schema comparison in there. I was referring to data comparison and syncing using mk-table-checksum and mk-table-sync – Hexastyle

it is on process code.google.com/p/maatkit/wiki/mk_schema_sync – Shinbone

Answer 13

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/

Legation

Red Gate tools don't seem to support anything other than SQL Server. – Farnham

Red Gate has a MySQL version as well, free while in early access: red-gate.com/products/MySQL_Compare/index.htm – Subset

Not available for OSX – Potassium

Answer 14

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.

Silt

Answer 15

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.

Titicaca

Answer 16

After hours searching on web for simple tool, i realized i didn't look in Ubuntu Software Center. Here is a free solution i found: http://torasql.com/ They claim to have a version for Windows also, but I'm only using it under Ubuntu.

Edit: 2015-Feb-05 If you need Windows tool, TOAD is perfect and free: http://software.dell.com/products/toad-for-mysql/

Photopia

Development of this tool stopped and is now included in Percona : percona.com/software/percona-toolkit – Inexhaustible

Answer 17

The apache zeta components library is a general purpose library of loosly coupled components for development of applications based on PHP 5.

eZ Components - DatabaseSchema allows you to:

   .Create/Save a database schema definition;
   .Compare database schemas;
   .Generate synchronization queries;

You can check the tutorial here: http://incubator.apache.org/zetacomponents/documentation/trunk/DatabaseSchema/tutorial.html

Vonvona

Answer 18

Very easy to use comparison and sync tool:
Database Comparer http://www.clevercomponents.com/products/dbcomparer/index.asp

Advantages:

• fast
• easy to use
• easy to select changes to apply

Disadvantages:

• does not sync length to tiny ints
• does not sync index names properly
• does not sync comments

Insult

True, they've made a superficial update with a few small changes in 5 years. But it is not being actively developed. – Insult

Answer 19

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

Wilma

Answer 20

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.

Occultation

MySQL has a similar command mysql_dump. This might be a solution if I could integrate it in a deployment process. Thanks. – Impellent

Also, for a more user-friendly experience, you can get the same using phpMyAdmin - a real killer for MySQL users! – Coopery

Identical schemas can easily result in different schema dumps. Different versions of the mysql client might produce slightly different dumps (a problem if you are comparing schemas from two different machines), and things like foreign keys and constraints may be dumped in a different order. – Ostracism

Answer 21

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

Bakeman

is that a promise? I tried it and it fell over with a fair few errors, not least that when migrating a function it attempts to use the same owner as the original database – Cora

Yes its a promise. For most people the tool is working just fine. We promise a license for life for any bug that you find and we can't fix within 5 business days. Please contact our support team. – Bakeman