I wish to discuss the creation and use of global variables for plugins.

Consider the following declaration of a global variable in audit_login:

static MYSQL_SYSVAR_BOOL(enabled, plugin_enabled, PLUGIN_VAR_NOCMDARG,
"enable/disable the plugin's operation, namely writing to file", NULL, NULL, 1);

static struct st_mysql_sys_var * audit_login_sysvars[] = {
    MYSQL_SYSVAR(enabled),
    NULL
};

The above creates a new global variables called “simple_login_audit_enabled”: it is composed of the plugin name (known to be “simple_login_audit” in our example) and declared name (“enabled”). It is a boolean, defaults to 1, and is associated with the internal plugin_enabled variable.

Once this variable is declared, you can expect to be able to:

mysql> show global variables like '%audit%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| simple_login_audit_enabled | ON    |
+----------------------------+-------+

mysql> set global simple_login_audit_enabled := 0;
Query OK, 0 rows affected (0.00 sec)

and you can expect using the following in your my.cnf file:

[mysqld]
...
simple_login_audit_enabled=1

Assuming your server agrees to start

Here’s the catch: the simple_login_audit_enabled variable is only recognized as long as the plugin is installed. As you may know, plugins can be loaded upon startup time using an explicit my.cnf entry such as:

plugin_load=audit_login.so

But you may also, at any given time, INSTALL or UNINSTALL the plugin dynamically.

install plugin SIMPLE_LOGIN_AUDIT soname 'audit_login.so';
uninstall plugin SIMPLE_LOGIN_AUDIT;

In fact there are good reasons to do so: you may be upgrading your plugin. You can’t just throw in the new binary (it’s a guaranteed crash on next server shutdown). You need to first UNINSTALL it; you then put the new binary, and re-INSTALL. This works well, and the price is some downtime for your plugin.

But what happens if you restart the server while your plugin is uninstalled? Yep: the global variable is unrecognised, and your MySQL server refuses to start:

130919  8:11:30 [ERROR] /usr/bin/mysqld: unknown variable 'simple_login_audit_enabled=1'
130919  8:11:30 [ERROR] Aborting
130919  8:11:30  InnoDB: Starting shutdown...
130919  8:11:31  InnoDB: Shutdown completed; log sequence number 40185651
130919  8:11:31 [Note] /usr/bin/mysqld: Shutdown complete

I did happen on this case a couple times; it is frustrating.

What are the alternatives?

So adding variables to my.cnf may prevent MySQL from starting. In my dictionary, this spells “NO GO”.

With audit_login I chose to (additionally) support an external config file, audit_login.cnf, expected to be found in the @@datadir. It is similar in essence to the master.info file which is expected by replication. The plugin reads this file (if existing) upon init(), which makes it execute upon server startup or upon INSTALL PLUGIN. I can’t argue that this is the best solution, but it is a solution that does not interfere with anyone. The file is ignored by all and does not disturb the public peace. The plugin does not require it to exist.

I was hoping to be able to directly read from my.cnf, but am unsure if there is a definitive way to do so from within the plugin. I did not go deep into this.

What would be best?

Hopefully I’m not missing on anything. But it would be nice to have plugin-dedicated variables in my.cnf which are ignored by the server. These could take the form of:

[mysql_plugin]
simple_login_audit_enabled=0

or similar. It would be the server’s responsibility to pass these declarations to the plugins, but ignore them itself (or just pass warning).

It seems that audit plugins are all the rage lately… We’ve developed out simple plugin a month ago as part of our database securing efforts; by auditing any login or login attempt we could either intercept or later investigate suspicious logins.

However we quickly realized there is much more to be gathered by this info.

In very short, you install this plugin onto your MySQL server, and your server starts writing into a text file called audit_login.log entries such as follows:

