For the table mydb.mytable, run this query:
SELECT update_time FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable'; If you want to know what tables have changed in the last 5 minutes, run this:
SELECT table_schema,table_name,update_time FROM information_schema.tables WHERE update_time > (NOW() - INTERVAL 5 MINUTE); Give it a Try !!!
UPDATE 2011-12-21 20:04 EDT
My employer (DB/Wweb hosting comany) has a client with 112,000 InnoDB tables. It is very difficult to read INFORMATION_SCHEMA.TABLES during peak hours. I have an alternate suggestion:
If you have innodb_file_per_table enabled and all the InnoDB tables are stored in .ibd files, there is a way to ascertain the time of the last update (up to the minute).
For the table mydb.mytable, do the following in the operating system:
$ cd /var/lib/mysql/mydb $ ls -l mytable.ibd | awk '{print $4,$5}' This timestamp is from the OS. You can't go wrong on this one.
UPDATE 2011-12-21 22:04 EDT [mysqld] innodb_max_dirty_pages_pct=0;
[mysqld] innodb_max_dirty_pages_pct=0; Add this to my.cnf, restart mysql, and all InnoDB tables will experience fast flushes from the buffer pool.
To avoid restarting, just run
mysql> SET GLOBAL innodb_max_dirty_pages_pct=0; UPDATE 2013-06-27 07:15 EDT
When it comes to retrieving the date and time for a file, ls has the --time-style option:
$ cd /var/lib/mysql/mydb $ ls -l --time-style="+%s" mytable.ibd | awk '{print $6}' You can compare the timestamp of the file against UNIX_TIMESTAMP(NOW()).