Last login of MySQL database users

MySQL hosting providers can easily loose the overview over their customers and which user or schema is still in use and which not.

The MySQL database becomes bigger and bigger, uses more and more RAM and disk space and the backup takes longer and longer.

In this situation it would be nice to know which MySQL database user has logged in within the last 6 months for example. MySQL database users who did not login within a defined period can be backuped and removed from the production MySQL database.

The following MySQL login trigger helps to track the login of all non-super privileged MySQL users.

First we need a table where to log the login of the users:

-- DROP DATABASE tracking;
CREATE DATABASE tracking;

use tracking;

-- DROP TABLE IF EXISTS login_tracking;
CREATE TABLE login_tracking (
  user VARCHAR(16)
, host VARCHAR(60)
, ts TIMESTAMP
, PRIMARY KEY (user, host)
) engine = MyISAM;

Then we need a MySQL stored procedure which does the logging of the login:

-- DROP PROCEDURE IF EXISTS login_trigger;

DELIMITER //

CREATE PROCEDURE login_trigger()
SQL SECURITY DEFINER
BEGIN
  INSERT INTO login_tracking (user, host, ts)
  VALUES (SUBSTR(USER(), 1, instr(USER(), '@')-1), substr(USER(), instr(USER(), '@')+1), NOW())
  ON DUPLICATE KEY UPDATE ts = NOW();
END;

//
DELIMITER ;

Then we have to grant the EXECUTE privilege to all users of the database which do not have the SUPER privilege. MySQL users with the SUPER privilege are not logged with the init_connect login trigger hook:

-- REVOKE EXECUTE ON PROCEDURE tracking.login_trigger FROM 'oli'@'%';
GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO 'oli'@'%';

Those GRANTSs can be created with the following query:

tee /tmp/grants.sql
SELECT CONCAT("GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO '", user, "'@'", host, "';") AS query
  FROM mysql.user
 WHERE Super_priv = 'N';
notee

+---------------------------------------------------------------------------------+
| query                                                                           |
+---------------------------------------------------------------------------------+
| GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO 'oli'@'localhost';         |
| GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO 'replication'@'127.0.0.1'; |
| GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO 'oli'@'%';                 |
| GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO ''@'localhost';            |
+---------------------------------------------------------------------------------+

As the last step we have to activate the stored procedure by hooking it into the login trigger hook:

-- SET GLOBAL init_connect="";
SET GLOBAL init_connect="CALL tracking.login_trigger()";

If something went wrong with the login trigger you find the needed information in the MySQL error log.

Reporting

To find out, which users have logged in we can run the following query:

SELECT * FROM tracking.login_tracking;
+------+-----------+---------------------+
| user | host      | ts                  |
+------+-----------+---------------------+
| oli  | localhost | 2012-11-30 15:36:39 |
+------+-----------+---------------------+

To find at what time a user has logged in last you can run:

SELECT u.user, u.host, l.ts
  FROM mysql.user AS u
  LEFT JOIN tracking.login_tracking AS l ON u.user = l.user AND l.host = u.host
 WHERE u.Super_priv = 'N';

+-------------+-----------+---------------------+
| user        | host      | ts                  |
+-------------+-----------+---------------------+
| oli         | localhost | 2012-12-01 09:55:33 |
| replication | 127.0.0.1 | NULL                |
| crm         | 127.0.0.1 | NULL                |
+-------------+-----------+---------------------+

And to find users which are logged but could not be found from the mysql user table you can run:

SELECT l.user, l.host
  FROM tracking.login_tracking AS l
  LEFT JOIN mysql.user AS u ON u.user = l.user AND l.host = u.host
 WHERE u.user IS NULL;