mod_log_mysql is a module for the Apache 2 webserver which permits request logging into a MySQL database (Apache 1 users see Chris Powell's mod_log_sql).
Key features are:
srclib/apr-util/misc/apr_reslist.c, select any version
newer than or equal to 1.5, click "view", then "download". Next, copy over your existing apr_reslist.c and recompile Apache 2.
In order to build mod_log_mysql you will have to copy mod_log_config.c and mod_log_config.h into the directory
modules/loggers of your Apache source. Second, do not forget to replace your old srclib/apr-util/misc/apr_reslist.c (see above)!
Next, build your Apache as usual.
Installation as shared module is recommended so you can keep and update this third-party module separate from the server (or update the
server separate from third-party modules, if you like :-).
Drop mod_log_mysql.c into a directory of your choice and use Apache's apxs tool. Example:
apxs -a -i -c -L/usr/mysql/lib -I/usr/mysql/include -lmysqlclient_r mod_log_mysql.c
In case you're using a threaded Apache, make sure you link with MySQL's thread-safe client library libmysqlclient_r,
not libmysqlclient. Although mod_log_mysql itself is thread-safe, the thread-safe
MySQL library will give you an extra bit of security.
See How to make a threaded
client in the MySQL documentation.
If compiled as shared module, don't forget to add the following line to your webserver configuration (if apxs did not already):
LoadModule log_mysql_module [path to your mod_log_mysql.so, usually "modules/mod_log_mysql.so"]
mod_log_mysql registers itself automatically with the modular mod_log_config. To select MySQL as target, use a URI of the following form in mod_mod_log_config's CustomLog or TransferLog:
mysql:[user[!password]][@[host][:port]][/database]
or
mysql:[user[!password]][@[host][:socket/path/]][/database]
Items in square brackets can be omitted.
Notice the way a Unix file system socket is handled in combination with a database, this is a bit tricky: The socket path must always end with a slash, even though it's of course not a directory. Second, the database name must start with a slash (but not end with one). However, if you enter both a socket path and a database name, you must not enter double slashes. Confused? Here are some examples:
That said, mod_log_mysql reads defaults for all items of this URI from the sections [client] and
[mod_log_mysql] (in that order) of your MySQL configuration file (usually /etc/my.cnf).
If MySQL is set up accordingly, you can ommit at least the host and port/socket parameters. Please
see your MySQL documentation to read more about configuration options for MySQL clients.
It's up to you what to store in your logs and how you do that, the pattern given to mod_mod_log_config's
CustomLog or
LogFormat can be any SQL command.
Both CustomLog and LogFormat work the same way as they always did.
For information
about the possible % directives, which log the characteristics of the request, see
Custom Log Formats in the original mod_log_config
documentation as well as mod_log_io,
mod_ssl and possibly other modules.
There is a special argument to all string directives named mysqlname. Its use is to convert the data to
insert into a valid MySQL column or table name by simply removing anything that is not a letter
or a number, i. e. anything not alphanumeric. This way you can use the server name directive %v
to select the table that stores the data for the host in question.
LogFormat "insert into %{mysqlname}v set host=%h,time=%t,method=%m,url=%R,status=%>s" mysql
Please note that no quotes are needed around strings since mod_log_mysql inserts these itself. This is due to the fact that mod_log_mysql may store NULL values if the requested item is not applicable, for example if the HTTP client did not send a referrer.
There is only one configuration directive and on a stable system, you do not even need it. Cool, isn't it? :)
In case the connection to the database server is down, mod_log_mysql can write the SQL commands into files, one per log target.
LogMySQLFallback sets the directory in which these files will be created. The second argument - if given - sets the time between
two connection retries in seconds. The default is 30 seconds, i. e. mod_log_mysql will only try to reconnect every 30 seconds.
mod_log_mysql will leave a message in the server's ErrorLog whenever the connection fails and when the connection comes back. The module will not automatically send the data it wrote into the fallback files to the database if the connection is up again. This might change in future releases, but for now you can simply parse the files with the mysql command line tool:
mysql -p -D httpd < /var/log/apache2/bitbrook_de
As mentioned above, mod_log_mysql uses one file per log target. The format is simply the log URI given to CustomLog or TransferLog with any unsafe characters replaced by an underscore.
mod_log_mysql has been written by Sönke Tesch. Feedback and comments welcome, please use the contact form or the email address at http://kino-fahrplan.de/intern/kontakt.