I have an unusual situation where I have a bunch of backups of a mysql database and I am interested in selecting the distinct members of a given table from these backups (since some records might have been added or deleted). My thought is to use a cross database join, but I'm not sure if that really is what I'm looking for. Any help would be appreciated.

  • so you want to comapre too almost dentical tables, then yes that is a good way
    – nbk
    Commented Jun 16, 2021 at 18:20
  • Is your goal to get the distinct set of records from that table across each database? Is the table definition the same in each database (if you could add your table definition to your post that would be helpful).
    – J.D.
    Commented Jun 16, 2021 at 18:51

2 Answers 2

SELECT ... INTO OUTFILE "out1" ... FROM db1.tbl ORDER BY ...;
SELECT ... INTO OUTFILE "out2" ... FROM db2.tbl ORDER BY ...;

From shell:

diff out1 out2

the ORDER BY is to make sure the rows are in the same, predictable, order. The PRIMARY KEY would be the optimal column(s) to specify.

If the tables were generated with different auto_increment values, then exclude that id from the SELECT list and use some other ORDER BY.


Assuming you're trying to get the data (not schema) differences, and the backups are already mounted as databases, then a simple UNION between all of the databases should be all you need to get the distinct set of rows, like this:

SELECT Column1, Column2, Column3
FROM Database1.YourTable


SELECT Column1, Column2, Column3
FROM Database2.YourTable


SELECT Column1, Column2, Column3
FROM Database3.YourTable

The UNION clause automatically removes duplicate records.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.