mysql script
Tips & Tricks

MySQL How to Check if Table is Modified Using Checksum

By comparing the checksum value of the table, it is simple to determine whether it has been updated. The checksum value will change each time we update, delete, or insert data into the table.

In order to determine whether the data in the live database and the backup database differs, we can also compare the checksum values between the live database and our backup database. This is helpful when we want to verify that the data in our backup is identical to the data in use.

Checksum Table

From MySQL documentation, it states that

CHECKSUM TABLE reports a checksum for the contents of a table. You can use this statement to verify that the contents are the same before and after a backup, rollback, or other operation that is intended to put the data back to a known state.

Use this query to return the checksum of a table CHECKSUM TABLE test.employer;

You could generate the CHECKSUM statements for all tables using this query below.

SELECT CONCAT('CHECKSUM TABLE ', table_name, ';') AS statement
FROM information_schema.tables
WHERE table_schema = 'test';
List of MySQL checksum query for all tables

Copy this output, and then paste it into Workbench or another tool if necessary. You would probably need to utilize pure dynamic MySQL if you needed to perform this from within application (for example, PHP code).

Additional Tip

Here’s another method how we can detect if our table has changed.

select table_schema, table_name, rows_fetched, rows_inserted, rows_updated, rows_deleted
from sys.schema_table_statistics where table_schema='test';
mysql checksum