mysql logging activity from specific user or ip

Question

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 ?

Answer 1

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:

• Jan 22, 2013 : How often does the slow_log table get updated in MySQL?
• Nov 10, 2012 : Redirecting MySQL errors and slow logs into another database
• Jan 08, 2012 : How to enable MySQL general log?
• Dec 27, 2011 : How to do MySQL User Accounting
• Nov 18, 2011 : Create a custom log table in MySQL
• Jun 30, 2011 : How do I output MySQL logs to syslog?
• Feb 24, 2011 : Audit logins on MySQL database

CAVEAT

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

Here are the options

• audit-log-buffer-size
• audit-log
• audit-log-file
• audit-log-flush
• audit-log-policy
• audit-log-rotate-on-size
• audit-log-strategy

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