We have an “untouchable” application here, a huge Java whatsit which, among
a gazillion other things, stores hostnames, IP, and hardware addresses in
a MySQL database. My task is “simple”: plug a BIND nameserver onto that
and ensure a timely addition of Address (A
) resource records into a DNS zone.
I’m suggesting we implement this as a two-part process:
- The first part will be a MySQL User Defined Function (UDF) – a function in a shared object that can be dynamically added to (and removed from) MySQL. The function will record a database operation (update, insert, delete) in a file on the file system. (Somewhere along the line, MySQL renamed these to Loadable Functions to better indicate what they actually are.)
- The second part will handle these notification files asynchronously and issue the dynamic DNS updates. Here again, I’m not going to “poll” a filesystem directory – instead relying on the inotify subsystem informing me when a file is created by the UDF. (The UDF could update the DNS directly, but I prefer keeping a bit of code that runs in MySQL’s address space as simple as possible.)
User Defined Function in MySQL
Adding a UDF to MySQL is not terribly difficult. You basically have to
- Create a shared object containing your function(s).
- Create a MySQL function to load the shared object into MySQL’s address space.
- Use the UDF.
Here’s the source of my function. (Some of you may recall I showed basically the same UDF in Appendix F. of my book, where we used that to add a zone clause to a configuration file when a new zone is added to a database back-end.)
/* tmod.c (C)2011 by Jan-Piet Mens. MySQL UDF
* gcc -Wall -fPIC -I/usr/include/mysql -shared -o /usr/lib64/tmod.so tmod.c
*/
#include <stdio.h>
#include <string.h>
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <m_string.h>
#include <time.h>
#define TOUCHDIR "/tmp/tmod" // must be writable by mysql!
#define BLEN 1024
static pthread_mutex_t LOCK_tmod;
/* mysql> CREATE FUNCTION tmod RETURNS INTEGER SONAME "tmod.so"; */
my_bool tmod_init(UDF_INIT *iid, UDF_ARGS *args, char *message)
{
if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT) {
strmov(message,"single arg must be STRING");
return 1;
}
iid->max_length = BLEN;
iid->maybe_null = 0;
pthread_mutex_init(&LOCK_tmod, MY_MUTEX_INIT_SLOW);
return 0;
}
void tmod_deinit(UDF_INIT *iid)
{
pthread_mutex_destroy(&LOCK_tmod);
}
longlong tmod(UDF_INIT *iid, UDF_ARGS *args, char *isnull, char *error)
{
uint len;
char yaml[BLEN], path[BLEN], pfmt[64];
FILE *fp;
struct timeval tv;
struct tm *tm;
if (!args->args[0] || !(len = args->lengths[0])) {
*isnull = 1;
return 0;
}
len = (len >= sizeof(yaml)) ? sizeof(yaml) - 1 : len;
memcpy(yaml, args->args[0], len);
yaml[len] = 0;
pthread_mutex_lock(&LOCK_tmod);
/* Construct filename */
gettimeofday(&tv, NULL);
tm = localtime(&tv.tv_sec);
strftime(pfmt, sizeof(pfmt) - 1, "%%s/%F.%H:%M:%S.%%06u", tm);
sprintf(path, pfmt, TOUCHDIR, tv.tv_usec);
if ((fp = fopen(path, "w")) != NULL) {
fprintf(fp, "%s\n", yaml);
fclose(fp);
}
pthread_mutex_unlock(&LOCK_tmod);
return 1L;
}
The UDF I’m creating here returns a single integer value because I don’t need more, but a UDF is free to return a string. The function takes a string and writes that into a new file on the file system. The filename is constructed from the current timestamp and the file’s content is the string passed to it.
MySQL database trigger
After building the UDF I add it and a required database trigger to the
database. I’m discussing INSERT
only, but you get the idea of what is
required for UPDATE
and DELETE
:
CREATE FUNCTION tmod RETURNS INTEGER SONAME "tmod.so";
DROP TRIGGER IF EXISTS tmodTrigINS;
DELIMITER $$
CREATE TRIGGER tmodTrigINS AFTER INSERT ON t
FOR EACH ROW
BEGIN
SET @error = tmod(CONCAT("---\nop: ins\nid: ", NEW.id,
"\nmac: ", NEW.mac, "\nip: ", NEW.ip,
"\nhost: ", NEW.hostname));
END $$
DELIMITER ;
That completes the preparation: we’ve loaded the UDF into MySQL and have
created a trigger that will fire the UDF upon inserting a row. The trigger
formats the column values as a YAML string using MySQL’s CONCAT()
function. Will it blend?
INSERT INTO t VALUES (NULL, '00:00:00:01:02:03', '192.168.1.98', 'www');
Query OK, 1 row affected (0.00 sec)
SELECT * FROM t;
+----+-------------------+--------------+----------+
| id | mac | ip | hostname |
+----+-------------------+--------------+----------+
| 3 | 00:00:00:01:02:03 | 192.168.1.98 | www |
+----+-------------------+--------------+----------+
As soon as I insert the row into the database table, a new file shows up in the
drop directory: cat 2011-08-03.18:14:10.974320
---
op: ins
id: 3
mac: 00:00:00:01:02:03
ip: 192.168.1.98
host: www
I create similar triggers for UPDATE
and DELETE
operations. So after each
database manipulation on the table, I get a new YAML file created for
it. But how do I process those files in a timely fashion?
Inotify and Python
Thanks to Linux’ inotify subsystem, we can easily react to files created in the drop directory by the UDF. I’ll show you an example using pyinotify, but you could just as well use any other tool with inotify support. (You may wish to look at the incron, lsyncd, pirsyncd, or watchdog projects for inspiration.)
The Python program notif.py
follows:
#!/usr/bin/env python
import pyinotify
import yaml
import dns.update
import dns.query
import dns.tsigkeyring
from pprint import pprint as pp
config = {
'dnsserver' : '127.0.0.1',
'dnsdomain' : 'example.net',
'ttl' : 3600,
'dnskeyname' : 'doll',
'dnskeyblob' : '3Bwrezcc5 ... 1HYstfzmPDQ=='
}
def dnsupdate(nodename, ip, dnsdomain="example.net"):
keyring = dns.tsigkeyring.from_text(
{ config['dnskeyname'] : config['dnskeyblob'] });
update = dns.update.Update(dnsdomain,
keyring = keyring,
keyname = config['dnskeyname'],
keyalgorithm = 'hmac-sha1')
domain = '%s.%s.' % (nodename, dnsdomain)
update.replace(domain, config['ttl'], 'A', ip)
response = dns.query.udp(update, config['dnsserver'], timeout=10)
print dns.rcode.to_text(response.rcode())
def dofile(filename):
y = yaml.load(open(filename, 'r'))
pp(y)
if y['op'] == 'ins':
dnsupdate(y['host'], y['ip'])
if __name__ == "__main__":
wm = pyinotify.WatchManager()
mask = pyinotify.IN_CLOSE_WRITE
class EventHandler(pyinotify.ProcessEvent):
def process_IN_CLOSE_WRITE(self, event):
print "Gotcha:", event.pathname
dofile(event.pathname)
handler = EventHandler()
notifier = pyinotify.Notifier(wm, handler)
wdd = wm.add_watch('/tmp/tmod', mask, rec=False)
notifier.loop()
The program sets up a notification handler (basically the tutorial
example) waiting for events of type IN_CLOSE_WRITE
which are fired
when writable files have been closed (i.e. writing to the file is complete).
When an event is received, the EventHandler()
calls dofile()
to open the
YAML file and process it. In this case, processing means check what
type of database operation (insert, update, delete) was recorded and proceed
to update the DNS accordingly. (The example above handles DNS add/replace only.)
Gotcha: /tmp/tmod/2011-08-03.18:14:10.974320
{'host': 'www',
'id': 3,
'ip': '192.168.1.98',
'mac': '00:00:00:01:02:03',
'op': 'ins'}
NOERROR
Please note:
- There is no way of returning an error to MySQL at this point: the database server has long completed its data manipulation operation and is busy doing other things. :)
- The
notif.py
should really clean up the files it has processed, e.g. by moving them into a “done” directory or simplyunlink()
ing them. - The spurious
NOERROR
above, is the result of the dynamic DNS update.
This architecture is a bit complex but in contrast to having a program poll the
database for changes (and having to remember which updates it has already
processed, particularly when it is first launched or restarted after a crash)
this method is “restartable”
in that we handle files in a file system which remain in place even if
notif.py
decides to die sometime. (A second similar utility would be needed
to handle start-up.)
To test the final prototype, I fired off a batch of 1,000 INSERTS to MySQL, and nothing got lost: my UDF created 1,000 files in its drop directory, my little pyinotify-based utility fired off 1,000 signed dynamic DNS updates, and the BIND name server we’re talking to added 1,000 new resource records to its zone.