Log MySQL logins

from the Artful MySQL Tips List

Usually this job is best done in the application layer. To solve a temporary problem, enabling the general query log will log MySQL logins, but that will impact performance on a production server. Baron Schwarz describes one other method. The MySQL init-connect setting will execute a MySQL command every time a non-super user logs in, so you can track non-super-user logins with this: 1. Create a table for login info (here we assume it will be in a sys DB):
CREATE TABLE sys.connections (
  connect_time DATETIME NOT NULL, 
  user_host VARCHAR(50) NOT NULL, 
  connection_id INT UNSIGNED NOT NULL, 
  UNIQUE INDEX idx_connect_time_user_host (connect_time, user_host)
2. In my.cnf/ini set the init_connect variable:
SET GLOBAL init_connect = 
   "INSERT INTO sys.connections (connect_time, user, connection_id) 
   VALUES (Now(), Current_User(), Connection_Id());";
3. Ensure that all users have Insert permission on the table you created in #1.

Return to the Artful MySQL Tips page