MyISAM
If using MyISAM you can use the following query to list all tables which has not been modified in the last 10 days (or not modified at all):
SELECT table_schema, table_name, create_time, update_time
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema')
AND engine IS NOT NULL
AND ((update_time < (now() - INTERVAL 10 DAY)) OR update_time IS NULL);InnoDB
This is not supported in InnoDB so we have to check the actual database files on the server when they were last modified. The DB files are located in /var/lib/mysql/<dbname> , but you will have to enter root prompt in order to access this directory.
Run this command to see when the DB tables were last modified:
ls -l --time-style="+%Y-%m-%d" | awk '{print $6,$7}' | grep ibdLet’s break down the command a bit. We simply use the regular ls to list all files in the directory, then we change the date format to YYYY-MM-DD, then we print only filename+date using awk, then finally we grep (filter) by ibd (because all tables have two files, one .ibd file and one .frm file. – However, only the .ibd file gives the correct modified date).
Example output:
