Log MySQL logins

from the Artful MySQL Tips List


Since 5.7, the simplest way to do this is to install connection control plugins.

Otherwise this 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.

Last updated 24 Jul 2020


Return to the Artful MySQL Tips page