How to log logon logoff and user activity on mySQL server for audit?

Question

My boss wants to have some kind of log on the server running mysql which would log user activity.

Kind of like:

1. User blah logged in
2. User blah ran some query
3. User blah logedd off
4. User asdf logged on
5. ..and so on

I did some research and found out about triggers: How to log successful login attempts to a database?

Also, is that information logged into the mysql general log?

More info:

• mysql is installed on a server running Redhat
• Installed mysql version is: 5.0.91

I'm not a DBA, so please go easy on me! Thanx in advance!

Answer 1

If you are willing to upgrade to MySQL 5.5, there is a way to store the general log as a MyISAM table. I have written about this before in past articles:

• Feb 24, 2011 : Audit logins on MySQL database
• Jun 30, 2011 : How do I output MySQL logs to syslog?
• Feb 11, 2012 : MySQL general log

Since you are using MySQL 5.0.91, you cannot configure the general log to be stored as a MyISAM table. You can only do so as a text file. Nevertheless, there have been other contributors in the DBA StackExchange that have suggested very good ideas for the text file version of the general log in your quest to audit DB Logins for MySQL 5.0. These suggestions may be more what you are looking for:

• Aug 26, 2011 : How do I output MySQL logs to syslog? from @JoelHanger
• Nov 16, 2011 : Log MySQL DB changing queries and users from @DTest