{"ts":"2013-09-11 09:11:47","type":"successful_login","myhost":"gromit03","thread":"74153868","user":"web_user","priv_user":"web_user","host":"web-87.localdomain","ip":"10.0.0.87"}
{"ts":"2013-09-11 09:11:55","type":"failed_login","myhost":"gromit03","thread":"74153869","user":"backup_user","priv_user":"","host":"web-32","ip":"10.0.0.32"}
{"ts":"2013-09-11 09:11:57","type":"failed_login","myhost":"gromit03","thread":"74153870","user":"backup_user","priv_user":"","host":"web-32","ip":"10.0.0.32"}
{"ts":"2013-09-11 09:12:48","type":"successful_login","myhost":"gromit03","thread":"74153871","user":"root","priv_user":"root","host":"localhost","ip":"10.0.0.111"}
{"ts":"2013-09-11 09:13:26","type":"successful_login","myhost":"gromit03","thread":"74153872","user":"web_user","priv_user":"web_user","host":"web-11.localdomain","ip":"10.0.0.11"}
{"ts":"2013-09-11 09:13:44","type":"successful_login","myhost":"gromit03","thread":"74153873","user":"web_user","priv_user":"web_user","host":"web-40.localdomain","ip":"10.0.0.40"}
{"ts":"2013-09-11 09:13:51","type":"successful_login","myhost":"gromit03","thread":"74153874","user":"web_user","priv_user":"web_user","host":"web-03.localdomain","ip":"10.0.0.03"}
{"ts":"2013-09-11 09:14:09","type":"successful_login","myhost":"gromit03","thread":"74153875","user":"web_user","priv_user":"web_user","host":"web-40.localdomain","ip":"10.0.0.40"}
{"ts":"2013-09-11 10:55:25","type":"successful_login","myhost":"gromit03","thread":"74153876","user":"web_user","priv_user":"web_user","host":"web-87.localdomain","ip":"10.0.0.87"}
{"ts":"2013-09-11 10:55:59","type":"successful_login","myhost":"gromit03","thread":"74153877","user":"web_user","priv_user":"web_user","host":"web-12.localdomain","ip":"10.0.0.12"}
{"ts":"2013-09-11 10:55:59","type":"failed_login","myhost":"gromit03","thread":"74153878","user":"(null)","priv_user":"(null)","host":"(null)","ip":"10.0.0.1"}

In the above your MySQL server is on gromit03, and is accepting connections from other hosts; some successful, some not. What kind of information can you gather from the above?

• You can tell how many connections are being created on your server
• Where they came from
• Where ‘root’ connections come from
• Port scans (see last row) can be identified by no credentials. These don’t have to be port scans per se; any telnet localhost 3006 followed by Ctrl+D will show the same. Typically these would be either load balancer or monitoring tools checks to see that the 3306 port is active.
• You can tell which accounts connect, and how many times
• And you can infer which accounts are stale and can be dropped — if an account does not connect within a week’s time, it’s probably stale (pick your own timeframe)

The above is quite interesting on one host; but we have dozens. We’ve installed this plugin on all our MySQL servers, and we use logstash to aggregate them. We aggregate to two destinations:

• All logs are being sent over to kibana, where they are easily searchable via lucene queries
• They are also all aggregated into one CSV formatted logfile, rotating daily; this format makes it easier for me to grep and uniq -c and generally produce very valuable data by scripting.

By aggregating the logs from multiple hosts, we are able to gather such insight as:

• How well our load balancers are distributing connections
• How our various applications servers connect on our multiple data centres to our MySQL hosts
• Catching misbehaving apps with hardcoded server IPs
• Catching naughty developers logging into our masters directly (we have more mechanisms now to prevent and audit such cases)
• Identifying differences in behaviour of monitoring tools across our data centres
• etc.

Free to use

The audit_login plugin has been made free to use, open source, licensed under GNU GPL v2. It is publicly available in our github repository, where more documentation on build and installation can be found.

As MySQL plugins go, you must compile your plugin with your particular MySQL version. For you convenience, pre-compiled binaries are available in the repository (at this time for 5.5.32 and 5.5.21 linux 64 bit; likely to add more)

MySQL plugins are a tricky business. To create a plugin, you must compile it against the MySQL version you wish the users to use it with. Theoretically, you should compile it against any existing MySQL version, minors as well (I’m not sure whether it may sometimes or most times work across minor versions).

But, most important, you must adapt your plugin to major versions.

Another option for plugin makers, is to actually not recompile it, but rather provide with the source code, and let the end user compile it with her own MySQL version. But here, too, the code must be compatible with whatever changes the new MySQL version may have.

And if it doesn’t compile with the new MySQL version?

That’s what happened to me. The particular case at hand was SphinxSE, a plugin which serves as a bridge between MySQL and a Sphinx Search server. I’ve been using it for years and was happy about it. But, as it happened, it took well over a year for sphinx to compile with MySQL 5,5. This meant I was unable to upgrade my 5.1 installation to 5.5, a thing I was aiming to do for quite a while.

Even when fixed, not all features were included, and thankfully I was able to come up with my own patch.

Not complaining about this particular project — I think Sphinx is awesome, with latest versions providing great features I’m putting into immediate use.

However, how much am I willing to rely on 3rd party projects when planning my MySQL upgrades? I’m now thinking I shouldn’t.

A central repository?

MariaDB does a great thing: it provides with several additional features over standard MySQL, including a set of plugins. They turn into a debian-like repository, in which they maintain the plugins for their own distribution.

[Darn! I just realized I should have looked at what they did with Sphinx in their 5.5 distribution! Need to do my monthly mental examination.]

Anyway, this is something I would like to see outside MariaDB as well: a central repository where plugins are maintained and kept up to the latest releases.

Thoughts, anyone?