我目前正在开发一个使用MySQL数据库的应用程序。
数据库结构在开发过程中仍在变化和变化(我更改本地副本，只保留测试服务器上的副本)。
是否有一种方法可以比较数据库的两个实例，以查看是否有任何更改?
虽然目前简单地丢弃之前的测试服务器数据库是可以的，但是随着测试开始输入测试数据，这可能会变得有点棘手。 同样的情况会在以后的制作中再次发生……
是否有一种简单的方法来增量地对生产数据库进行更改，最好是自动创建一个脚本来修改它?
答案中提到的工具:
Red-Gate的MySQL模式和数据比较(商用) Maatkit(现为Percona) liquibase 蟾蜍 Nob Hill数据库比较(商业) MySQL Diff SQL EDT(商用)
当前回答
我正在与Nob Hill的营销团队合作，我想告诉你，我很高兴听到你的问题，建议或其他任何东西，请随时与我联系。
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
其他回答
我正在与Nob Hill的营销团队合作，我想告诉你，我很高兴听到你的问题，建议或其他任何东西，请随时与我联系。
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
对于问题的第一部分，我只是把两者都做了一下，并将它们区别开来。不确定mysql，但postgres pg_dump有一个命令，只转储模式而不转储表内容，所以你可以看到你是否改变了模式。
如果您正在使用小型数据库，我发现在两个数据库上运行mysqldump，使用——skip-comments和——skip-extended-insert选项生成SQL脚本，那么在SQL脚本上运行diff效果非常好。
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
非常容易使用的比较和同步工具: 数据库比较器 http://www.clevercomponents.com/products/dbcomparer/index.asp
优点:
快 使用方便 易于选择要应用的更改
缺点:
不同步长度到小整数 没有正确地同步索引名 不同步注释
dbSolo，它是付费的，但这个功能可能是你正在寻找的 http://www.dbsolo.com/help/compare.html
它适用于Oracle, Microsoft SQL Server, Sybase, DB2, Solid, PostgreSQL, H2和MySQL
