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

Question

In later versions of MySQL you can use the information_schema database to tell you when another table was updated:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
   AND TABLE_NAME = 'tabname'

This does of course mean opening a connection to the database.

---

An alternative option would be to "touch" a particular file whenever the MySQL table is updated:

On database updates:

• Open your timestamp file in O_RDRW mode
• close it again

or alternatively

• use touch(), the PHP equivalent of the utimes() function, to change the file timestamp.

On page display:

• use stat() to read back the file modification time.

I'm surprised no one has suggested tracking last update time per row:

mysql> CREATE TABLE foo (
  id INT PRIMARY KEY
  x INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
  KEY (updated_at)
);

mysql> INSERT INTO foo VALUES (1, NOW() - INTERVAL 3 DAY), (2, NOW());

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | NULL | 2013-08-18 03:26:28 |
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

mysql> UPDATE foo SET x = 1234 WHERE id = 1;

This updates the timestamp even though we didn't mention it in the UPDATE.

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | 1235 | 2013-08-21 03:30:20 | <-- this row has been updated
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

Now you can query for the MAX():

mysql> SELECT MAX(updated_at) FROM foo;
+---------------------+
| MAX(updated_at)     |
+---------------------+
| 2013-08-21 03:30:20 |
+---------------------+

Admittedly, this requires more storage (4 bytes per row for TIMESTAMP).
But this works for InnoDB tables before 5.7.15 version of MySQL, which INFORMATION_SCHEMA.TABLES.UPDATE_TIME doesn't.

I don't have information_schema database, using mysql version 4.1.16, so in this case you can query this:

SHOW TABLE STATUS FROM your_database LIKE 'your_table';

It will return these columns:

| Name      | Engine | Version | Row_format | Rows | Avg_row_length 
| Data_length | Max_data_length | Index_length | Data_free | Auto_increment 
| Create_time         | Update_time         | Check_time | Collation         
| Checksum | Create_options | Comment |

As you can see there is a column called: "Update_time" that shows you the last update time for your_table.

The simplest thing would be to check the timestamp of the table files on the disk. For example, You can check under your data directory

cd /var/lib/mysql/<mydatabase>
ls -lhtr *.ibd

This should give you the list of all tables with the table when it was last modified the oldest time, first.

For a list of recent table changes use this:

SELECT UPDATE_TIME, TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
ORDER BY UPDATE_TIME DESC, TABLE_SCHEMA, TABLE_NAME