5

Is there any commands to look up databases that has no any activity in a month or so?

Just take SQL dumps for a month and run diff and if there's no differences delete that DB?

0

4 Answers 4

4

It depends on what you mean by activity? You wouldn't see updates to a database or changes to the backup for a database that has been designed to store read-only data. But is is possible that database is access frequently.

You could enable the mysql query log and see everything that is accessed, but that will come at the cost of performance.

You could probably do something Mysql Proxy. You could set it up with a bit of work to log all databases that have been accessed. This might have less of a performance impact then enabling logging of everything.

Just looking at the file dates of *.myd files would work for myisam databases provided that active meant that someone was writing too them.

2

A very handy query to show the last update time of a DB:

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

Thanks to http://james-berger.blogspot.fr/2013/04/find-unused-mysql-databases.html

1
  • Innodb doesn't write a value to the last_update field so this solution only works for myisam.
    – user134840
    Oct 9, 2014 at 4:02
1

Diffing SQL dumps will only show you if a database has been modified, not if it's been queried.

You could potentially use the slow query log to see what queries are being run against the database, although I can't be certain right now if they include the database the query was run against.

If you don't care about reads, you could use the MySQL replication logs to see if there's been any write activity against those databases.

0

Dumps wouldn't show no activity, they'd show lack of updates which is NOT the same thing.

I do not know if mysql continually touches the databases that it has open or not. If it doesn't, mtime should tell you. If it does, you'll probably have to look at the logs.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .