0

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!

  • Have you considered the binary log as a source? – Michael - sqlbot Oct 6 '14 at 21:22
  • @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 Oct 6 '14 at 21:57
  • @Michael-sqlbot: Also, this should work for copied databases too (i.e. binlog may not be copied over or exist). – pathikrit Oct 6 '14 at 22:11
  • I'm not sure what you mean by "copied" databases. – Michael - sqlbot Oct 7 '14 at 0:56
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

Give Them a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.