比较两个MySQL数据库 [英] Compare two MySQL databases

问题描述

我目前正在使用MySQL数据库开发应用程序。

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?

答案中提及的工具：

• Red-Gate的MySQL架构&数据比较（商业）


• Maatkit（现为Percona） a>


• liquibase


• Toad


• Nob Hill数据库比较（商业）


• MySQL Diff


• SQL EDT （商业）

• Red-Gate's MySQL Schema & Data Compare (Commercial)
• Maatkit (now Percona)
• liquibase
• Toad
• Nob Hill Database Compare (Commercial)
• MySQL Diff
• SQL EDT (Commercial)

推荐答案

数据库我发现在两个数据库上运行mysqldump与 - skip-comments 和 - skip-extended-insert 选项生成SQL脚本，然后在SQL脚本上运行差异很好。

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.

通过跳过注释，可以避免无意义的差异，例如运行mysqldump命令的时间。通过使用 - skip-extended-insert 命令，确保每行插入其自己的insert语句。这消除了单个新的或修改的记录可能在所有将来的insert语句中引起链式反应的情况。运行这些选项产生更大的转储没有意见，所以这可能不是你想在生产使用，但对于开发，它应该是很好的。我把下面使用的命令的例子：

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

这篇关于比较两个MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！