Compare 2 large sql files and find differences to recover data

Question

I have 2 large SQL files of around 8GB each. But, in the latest backup I find that one file has 300MB data missing.

I just want to compare which data is missing, so that I can check that was it just temporary data OR important data that has vanished.

On comparing both files via diff on Ubuntu 14.04 I always get memory allocation error. I have also tried other allowing more than memory solutions and all that, but still no help.

I want to gather all data which exists in sql1 but missing in sql2 to a new file sql3.

Please help!

EDIT: I moved from Simple MySQl-Server to Percona XtraDB Cluster recently, A lot of tables were converted from MyISAM to INNODB in the process. So, can that be a reason for 300MB decreases in mysqldump SQL files? I seriously doubt this because a SQL will be an SQL, but does INNODB SQL code is decreased in any case? An expert advice on this will help.

Answer 1

SQL dumps comparison is quite hard to do when dealing with large amounts of data. I would try the following:

1. Import each SQL file data into its own database
2. Use one of the methods indicated here to compare database content (I assume the schema is the same). E.g. Toad for MySql

This way of comparison should be faster, as data manipulation is much faster when stored into database and also has the advantage the missing data can easily used. E.g.

SELECT *
FROM db1.sometable
WHERE NOT EXISTS (SELECT 1 
                  FROM db2.sometable 
                  WHERE db1.sometable.pkcol = db2.sometable.pk2)

will return the exact missing information into a convenient way.