login trigger

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.

A while ago I did some research about MySQL audit functionality and logon a and logoff triggers. MySQL and MariaDB provide a logon trigger in the form of the init_connect variable but no logoff trigger where most of the work for auditing would be done. When we would have a logoff trigger we could track the login and possibility some activity of a user and implement auditing functionality.

With MySQL 5.0 the database provides trigger functionality on INSERT, REPLACE, UPDATE and DELETE.

Those of you who know some other RDBMS know, that there are also some system events where one would like to have triggers.

Unfortunately MySQL does not (yet) provide such functionality. This is sad because as database administrator this would be sometimes very helpful.

But you can build your own LOGON and STARTUP trigger.

MySQL provides some hooks for these events...