Analyse MySQL General Query Log in Real-time? We want to use mysql general query log to do real-time monitoring and auditing.

Currently our approach is:


set general_log=on;
sleep 15m;
set general_log=off;
scp &amp; rm xxx.log;
set general_log=on;...


But the main problem is when turn on/off general log it'll cause a peak of slow query.

I also thought of another approach: turn on genlog; tail -f it and send out the log; periodically truncate the logfile (with "> xxx.log" or "cat /dev/null >xxx.log").

I'm wondering whether it's practical.

If only mysql would provide some built-in general log message queue stuff...
If you are really interesting in monitoring via the general log, there are two major approaches you can try:

APPROACH #1 : Rotating Logs

Let's say you have the following configuration in my.cnf:

[mysqld]
general-log
general-log-file=/var/log/mysql_general.log


You could rotate the general log with the following script:

MYSQL_CONN="-uroot -prootpassword"
DT=`date +"%Y%m%d"`
OLDLOG=mysql_general_${DT}.log
cd /var/log
mysql ${MYSQL_CONN} -e"SET GLOBAL general_log = 'OFF';"
cp mysql_general.log ${OLDLOG}
echo -n &gt; mysql_general.log
mysql ${MYSQL_CONN} -e"SET GLOBAL general_log = 'ON';"


As for your monitoring, you would scan the text file as you already do. You can always look back at previous copies of the general.log as archives.

APPROACH #2 : Make the General Log a MyISAM table

Try setting up the general log as MyISAM.

First let's configure the table for logging to tables

[mysqld]
general-log
log-output=TABLE


Next, MySQL supplies the table mysql.general_log. It looks like this:

mysql&gt; 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' 


Of course, the general log as a CSV table is hardly useful. You can do two things:


Turn it into a MyISAM table
Index it however you need, starting with the event_time


Here is that code:

SET @old_log_state = @@global.general_log; 
SET GLOBAL general_log = 'OFF'; 
ALTER TABLE mysql.general_log ENGINE = MyISAM; 
ALTER TABLE mysql.general_log ADD INDEX (event_time); 
SET GLOBAL general_log = @old_log_state; 


When the smoke clears, mysql.general_log now looks like this:

mysql&gt; show create table 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,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)


Events get recorded in this table instead of the text file. Naturally, you want to rotate this as well. Here is that script

SCRIPT=/tmp/rotate_general_log_table.sql
MYSQL_CONN="-uroot -prootpassword"
DT=`date +"%Y%m%d"`
OLDLOG=general_log_${DT}
echo "SET GLOBAL general_log = 'OFF';" &gt; ${SCRIPT}
echo "ALTER TABLE mysql.general_log RENAME mysql.${OLDLOG};" &gt;&gt; ${SCRIPT}
echo "CREATE TABLE mysql.general_log LIKE mysql.${OLDLOG};" &gt;&gt; ${SCRIPT}
echo "SET GLOBAL general_log = 'ON';" &gt;&gt; ${SCRIPT}
mysql ${MYSQL_CONN} &lt; ${SCRIPT}


As for your monitoring, you would query the mysql.general_log table like this:

SELECT * FROM mysql.general_log WHERE argument LIKE '...';


Perhaps yo u may want to check the last 4 hours

SELECT * FROM mysql.general_log
WHERE event_time &gt;= (NOW() - INTERVAL 4 HOUR)
AND argument LIKE '...';


CAVEAT

If you want both the text file and the table, then configure them both like this:

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


Of course, you would have to implement the rotation of both.

I have written other past posts about mysql.general_log:


Apr 04, 2013 : Stress test MySQL with queries captured with general log in MySQL
Feb 11, 2012 : MySQL general log
Jan 08, 2012 : How to enable MySQL general log?
Jun 30, 2011 : How do I output MySQL logs to syslog?


Give it a Try !!!