How do I see the differences between 2 MySQL dumps?

I have 2 MySQL dump files. I want to find the table data difference between 2 tables.

Contents hide

Answers:

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

run mysqldump with “–skip-opt” to get the 2 dumps files i.e:

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

Method 2

Use a DIFF tool – here are some graphical ones (both are free):

Method 3

This tool is not available anymore, 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.

~~http://www.mysqldiff.org/~~

Method 4

This was very useful for me, so adding my two cents:

git diff --word-diff=color dump1.sql dump2.sql | less -R

Method 5

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.

This was at one point not totally possible with mysqldump, I am not sure if this has changed in the meantime.

One good tool for the job is pydumpy https://code.google.com/p/pydumpy/ (mirror: 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.

Method 6

I just had to add lines breaks at ),(‘s so that each record becomes a separate line. Then the result can be fed to a tool like diff. This command does the job:

FORMAT_="s/),(/),n(/g"
diff <(sed $FORMAT_ old-dump.sql) <(sed $FORMAT_ new-dump.sql)

Method 7

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating