Skip to content

# oak-hook-general-log: streaming general log

MySQL logs openark kit python scripts March 21, 2010March 21, 2010

I’m seeking input on a new openark kit utility I’ve started to implement.

The tool, oak-hook-general-log, will hook up to a MySQL (>= 5.1) server, and stream the general log into standard output. It looks like this:

>     bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root
>     2010-03-21 10:18:42     root[root] @ localhost []       79      1       Query   SELECT COUNT(*) FROM City
>     2010-03-21 10:18:48     root[root] @ localhost []       79      1       Query   DELETE FROM City WHERE id=1000
>     2010-03-21 10:18:54     root[root] @ localhost []       79      1       Query   SHOW PROCESSLIST
>     2010-03-21 10:19:06     root[root] @ localhost []       79      1       Quit
>     2010-03-21 10:19:07     root[root] @ localhost []       93      1       Connect root@localhost on
>     2010-03-21 10:19:07     root[root] @ localhost []       93      1       Query   select @@version_comment limit 1
>     2010-03-21 10:22:33     root[root] @ localhost []       93      1       Query   SELECT City.Name, Country.Name FROM Country JOIN City ON Country.Capit
>     2010-03-21 10:22:58     root[root] @ localhost []       93      1       Quit

Since output is written to stdout, one can further:

>     bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root | grep Connect
>     bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root | grep webuser@webhost

What the tool does is to enable table logs, and periodically rotate the mysql.general_log table, read and dump its content.

The tool:

- Stores and restores the original log state (general log enabled/disabled, log output).
- Disables printing of its own queries to the general log.
- Automatically times out (timeout configurable) so as not to enter a situation where the general log is forgotten to be turned on.
- Can discard pre-existing data on the mysql.general_log table.
- Will cleanup the mysql.slow_log table, if it wasn’t originally used (turning on table logs applies to both general log and slow log).

What would you have the tool do further? Should it provide filtering, or should we just use grep/sed/awk for that? Any internal aggregation of data?

I would love to hear your thoughts. Meanwhile, view or grab the python script file.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
9
Leave a Reply

9 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
5 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Giuseppe Maxia

Careful with table logs. They take a huge toll on performance. While general log on a file accounts for 12% to 20% speed loss, table logs can slow your server down 65%, which is quite a blow!

Giuseppe

Author
shlomi

Hi Giuseppe,

Indeed. Nevertheless, on a recent job I did I had the need to monitor the general log. And there’s a few other cases I have in mind.

This is the reason why the tool forces you to timeout, and does not leave the general log open.

regards

Author
shlomi

BTW,

Is the 65% slowdown measured for CSV tables or MyISAM?

Guest
gba

You could pipe the data into a file and consume it with Splunk this will index the log data and make it searchable. From there you can create alerts or do take any number of actions on its contents.

e.g.:

$ python src/oak/oak-hook-general-log.py –socket=/tmp/mysql.sock –user=root > /var/log/general.log
$ splunk add monitor /var/log/general.log

Guest
Henrik Ingo

I’ve often thought that the next “destination” for general log should be just a socket. A tool like this could then connect to it and receive the data. No need to write to table or file or anything on disk. If nobody is listening, then the log disappears into cyberspace.

Should be a fun project for someone…

Author
shlomi

@gba
Thanks. For this case my tool would not be needed. Just use the normal general log file.

@Henrik,
I agree. What I would like to see is a hooking mechanism for all logs. So that one is able to hook up a streaming listener on the slow log, or a socket listener, or a file listener, or… you get my point.
The current status is too hard coded. Either there’s a log file or a log table or both. But if there’s log table then it applies to slow log as well. And you can’t stream it…

Guest
Henrik Ingo

Heh, it seems this wishlist item was implemented in record speed 🙂

http://karlssonondatabases.blogspot.com/2010/03/mysql-audit-plugin-api.html

Author
shlomi

HA! 😀

Guest
mensajes claro

Gadb Your comment was so usefull for me “From there you can create alerts or do take any number of actions on its contents.”

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.