How to Analyse MySQL General Query Log in Real-Time

logMySQL

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 & 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…

Best Answer

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 > 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> 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> 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';" > ${SCRIPT}
echo "ALTER TABLE mysql.general_log RENAME mysql.${OLDLOG};" >> ${SCRIPT}
echo "CREATE TABLE mysql.general_log LIKE mysql.${OLDLOG};" >> ${SCRIPT}
echo "SET GLOBAL general_log = 'ON';" >> ${SCRIPT}
mysql ${MYSQL_CONN} < ${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 >= (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:

Give it a Try !!!

Related Question