Log MySQL logins

from the Artful MySQL Tips List


Since 5.7, the simplest way to do this is to connection control plugin installation.

For example, the plugin automates login failure tracking ...

1 Execute ...

INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
2 Enable it by listing it in my.cnf/ini under [mysqld]...
[mysqld]
...
plugin-load-add=connection_control.so
3 To see login failures, query information_schema.connection_control_failed_login_attempts.

Some time ago, Baron Schwarz described another 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.

Absent such methods, it's 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.

Last updated 11 Jul 2021


Return to the Artful MySQL Tips page