How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute? I have to create a xls datafeed for a website and I would like to know which tables are getting affected when I do a manual entry from CMS. 

If i have installed fresh database and I'm doing first entry in it using CMS: I would like to know which tables got updated/appended in last 1 min in that DB.

It is somewhat similar to this question https://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated

But in my case I dont know which tables to check. I can check each and every table in the db using the solution posted in the question but I have a gut feeling that there is a better solution for this.
There are two distinct answers I have to give by Storage Engine

MyISAM

For all MyISAM tables, checking the last time a table was updated is quick and dirty

SET @IntervalMinutes = 2;
SET @IntervalSeconds = @IntervalMinutes * 60;
SET @DBtoCheck = DATABASE();
SELECT table_schema,table_name,update_time
FROM information_schema.tables WHERE engine='MyISAM'
AND table_schema = @DBtoCheck
AND update_date &gt; (NOW() - INTERVAL @IntervalSeconds SECOND)
ORDER BY update_time DESC A;


InnoDB

I have some bad news: you cannot trust the INFORMATION_SCHEMA database because


Transaction Behavior of InnoDB
Location of the Data and Index Pages
Heavy duty locking of the INFORMATION_SCHEMA when reading about an InnoDB table


In order to know the last update by some timestamp, you must have innodb_file_per_table enabled. If not, all the data and index pages will sit inside ibdata1 (system tablespace). It would be anybody's guess when a table experienced an update.

You should first cleanup the InnoDB Infrastructure.

Then, the easiest way to get the timestamp is to ask the OS for it

EXAMPLE

Given this scenario


datadir is /var/lib/mysql
you want timetamp of mydb.mytable


Simply run this in the OS

TS_TAB=`ls -l --time-style="+%s" /var/lib/mysql/mydb/mytable.ibd | awk '{print $6}'`
TS_NOW=`date +%s`
(( TS_DIFF = TS_NOW - TS_TAB ))


TS_DIFF will be the number of seconds elapsed since the last time a write was posted to that table. That write can be anything (INSERT, UPDATE, DELETE, DDL).

That's as fast as I can get it for InnoDB

Give it a Try !!!