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  amp  Data Compare  Commercial  Maatkit  now Percona  liquibase Toad Nob Hill Database Compare  Commercial  MySQL Diff SQL EDT  Commercial

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt file1 sql mysqldump --skip-comments --skip-extended-insert -u root -p dbName2 gt file2 sql diff file1 sql file2 sql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt file1 sql mysqldump --skip-comments --skip-extended-insert -u root -p dbName2 gt file2 sql diff file1 sql file2 sql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Have a look at http   www liquibase org

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   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  gt  gt   tmp db diff   else     echo  Ignored   table        fi done less  tmp db diff rm -f  tmp file1 sql  tmp file2 sql   Feedback is welcome

User · Answer

Have a look at http   www liquibase org

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   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  gt  gt   tmp db diff   else     echo  Ignored   table        fi done less  tmp db diff rm -f  tmp file1 sql  tmp file2 sql   Feedback is welcome

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Have a look at http   www liquibase org

User · Answer

Have a look at http   www liquibase org

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

There is another open source command-line mysql-diff tool   http   bitbucket org stepancheg mysql-diff

User · Answer

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

User · Answer

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

User · Answer

There is another open source command-line mysql-diff tool   http   bitbucket org stepancheg mysql-diff

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Compare two MySQL databases

Examples related to mysql

Examples related to database

Examples related to diff