3

I am having a cleaning situation over here, the old programmer didn't clean up his out-of-use databases & users. While some of the databases are still in use by external sites (on other ftp-servers), some are obsolete and just cluttering up the system.

My question is: How can I figure out which databases (& users) are still in use by other websites? (without checking every website that has ever been created where ever this might be located)

I need to be sure that a MySQL database (& user) are not actively being used by any site anymore, so I can safely delete it to clean up the system.

p.s.: It could also be that a database is still in use, but doesn't do any INSERTs or UPDATEs at all, but only SELECTs the data to load the website.

p.p.s.: I can't (temporary) deactivate/remove databases (& users), because this will cause clients to lose revenue, customers, search ranking etc. etc. and at the end will cost us/me.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
K. Tromp
  • 350
  • 1
  • 13
  • 1
    RoFLoL!! You funny :-P No can't do.. doing those kind of things will costs clients to lose revenue and customers even search rankings, and at the end that will cost us/me – K. Tromp May 09 '17 at 11:05
  • you can try last_update on a certain DB? that gives you some insight and maybe a look at the full process list? – izk May 09 '17 at 11:12
  • As mentioned, it is not necessary to contain recently updated fields, it might just contain fields wich are created when building the website, and after that only being used for SELECTs – K. Tromp May 09 '17 at 11:19

1 Answers1

3

Well, if you want even selects I think your options is general query log https://dev.mysql.com/doc/refman/5.7/en/query-log.html Enable it, parse it and get used dbs/tables

Alexey
  • 2,388
  • 1
  • 16
  • 32
  • I think that is exactly what I was looking for, thanks. To bad that the general query log was disabled. But do you know how I can enable this by using PuTTy? (I can login as root) (I will also start up another question for this) – K. Tromp May 09 '17 at 11:46
  • 1
    You ssh into server, change MySQL config and will need a server restart (as you didn't enable it before, just setting ON or OFF won't be sufficient, you'll need to provide log path) – Alexey May 09 '17 at 11:55
  • In what directory/file should I add this, and how/what do I add in it? – K. Tromp May 09 '17 at 11:56
  • 1
    https://www.psce.com/blog/2012/04/01/how-to-find-mysql-configuration-file/ find conf file using this guide. then add `general_log_file = /path/to/query.log general_log = 1` to it – Alexey May 09 '17 at 12:05
  • 1
    there is a new line before `general_log ` (comments don't allow new lines) – Alexey May 09 '17 at 12:05