[mysql] Compare two MySQL databases

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)

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/

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.

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

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?

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.

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

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

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.

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

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

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

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

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

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

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.

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

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

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.

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?

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

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.

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/

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

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

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/

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?

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

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

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/

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

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

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

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

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

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.

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

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?

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

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

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

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

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

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.

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

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

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.

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

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.

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

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.

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.

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.

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.

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

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.

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.

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

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.

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/