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 (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, 
  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