使用免费工具在命令行上比较两个 MySQL 数据库

发布于 2024-11-19 23:43:27 字数 187 浏览 2 评论 0原文

我想生成类似 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技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

凡尘雨 2024-11-26 23:43:27

下面将尝试向您展示差异(问题的第一部分),但输出实际上不能用作补丁文件。

比较两个表:

 mysql -u whatever -e "describe table" database1 > file1.txt
 mysql -u whatever -e "describe table" database2 > file2.txt
 diff file1.txt file2.txt

比较数据:

 mysql -u whatever -e "select * from table" database1 > file1.txt
 mysql -u whatever -e "select * from table" database2 > file2.txt
 diff file1.txt file2.txt

比较数据库:

 mysqldump --extended-insert=FALSE -u whatever database1 > file1.txt
 mysqldump --extended-insert=FALSE -u whatever database2 > file2.txt
 diff file1.txt file2.txt

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:

 mysql -u whatever -e "describe table" database1 > file1.txt
 mysql -u whatever -e "describe table" database2 > file2.txt
 diff file1.txt file2.txt

To compare data:

 mysql -u whatever -e "select * from table" database1 > file1.txt
 mysql -u whatever -e "select * from table" database2 > file2.txt
 diff file1.txt file2.txt

To compare databases:

 mysqldump --extended-insert=FALSE -u whatever database1 > file1.txt
 mysqldump --extended-insert=FALSE -u whatever database2 > file2.txt
 diff file1.txt file2.txt
屋檐 2024-11-26 23:43:27

查看开源 Percona 工具包 --- 具体来说, pt-table-sync 实用程序。它使用索引的校验和以及其他策略来快速比较表。它的主要目的是同步副本,但通过一些额外的工作,它是一个很棒的差异工具。请参阅我对此的完整回答

编辑:我忘了提及结构的比较是不同的野兽。我使用一个存储过程来执行此操作,您可以从命令行调用该存储过程,但这可能不是您想要的。

下面是我为显示模式差异而编写的 shell 脚本包装器的一个片段:

mysql ${MYSQL_CNF_OPTION} -u ${DB_USER} \
-e "USE sys; CALL compareDBs('${DBDIFF_LOCAL_DB1}','${DBDIFF_LOCAL_DB2}');"

它调用了 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:

mysql ${MYSQL_CNF_OPTION} -u ${DB_USER} \
-e "USE sys; CALL compareDBs('${DBDIFF_LOCAL_DB1}','${DBDIFF_LOCAL_DB2}');"

It calls the compareDBs stored procedure, which I got from the Artful Software query archive page.

温柔嚣张 2024-11-26 23:43:27

用于模式比较的工具更为常见。有一些 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/

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文