比较两个 MySQL 数据库

2021-11-20 00:00:00 database diff mysql

我目前正在使用 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 Schema &数据比较(商业)
  • Maatkit(现为 Percona)
  • liquibase
  • 蟾蜍
  • Nob Hill 数据库比较(商业)
  • MySQL 差异
  • 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)

推荐答案

如果您使用的是小型数据库，我发现在两个数据库上都使用 --skip-comments 和 --skip-extended-insert 选项来生成 SQL 脚本，然后在 SQL 脚本上运行 diff 效果很好.

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 命令，您可以确保使用自己的插入语句插入每一行.这消除了单个新记录或修改记录可能导致所有未来插入语句中的连锁反应的情况.使用这些选项运行会产生更大的转储，没有注释，所以这可能不是你想要在生产使用中做的事情，但对于开发来说应该没问题.我在下面列出了我使用的命令示例:

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

相关文章