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

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 query.

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.

Answer 3

InnoDB still currently lacks a native mechanism to retreive this information. In the related feature request at MySQL, someone advises to set AFTER [all events] triggers on each table to be monitored. The trigger would issue a statement such as

INSERT INTO last_update VALUE ('current_table_name', NOW())
ON DUPLICATE KEY UPDATE update_time = NOW();

in a table like this:

CREATE TABLE last_update (
    table_name VARCHAR(64) PRIMARY KEY,
    update_time DATETIME
) ENGINE = MyISAM; -- no need for transactions here

Alternatively, if a slight inaccuracy in this data (in the range of one second) is acceptable, and if you have read access to the MySQL data files, you could switch to a setting where inndb_files_per_table = ON (recommended in any case) and check the last modification time of the underlying data files.

These files are found under /var/lib/mysql/[database_name]/*.ibd in most default installations.

Please note, if you decide to take this route, you need to recreate existing tables for the new setting to apply.

Answer 4

I have answered a question like this in the DBA StackExchange about 1.5 years ago: Fastest way to check if InnoDB table has changed.

Based on that old answer, I recommend the following

Flushing Writes to Disk

This is a one-time setup. You need to set innodb_max_dirty_pages_pct to 0.

First, add this to /etc/my.cnf

[mysqld]
innodb_max_dirty_pages_pct=0

Then, run this to avoid having to restart mysql:

mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

Get Timestamp of InnoDB table's .ibd file

ls has the option to retrieve the UNIX timestamp in Seconds. For an InnoDB table mydb.mytable

$ cd /var/lib/mysql/mydb
$ ls -l --time-style="+%s" mytable.ibd | awk '{print $6}'

You can then compute UNIX_TIMESTAMP(NOW()) - (timestamp of the .ibd file) and see if it is 3600 or less.

Give it a Try !!!

Answer 5

SELECT *
FROM  information_schema.tables
WHERE UPDATE_TIME >= SYSDATE() - INTERVAL 1 DAY  && TABLE_TYPE != 'SYSTEM VIEW'

SELECT * 
FROM information_schema.tables
WHERE UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) && TABLE_TYPE != 'SYSTEM VIEW'