MariaDB/MySQL: Get checksum of InnoDB table

Question

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.

Answer 1

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

Answer 2

Another approach: Turn on the binlog. All changes go to it. Monitor it.

Answer 3

The MODIFIED_COUNTER not changing on UPDATEs is a bug in MySQL and MariaDB. Reported as http://bugs.mysql.com/bug.php?id=77301 I think I'll just wait for this to be fixed, since it nicely solves my problem.