MySQL Logging Activity from Specifit User or IP

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

Source : link