Tech Comparing/Diff-ing MySQL Tables - GoFuckYourself.com

Cyber Fucker · 04-05-2017, 01:05 PM

What are the best tools to do it? I need this badly.

A few days ago some very bad shit happened to my mysql databases. Fortunately I had backups and only 1 day was lost. However, I realized that having something to do diff on tables would be very helpful. I can compare raw dumps using text editors and comparison tools but that's not a really cool way to do it. I would be very grateful for any tips and suggestions.

woj · 04-05-2017, 01:16 PM

you don't need any "tool", just run a query using the 2 tables...
most straight forward way is probably to "LEFT JOIN" on id fields on the 2 tables and then search for "table2.id IS NULL" that will find any missing rows...

Barry-xlovecam · 04-05-2017, 06:02 PM

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

Code:

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:

Code:

diff -y --suppress-common-lines /tmp/dump1 /tmp/dump2

diff - How do I see the differences between 2 MySQL dumps? - Stack Overflow

Interesting thread you would have to try and see ...

see: $ man diff

04-05-2017, 01:05 PM	#1
Cyber Fucker Hmm Industry Role: Join Date: Sep 2005 Location: On an endless road around the world for rock and roll. Posts: 12,642	Comparing/Diff-ing MySQL Tables What are the best tools to do it? I need this badly. A few days ago some very bad shit happened to my mysql databases. Fortunately I had backups and only 1 day was lost. However, I realized that having something to do diff on tables would be very helpful. I can compare raw dumps using text editors and comparison tools but that's not a really cool way to do it. I would be very grateful for any tips and suggestions. __________________

04-05-2017, 01:16 PM	#2
woj <&(©¿©)&> Industry Role: Join Date: Jul 2002 Location: Chicago Posts: 47,882	you don't need any "tool", just run a query using the 2 tables... most straight forward way is probably to "LEFT JOIN" on id fields on the 2 tables and then search for "table2.id IS NULL" that will find any missing rows... __________________ Custom Software Development, email: woj#at#wojfun#.#com to discuss details or skype: wojl2000 or gchat: wojfun or telegram: wojl2000 Affiliate program tools: Hosted Galleries Manager Banner Manager Video Manager Wordpress Affiliate Plugin Pic/Movie of the Day Fansign Generator Zip Manager

04-05-2017, 06:02 PM	#3
Barry-xlovecam It's 42 Industry Role: Join Date: Jun 2010 Location: Global Posts: 18,083	run mysqldump with "--skip-opt" to get the 2 dumps files i.e: Code: 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: Code: diff -y --suppress-common-lines /tmp/dump1 /tmp/dump2 diff - How do I see the differences between 2 MySQL dumps? - Stack Overflow Interesting thread you would have to try and see ... see: $ man diff