1

This post is excellent and shows when last time your table changed How can I tell when a MySQL table was last updated?

but I need to find out the changes and times in last 24h since there was more than one change

Community
  • 1
  • 1
Benn
  • 4,840
  • 8
  • 65
  • 106
  • Do you need this on an application level (to show your users "here's what happened in the last 24 hours") or on the management/development level to monitor what is going on in your system? – Percutio Sep 15 '12 at 18:24

1 Answers1

1

You can try like this:

SELECT * 
FROM information_schema.tables
WHERE UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)

Or,

SELECT *
FROM  information_schema.tables
WHERE UPDATE_TIME >= SYSDATE() - INTERVAL 1 DAY
Akash KC
  • 16,057
  • 6
  • 39
  • 59
  • well , maybe i spoke to soon ,, here is the prob. in mod of the night someone edited my table , this morning i did the change to revert the edits , your query shows only the time i changed it , not the time the guy before me did the change – Benn Sep 15 '12 at 18:33
  • second query works also but again , shows only last time any table was changed in 24h, not changes in past 24 – Benn Sep 15 '12 at 18:38
  • Don't you want to get the changes in last 24 hours? – Akash KC Sep 15 '12 at 18:46
  • I need to get all change times in last 24 , im trying to find who did this and was stupid enough to fast fix the issue without checking last time changed before my fix , now all i see is time i did the change – Benn Sep 15 '12 at 18:47
  • I think, for that you need to create trigger that keep the log of updates in the table.... – Akash KC Sep 15 '12 at 19:04
  • @Benn: Take a look at [Point-in-Time (Incremental) Recovery Using the Binary Log](http://dev.mysql.com/doc/en/point-in-time-recovery.html). – eggyal Sep 15 '12 at 22:01