Log all table updates for all tables in MySQL

Question

Is there anyway I can get the last updated time of a set of tables? By last updated time, I mean not only when the table's schema changed but when any of the rows changed or even when rows were created/deleted in the given set of tables.

I tried both SELECT * FROM information_schema.tables and SHOW TABLE STATUS FROM myDb and I only see the create_time which is only when table was created.

I can alternatively create triggers like:

CREATE TRIGGER tbl1_delete AFTER DELETE ON tbl1 
FOR EACH ROW
BEGIN
 UPDATE table_updates SET update_time = now() WHERE table_name = tbl1
END

But, now I have to repeat this over and over for deletes, updates, creations and schema changes. And, I want to automate this for all my tables!

• Michael - sqlbot: Have you considered the binary log as a source?
• pathikrit: @Michael-sqlbot: This is not some one time tool I am building (else I would consider parsing the bin log). I want this for some live APIs (so something from metadat tables and/or triggers are needed)
• pathikrit: @Michael-sqlbot: Also, this should work for copied databases too (i.e. binlog may not be copied over or exist).
• Michael - sqlbot: I'm not sure what you mean by "copied" databases.

Answer 1

I got some interesting news for you. You cannot use information_schema.tables because it does not track changes to InnoDB tables.

The best way to find out when a table changed is to go to the OS and get the most recent timestamp of each table. Rather than plagiarize my own posts, here are my posts where I show you how to do this

• Sep 25, 2014 : want to find out which databases are used in last 30 days or not
• Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?
• Apr 04, 2013 : How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?
• Dec 21, 2011 : Fastest way to check if InnoDB table has changed

Give Them a Try !!!