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