So you have inherited a MySQL server and no one know what applications are use what databases. Space is every tight and every night transactions logs almost fill your hard disk. The server was once used as a replication server and it seems every database the company uses is duplicated here.
You could do a full dump, drop everything and wait for the requests to restore. (This might be OK on a test database.)
We could ask MySQL for all the databases that have tables that have been updated in the last 30 days.
$ mysql information_schema -e \ "select DISTINCT TABLE_SCHEMA as NAME from TABLES WHERE UPDATE_TIME IS NOT NULL and UPDATE_TIME > NOW() - INTERVAL 30 DAY" \ > Databases
But this only gives a list of databases that have changed. What about tables that are only read from?
Bin-Logs don’t help because they too only have updates. What we really need is a long snapshot of the …
[Read more]