Monday, March 22, 2010

The MySQL Audit plugin API

MySQl 5.5 is getting more and more solid, and one thing recently (at least I think so) pushed is the Audit plugin. Currently, this is only in the source repository (I tried from 5.5.4-m3). This prugin is actually quite useful, more useful than you might expect. Any event that would cause a write to the general query log will send a notification to a function in this API, and note that this will happen even if the general query log is enabled or not.

The stuff that gets passed to the notification function is a struct with similar data that gets written to an entry of the general query log, but don't let this limit you, also passed is a pointer to the THD class for the thread that caused the notification. And if you haven't looked a lot at the MySQL sourcecode, note that the THD class has MANY members, and contains all sorts of information.

If you wanted to create your own general log, that would allow filtering on, say, specific nodes, statements, users or something, this is where to hook into for that. Another advantage over using the general query log, is that you can log a lot more information, by virtue of the THD being passed.

Currently, there is a lack of documentation for the AUDIT plugin API, but you can figure it out from the null_audit plugin that is part of the 5.5.4-m3 source. The API is pretty simple, and only three functions are usually needed, one called when initializing the plugin, one to handle the event notification, and one that is called after when deinitalizing the plugin. A complete plugin that does this, that is based on the null_audit one, but which is written to support C++, as I want to use the THD which is a C++ class, this is a requirement if you want to get at the THD (unless you are cheating that is).

To begin with, we need some headers and stuff:
#include <../sql/mysql_priv.h>

#if !defined(__attribute__) && (defined(__cplusplus) || !defined(__GNUC__) || __GNUC__ == 2 && __GNUC_MINOR__ <>
#define __attribute__(A)

static FILE *log_fp;
static volatile int localhost_commands;

Then we need to init, deinit and notification functions:
static int my_general_log_plugin_init(void *arg __attribute__((unused)))
log_fp = NULL;
localhost_commands = 0;

static int my_general_log_plugin_deinit(void *arg __attribute__((unused)))

static void my_general_log_notify(MYSQL_THD thd, const struct mysql_event *event)
mysql_event_general *pEvent;

/* Open the log file, if not already done. */
if(log_fp == NULL)
log_fp = fopen("/tmp/my_general_log.log", "a");

/* Only log if on localhost. */
if(thd->net.vio->localhost && event->event_class == MYSQL_AUDIT_GENERAL_CLASS
&& log_fp != NULL)
pEvent = (mysql_event_general *) event;

if(pEvent->general_query != NULL && *(pEvent->general_query) != '\0')
fprintf(log_fp, "%s;\n\n", pEvent->general_query);

As you see, this is kept real simple, so as not to confuse things. But it is rather powerful, as I have both the event and the THD data available. Before I compile the above, I also need to add the structs that control the plugin itself:
static struct st_mysql_audit my_general_log_descriptor=
{(unsigned long) -1}

static struct st_mysql_show_var my_general_log_status[]=
{"my_general_log_commands", (char *) &localhost_commands, (enum_mysql_show_type) SHOW_INT},
{0,0,(enum_mysql_show_type) 0}

"Anders Karlsson",
"A general log that will log localhost access only",

The st_mysql_show_var struct provides a simple status value, that is displayed when you issue a SHOW STATUS command.

Beyond the above, you need a Makefile. I wrote mine myself, to keep things simple, and I also use no fancy macros, just the simple stuff:

g++ $(CFLAGS) -I$(MYSQL_BASE)/include -I$(MYSQL_BASE)/regex -I$(MYSQL_BASE)/sql -c my_general_log.o
g++ -shared my_general_log.o -o


If you are to try this yourself, you will need to modify the names and the paths and stuff, but I think you get the point and then run make and make install. The last thing to do is to enable the plugin. Just enter the mysql commandline client as root and run:
install plugin my_general_log soname '';

As foir the MySQL base source for this, either get 5.5.4-m3 from launchpad, or wait a few days and get it from when it is available there.

Hey, even being audited can be fun!



hingo said...

Interesting that you would post this today, I was just thinking about this 12 hours ago...

I can see this API becoming quite useful - at least in telco audit popped up every now and then, I'm sure banking etc is the same.

ht70363@ said...

We are very happy to play games regularly. It's great!
Friv 2
Friv 4

hahala said...

The best online games website has many more games in store for you!
Kizi 10
Kizi 100
Happy wheels

brianoconner7474 said...

Thanks for posting this. Your blog is so atractive and very informative and helpful.
Read Manga Online
Read Manga Online
Manga Online
Manga Online
Hooda Math

davidbeckham72 said...

It's very interesting. With this, and of course I'm interested.
Manga Kiss