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.
@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…
Heh, it seems this wishlist item was implemented in record speed 🙂
http://karlssonondatabases.blogspot.com/2010/03/mysql-audit-plugin-api.html
HA! 😀
Gadb Your comment was so usefull for me “From there you can create alerts or do take any number of actions on its contents.”