I have 2 MySQL dump files. I want to find the table data difference between 2 tables.
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.
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
Use a DIFF tool – here are some graphical ones (both are free):
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.
This was very useful for me, so adding my two cents:
git diff --word-diff=color dump1.sql dump2.sql | less -R
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.
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.
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)
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