Problem
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.
Solution
- 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:
123456mysql>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:
1use seconddb;
Export the second table in the second file:
123456mysql>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