Get the list of tables updated in last 24 hours of a database in MySQL with engine myisam or innodb

Question

I have tried a php script like:

$res=mysql_query("SELECT TABLE_NAME 
                  FROM  information_schema.'TABLES' 
                  WHERE  UPDATE_TIME > NOW() - INTERVAL 2 DAY 
                  AND  ABLE_SCHEMA = 'mvgis_lewiscounty'");

which is not working.

Can anyone please guide me how should I proceed with this?

score 3 · Accepted Answer · edited May 23 '17 at 10:32

3

There's no solution using INFORMATION_SCHEMA that works for both InnoDB and MyISAM.

The UPDATE_TIME is not updated for InnoDB tables. Update time for an InnoDB table is kind of subjective. Is it the time someone last did an UPDATE? Is it the time that update was COMMITed? Is it the time the change was flushed to the tablespace?

You'd be better off using a shell command like:

$ find /var/lib/mysql -name '*.ibd' -mtime -1

Another solution would be for all tables to have a trigger that updates a summary table with one row per table name, to record the latest INSERT/UPDATE/DELETE.

See also How can I tell when a MySQL table was last updated?

edited May 23 '17 at 10:32

Community

1
1

answered May 28 '14 at 23:10

Bill Karwin

499,602
82
638
795

I want to keep a check on update. How about MyISAM only? Is there any way where I can get this information without using shell command if I use MyISAM instead? – Kunal Shrivastava May 28 '14 at 23:15
Please don't use MyISAM. It's slow, susceptible to corruption easily, and does not support ACID behavior. It's likely to be deprecated in a few years. – Bill Karwin May 28 '14 at 23:23
Thank you so much for your suggestion. I appreciate it Sir. – Kunal Shrivastava May 29 '14 at 14:07

Get the list of tables updated in last 24 hours of a database in MySQL with engine myisam or innodb

1 Answers1