如何查看 2 个 MySQL 转储之间的差异？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3840908/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I see the differences between 2 MySQL dumps?
提问by Tree
I have 2 MySQL dump files. I want to find the table data difference between 2 tables.
我有 2 个 MySQL 转储文件。我想找到两个表之间的表数据差异。
采纳答案by OMG Ponies
回答by Fabio Pedrazzoli Grazioli
run mysqldump with "--skip-opt" to get the 2 dumps files i.e:
使用“--skip-opt”运行 mysqldump 以获取 2 个转储文件，即：
mysqldump --skip-opt -u $MY_USER -p$MY_PASS mydb1 > /tmp/dump1.sql
mysqldump --skip-opt -u $MY_USER -p$MY_PASS mydb2 > /tmp/dump2.sql
compare using these diff options:
使用这些差异选项进行比较：
diff -y --suppress-common-lines /tmp/dump1 /tmp/dump2
回答by vabada
This was very useful for me, so adding my two cents:
这对我非常有用，所以加上我的两分钱：
git diff --word-diff=color dump1.sql dump2.sql | less -R
回答by mit
In order to compare 2 mysql diffs they need to be done in a certain manner, so that the order is in a defined way and non relevant data is omitted.
为了比较 2 个 mysql 差异，它们需要以某种方式完成，以便以定义的方式进行排序并且省略不相关的数据。
This was at one point not totally possible with mysqldump, I am not sure if this has changed in the meantime.
这在某一点上并非完全可能mysqldump，我不确定在此期间是否发生了变化。
One good tool for the job is pydumpyhttps://code.google.com/p/pydumpy/(mirror: https://github.com/miebach/pydumpy)
完成这项工作的一个好工具是pydumpyhttps://code.google.com/p/pydumpy/（镜像：https: //github.com/miebach/pydumpy）
If you want to compare to an old dump, like in the question, you could first create a temporary database from the dump and then start there.
如果您想与旧转储进行比较，如问题中所述，您可以首先从转储创建一个临时数据库，然后从那里开始。
回答by CharlesLeaf
This tool is not availableanymore, as the website is no longer functional.
此工具不再可用，因为该网站不再可用。
Maybe you can give a tool called mysqldiff a go, I haven't tried it myself yet but it's been on my list for a while.
也许你可以试试一个叫做 mysqldiff 的工具，我自己还没有尝试过，但它已经在我的清单上有一段时间了。
回答by Mike Makuch
Here's what I use. It works.
这是我使用的。有用。
#!/bin/bash
# Do a mysqldump of the a db, once a day or so and diff to the previous day. I want to catch when data has changed
# Use the --extended-insert=false so that each row of data is on a single line, that way the diff catches individual record changes
mv /tmp/dbdump0.sql /tmp/dbdump1.sql 2>/dev/null
mysqldump -h dbhostname.com -P 3306 -u username -p password --complete-insert --extended-insert=false dbname > /tmp/dbdump0.sql
# Ignore everything except data lines
grep "^INSERT" /tmp/dbdump0.sql > /tmp/dbdump0inserts
grep "^INSERT" /tmp/dbdump1.sql > /tmp/dbdump1inserts
diff /tmp/dbdump1.sql /tmp/dbdump0.sql > /tmp/dbdumpdiffs
r=$?
if [[ 0 != "$r" ]] ; then
# notifier code remove
fi
