When was the last time a mysql table was accessed?

Question

Is there a way to tell the last access time of a mysql table? By access I mean any type of operation in that table including update, alter or even select or any other operation.

Thanks.

possible duplicate of [How can I tell when a MySQL table was last updated?](http://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated) — Shakti Singh, Apr 23 '11 at 06:12
This is not a possible duplicate. Updated is different from accessed. — dedunu, May 22 '20 at 11:32

score 12 · Answer 1 · answered Apr 23 '11 at 06:11

12

You can get the last update time of a table.

SELECT update_time FROM information_schema.tables WHERE table_name='tablename'

answered Apr 23 '11 at 06:11

BrandonG

367
1
5

1

Will that be affected by select operation too? – Ferdous Apr 23 '11 at 06:17
1

No, it doesn't store the last time data was read from the table. – BrandonG Apr 23 '11 at 06:24
1

You could enable the General Query Log and parse it for select statements related to the table in question. http://dev.mysql.com/doc/refman/5.1/en/query-log.html This is not the best solution, however, if you're performing a lot of queries on the server. – BrandonG Apr 23 '11 at 06:40
2

If isn't unfeasible to modify the queries submitted to the database engine, you can change from SELECT to a stored program and add an query that insert this metadata in other table. – PEdroArthur Apr 23 '11 at 06:46
3

It should be noted that this only works for MyISAM tables not InnoDB – Jericon Mar 10 '14 at 20:49

score 5 · Answer 2 · answered Nov 09 '18 at 08:40

5

You can use the OS level stat command. Locate the ibd file for that particular table and run the below command

stat file_location

If the Table is being queried by SELECT, You can find the timestamp of when it was accessed with under the Access field.

answered Nov 09 '18 at 08:40

Vinay Gurram

73
1
7

No, this is wrong. If every select resulted in reading from the filesystem (which is a slow operation) then it would be impossible to achieve the performance required from databases. From what I observe the .ibd file is read only for those selects by the time of which its modify time is newer than the access time, in other words the table has changed since the time of the last access. – user3071170 Jun 26 '21 at 12:44

MatrixManAtYrService · Answer 3 · 2018-07-02T19:47:21.267

I don't know how to get the exact time after-the-fact, but you can start dumping logs, do something, and then stop dumping logs. Whichever tables show up in the logs are the ones that were accessed during that time.

If you care to dig through the log, the queries are shown with timestamps.

Tell mysql where to put the log file

Add this line to my.cnf (on some systems it will be mysql.conf.d/mysqld.cnf).

general_log_file = /path/to/query.log

Enable the general log

mysql> SET global general_log = 1;

(don't forget to turn this off, it can grow very quickly)

Do the thing

All mysql queries will be added to /path/to/query.log

Disable the general log

mysql> SET global general_log = 0;

See which tables appeared

If it's short, you can just scroll through query.log. If not, then you can filter the log for known table names, like so:

query_words=$(cat mysql_general.log | tr -s [:space:] \\n | tr -c -d '[a-zA-Z0-9][:space:][_\-]' | egrep -v '[0-9]' | sort | uniq)

table_names=$(mysql -uroot -ptest -Dmeta -e"show tables;" | sort | uniq)

comm -12 <(echo $table_names) <(echo $query_words)

From there, you can grep the log file for whatever showed up in table_names. There you will find timestammped queries.

See also, this utility, which I made.

score 1 · Answer 4 · edited Oct 26 '12 at 05:43

1

For a more detailed (db name and table name) plus the period (range), try this query:

select table_schema as DatabaseName,
  table_name as TableName,
  update_time as LastAccessTime
from information_schema.tables
where update_time < 'yyyy-mm-dd'
group by table_schema
order by update_time asc

edited Oct 26 '12 at 05:43

j.w.r

4,136
2
27
29

answered Jan 05 '12 at 03:38

Mon

11
1

score 0 · Answer 5 · answered Jan 17 '18 at 07:00

0

Use information_schema database to find which table of respective database was updated:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
AND TABLE_NAME = 'tablename'
order by UPDATE_TIME DESC

answered Jan 17 '18 at 07:00

Ahmad Vaqas Khan

628
8
9

3

This will only tell him the last time the table structure was modified, not the last time the table was accessed or used. I would assume OP is trying to cleanup a database and find out if tables are even used anymore. – BilliD Feb 22 '18 at 17:35