|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
1. Create a table for login info (here we assume it will be in a
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
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