mysql - How to verify if two tables have exactly the same data?

Question

Basically, we have one table (original table) and it is backed up into another table (backup table); thus the two tables have exactly the same schema.

In the beginning, both tables (original table and backup table) contains exactly the same set of data. After some time for some reason, I need to verify whether dataset in the original table has changed or not.

In order to do this, I have to compare the dataset in the original table against the backup table.

Let's say the original table has the following schema:

create table LemmasMapping (
   lemma1 int,
   lemma2 int,
   index ix_lemma1 using btree (lemma1),
   index ix_lemma2 using btree (lemma2)
)

How could I achieve the dataset comparison?

Update: the table does not have a primary key. It simply stores mappings between two ids.

Answer 1

You can just use CHECKSUM TABLE and compare the results. You can even alter the table to enable live checksums so that they are continuously available.

CHECKSUM TABLE original_table, backup_table;

It doesn't require the tables to have a primary key.