How to detect inactive tables?

I started working on a project whose former developer ragequited, and it left a complete mess both in the code and database. The server is using MySQL 5.5

There are a lot of tables created for backup or test purposes. For example, from all this tables, only two are really being used.

Is there any way to check which tables weren't used in the last XX hours, to be able to safely delete them? Can I set a log for that, besides from the general log, which is really big and fills the available space too quickly?

I cant trust names as in some cases, the table being used is the one that ends with _test or _bk.

Unfortunately, using the update_time column from INFORMATION_SCHEMA.TABLES only works for MyISAM tables. It does not work for InnoDB.

The most effective way to get the last time a table was written is to rely on the OS. You must check the timestamp of the .ibd or .MYD files within a database folder.

I wrote earlier posts about how to do this:

• Dec 21, 2011 : Fastest way to check if InnoDB table has changed

• Apr 04, 2013 : How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?

• Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?

• Sep 25, 2014 : want to find out which databases are used in last 30 days or not

• Jan 16, 2015 : How can I determine which Innodb table is being written?

StackExchange Database Administrators Q#94396, answer score: 2