4

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.

tables list

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.

  • 1
    Be sure you take a back up before you start deleting tables. And test it. Twice. – ypercubeᵀᴹ Mar 4 '15 at 16:02
2

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:

1

I would ensure that MySQL query logging is enabled and then duplicate the query log. In the duplicated log I would find and replace FROM [table_name] one-by-one in order to determine which tables aren't being actively used.

Good luck! Those table names do not reflect a positive path for that developer. :-( Let me know if you need to figure out how to enable query logging in MySQL and I'll find the answer for you on that as well.

  • Also look for INTO because of INSERTs. And check for other possible syntaxes. – Rick James Mar 6 '15 at 0:12
1

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.

0
SELECT update_time FROM information_schema.tables WHERE table_name='yourtablename'

Or if you want a list of all tables, with last accessed time in order

SELECT table_schema ,
  table_name,
  update_time as LastAccessed
FROM information_schema.tables
GROUP BY table_schema
ORDER BY update_time ASC 

For tables weren't used in the last XX hours add WHERE clause

WHERE update_time < 'yyyy-mm-dd'
  • 1
    As already mentioned, update_time is available only for MyISAM tables. – Rick James Mar 6 '15 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.