You have two big tables in MySQL (>640K records), that maybe differ in the number of fields, but you want to make sure that the data in the common fields in both tables are the same.
- Use mysql to export the data from the first table in a csv file, selecting only the common fields.
We use the /tmp folder on the server to make sure we have the right permissions to create the file:
mysql>SELECT common_field1, common_field2, ... INTO OUTFILE '/tmp/first_table.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table1;
- If the tables are in different databases remember to switch db:
Export the second table in the second file:
mysql>SELECT common_field1, common_field2, ... INTO OUTFILE '/tmp/second_table.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table2;
- now use the diff. You can use any of the following options:
- diff -q first_table.txt second_table.txt
- diff first_table.txt second_table.txt > diff.txt