mod_mysql_accesslog: MySQL Logging for lighttpd

mod_mysql_accesslog is a lighttpd 1.4 module that turns selected access-log fields into parameters of a MySQL statement. It was designed for direct database logging and for a proof-of-concept topology in which local MySQL instances write access events to their binary logs and replicate them to a central collector.

The complete C source is available from the mod_mysql_accesslog repository under the BSD 3-Clause license. The source is compact enough to study, but it targets an older lighttpd 1.4 internal plugin API. It is not a drop-in module for a current lighttpd source tree.

How the Module Works

At configuration time, the module parses mysql-accesslog.query. It keeps literal SQL and replaces each supported format field with a ? placeholder. When a request finishes, the handle_request_done hook binds the request values with mysql_stmt_bind_param() and executes the prepared statement.

This separation is important: request headers, URLs, and other logged values are not concatenated into SQL. They are passed as typed parameters. The configured SQL text remains trusted configuration, however, and must never be assembled from request input.

The parser also recognizes several legacy format letters that the writer does not populate, including remote-ident, local-address, cookie, and millisecond-duration variants. They become SQL NULL. Current mod_accesslog format fields are broader and do not have an exact one-to-one contract with this source.

Building the Original Source

For a compatible lighttpd source tree, copy mod_mysql_accesslog.c into src/ and add the original Automake target:

lib_LTLIBRARIES += mod_mysql_accesslog.la
mod_mysql_accesslog_la_SOURCES = mod_mysql_accesslog.c
mod_mysql_accesslog_la_LDFLAGS = -module -export-dynamic -avoid-version -no-undefined
mod_mysql_accesslog_la_LIBADD = $(MYSQL_LIBS) $(common_libadd)
mod_mysql_accesslog_la_CPPFLAGS = $(MYSQL_INCLUDE)

./configure --with-mysql
make clean
make
make install

These commands describe the source generation for which the module was written. A current port should begin with lighttpd's current mod_skeleton.c, use request_st rather than the old connection structure, adopt the current configuration-value API and module linker flags, and review every request-field accessor. IPv6 support also requires replacing the original direct read of the IPv4 member.

A Minimal Database Contract

The original documentation listed every possible column. A smaller table is easier to operate because its schema follows the fields actually selected by the configured statement:

CREATE TABLE accesslog (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  remote_host INT UNSIGNED NOT NULL,
  timestamp INT UNSIGNED NOT NULL,
  status SMALLINT UNSIGNED NOT NULL,
  user_agent VARCHAR(2048) NULL,
  query_string VARCHAR(2048) NULL,
  url VARCHAR(2048) NOT NULL,
  PRIMARY KEY (id),
  KEY accesslog_timestamp (timestamp),
  KEY accesslog_status (status)
) ENGINE=InnoDB;

remote_host matches the original module's IPv4-only integer binding. A port should store a 16-byte address representation and support both address families. Byte counters and high-resolution durations should likewise use 64-bit bindings and BIGINT UNSIGNED columns rather than the original 32-bit values.

Configuration

The module defines six directives. Use either a local socket or a TCP host, and place the module name in server.modules according to the surrounding lighttpd configuration:

server.modules += ( "mod_mysql_accesslog" )

mysql-accesslog.user = "lighttpd_logger"
mysql-accesslog.pass = "replace-with-a-secret"
mysql-accesslog.data = "logs"
mysql-accesslog.sock = "/run/mysqld/mysqld.sock"
# mysql-accesslog.host = "127.0.0.1"

mysql-accesslog.query = "INSERT INTO accesslog SET remote_host=%h, timestamp=%t, status=%s, user_agent=%{User-Agent}i, query_string=%q, url=%U"

The comma after remote_host=%h is required. It was missing from the legacy example, making that statement invalid SQL. Header names inside %{...}i or %{...}o are resolved by the module and their values are bound as parameters.

The Synchronous-I/O Boundary

The original request-completion hook calls mysql_stmt_prepare() and mysql_stmt_execute() for every logged request. Both calls are synchronous. Because lighttpd normally advances many connections from one event loop, a synchronous database operation delays unrelated requests handled by the same process. The reconnect option does not turn this path into a queue or make it asynchronous.

This is the principal operational limit of the design. A database outage, lock wait, network pause, or overloaded server can become web-serving latency. Any production port should either enqueue a bounded log record without blocking the event loop or keep database ingestion outside lighttpd entirely. The queue needs an explicit overflow policy, durable buffering where loss is unacceptable, and metrics for drops, backlog, and collector failures.

Use a dedicated least-privilege database account that can insert only into the intended schema. Keep credentials out of world-readable configuration, prefer a protected local socket when the database is local, and require authenticated encryption for remote database connections. Logged URLs, headers, and user identifiers can contain personal or secret data; apply retention, access-control, and redaction policies before collection.

Collecting Logs Through the Binary Log

The original scaling experiment placed a local MySQL instance next to each lighttpd server. The module inserted into a table using MySQL's BLACKHOLE storage engine. BLACKHOLE discarded the local row while MySQL's binary log retained the change for replication to a central server whose corresponding table used a persistent engine.

This removes a second local copy and lets replication buffer temporary collector disconnects, but it remains a proof of concept rather than a general logging recommendation. Binary-log format, BLACKHOLE behavior, replication filters, multi-source channels, retention, failover, and recovery all have to be tested with the exact MySQL release. A central replica is also not automatically a horizontally scalable analytics system.

A Current Deployment Direction

For a new system, let lighttpd's maintained mod_accesslog serialize the request and move transport and storage into a separate process. Current lighttpd can write a file, send to Syslog, or feed a piped logger. Since lighttpd 1.4.66, accesslog.escaping = "json" can produce records that a collector can parse without reconstructing an ad hoc text format:

server.modules += ( "mod_accesslog" )

accesslog.escaping = "json"
accesslog.format = "{ \"remote_addr\": \"%h\", \"request\": \"%r\", \"status\": %>s, \"bytes\": %b }"
accesslog.filename = "|/usr/local/sbin/accesslog-collector"

Syslog or a piped collector keeps the database client, retries, batching, and backpressure policy out of the web server module. The collector can write to MySQL when relational queries are genuinely useful, or to a log system designed for append-heavy ingestion. The original module remains useful as a compact example of parsing access-log fields into prepared MySQL parameters and as a precise specification for anyone maintaining a compatible deployment.

References

• [Source]mod_mysql_accesslog source repository, C source and BSD 3-Clause license.
• [LighttpdAccesslog]lighttpd mod_accesslog documentation, current output targets and format fields.
• [LighttpdPlugins]lighttpd plugin development documentation, current skeleton and request API.
• [MySQLBlackhole]MySQL Reference Manual: The BLACKHOLE Storage Engine.
• [MySQLReplication]MySQL Reference Manual: Replication.