3

I want to determine when an event occurred in the General Query Log of MySQL (v5.1.71). As an example, on the server OS CLI (Unix), I ran a simple grep to pull out data from the log file:

grep "Access denied" /data/mysql/my_servername.log

            1249390 Connect Access denied for user 'user1'@'user.1.ip.add' (using password: YES)
131204 17:51:00 1254417 Connect Access denied for user 'user2'@'user.2.ip.add' (using password: NO)

I looked at the entire log for other types of information I would be pulling out, and noticed entries were missing timestamps as well. It appears MySQL only precedes the log entry with YYMMDD HH:MI:SS when the SS value has changed since the last log entry. In the above example, I would have no way of determing the DATE/TIME of user1's log entry, since I would only have the milli/nano/whatever second.

I'm currently running mysql with the following options: --user=mysql --general_log --ssl-ca=/path_to_file1 --ssl-cert=/path_to_file2 --ssl-key=/path_to_file3

So how do get MySQL to put a timestamp on every line?

SK

scryptKiddy
  • 427
  • 2
  • 9
  • 18

1 Answers1

3

You should use pt-query-digest for this.

Start with something like this:

pt-query-digest \
--type genlog \
--print \
--no-report \
--filter '$event->{arg} && $event->{arg} =~ /Access denied/' \
/data/mysql/my_servername.log

Alternatively, you can switch the log format from FILE to TABLE (do this in your my.cnf file too so it survives a restart), and the timestamp will be present for each row in the table:

set global log_output='TABLE';

Now you can query the table instead of grepping the file:

select * from mysql.general_log where argument like 'Access denied%';
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • That does look like a great solution, and I thank you for the response. However, I should have added that due to our organization policies here, I have to use something from MySQL, not an additional 3rd party tool. – scryptKiddy Dec 04 '13 at 21:48
  • I'm wondering if this suggestion to change the log to go to a table would work:[link]http://stackoverflow.com/questions/303994/log-all-queries-in-mysql?rq=1[/link] It appears to have a timestamp, but I'm not sure if it would insert a value to the column for each entry in the log or not. Thoughts? – scryptKiddy Dec 04 '13 at 22:08
  • Excellent! Few questions then. 1. The my.cnf setting is simply log_output = 'TABLE'; correct? 2. Table name has to be general_log (or else how does MySQL know which table to use?) 3. Does MySQL still log other types: errors, transaction (I'm using InnoDB) / restore, binary, etc to their respective files? In other words, doing this will only move General Query logging from a file to a table, not all the other logging as well, correct? – scryptKiddy Dec 04 '13 at 22:32
  • A1: You can put `log_output=TABLE` in /etc/my.cnf, just make sure it's in the `[mysqld]` section. A2: MySQL should create the tables for you. They are named `mysql.general_log` and `mysql.slow_log`. A3: log_output affects two logs: the general log and the slow query log (if enabled). It does not affect any other logs. – Ike Walker Dec 05 '13 at 21:40