Find Unused MySQL Databases

Here's a quick MySQL trick. Let's say you've inherited a legacy MySQL database server and you have no idea what databases are in use.

One method of determining what databases are old and dusty is to do the following:

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(MAX(UPDATE_TIME))) as last_update FROM information_schema.tables WHERE TABLE_SCHEMA='YourDatabaseNameHere' GROUP BY TABLE_SCHEMA;

That should get you something like this:

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(MAX(UPDATE_TIME))) as last_update FROM information_schema.tables WHERE TABLE_SCHEMA='blah' GROUP BY TABLE_SCHEMA; +---------------------+ | last_update | +---------------------+ | 2011-02-15 08:24:31 | +---------------------+ 1 row in set (0.05 sec) mysql>

That will let you see when the database was last updated. Then you can remove the ones that haven't been touched in ages.