Compare two MySQL databases

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)

Red Gate now has a MySQL version as well, currently free as it's in extended early access: http://www.red-gate.com/products/MySQL_Compare/index.htm — David Atkinson, Oct 24 '10 at 12:58
It is a real problem. I deploy from dev to production machine and it ALWAYS breaks something. Thank you for this informative post — Herr, May 15 '11 at 09:37
The MySQL tool from Redgate is now $70/user. Even at that price I'll evaluate and post comments here. — Jeremy McGee, Jul 17 '11 at 21:01
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. — Tass, Jan 20 '12 at 21:52
Another Windows tool: [Database Workbench](http://www.upscene.com/go/?go=dbw), 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. — Martijn Tonies, May 02 '14 at 15:46
I like to use SQL Architect. Is has a nice compare feature. Here: http://www.sqlpower.ca/page/architect_download_os — Nelson Teixeira, Jun 17 '14 at 20:03
A fantastic alternative for Windows, Linux or Mac is the [mysqldbcompare](http://dev.mysql.com/doc/mysql-utilities/1.4/en/mysqldbcompare.html) tool developed by MySQL themselves. Please see my answer here for further information: http://stackoverflow.com/a/26192873/1365289 ! This took me a LONG time to find and it was worth the effort! — Jasdeep Khalsa, Oct 04 '14 at 14:17
This one doesn't require a database connection, provides an API and a web interface: http://blog.geneticcoder.com/2015/11/12/mysql-diff-compare-library-api-and-online-tool-finished/ — I wrestled a bear once., Nov 12 '15 at 07:10

score 210 · Accepted Answer · edited Oct 07 '14 at 15:40

210

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

edited Oct 07 '14 at 15:40

ydaetskcoR

53,225
8
158
177

answered Jan 03 '12 at 21:03

Jared

39,513
29
110
145

12

Double-plus upvotes for command-line literacy!!! – dogenpunk Jan 20 '12 at 21:56
1

command line ftw! now i can use this in any script :) – hyprnick Feb 29 '12 at 23:14
5

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` – zanlok Mar 06 '12 at 21:53
41

`-d`, `--no-data` may be of interest to those needing production use but only care about the schema – lsl May 10 '13 at 05:06
7

A better tool to use would be the [mysqldbcompare](http://dev.mysql.com/doc/mysql-utilities/1.4/en/mysqldbcompare.html) 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. – Jasdeep Khalsa Oct 04 '14 at 14:22
@jasdeepkhalsa The link is broken, may you please provide a new one? – Handsome Nerd Feb 17 '15 at 05:34
`--all-databases` is also useful if you wish to compare the entire server schema instead of a specific database. – DOOManiac Apr 21 '15 at 15:33
4

For a nice diff with colors try `vimdiff` – gitaarik May 20 '15 at 12:22
@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! – Jasdeep Khalsa May 21 '15 at 15:30
@PHPst Try out https://github.com/DBDiff/DBDiff – Jasdeep Khalsa Jun 19 '15 at 09:47
@jasdeepkhalsa Thanks, I will give it a try. – Handsome Nerd Jun 19 '15 at 12:26
Brilliant answer! But yeah, `-d` is needed to skip data. – Ashesh Jul 10 '15 at 08:59
`-iEwB` could be useful if you don't care about whitespaces, tabs and case. – Alex2php Aug 04 '15 at 14:45

score 98 · Answer 2 · edited Jul 07 '14 at 00:36

98

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.

edited Jul 07 '14 at 00:36

Danny Beckett

20,529
24
107
134

answered Oct 22 '08 at 13:48

Anson Smith

6,194
1
23
10

2

All the tools mentioned look good. I am selecting Toad arbitrarily for now until I could conduct some more research. – Vincent Ramdhanie Oct 22 '08 at 14:25
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. – javydreamercsw Sep 15 '10 at 15:17
Toad is very unhandy :( you cannot just compare two different sql dumps – pars Jan 06 '11 at 14:28
64

I got all excited about this tool until I realized that it runs in windows, not linux. Back to searching... – jdias Sep 10 '11 at 21:45
2

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. – thames Jan 02 '12 at 04:35
@jdias 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?) – zanlok Mar 06 '12 at 19:59
4

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). – schemacs Oct 27 '12 at 11:08
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 – clieu Jan 03 '13 at 19:22
4

@Anson Smith Can you tell me the alternative for linux? – Visruth Sep 25 '13 at 05:11
This tool is Windows only, rendering it useless for me. I think the answer should mention this. – hek2mgl Feb 17 '14 at 11:24
it is now eclipse plugin, still this plugin is quite buggy. still better then windows. – SkorpEN Oct 15 '14 at 09:42
Today Toad is no longer freeware. It is free trail and you have to buy to use full function. – vee Nov 06 '14 at 19:09
@jdias, Get a VM then, Settled. – Pacerier Feb 24 '15 at 07:07

score 20 · Answer 3 · answered Oct 22 '08 at 13:52

20

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.

answered Oct 22 '08 at 13:52

seanyboy

5,623
7
43
56

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. – Colonel Sponsz Oct 22 '08 at 14:10
2

Nice catch, I didn't even know it had those features. It's the best thing on the mac so far. – O.O Jul 24 '11 at 04:33
It seems to only compare databases that live on servers, not native sql files – AlxVallejo Dec 11 '14 at 14:42
@seanyboy, Why do you like the whacky UI? – Pacerier Feb 24 '15 at 07:08

score 17 · Answer 4 · answered Apr 01 '11 at 16:32

17

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

enter image description here

answered Apr 01 '11 at 16:32

Yury Litvinov

1,354
2
16
24

1

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.. – Anupam Feb 11 '12 at 13:00
over-costly and heavy, not good for after-the-fact patchings – zanlok Mar 06 '12 at 20:00
1

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. – Artem Goutsoul Mar 08 '12 at 12:54

score 13 · Answer 5 · answered Apr 23 '12 at 18:02

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 :)

score 13 · Answer 6 · answered Oct 22 '08 at 13:50

13

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

answered Oct 22 '08 at 13:50

andyhky

1,798
1
17
30

7

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 – bentzy Jun 09 '14 at 14:29
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! – scipilot Apr 23 '15 at 23:43
Additional info on this can be found on this [link](https://dev.mysql.com/doc/workbench/en/wb-database-synchronization.html) – Steven Ryssaert May 21 '15 at 18:42

jmpeace · Answer 7 · 2010-10-21T22:30:48.507

12

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 alt text

edited Oct 21 '10 at 22:30

answered Oct 04 '10 at 22:17

jmpeace

179
2
10

Zac · Answer 8 · 2015-02-18T15:02:35.547

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?

Don't forget to provide both `--hostN` and `--userN` or it will fail silently. — Markus Hedlund, May 03 '14 at 12:30
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. — Robert K, Feb 18 '15 at 14:27

score 7 · Answer 9 · answered Oct 22 '08 at 13:46

7

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

answered Oct 22 '08 at 13:46

anders.norgaard

1,062
13
23

score 6 · Answer 10 · answered Apr 12 '11 at 18:08

6

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

answered Apr 12 '11 at 18:08

likeuclinux

69
1
1

1

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. – Bijou Trouvaille Jul 06 '12 at 23:00

score 4 · Answer 11 · answered Feb 07 '09 at 21:22

4

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

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

answered Feb 07 '09 at 21:22

stepancheg

4,262
2
33
38

This project is no longer in development. – Feb 28 '15 at 19:41

score 3 · Answer 12 · answered Oct 22 '08 at 13:44

3

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

answered Oct 22 '08 at 13:44

Jarod Elliott

15,460
3
35
34

I did not know about this project! Thanks, it looks like it has quite a few tools that would be extremely useful. – Vincent Ramdhanie Oct 22 '08 at 13:51
2

I haven't found schema comparison tools in Maatkit. – stepancheg Sep 24 '10 at 04:25
Me neither - where in the tools might we find this? – Shabbyrobe Feb 03 '11 at 11:10
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 – Jarod Elliott Feb 18 '11 at 10:32
it is on process http://code.google.com/p/maatkit/wiki/mk_schema_sync – Pedro Jul 15 '11 at 15:01

score 3 · Answer 13 · answered Oct 22 '08 at 13:53

3

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/

answered Oct 22 '08 at 13:53

George

7,864
5
29
25

1

Red Gate tools don't seem to support anything other than SQL Server. – Rytmis Dec 06 '09 at 10:15
2

Red Gate has a MySQL version as well, free while in early access: http://www.red-gate.com/products/MySQL_Compare/index.htm – David Atkinson Oct 24 '10 at 12:58
Not available for OSX – AlxVallejo Dec 11 '14 at 14:35

score 3 · Answer 14 · answered Oct 22 '08 at 13:54

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.

score 3 · Answer 15 · answered Sep 24 '10 at 11:21

3

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.

dbForge Data Compare for MySQL

answered Sep 24 '10 at 11:21

Devart

119,203
23
166
186

Nikolay Ivanov · Answer 16 · 2015-02-05T12:29:20.280

3

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/

edited Feb 05 '15 at 12:29

answered Aug 16 '11 at 18:13

Nikolay Ivanov

5,159
1
26
22

2

Development of this tool stopped and is now included in Percona : http://www.percona.com/software/percona-toolkit – mrmuggles Apr 23 '14 at 15:21

score 2 · Answer 17 · answered Nov 24 '10 at 09:01

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

score 2 · Answer 18 · edited Feb 28 '13 at 11:42

2

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

edited Feb 28 '13 at 11:42

Taz

3,718
2
37
59

answered Mar 08 '12 at 13:00

Artem Goutsoul

733
5
17

True, they've made a superficial update with a few small changes in 5 years. But it is not being actively developed. – Artem Goutsoul Mar 04 '13 at 16:57

score 1 · Answer 19 · answered Jan 25 '11 at 08:05

1

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

answered Jan 25 '11 at 08:05

score 0 · Answer 20 · answered Oct 22 '08 at 13:43

0

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.

answered Oct 22 '08 at 13:43

Paul Tomblin

179,021
58
319
408

MySQL has a similar command mysql_dump. This might be a solution if I could integrate it in a deployment process. Thanks. – Vincent Ramdhanie Oct 22 '08 at 13:47
Also, for a more user-friendly experience, you can get the same using phpMyAdmin - a real killer for MySQL users! – schonarth Oct 22 '08 at 13:51
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. – Mark E. Haase Dec 01 '11 at 19:32

score 0 · Answer 21 · answered Jan 22 '09 at 19:09

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

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 — Cruachan, May 29 '09 at 11:52
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. — , Jun 04 '09 at 05:22

Compare two MySQL databases

21 Answers21

Linked

Related