使用免费工具在命令行上比较两个 MySQL 数据库
我想生成类似 diff 的 SQL 文件来比较两个 MySQL 数据库的数据和结构。
这些生成的文件将作为 SQL 查询在命令行上执行。
SO 和各种论坛上有各种相关的旧线程,但它们处理带有用户界面的非自由工具(不是命令行工具)。
此外,不应直接同步数据库,而只能通过执行生成的 diff SQL 脚本来同步。
I would like to generate diff-like SQL files to compare the DATA and the STRUCTURE of two MySQL databases.
These resulting files would be executed as SQL queries, on command-line.
There is various old threads related on SO, and on various forums, but they deal with non-free tool with user interface (not command-line tools).
Also, the database should not be synced directly, but only by executing the resulting diff SQL script.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
下面将尝试向您展示差异(问题的第一部分),但输出实际上不能用作补丁文件。
比较两个表:
比较数据:
比较数据库:
The following will have a go at showing you the differences (the first part of your question) but the output won't really be useable as patch files.
To compare two tables:
To compare data:
To compare databases:
查看开源 Percona 工具包 --- 具体来说, pt-table-sync 实用程序。它使用索引的校验和以及其他策略来快速比较表。它的主要目的是同步副本,但通过一些额外的工作,它是一个很棒的差异工具。请参阅我对此的完整回答。
编辑:我忘了提及结构的比较是不同的野兽。我使用一个存储过程来执行此操作,您可以从命令行调用该存储过程,但这可能不是您想要的。
下面是我为显示模式差异而编写的 shell 脚本包装器的一个片段:
它调用了
compareDBs
存储过程,这是我从 Artful Software 查询存档页面。Check out the open-source Percona Toolkit ---specifically, the pt-table-sync utility. It uses checksums on indexes and other strategies to diff tables fast. Its primary purpose is syncing replicas, but with a little extra work, it's a great diff tool. See my full answer about it here.
EDIT: I forgot to mention that comparison of the structure is a different beast. I do this with a stored procedure that you can call from the command line, but it may not be what you're looking for.
Here's a snippet from a shell-script wrapper that I wrote for showing schema diffs:
It calls the
compareDBs
stored procedure, which I got from the Artful Software query archive page.用于模式比较的工具更为常见。有一些 perl 软件包可以轻松地从命令行运行。我还找到了一个用于数据差异的工具(这是一个相当复杂的问题!),但它有点旧,我不确定它有多好。
架构比较:
http://adamspiers.org/computing/mysqldiff/
数据比较:
http://rossbeyer.net/software/mysql_coldiff/
Tools for schema compare are more common. There are some perl packages for this which can easily be run from command line. I also found one for data diff (which is quite a complex problem!), but it's kind of old and I am not sure how good it is.
Schema compare:
http://adamspiers.org/computing/mysqldiff/
Data Compare:
http://rossbeyer.net/software/mysql_coldiff/