2uzhan.com
Advertisement
Now Place:2uzhan.com » filter type when generalting general_log into table

filter type when generalting general_log into table

MySQL @ June 23, 2012   Views:0

Hi everyone:

I'm using MySQL 5.5.25-1 and enable general_log, log target is table mysql.general_log.

In fact, I just want to monitor those action about connect/authentication, but there is so many select/insert/update...log make the general_log table grow quickly in size and cause performance issue.

Is't possible to enable general_log, but only log those actions about CONNECT?

thanks!!

--------------Solutions-------------

>Is't possible to enable general_log, but only log those actions about CONNECT?

I am afraid this isn't possible, 'general log' captures everything that is sent to the MySQL server, however, you can query 'mysql.general_log.' table to extract required information, for example:

mysql> SELECT * FROM mysql.general_log WHERE command_type='Connect';

but I would convert general_log table storage engine to 'myisam' and add index on 'command_type' column:

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 `command_type` (`command_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log';

If space is an issue, you can disable 'general log' at anytime e.g. SET general_log=[1/0]; Alternatively you can purge older records but this may impact on your database performance. How long do you need general log enabled for ?

Tags:
© 2018 2uzhan.com Contact