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.

I can't seem to find any information on this anywhere. I found a question here called Monitor MySQL activity per database? but that's not quite the answer either.

I'd like to be able to log the following to a system log somewhere, preferably via syslog:

  • User (not master/slave) login
  • User logout
  • User connect time
  • User IP

Something similar to last and lastb for MySQL. Can this be done? Is anything already written and in the Ubuntu and Red Hat Enterprise Linux repositories? Can MaatKit do this?

Thanks!

share|improve this question
add comment

1 Answer

Your only recourse would be to activate the slow log and use it as a MyISAM table. By default, the slow log would normally be a text file. However, a general_log table was provided in newer release of MySQL as a CSV table.

mysql> use mysql
Database changed
mysql> show create table slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  KEY `start_time` (`start_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.03 sec)

mysql>

Make sure you have this in my.cnf and then restart mysql if you had to add this:

[mysqld]
log-output=TABLE
log-slow-queries
long-query-time=0.00001

Next, convert the table to MyISAM and index it on the needed columns:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX starttime_index (start_time);
ALTER TABLE mysql.slow_log ADD INDEX db_starttime_index (db,start_time);
SET GLOBAL slow_query_log = @old_log_state;
SHOW CREATE TABLE mysql.slow_log\G

You can query the table and retrieve the user and host from mysql.slow_log column user_host.

You can crontab some tasks to copy the data from mysql.slow_log where you need it, may a separate database.

CREATE DATABASE IF NOT EXISTS mystatus;

Perhaps, you can run queries like this:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log RENAME mystats.slow_log;
CREATE TABLE mysql.slow_log LIKE mystats.slow_log;
SET GLOBAL slow_query_log = @old_log_state;

This will move the slow log elsewhere and start with an empty one.

This may not be a full answer, but I hope this is useful to you as to some direction to take.

share|improve this answer
    
Interesting! Fantastic and well-written detailed description. Thanks! –  David Dec 28 '11 at 16:23
    
This isn't necessarily useful in my case (unfortunately) because the user logging in is also being used by the system application - unless I rule out the user running on localhost (hmmm...) I also note that the slow log time is fractional; I thought that was either introduced in a newer version of MySQL or was solely available from Percona as a patch or as part of their MySQL build. –  David Dec 28 '11 at 16:45
    
You saw this answer and commented positively on it. Do you think it will work for my purposes? –  David Dec 28 '11 at 16:51
    
@David : That answer from DTest may be just what you need. However, keep in mind that it will increase disk I/O very slightly and slow down the recording of binary log events with any surge of DB Connections. In a low-to-moderately trafficked site, this may not be a problem. –  RolandoMySQLDBA Dec 28 '11 at 17:14
add comment

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.