How to find difference between multiple tables and all their fields

[dlite922]

Short Version
How do you find the difference between two database with the same structure but different data? with SQL (results will be ingested with PHP).

Long Version
Seems like a little daunting task for a SQL n00b like me, but I'm here to seek some guidance and clear my head from all the googling.

I thought this would be common thing to do. I have two "database dumps" at different times, I want to know what data is different between them. I can do a check sum and know they're different, but I want to get those exact fields that different.

For this purpose, say there are 3 main tables

events: list of tv shows and movies
actors: list of actors in those movies and shows
image: image of the actors and tv shows and movie logos
*connector tables*: A few tables that connect all of these together.

Between two dumps of these tables, there could be new events, new actors, some actors with changed IDs, or just changes names (rare), and of course new images, but please note i'm not just talking added or deleted records, there could be changes to the data. I.e. same image ID, but now has a different image name, or same actor ID, but a middle initial is added, etc.

I want to display these differences with mysql queries.

INNER JOINS? some tool or procedure script?

Any direction appreciated,



Dan

[code green]

I would be tempted to do this via code. You mention PHP.
I have done similar projects and retrieved the data from each database in seperate queries,
then manipulated the data into arrays and compared the arrays.
You have much more flexibility for creating reports this way, and if you wish to update from one to the other, the data can be easily manipulated into an UPDATE or INSERT statement

[dlite922]

Problem is these tables are huge, approaching millions.

Could take a lot longer with code.

I can't believe MySQL doesn't provide a open source diff report/script between two DBs.

How does a DBA quickly finds the difference between two database dumps?




Dan

[Annalyzer]

@dlite922
Can't you use something like

SELECT *
FROM table1 INNER JOIN table2 ON table1.id=table2.id
WHERE table1.field1 != table2.field1 OR table1.field2 != table2.field2...

[code green]

I am not aware of anything. I spotted a couple of tools on Google

SQLyog
Maatkit.
red-gate's SQL Compare (Commercial)
liquibase
Toad
Nob Hill Database Compare
MySQL Diff

Performance-wise with code, it all depends.
If a lot of fields are text then string comparisons are painfully slow in SQL.