Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
Innodb doesn't write a value to the last_update field so this solution only works for myisam. –  Wolfe Oct 9 at 4:02

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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