Determine last time a mysql table was accessed (i.e. SELECT'ed)

Question

In my mysql DB schema there are old tables that are no longer used. I want to come up with a systematic way to show my developer a list of tables and last time they were read (SELECT'ed, not UPDATE'ed: that would be easy).

There's a way of getting the general_log and parsing the gigantic file, but it's not elegant. I'm wondering if there's a tool or technique to learn and record time as each table gets accessed/read. Thanks.

My environment is 5.5 and 5.6

Answer 1

It is not possible to view accessed information unless and untill General Query Log is enabled in Mysql

you can enable general query log in mysql 5.5 as follows, which would help you in future

Here's how in Dhanesh Mane's words:

OS : Ubuntu 12.04.1 LTS

MySQL version : 5.5.28

Locate and edit my.cnf file. We need to change two variables in this file to enable general query log.

1. sudo gedit /etc/mysql/my.cnf
2. Locate this variable in the file : general_log_file
3. It must have some default value, this variable is nothing but the path of log file.
4. The default value is "/var/log/mysql/mysql.log", you can change it as per your wish.
5. Now locate variable "general_log", and set it to 1. The means turning on the log system.
6. Now restart the MySQL server by running : sudo service mysql restart
7. now if you open the mysql.log file, you can see all SQL queries in it

Hope this helps!