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.

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...

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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 !!!

share|improve this answer
    
I see another post of yours, saying tail and send out the general log, and truncate the log file at midnight. I'm wondering whether this "echo -n > general.log" operation will do any harm to mysql, or result in corrupted general log format? e.g. truncate when mysql is still logging into it, so there's only half of the statement, etc. –  Jerry Jun 24 '13 at 6:20
    
I do this for some clients now. This technique works well, especially when shut off the general log to do the switch. Truncating a disabled general log is trivial. The harm is eliminated. You should make sure the log does not get too big or the "echo -n" trick will take a bit of time. That's why I also suggest approach #2 for a quick switch and making the general log immediately svailable and operational. –  RolandoMySQLDBA Jun 24 '13 at 9:15

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.