Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have mysql server.

The server is accessed by my application, and by external auditor (person using mysql workbench). The auditor has specific user and password and dedicated IP and it is granted only for select privileges.

I need to log the activities from the auditor. Is it possible to do it somehow ? If not can I log the packages that are coming from the network ?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

You could use the general log. In fact, you should try using the MySQL table version of the general log.

If you run this:

SHOW CREATE TABLE mysql.general_log\G

You should see something like this:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.14 sec)

mysql>

The user_host column will record the MySQL user and the IP Address/DNS Name the command came from.

You are probably saying right now, "That table is a CSV file. I would have to parse it." That's true, you would have to. However, did you know that you can convert it to a MyISAM table? I have actually tried this out for one of my employer's DB Hosting Clients, and I wrote a post explaining it (See the Feb 24, 2011 post down below).

Here are the basic steps:

  1. Make the mysql.general_log table MyISAM

    Run the following:

    CREATE TABLE mysql.general_log_original LIKE mysql.general_log;
    ALTER TABLE mysql.general_log ENGINE=MyISAM;
    ALTER TABLE mysql.general_log ADD INDEX (event_time);
    
  2. Enable the general log

    Add the following to /etc/my.cnf

    [mysqld]
    log-output=TABLE
    general-log
    

    If you also want the text version of the general log, add this:

    [mysqld]
    log-output=TABLE,FILE
    general-log
    general-log-file=/var/log/mysql_general.log
    
  3. Restart mysql

    Simply run service mysql restart

I have many posts about using the general log and the slow log as MyISAM tables:

CAVEAT

MySQL 5.5.28 has recently introduced the audit log plugin and how to install it.

Here are the options

These are new to me so I have nothing pros or cons at this time on the Audit Log Plugin.

share|improve this answer
    
I new you would answer this :) , great answer, thanks. But will general log decrease performance (because there will be thousands queries there)? I tough that it is possible only for the specified user to be enabled the log. –  darpet Jan 28 '13 at 20:53
    
Look back at my Feb 24, 2011 post ( dba.stackexchange.com/a/1434/877 ). I mapped mysql.general_log to another disk using symlinks. That will reduced disk spindles. If mysql.general_log is on the same disk as the data, then it could be noticeably slow. –  RolandoMySQLDBA Jan 28 '13 at 20:57
    
I will also take a look at the audit log plugin - seems interesting. –  darpet Jan 28 '13 at 20:58
    
The audit log plugin is only an "Enterprise Edition" $$$ offering: dev.mysql.com/doc/refman/5.5/en/audit-log-plugin.html –  Michael - sqlbot Jan 28 '13 at 21:31
    
@Michael-sqlbot Oh that stinks !!! –  RolandoMySQLDBA Jan 28 '13 at 21:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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