mysql - Query to find tables modified in the last hour

Question

I want to find out which tables have been modified in the last hour in a MySQL database. How can I do this?

Answer 1

MySQL 5.x can do this via the INFORMATION_SCHEMA database. This database contains information about tables, views, columns, etc.

SELECT * 
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE 
    DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`

Returns all tables that have been updated (UPDATE_TIME) in the last hour. You can also filter by database name (TABLE_SCHEMA column).

An example query:

SELECT 
    CONCAT(`TABLE_SCHEMA`, '.', `TABLE_NAME`) AS `Table`, 
    UPDATE_TIME AS `Updated`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
    DATE_SUB(NOW(), INTERVAL 3 DAY) < `UPDATE_TIME`
    AND `TABLE_SCHEMA` != 'INFORMATION_SCHEMA'
    AND `TABLE_TYPE` = 'BASE TABLE';