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?

I am new to MySQL so please try to explain it as simply as you can.

thank you!

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';

Answer 2

For each table you want to detect change, you need to have a column that holds the last change's timestamp.

For every insert or update in the table, you need to update that column with the current date and time.

Alternatively, you can set up a trigger which updates the column automatically on each insert or modify. That way you don't have to modify all of your querie.

Once this works, to find out if rows from a table have been modified in the last hour, perform the query

select count(*) from mytable where datemod>subtime(now(),'1:0:0')

repeat for every table you want to check.