When was the last time a mysql table was accessed?

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 开发者_Python百科update, alter or even select or any other operation.

Thanks.

You can get the last update time of a table.

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

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.

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.

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 

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