1

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!

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:

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:

5
  • thank you for your answer! I will look into these and get back to you!
    – seed_87
    Aug 17 '12 at 20:21
  • Okay, I activated the general log to see what kind of output it gives me and it seems like it has all I want. Been also searching for some kind of log parser to help me sort through all the data. Found these two: hackmysql.com/mysqlsla, myprofi.sourceforge.net. Any more you think i should maybe take a look at? Thank u rolando for ur help !
    – seed_87
    Aug 19 '12 at 21:31
  • If you are willing to upgrade to MySQL 5.5, you can have the general log as a MyISAM table. That way, not only is it parsed into a table, but you can also run queries against in terms of time and address. Aug 19 '12 at 21:35
  • Yeah that does sound good. Also the overhead of having to write a text file scares me.. I will see if upgrading is even an option for us since I (sadly) am not in charge of administrating servers.. I will get back to you on that. Thanks again for the quick and accurate responses :)
    – seed_87
    Aug 19 '12 at 21:58
  • Okay im back, turns out we can update to MySQL 5.5, ill read up on the MySQL documentation and go from there. Thank you Rolando for your help :)
    – seed_87
    Aug 22 '12 at 22:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.