Want to establish what MySQL DBs are "old"

Question

I want to put some (minimal) logging on our MySQL server, with the aim of determining which DBs on the server aren't being regularly accessed. We intend to log for approximately a month, and any DBs which aren't accessed by clients over this time we want to archive.

Could someone please point me in the right direction regarding this - most of the logging tutorials I found on the web were concerned with logging actual SQL queries etc., whereas I just want a "Connection made from Client IP X at Time Y to DB Z" logged.

Thanks in advance.

Regards,

Patrick

Answer 1

You can use init_connect parameter to execute query on user log-on, e.g.

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user, connection_id) VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

See this for more detailed explanation.