How do I get a reliable checksum on an InnoDB table? I want something like the CHECKSUM TABLE foo QUICK; that MyISAM has, and it needs to be fast. I'm developing a tool to reliably monitor and swiftly react to changes in my database.
InnoDB tables don't update UPDATE_TIME in information_schema.TABLES, and I'm not at liberty to add an auto-updated last_update column to the table so I can do MAX(last_update). Anyway, it wouldn't work for deletes. Also, I'm not happy with the race conditions that timestamps with only full-second precision introduces.
I've seen suggestions to use NUM_ROWS and MODIFIED_COUNTER from information_schema.INNODB_SYS_TABLESTATS as a checksum but in my testing on MariaDB 10.0, these values are not changed on UPDATE queries.
Log sequence number increases each time a client writes to InnoDB.
mysql> pager grep "Log sequence number"
PAGER set to 'grep "Log sequence number"'
mysql> show engine innodb status\G
Log sequence number 243755747560
1 row in set (0.00 sec)
To know which table was modified you can scan the REDO log. See the description of the REDO log file format https://www.sba-research.org/wp-content/uploads/publications/WSDF2012_InnoDB.pdf