Skip to main content

Simple MySQL schema comparison on Windows

File under

If you do any database work, chances are you've run into the following situation: you've made some structural changes to a few tables on the local or staging copy of your database, and now you need to replicate those changes on your staging or production database. The problem is, how can you be sure that you make the same changes to each copy of the database, other than by comparing each table one-by-one (which is tedious and error-prone)?

The obvious solution is to run a diff on your schemas; and after looking at all the options out there, I've found that running a diff is indeed one of your best options. I also found another solution that has some real advantages over running diffs – a free web app called MySQLDiff.

Read on for a quick run-down of MySQLDiff, and a brief explanation of how to compare your schemas the old-fashioned way.

MySQLDiff

MySQLDiff (not to be confused with mysqldiff) is a free collection of PHP files written by some folks in Germany. You extract the files to a path in your web server's doc root and run it from there. It has everything I was looking for, and then some.

Pros

  • Works with live databases, both local and remote – something that many other products in the database synchronization space either don't offer, or make you pay extra for
  • Generates a SQL script that you can use to bring one database's table structure into sync with the other
  • Requires nothing more than PHP and a web server

In addition, the SQL script that MySQLDiff generates is a great piece of information to have even if you don't want to actually run the script. For example, output like this makes it pretty easy to tell what you have to do:

ALTER TABLE category_lookup
     DROP INDEX category_id,
     ADD INDEX post_id (post_id, category_id);

Cons

  • Hasn't been updated since 2004

Pretty short list of cons there. But so far, this tool has done the one thing I asked of it to my satisfaction. Many others failed.

Shared Hosting

If you're running a site on shared hosting, your hosting provider might not allow you to connect to your MySQL database remotely, but you can still use MySQLDiff. Just use phpMyAdmin to do a schema-only export of your database and use that for the comparison. To get just the schema in your export, on the Export tab, leave the "Structure" check box selected, but clear the "Data" checkbox. Easy.

diff + mysqldump (or phpMyAdmin)

If for some reason you don't want to bother with MySQLDiff, I recommend you go the old-school route and just run a diff on dumps of your databases. We've already covered how to do a schema-only dump using phpMyAdmin. In case you don't remember the mysqldump syntax off the top of your head, to have mysqldump return just the schema of your database, dump it thusly:

mysqldump -u user -p databaseToDump --no-data > someFile.sql

Do this for your two databases and then run a diff against the results. On Windows, I like WinMerge (GPL) for my diff tool.

Pros

  • Always available, provided you have access to the databases
  • On Linux, requires no extra software

Cons

  • diff output isn't as pretty as what you get from MySQLDiff
  • You have to run the dumps yourself

But What About Product X?

I looked at a lot of other tools for this, but they were all unacceptable for one reason or another:

  • Didn't do what I needed (no diff tools, or charged a fee for the diff tools)
  • Didn't work (crashed)
  • Too much effort to set up on Windoze

The biggest disappointment here was MySQL Workbench, the free version of which does not support schma diffs, even though it's supposed to. I have a post in the MySQL forums about the discrepancy between what the comparison matrix says and what I found in the product. If I get a response from MySQL, I'll update this post.

Still, if all you want to do is keep track of the differences between a couple of databases, you don't need anything fancy – just stick with diff or MySQLDiff. As the old saying goes, don't use a shotgun to swat a fly. If you later get overrun by a swarm of half-men, half-fly hybrids from The Fly, blast away. But until that day comes … keep it simple.

jet another diff tool for mysql

I recently started the project mydbdiff at https://sourceforge.net/projects/mydbdiff/ you can test it but is not ready to use in one o more meeks I will write more code.

Schema Compare Tool

Our tool does a schema compare, produces a pretty report, and creates a synchronization script within seconds. This is in addition to a ton of other features, like Data Compare, Test Data Generation, SQL Intellisense, and a whole lot more! Try it out http://www.sqledt.com

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.