From f1fd1c9714256bb9b212462dd31ca6dc56ea31ef Mon Sep 17 00:00:00 2001 From: Edward Rudd Date: Thu, 21 Jul 2011 23:10:20 +0000 Subject: add in project web page --- (limited to 'docs-2.0') diff --git a/docs-2.0/index.xml b/docs-2.0/index.xml new file mode 100644 index 0000000..e04d194 --- /dev/null +++ b/docs-2.0/index.xml @@ -0,0 +1,11 @@ + + + + + + + Error loading XML Documentation + + + + diff --git a/docs-2.0/manual.xml b/docs-2.0/manual.xml new file mode 100644 index 0000000..9019e80 --- /dev/null +++ b/docs-2.0/manual.xml @@ -0,0 +1,3927 @@ + + +urkle <at> outoforder <dot> cc"> +]> +
+ + mod_log_sql Manual + + Edward + Rudd + Conversion from Lyx to DocBook + Current Maintainer + + + &EmailContact; + + + + + Christopher + B. + Powell + Original documentation author. + + + chris <at> grubbybaby <dot> com + + + + + 2001 + 2002 + 2003 + Christopher B. Powell + + + 2004 + 2005 + 2006 + Edward Rudd + + + + 1.5 + 2006-11-04 + Added documentation about logio parameters and added DBParam Mysql driver parameters (including tabletype) + + + 1.4 + 2006-02-13 + Added missing logformat types, switched to simplified docbook 1.1 + + + 1.3 + 2005-01-11 + Updated for mod_log_sql v1.100 + + + 1.2 + 2004-04-08 + Updated for mod_log_sql v1.97 + + + 1.1 + 2004-03-02 + Updated for mod_log_sql v1.96 + + + 1.0 + 2004-01-22 + Initial Conversion from Lyx to Docbook + + + +
+ Introduction +
+ Summary + + This Apache module will permit you to log to a SQL database; it + can log each access request as well as data associated with each + request: cookies, notes, and inbound/outbound headers. Unlike + logging to a flat text file -- which is standard in Apache -- a + SQL-based log exhibits tremendous flexibility and power of data + extraction. (See FAQ entry + + for further discussion and examples of the advantages to SQL.) + + + This module can either replace or happily coexist with + mod_log_config, Apache's text file logging facility. In addition + to being more configurable than the standard module, mod_log_sql + is much more flexible. + +
+
+ Approach + + This project was formerly known as "mod_log_mysql." It was + renamed "mod_log_sql" in order to reflect the project goal of + database in-specificity. The module currently supports MySQL, + but support for other database back-ends is underway. + + + In order to save speed and overhead, links are kept alive in + between queries. This module uses one dedicated SQL link per + httpd child, opened by each child process when it is born. Among + other things, this means that this module supports logging into + only one MySQL server, and for now, also, only one SQL database. + But that's a small tradeoff compared to the blinding speed of + this module. Error reporting is robust throughout the module and + will inform the administrator of database issues in the Apache + ErrorLog for the server/virtual server. + + + Virtual hosts are supported in the same manner they are in the + regular logging modules. The administrator defines some basic + 'global' directives in the main server config, then defines more + specific 'local' directives inside each VirtualHost stanza. + + + A robust "preserve" capability has now been implemented. This + permits the module to preserve any failed INSERT commands to a + local file on its machine. In any situation that the database is + unavailable -- e.g. the network fails or the database host is + rebooted -- mod_log_sql will note this in the error log and + begin appending its log entries to the preserve file (which is + created with the user and group ID of the running Apache + process, e.g. "nobody/nobody" on many Linux installations). When + database availability returns, mod_log_sql seamlessly resumes + logging to it. When convenient for the sysadmin, he/she can + easily import the preserve file into the database because it is + simply a series of SQL insert statements. + +
+
+ What gets logged by default? + + All the data that would be contained in the "Combined Log + Format" is logged by default, plus a little extra. Your best bet + is to begin by accepting this default, then later customize the + log configuration based on your needs. The documentation of the + run-time directives includes a full explanation of what you can + log, including examples -- see section + + . + +
+
+ Miscellaneous Notes + + + + Note which directives go in the 'main server config' and + which directives apply to the 'virtual host config'. This is + made clear in the directive documentation. + + + + + The 'time_stamp' field is stored in an UNSIGNED INTEGER + format, in the standard unix "seconds since the epoch" + format. This is superior to storing the access time as a + string due to size requirements: an UNSIGNED INT requires 4 + bytes, whereas an Apache date string (e.g. + "18/Nov/2001:13:59:52 -0800") requires 26 bytes: those extra + 22 bytes become significant when multiplied by thousands of + accesses on a busy server. Besides, an INT type is far more + flexible for comparisons, etc. + + + In MySQL 3.21 and above you can easily convert this to a + human readable format using from_unixtime(), e.g.: + + SELECT remote_host,request_uri,from_unixtime(time_stamp) +FROM access_log; + + The enclosed perl program "make_combined_log.pl" extracts + your access log in a format that is completely compatible + with the Combined Log Format. You can then feed this to your + favorite web log analysis tool. + + + + + The table's string values can be CHAR or VARCHAR, at a + length of your choice. VARCHAR is superior because it + truncates long strings; CHAR types are fixed-length and will + be padded with spaces, resulting in waste. Just like the + time_stamp issue described above, that kind of space waste + multiplies over thousands of records. + + + + + Be careful not to go overboard setting fields to NOT NULL. + If a field is marked NOT NULL then it must contain data in + the INSERT statement, or the INSERT will fail. These + mysterious failures can be quite frustrating and difficult + to debug. + + + + + When Apache logs a numeric field, it uses a '-' character to + mean "not applicable," e.g. the number of bytes returned on + a 304 (unchanged) request. Since '-' is an illegal character + in an SQL numeric field, such fields are assigned the value + 0 instead of '-' which, of course, makes perfect sense + anyway. + + + +
+
+ Author / Maintainer + + The actual logging code was taken from the already existing flat + file text modules, so all that credit goes to the Apache + Software Foundation. + + + The MySQL routines and directives were added by Zeev Suraski + <bourbon@netvision.net.il>. + + + All changes from 1.06+ and the new documentation were added by + Chris Powell + chris <at> grubbybaby <dot> com + . It seems that the module had fallen into the "un-maintained" + category -- it had not been updated since 1998 -- so Chris + adopted it as the new maintainer. + + + In December of 2003, Edward Rudd + &EmailContact; + porting the module to Apache 2.0, cleaning up the code, + converting the documentation to DocBook, optimizing the main + logging loop, and added the much anticipated database + abstraction layer. + + + As of February 2004, Chris Powell handed over maintenance of the + module over to Edward Rudd. So you should contact Edward Rudd + about the module from now on. + +
+
+ Mailing Lists + + A general discussion and support mailing list is provided for + mod_log_sq at lists.outoforder.cc. To subscribe to the mailing + list send a blank e-mail to + mod_log_sql-subscribe@lists.outoforder.cc. The list archives can + be accessed via Gmane.org's mailng list gateway via any new + reader + + news://news.gmane.org/gmane.comp.apache.mod-log-sql + + , or via a web browser at + + http://news.gmane.org/gmane.comp.apache.mod-log-sql + + . + +
+
+
+ Installation +
+ Requirements + + + + A compatible system. mod_log_sql was authored and tested on + systems based on Red Hat Linux (Red Hat, Mandrake), but the + module should easily adapt to any modern distribution. + mod_log_sql has also been ported successfully to Solaris and + FreeBSD. + + + + + Apache 1.3 or 2.0, 1.2 is no longer supported, but may still + compile. Ideally you should already have successfully + compiled Apache and understand the process, but this + document tries to make it simple for beginners. + + + + + The MySQL development headers. This package is called + different things on different distributions. For example, + Red Hat 6.x calls this RPM "MySQL-devel" whereas Mandrake + calls it "libmysql10-devel." Both MySQL 3.23.x and 4.x are + supported. + + + + + MySQL >= 3.23.15 configured, installed and running on + either localhost or an accessible networked machine. You + should already have a basic understanding of MySQL and how + it functions. + + + + + Optionally, if you want to be able to log SSL information + such as keysize or cipher, you need OpenSSL and mod_ssl + installed. + + + +
+
+ Compiling and Installing + + + Unpack the archive into a working directory. + $ tar -xzf mod_log_sql-1.94.tar.gz +$ cd mod_log_sql-1.94 + + + run configure to configure the source directory. + $ ./configure + + The + configure + script should automatically detect all the required + libraries and program if the are installed in standard + locations.. If it returns an error, here is a description of + the arguments you can specify when you run + configure + . + + + + --with-apxs=/usr/sbin/apxs + + + This is the full path to the apxs binary, or the + directory which contains the program. This program is + part of the Apache 1.3 and 2.0 installation. + + + The default is to search + /usr/bin/apxs + and + /usr/sbin/apxs + . + + + Specifying a directory here will search + $directory/apxs, $directory/bin/apxs, and + $directory/sbin/apxs + + + If you have more than one version of Apache installed, + you need to specify the correct apxs binary for the + one you wish to compile for. + + + + + --with-mysql=/path/to/mysql + + + This is the directory to search for the + libmysqlclient + library and the + MySQL + headers. + + + The default is to search + /usr/include + , + /usr/include/mysql + , + /usr/local/include + , and + /usr/local/include/mysql + for + MySQL + headers.. And + /usr/lib + . + /usr/lib/mysql + , + /usr/local/lib + , and + /usr/local/lin/mysql + for the + MySQL + libraries. + + + Specifying this testargument will search + $directory/include and $directory/mysql for + MySQL + headers. And $directory/lib and $directory/lib/mysql + for + MySQL + libraries. + + + + + --enable-ssl + + + Specifying this argument will enable the search for + mod_ssl and SSL headers, and if found will enable + compilation of SSL support into mod_log_sql. SSL + support is compiled into a separate module that can be + loaded after the main mod_log_sql. + + + + + --with-ssl-inc=/usr/include/openssl + + + This is the path to the SSL toolkit header files that + were used to compile mod_ssl. If you want SSL support + you most likely need to specify this. + + + The default is to search + /usr/include + and + /usr/include/openssl + . + + + Specifying this argument will search that directory + for the SSL headers. + + + + + --with-db-inc=/usr/include/db1 + + + This argument is only needed when compiling SSL + support for Apache 1.3, and needs to be the directory + which contains the ndbm.h header file. You can find + this by using + + $ locate ndbm.h +/usr/include/db1/ndbm.h +/usr/include/gdbm/ndbm.h + + As far as I can tell, there is no difference as to + which you specify, but it should be the one that you + compiled mod_ssl with. + + + The default is + /usr/include/db1 + , which should work on most systems. + + + + + --disable-apachetest + + + This will disable the apache version test. However + there is a side affect if you specify this where I + will not be able to determine which version of Apache + you are compiling for. So don't specify this.. If you + are having troubles with the script detecting your + Apache version, then send a bug report along with your + system OS version and versions of related packages. + + + + + --disable-mysqltest + + + This will disable the MySQL compile test. Specify this + if for some reason the test fail but you know you have + specified the correct directories. If mod_los_sql also + fails to compile report a bug along with your system + OS version and versions of related packages. + + + + + + + + Now compile the module with GNU make. You may have to + specify gmake on some systems like FreeBSD. + + $ gmake + + + + If there were no errors, you can now install the module(s). + If you compiled as a non-root user you may need to switch + users with + su + or + sudo + . + + $ su -c "gmake install" +Password: + + + + Now edit your Apache configuration and load the modules. + + + + + + If you are loading the SSL logging module, you need to + make sure it is loaded after mod_ssl and mod_log_sql. + + + + + If you have previously used mod_log_sql version 1.18, + the name of the module has changed from sql_log_module + to log_sql_module (the first parameter to LoadModule) + + + + + If you are upgrading from any release earlier than + 1.97 you need to add an extra LoadModule directive to + load the database driver (ie mysql). + + + + + + + + Insert these lines to either the main + httpd.conf + or a file included via an include directive. + + LoadModule log_sql_module modules/mod_log_sql.so +LoadModule log_sql_mysql_module modules/mod_log_sql_mysql.so +<IfModule mod_ssl.c> +LoadModule log_sql_ssl_module moduels/mod_log_sql_ssl.so +</IfModule> + + + If you did not compile SSL support in mod_log_sql, do + not include the lines between the <IfModule> + directives. + + + + + + If you are using Apache 1.3 you may need add these lines + later in the configuration. + + AddModule mod_log_sql.c +AddModule mod_log_sql_mysql.c +<IfModule mod_ssl.c> +AddModule mod_log_sql_ssl.c +</IfModule> + + + If you did not compile SSL support in mod_log_sql, do + not include the lines between the <IfModule> + directives. + + + + + + +
+
+
+ Configuration +
+ + Preparing MySQL for logging + + + You have to prepare the database to receive data from + mod_log_sql + , and set up run-time directives in + httpd.conf + to control how and what + mod_log_sql + logs. + + + This section will discuss how to get started with a basic + configuration. Full documentation of all available run-time + directives is available in section + + . + + + + + mod_log_sql can make its own tables on-the-fly, or you can + pre-make the tables by hand. The advantage of letting the + module make the tables is ease-of-use, but for raw + performance you will want to pre-make the tables in order to + save some overhead. In this basic setup we'll just let the + module create tables for us. + + + + + We still need to have a logging database created and ready, + so run the MySQL command line client and create a database: + + # mysql -uadmin -p +Enter password: +mysql> create database apachelogs; + + + + If you want to hand-create the tables, run the enclosed + 'create-tables' SQL script as follows ("create_tables.sql" + needs to be in your current working directory). + + mysql> use apachelogs +Database changed +mysql> source create_tables.sql + + + + Create a specific + MySQL + userid that + httpd + will use to authenticate and enter data. This userid need + not be an actual Unix user. It is a userid internal to + MySQL + with specific privileges. In the following example command, + "apachelogs" is the database, "loguser" is the userid to + create, "my.apachemachine.com" is the name of the Apache + machine, and "l0gger" is the password to assign. Choose + values that are different from these examples. + + mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger'; + + + + You may be especially security-paranoid and want "loguser" + to not have "create" capability within the "apachelogs" + database. You can disable that privilege, but the cost is + that you will not be able to use the module's on-the-fly + table creation feature. If that cost is acceptable, + hand-create the tables as described in step + + and use the following GRANT statement instead of the one + above: + + mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger'; + + + + Enable full logging of your + MySQL + daemon (at least temporarily for debugging purposes) if you + don't do this already. Edit /etc/my.cnf and add the + following line to your [mysqld] section: + + log=/var/log/mysql-messages + + Then restart + MySQL + + # /etc/rc.d/init.d/mysql restart + + +
+
+ A very basic logging setup in Apache + + + + Tell the module what database to use and the appropriate + authentication information. + + + So, edit httpd.conf and insert the following lines somewhere + after any LoadModule / AddModule statements. Make sure these + statements are "global," i.e. not inside any VirtualHost + stanza. You will also note that you are embedding a password + in the file. Therefore you are advised to "chmod 660 + httpd.conf" to prevent unauthorized regular users from + viewing your database user and password. + + + Use the + MySQL + database called "apachelogs" running on "dbmachine.foo.com". + Use username "loguser" and password "l0gg3r" to authenticate + to the database. Permit the module create tables for us. + + + Basic Example + LogSQLLoginInfo mysql://loguser:l0gg3r@dbmachine.foo.com/apachelogs +LogSQLCreateTables on + + + If your database resides on localhost instead of another + host, specify the MySQL server's socket file as follows: + + LogSQLDBParam socketfile /your/path/to/mysql.sock + + If your database is listening on a port other than 3306, + specify the correct TCP port as follows: + + LogSQLDBParam port 1234 + + + + The actual logging is set up on a virtual-host-by-host + basis. So, skip down to the virtual host you want to set up. + Instruct this virtual host to log entries to the table + "access_log" by inserting a LogSQLTransferLogTable + directive. (The LogSQLTransferLogTable directive is the + minimum required to log -- other directives that you will + learn about later simply tune the module's behavior.) + + <VirtualHost 1.2.3.4> + [snip] + LogSQLTransferLogTable access_log + [snip] +</VirtualHost> + + + Restart apache. + # /etc/rc.d/init.d/httpd stop +# /etc/rc.d/init.d/httpd start + + +
+
+ Testing the basic setup + + + + Visit your web site in a browser to trigger some hits, then + confirm that the entries are being successfully logged: + + # mysql -hdbmachine.foo.com -umysqladmin -p -e "SELECT * FROM access_log" apachelogs +Enter password: + + Several lines of output should follow, corresponding to your + hits on the site. You now have basic functionality. Don't + disable your regular Apache logs until you feel comfortable + that the database is behaving as you'd like and that things + are going well. If you do not see any entries in the + access_log, please consult section + + of the FAQ on how to debug and fix the situation. + + + + + You can now activate the advanced features of mod_log_sql, + which are described in the next section. + + + +
+
+ How to tune logging with run-time directives +
+ Instructing the module what to log + + The most basic directive for the module is + LogSQLTransferLogFormat, which tells the module which + information to send to the database; logging to the database + will not take place without it. Place a + LogSQLTransferLogFormat directive in the VirtualHost stanza of + each virtual host that you want to activate. + + + After LogSQLTransferLogFormat you supply a string of + characters that tell the module what information to log. In + the configuration directive reference (section + + ) there is a table which clearly defines all the possible + things to log. Let's say you want to log only the "request + time," the "remote host," and the "request"; you'd use: + + LogSQLTransferLogFormat hUS + But a more appropriate string to use is + LogSQLTransferLogFormat AbHhmRSsTUuv + + which logs all the information required to be compatible with + the Combined Log Format (CLF). + + + If you don't choose to log everything that is available, + that's fine. Fields in the unused columns in your table will + simply contain NULL. + + + Some of the LogSQLTransferLogFormat characters require a + little extra configuration: + + + + + If you specify 'c' to indicate that you want to log the + cookie value, you must also tell the module which cookie + you mean by using LogSQLWhichCookie -- after all, there + could be many cookies associated with a given request. + Fail to specify LogSQLWhichCookie, and no cookie + information at all will be logged. + + + + + If you specify 'M' to indicate that you want to log the + machine ID, you must also tell the module this machine's + identity using the LogSQLMachineID directive. Fail to + specify LogSQLMachineID, and a simple '-' character will + be logged in the machine_id column. + + + +
+
+ + Instructing the module what NOT to log using filtering + directives + + + One "accept" and two "ignore" directives allow you to + fine-tune what the module should not log. These are very handy + for keeping your database as uncluttered as possible and + keeping your statistics free of unneeded numbers. Think of + each one as a gatekeeper. + + + + It is important to remember that each of these three + directives is purely optional. mod_log_sql's default is to + log everything. + + + + When a request comes in, the contents of LogSQLRequestAccept + are evaluated first. This optional, "blanket" directive lets + you specify that only certain things are to be accepted for + logging, and everything else discarded. Because it is + evaluated before LogSQLRequestIgnore and LogSQLRemhostIgnore + it can halt logging before those two filtering directives "get + their chance." + + + Once a request makes it past LogSQLRequestAccept, it still can + be excluded based on LogSQLRemhostIgnore and + LogSQLRequestIgnore. A good way to use LogSQLRemhostIgnore is + to prevent the module from logging the traffic that your + internal hosts generate. LogSQLRequestIgnore is great for + preventing things like requests for "favicon.ico" from + cluttering up your database, as well as excluding the various + requests that worms make, etc. + + + You can specify a series of strings after each directive. Do + not use any type of globbing or regular-expression syntax -- + each string is considered a match + + if it is a substring of the larger request or remote-host; + the comarison is case-sensitive + + . This means that "LogSQLRemhostIgnore micro" will ignore + requests from "microsoft.com," "microworld.net," + "mymicroscope.org," etc. "LogSQLRequestIgnore gif" will + instruct the module to ignore requests for "leftbar.gif," + "bluedot.gif" and even "giftwrap.jpg" -- but "RED.GIF" and + "Tree.Gif" would still get logged because of case sensitivity. + + A summary of the decision flow: + + + + If LogSQLRequestAccept exists and a request does not match + anything in that list, it is discarded. + + + + + If a request matches anything in the LogSQLRequestIgnore + list, it is discarded. + + + + + If a reqiest matches anything in the LogSQLRemhostIgnore + list, it is discarded. + + + + Otherwise the request is logged. + + + + This means that you can have a series of directives similar to + the following: + + LogSQLRequestAccept .html .gif .jpg +LogSQLRequestIgnore statistics.html bluedot.jpg + + So the first line instructs the module to only log files with + html, gif and jpg suffixes; requests for "formail.cgi" and + "shopping-cart.pl" will never be considered for logging. + ("LeftArrow.JPG" will also never be considered for logging -- + remember, the comparison is case sensitive.) The second line + prunes the list further -- you never want to log requests for + those two objects. + + + + + + If you want to match all the hosts in your domain such + as "host1.corp.foo.com" and "server.dmz.foo.com", simply + specify: + + LogSQLRemhostIgnore foo.com + + + + A great way to catch the vast majority of worm-attack + requests and prevent them from being logged is to + specify: + + LogSQLRequestIgnore root.exe cmd.exe default.ida + + + + To prevent the logging of requests for common graphic + types, make sure to put a '.' before the suffix to avoid + matches that you didn't intend: + + LogSQLRequestIgnore .gif .jpg + + + +
+
+
+ Advanced logging scenarios +
+ Using the module in an ISP environment + mod_log_sql has three basic tiers of operation: + + + + The administrator creates all necessary tables by hand and + configures each Apache VirtualHost by hand. + (LogSQLCreateTables Off) + + + + + The module is permitted to create necessary tables + on-the-fly, but the administrator configures each Apache + VirtualHost by hand. (LogSQLCreateTables On) + + + + + The module is permitted to create all necessary tables and + to make intelligent, on-the-fly configuration of each + VirtualHost. (LogSQLMassVirtualHosting On) + + + + + Many users are happy to use the module in its most minimal + form: they hand-create any necessary tables (using + "create_tables.sql"), and they configure each VirtualHost by + hand to suit their needs. However, some administrators need + extra features due to a large and growing number of + VirtualHosts. The LogSQLMassVirtualHosting directive activates + module capabilities that make it far easier to manage an ISP + environment, or any situation characterized by a large and + varying number of virtual servers. + + + + + the on-the-fly table creation feature is activated + automatically + + + + + the transfer log table name is dynamically set from the + virtual host's name (example: a virtual host + "www.grubbybaby.com" gets logged to table + "access_www_grubbybaby_com") + + + + + There are numerous benefits. The admin will not need to create + new tables for every new VirtualHost. (Although the admin will + still need to drop the tables of virtual hosts that are + removed.) The admin will not need to set + LogSQLTransferLogTable for each virtual host -- it will be + configured automatically based on the host's name. Because + each virtual host will log to its own segregated table, data + about one virtual server will segregate from others; an admin + can grant users access to the tables they need, and they will + be unable to view data about another user's virtual host. + + + In an ISP scenario the admin is likely to have a cluster of + many front-end webservers logging to a back-end database. + mod_log_sql has a feature that permits analysis of how well + the web servers are loadbalancing: the LogSQLMachineID + directive. The administrator uses this directive to assign a + unique identifier to each machine in the web cluster, e.g. + "LogSQLMachineID web01," "LogSQLMachineID web02," etc. Used in + conjunction with the 'M' character in LogSQLTransferLogFormat, + each entry in the SQL log will include the machine ID of the + machine that created the entry. This permits the administrator + to count the entries made by each particular machine and + thereby analyze the front-end loadbalancing algorithm. + +
+
+ + Logging many-to-one data in separate tables + + + A given HTTP request can have a one-to-many relationship with + certain kinds of data. For example, a single HTTP request can + have 4 cookies, 3 headers and 5 "mod_gzip" notes associated + with it. mod_log_sql is capable of logging these relationships + due to the elegance of SQL relational data. + + + You already have a single table containing access requests. + One of the columns in that table is 'id' which is intended to + contain the unique request ID supplied by the standard Apache + module mod_unique_id -- all you need to do is compile in that + module and employ the LogSQLTransferLogFormat character 'I'. + Thereafter, each request gets a unique ID that can be thought + of as a primary key within the database, useful for joining + multiple tables. So let's envision several new tables: a notes + table, a cookies table, and a table for inbound and outbound + headers. + + + <tblAcc>access_log + + + + + + + + + + id + remote_host + request_uri + time_stamp + status + bytes_sent + + + + + PPIDskBRH30AAGPtAsg + zerberus.aiacs.net + /mod_log_sql/index.html + 1022493617 + 200 + 2215 + + + +
+ + <tblNotes>notes_log + + + + + + + id + item + val + + + + + PPIDskBRH30AAGPtAsg + mod_gzip_result + OK + + + PPIDskBRH30AAGPtAsg + mod_gzip_compression_ratio + 69 + + + +
+ + <tblHdr>headers_log + + + + + + + id + item + val + + + + + PPIDskBRH30AAGPtAsg + Content-Type + text/html + + + PPIDskBRH30AAGPtAsg + Accept-Encoding + gzip, deflate + + + PPIDskBRH30AAGPtAsg + Expires + Tue, 28 May 2002 10:00:18 GMT + + + PPIDskBRH30AAGPtAsg + Cache-Control + max-age=86400 + + + +
+ + We have a certain request, and its unique ID is + "PPIDskBRH30AAGPtAsg". Within each separate table will be + multiple entries with that request ID: several cookie entries, + several header entries, etc. As you can see in tables + [tblAcc], [tblNotes] and [tblHdr], you have a one-to-many + relationship for request PPIDskBRH30AAGPtAsg: that one access + has two associated notes and four associated headers. You can + extract this data easily using the power of SQL's "select" + statement and table joins. To see the notes associated with a + particular request: + + SELECT a.remote_host, a.request_uri, n.item, n.val +FROM access_log a JOIN notes_log n ON a.id=n.id +WHERE a.id='PPIDskBRH30AAGPtAsg'; + + access_log joined to notes_log + + + + + + + + remote_host + request_uri + item + val + + + + + zerberus.aiacs.net + /mod_log_sql/index.html + mod_gzip_result + OK + + + zerberus.aiacs.net + /mod_log_sql/index.html + mod_gzip_compression_ratio + 69 + + + +
+ + Naturally you can craft similar statements for the outboud + headers, inbound headers and cookies, all of which can live in + separate tables. Your statements are limited in power only by + your skill with SQL. + + + In order to use this capability of mod_log_sql, you must do + several things. + + + + + Compile mod_unique_id into Apache (statically or as a + DSO). mod_log_sql employs the unique request ID that + mod_unique_id provides in order to key between the + separate tables. You can still log the data without + mod_unqiue_id, but it will be completely uncorrelated and + you will have no way to discern any meaning. + + + + + Create the appropriate tables. This will be done for you + if you permit mod_log_sql to create its own tables using + LogSQLCreateTables On, or if you use the enclosed + "create_tables.sql" script. + + + + + Create a SQL index on the "id" column. Without this index, + table joins will be deathly slow. I recommend you consult + the MySQL documentation on the proper way to create a + column index if you are not familiar with this operation. + + + + + Within each appropriate VirtualHost stanza, use the + LogSQLWhich* and LogSQL*LogTable directives to tell the + module what and where to log the data. In the following + example, I have overridden the name for the notes table + whereas I have left the other table names at their + defaults. I have then specified the cookies, headers and + notes that interest me. (And as you can see, these + directives do not require me to add any characters to + LogSQLTransferLogTable.) + + <VirtualHost 216.231.36.128> + (snip) + LogSQLNotesLogTable notestable + LogSQLWhichCookies bluecookie redcookie greencookie + LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio + LogSQLWhichHeadersOut Expires Content-Type Cache-Control + LogSQLWhichHeadersIn User-Agent Accept-Encoding Host + (snip) +</VirtualHost> + + +
+
+ Using the same database for production and test + + Although sub-optimal, it is not uncommon to use the same + back-end database for the "production" webservers as well as + the "test" webservers (budgetary constraints, rack-space + limits, etc.). Furthermore, an administrator in this situation + may be unable to use LogSQLRemhostIgnore to exclude requests + from the test servers -- perhaps the generated entries are + genuinely useful for analytical or QA purposes, but their + value after analysis is minimal. + + + It is wasteful and potentially confusing to permit this + internal test data to clutter the database, and a solution to + the problem is the proper use of the LogSQLMachineID + directive. Assume a scenario where the production webservers + have IDs like "web01," "web02," and so on -- and the test + webservers have IDs like "test01," "test02," etc. Because + entries in the log database are distinguished by their source + machine, an administrator may purge unneeded test data from + the access log as follows: + + DELETE FROM access_log WHERE machine_id like 'test%'; +
+
+ + Optimizing for a busy database + + + A busy MySQL database will have SELECT statements running + concurrently with INSERT and UPDATE statements. A long-running + SELECT can in certain circumstances block INSERTs and + therefore block mod_log_sql. A workaround is to enable + mod_log_sql for "delayed inserts," which are described as + follows in the MySQL documentation. + + + The DELAYED option for the INSERT statement is a + MySQL-specific option that is very useful if you have clients + that can't wait for the INSERT to complete. This is a common + problem when you use MySQL for logging and you also + periodically run SELECT and UPDATE statements that take a long + time to complete. DELAYED was introduced in MySQL Version + 3.22.15. It is a MySQL extension to ANSI SQL92. + + + INSERT DELAYED only works with ISAM and MyISAM tables. Note + that as MyISAM tables supports concurrent SELECT and INSERT, + if there is no free blocks in the middle of the data file, you + very seldom need to use INSERT DELAYED with MyISAM. + + + When you use INSERT DELAYED, the client will get an OK at once + and the row will be inserted when the table is not in use by + any other thread. + + + Another major benefit of using INSERT DELAYED is that inserts + from many clients are bundled together and written in one + block. This is much faster than doing many separate inserts. + + The general disadvantages of delayed inserts are + + + + The queued rows are only stored in memory until they are + inserted into the table. If mysqld dies unexpectedly, any + queued rows that were not written to disk are lost. + + + + + There is additional overhead for the server to handle a + separate thread for each table on which you use INSERT + DELAYED. + + + + + + The MySQL documentation concludes, "This means that you + should only use INSERT DELAYED when you are really sure you + need it!" Furthermore, the current state of error return + from a failed INSERT DELAYED seems to be in flux, and may + behave in unpredictable ways between different MySQL + versions. See FAQ entry + + -- you have been warned. + + + + If you are experiencing issues which could be solved by + delayed inserts, then set LogSqlDelayedInserts On in the + httpd.conf + . All regular INSERT statements are now INSERT DELAYED, and + you should see no more blocking of the module. + +
+
+
+ + Configuration Directive Reference + + + It is imperative that you understand which directives are used + only once in the main server config, and which are used inside + VirtualHost stanzas and therefore multiple times within + httpd.conf. The "context" listed with each entry informs you of + this. + +
+ DataBase Configuration + + + LogSQLLoginInfo + + + LogSQLLoginInfo + + connection URI + + + + Example: LogSQLLoginInfo + mysql://logwriter:passw0rd@foobar.baz.com/Apache_log + + Context: main server config + + Defines the basic connection URI to connect to the + database with. The format of the connection URI is + + + driver://username[:password]@hostname[:port]/database + + + + driver + + + The database driver to use (mysql, pgsql, etc..) + + + + + username + + + The database username to login with INSERT + privileges on the logging table defined in + LogSQLtransferLogTable. + + + + + password + + + The password to use for username, and can be + omitted if there is no password. + + + + + hostname + + + The hostname or Ip address of the Database + machine, ans is simple "localhost" if the database + lives on the same machine as Apache. + + + + + port + + + Port on hostname to connect to the Database, if + not specified use the default port for the + database. + + + + + database + + + The database to connect to on the server. + + + + + + + This is defined only once in the + httpd.conf + file. + + + This directive Must be defined for logging to be + enabled. + + + + + + LogSQLDBParam + + + LogSQLDBParam + + parameter-name + + + value + + + + Example: LogSQLDBParam socketfile + /var/lib/mysql/mysql.socket + + Context: main server config + + This is the new method of specifying Database connection + credentials and settings. This is used to define + database driver specific options. For a list of options + read the documentation for each specific database + driver. + + + MySQL Driver parameters + + + + + + + Parameter + Meaning + Default + + + + + hostname + MySQL Server hostname + none (use LogSQLLoginInfo to set) + + + username + The username to log in with + none (use LogSQLLoginInfo to set) + + + password + The password to use + none (use LogSQLLoginInfo to set) + + + database + Which database to connect to + none (use LogSQLLoginInfo to set) + + + port + The TCP port to connect to the MySQL server over + 3306 (use LogSQLLoginInfo to set) + + + socketfile + The MySQL Unix socket file to use + none + + + tabletype + MySQL Table Engine to use + MySQL server default + + + +
+ + + Each parameter-name may only be defined once. + + + + + + LogSQLCreateTables + + + LogSQLCreateTables + flag + + Example: LogSQLCreateTables On + Default: Off + Context: main server config + + mod_log_sql has the ability to create its tables + on-the-fly. The advantage to this is convenience: you + don't have to execute any SQL by hand to prepare the + table. This is especially helpful for people with lots + of virtual hosts (who should also see the + LogSQLMassVirtualHosting directive). + + + There is a slight disadvantage: if you wish to activate + this feature, then the userid specified in + LogSQLLoginInfo must have CREATE privileges on the + database. In an absolutely paranoid, locked-down + situation you may only want to grant your mod_log_sql + user INSERT privileges on the database; in that + situation you are unable to take advantage of + LogSQLCreateTables. But most people -- even the very + security-conscious -- will find that granting CREATE on + the logging database is reasonable. + + + + This is defined only once in the + httpd.conf + file. + + + + + + LogSQLForcePreserve + + + LogSQLForcePreserve + flag + + Example: LogForcePreserve On + Default: Off + Context: main server config + + You may need to perform debugging on your database and + specifically want mod_log_sql to make no attempts to log + to it. This directive instructs the module to send all + its log entries directly to the preserve file and to + make no database INSERT attempts. + + + This is presumably a directive for temporary use only; + it could be dangerous if you set it and forget it, as + all your entries will simply pile up in the preserve + file. + + + + This is defined only once in the + httpd.conf + file. + + + + + + LogSQLDisablePreserve + + + LogSQLDisablePreserve + flag + + Example: LogDisablePreserve On + Default: Off + Context; main server config + + This option can be enabled to completely disable the + preserve file fail back. This may be useful for servers + where the file-system is read-only. + + + If the database is not available those log entries will + be lost. + + + + This is defined only once in the + httpd.conf + file. + + + + + + LogSQLMachineID + + + LogSQLMachineID + machineID + + Example: LogSQLMachineID web01 + Context: main server config + + If you have a farm of webservers then you may wish to + know which particular machine made each entry; this is + useful for analyzing your load-balancing methodology. + LogSQLMachineID permits you to distinguish each + machine's entries if you assign each machine its own + LogSQLMachineID: for example, the first webserver gets + ``LogSQLMachineID web01,'' the second gets + ``LogSQLMachineID web02,'' etc. + + + + This is defined only once in the + httpd.conf + file. + + + + + + LogSQlPreserveFile + + + LogSQLPreserveFile + + filename + + + + Example: LogSQLPreserveFile offline-preserve + + Default: /tmp/sql-preserve + Context: virtual host + + mod_log_sql writes queries to this local preserve file + in the event that it cannot reach the database, and thus + ensures that your high-availability web frontend does + not lose logs during a temporary database outage. This + could happen for a number of reasons: the database goes + offline, the network breaks, etc. You will not lose + entries since the module has this backup. The file + consists of a series of SQL statements that can be + imported into your database at your convenience; + furthermore, because the SQL queries contain the access + timestamps you do not need to worry about out-of-order + data after the import, which is done in a simple manner: + + # mysql -uadminuser -p mydbname < /tmp/sql-preserve + + If you do not define LogSQLPreserveFile then all virtual + servers will log to the same default preserve file ( + /tmp/sql-preserve + ). You can redefine this on a virtual-host basis in + order to segregate your preserve files if you desire. + Note that segregation is not usually necessary, as the + SQL statements that are written to the preserve file + already distinguish between different virtual hosts if + you include the 'v' character in your + LogSQLTransferLogFormat directive. It is only necessary + to segregate preserve-files by virualhost if you also + segregate access logs by virtualhost. + + + The module will log to Apache's ErrorLog when it notices + a database outage, and upon database return. You will + therefore know when the preserve file is being used, + although it is your responsibility to import the file. + + + The file does not need to be created in advance. It is + safe to remove or rename the file without interrupting + Apache, as the module closes the filehandle immediately + after completing the write. The file is created with the + user & group ID of the running Apache process (e.g. + 'nobody' on many Linux distributions). + + + + +
+
+ Table Names + + + LogSQLTransferLogTable + + + LogSQLTransferLogTable + + table-name + + + + Example: LogSQLTransferLogTable access_log_table + + Context: virtual host + + Defines which table is used for logging of Apache's + transfers; this is analogous to Apache's TransferLog + directive. table-name must be a valid table within the + database defined in the LogSQLLoginInfo connection URI. + + + This directive is + not + necessary if you declare LogSQLMassVirtualHosting On, + since that directive activates dynamically-named tables. + If you attempt to use LogSqlTransferlogTable at the same + time a warning will be logged and it will be ignored, + since LogSQLMassVirtualHosting takes priority. + + + + Requires unless LogSQLMassVirtualHosting is set to On + + + + + + LogSQLCookieLogTable + + + LogSQLCookieLogTable + + + table-name + + + + Example: LogSQLCookieLogTable cookie_log + + Default: cookies + Context: virtual host + + Defines which table is used for logging of cookies. + Working in conjunction with LogSQLWhichCookies, you can + log many of each request's associated cookies to a + separate table. For meaningful data retrieval the cookie + table is keyed to the access table by the unique request + ID supplied by the standard Apache module mod_unique_id. + + + + You must create the table (see create-tables.sql, + included in the package), or LogSQLCreateTables must + be set to "on". + + + + + + LogSQLHeadersInLogTable + + + LogSQLHeadersInLogTable + + table-name + + + + Example: LogSQLHeadersInLogTable headers + + Default: headers_in + Context: virtual host + + Defines which table is used for logging of inbound + headers. Working in conjunction with + LogSQLWhichHeadersIn, you can log many of each request's + associated headers to a separate table. For meaningful + data retrieval the headers table is keyed to the access + table by the unique request ID supplied by the standard + Apache module mod_unique_id. + + + + Note that you must create the table (see + create-tables.sql, included in the package), or + LogSQLCreateTables must be set to "on". + + + + + + LogSQLHeadersOutLogTable + + + LogSQLHeadersOutLogTable + + table-name + + + + Example: LogSQLHeadersOutLogTable headers + + Default: headers_out + Context: virtual host + + Defines which table is used for logging of outbound + headers. Working in conjunction with + LogSQLWhichHeadersOut, you can log many of each + request's associated headers to a separate table. For + meaningful data retrieval the headers table is keyed to + the access table by the unique request ID supplied by + the standard Apache module mod_unique_id. + + + + Note that you must create the table (see + create-tables.sql, included in the package), or + LogSQLCreateTables must be set to "on". + + + + + + LogSQLNotesLogTable + + + LogSQLNotesLogTable + + table-name + + + Example: LogSQLNotesLogTable notes-log + Default: notes + Context: virtual_host + + Defines which table is used for logging of notes. + Working in conjunction with LogSQLWhichNotes, you can + log many of each request's associated notes to a + separate table. For meaningful data retrieval the notes + table is keyed to the access table by the unique request + ID supplied by the standard Apache module mod_unique_id. + + + + This table must be created (see create-tables.sql + included in the package), or LogSQLCreateTables must + be set to 'On'. + + + + + + LogSQLMassVirtualHosting + + + LogSQLMassVirtualHosting + flag + + Example: LogSQLMassVirtualHosting On + Default: Off + Context: main server config + + If you administer a site hosting many, many virtual + hosts then this option will appeal to you. If you turn + on LogSQLMassVirtualHosting then several things happen: + + + + + the on-the-fly table creation feature is activated + automatically + + + + + the transfer log table name is dynamically set from + the virtual host's name after stripping out + SQL-unfriendly characters (example: a virtual host + www.grubbybaby.com gets logged to table + access_www_grubbybaby_com) + + + + + which, in turn, means that each virtual host logs to + its own segregated table. Because there is no data + shared between virtual servers you can grant your + users access to the tables they need; they will be + unable to view others' data. + + + + + This is a huge boost in convenience for sites with many + virtual servers. Activating LogSQLMassVirtualHosting + obviates the need to create every virtual server's table + and provides more granular security possibilities. + + + + This is defined only once in the + httpd.conf + file. + + + + + +
+
+ Configuring What Is logged + + + LogSQLTransferLogFormat + + + LogSQLTransferLogFormat + + format-string + + + Example: LogSQLTransferLogFormat huSUTv + Default: AbHhmRSsTUuv + Context: virtual host + + Each character in the format-string defines an attribute + of the request that you wish to log. The default logs + the information required to create Combined Log Format + logs, plus several extras. Here is the full list of + allowable keys, which sometimes resemble their Apache + counterparts, but do not always: + + + Core LogFormat parameters + + + + + + + + + Symbol + Meaning + DB Field + Data Type + Example + + + + + A + User Agent + agent + varchar(255) + + Mozilla/4.0 (compat; MSIE 6.0; Windows) + + + + a + CGi request arguments + request_args + varchar(255) + + user=Smith&cart=1231&item=532 + + + + b + Bytes transfered + bytes_sent + int unsigned + 32561 + + + + c + + + Text of cookie + cookie + varchar(255) + + Apache=sdyn.fooonline.net 1300102700823 + + + + f + Local filename requested + request_file + varchar(255) + /var/www/html/books-cycroad.html + + + H + HTTP request_protocol + request_protocol + varchar(10) + HTTP/1.1 + + + h + Name of remote host + remote_host + varchar(50) + blah.foobar.com + + + I + Request ID (from modd_unique_id) + id + char(19) + POlFcUBRH30AAALdBG8 + + + l + Ident user info + remote_logname + varcgar(50) + bobby + + + M + + Machine ID + + + machine_id + varchar(25) + web01 + + + m + HTTP request method + request_method + varchar(10) + GET + + + P + httpd cchild PID + child_pid + smallint unsigned + 3215 + + + p + http port + server_port + smallint unsigned + 80 + + + R + Referer + referer + varchar(255) + + http://www.biglinks4u.com/linkpage.html + + + + r + Request in full form + request_line + varchar(255) + GET /books-cycroad.html HTTP/1.1 + + + S + + Time of request in UNIX time_t format + + time_stamp + int unsigned + 1005598029 + + + s + HTTP Response Code Status + status + smallint + 200 + + + T + Seconds to service request + request_duration + smallint unsigned + 2 + + + t + Time of request in human format + request_time + char(28) + [02/Dec/2001:15:01:26 -0800] + + + U + Request in simple form + request_uri + varchar(255) + /books-cycroad.html + + + u + User info from HTTP auth + remote_user + varchar(50) + bobby + + + v + Virtual host servicing the request + virtual_host + varchar(255) + www.foobar.com + + + V + + requested Virtual host name (mass + virtualhosting) + + virtual_host + varchar(255) + www.foobar.org + + + +
+ + + [1] You must also specify LogSQLWhichCookie for this + to take effect. + + + [2] You must also specify LogSQLmachineID for this to + take effect. + + + + SSL LogFormat Parameters + + + + + + + + + Symbol + Meaning + DB Field + Data Type + Example + + + + + z + SSL cipher used + ssl_cipher + varchar(25) + RC4-MD5 + + + q + + Keysize of the SSL connection + + ssl_keysize + smallint unsigned + 56 + + + Q + + maximum keysize supported + + ssl_maxkeysize + smallint unsigned + 128 + + + +
+ + LogIO LogFormat Parameters + + + + + + + + + Symbol + Meaning + DB Field + Data Type + Example + + + + + i + Number of actual Bytes transfered in with the request + bytes_in + int unsigned + 505 + + + o + Number of actual Bytes transfered out with the request + bytes_out + int unsigned + 4168 + + + +
+ + + + LogSQLRemhostIgnore + + + LogSQLRemhostIgnore + + hostname + + + + Example: LogSQLRemhostIgnore localnet.com + + Context: virtual host + + Lists a series of smortrings that, if present in the + REMOTE_HOST, will cause that request to + not + be logged. This directive is useful for cutting down on + log clutter when you are certain that you want to ignore + requests from certain hosts, such as your own internal + network machines. See section + + for some tips for using this directive. + + + Each string may contain a + or - prefix in a + <VirtualHost> context and will cause those strings + to be added (+) or removed (-) from the global + configuration. Otherwise the global is completely + ignored and overridden if defined in a + <VirtualHost> + + + Each string is separated by a space, and no regular + expressions or globbing are allowed. Each string is + evaluated as a substring of the REMOTE_HOST using + strstr(). The comparison is case sensitive. + + + + + LogSQLRequestAccept + + + LogSQLRequestAccept + + substring + + + + Example: LogSQLRequestAccept .html .php .jpg + + + Default: if not specified, all requests are 'accepted' + + Context: virtual host + + Lists a series of strings that, if present in the URI, + will permit that request to be considered for logging + (depending on additional filtering by the "ignore" + directives). Any request that fails to match one of the + LogSQLRequestAccept entries will be discarded. + + + Each string may contain a + or - prefix in a + <VirtualHost> context and will cause those strings + to be added (+) or removed (-) from the global + configuration. Otherwise the global is completely + ignored and overridden if defined in a + <VirtualHost> + + + This directive is useful for cutting down on log clutter + when you are certain that you only want to log certain + kinds of requests, and just blanket-ignore everything + else. See section + + for some tips for using this directive. + + + Each string is separated by a space, and no regular + expressions or globbing are allowed. Each string is + evaluated as a substring of the URI using strstr(). The + comparison is case sensitive. + + + This directive is completely optional. It is more + general than LogSQLRequestIgnore and is evaluated before + LogSQLRequestIgnore . If this directive is not used, + all + requests are accepted and passed on to the other + filtering directives. Therefore, only use this directive + if you have a specific reason to do so. + + + + + LogSQLRequestIgnore + + + LogSQLRequestIgnore + + substring + + + + Example: LogSQLRequestIgnore root.exe cmd.exe + default.ida favicon.ico + + Context: virtual host + + Lists a series of strings that, if present in the URI, + will cause that request to + NOT + be logged. This directive is useful for cutting down on + log clutter when you are certain that you want to ignore + requests for certain objects. See section + + for some tips for using this directive. + + + Each string may contain a + or - prefix in a + <VirtualHost> context and will cause those strings + to be added (+) or removed (-) from the global + configuration. Otherwise the global is completely + ignored and overridden if defined in a + <VirtualHost> + + + Each string is separated by a space, and no regular + expressions or globbing are allowed. Each string is + evaluated as a substring of the URI using strstr(). The + comparison is case sensitive. + + + + + LogSQLWhichCookie + + + LogSQLWhichCookie + + cookiename + + + Example; LogSQLWhichCookie Clicks + Context: virtual host + + In HTTP, cookies have names to distinguish them from + each other. Using mod_usertrack, for example, you can + give your user-tracking cookies a name with the + CookieName directive. + + + mod_log_sql allows you to log cookie information. + LogSQL_WhichCookie tells mod_log_sql which cookie to + log. This is necessary because you will usually be + setting and receiving more than one cookie from a + client. + + + + You must include a 'c' character in + LogSQLTransferLogFormat for this directive to take + effect. + + + although this was origintally intended for people + using mod_usertrack to create user-tracking cookies, + you are not restricted in any way. You can choose + which cookie you wish to log to the database - any + cookie at all - and it does not necessarily have to + have anything to do with mod_usertrack. + + + + + + LogSQLWhichCookies + + + LogSQLWhichCookies + + cookie-name + + + + Example: logSQLWhichCookies userlogin cookie1 cookie2 + + Context: virtual host + + Defines the list of cookies you would like logged. This + works in conjunction with LogSQLCookieLogTable. This + directive does + not + require any additional characters to be added to the + LogSQLTransferLogFormat string. The feature is activated + simply by including this directive, upon which you will + begin populating the separate cookie table with data. + + + Each string may contain a + or - prefix in a + <VirtualHost> context and will cause those strings + to be added (+) or removed (-) from the global + configuration. Otherwise the global is completely + ignored and overridden if defined in a + <VirtualHost> + + + + The table must be created (see create-tables.sql, + included in the package), or LogSQLCreateTables must + be set to 'On'. + + + + + + LogSQLWhichHeadersIn + + + LogSQLWhichHeadersIn + + header-name + + + + Example: LogSQLWhichHeadersIn User-Agent Accept-Encoding + Host + + Context: virtual host + + Defines the list of inbound headers you would like + logged. This works in conjunction with + LogSQLHeadersInLogTable. This directive does not require + any additional characters to be added to the + LogSQLTransferLogFormat string. The feature is activated + simply by including this directive, upon which you will + begin populating the separate inbound-headers table with + data. + + + Each string may contain a + or - prefix in a + <VirtualHost> context and will cause those strings + to be added (+) or removed (-) from the global + configuration. Otherwise the global is completely + ignored and overridden if defined in a + <VirtualHost> + + + + The table must be created (see create-tables.sql, + included in the package), or LogSQLCreateTables must + be set to 'On'. + + + + + + LogSQLWhichHeadersOut + + + LogSQLWhichHeadersOut + + header-name + + + + Example: LogSQLWhichHeadersOut Expires Content-Type + Cache-Control + + Context: virtual host + + Defines the list of outbound headers you would like + logged. This works in conjunction with + LogSQLHeadersOutLogTable. This directive does not + require any additional characters to be added to the + LogSQLTransferLogFormat string. The feature is activated + simply by including this directive, upon which you will + begin populating the separate outbound-headers table + with data. + + + Each string may contain a + or - prefix in a + <VirtualHost> context and will cause those strings + to be added (+) or removed (-) from the global + configuration. Otherwise the global is completely + ignored and overridden if defined in a + <VirtualHost> + + + + The table must be created (see create-tables.sql, + included in the package), or LogSQLCreateTables must + be set to 'On'. + + + + + + LogSQLWhichNotes + + + LogSQLWhichNotes + + note-name + + + + Example: LogSQLWhichNotes mod_gzip_result + mod_gzip_ompression_ratio + + Context: virtual host + + Defines the list of notes you would like logged. This + works in conjunction with LogSQLNotesLogTable. This + directive does not require any additional characters to + be added to the LogSQLTransferLogFormat string. The + feature is activated simply by including this directive, + upon which you will begin populating the separate notes + table with data. + + + Each string may contain a + or - prefix in a + <VirtualHost> context and will cause those strings + to be added (+) or removed (-) from the global + configuration. Otherwise the global is completely + ignored and overridden if defined in a + <VirtualHost> + + + + The table must be created (see create-tables.sql, + included in the package), or LogSQLCreateTables must + be set to 'On'. + + + + + +
+
+ Deprecated Commands + + + LogSQLSocketFile [Deprecated] + + + LogSQLSocketFile + + filename + + + + Example: LogSQLSocketFile /tmp/mysql.sock + + Default: (database specific) + + Default (MySQL): /var/lib/mysql/mysql.sock + + Context: main server config + + At Apache runtime you can specify the MySQL socket file + to use. Set this once in your main server config to + override the default value. This value is irrelevant if + your database resides on a separate machine. + + + mod_log_sql will automatically employ the socket for db + communications if the database resides on the local + host. If the db resides on a separate host the module + will automatically use TCP/IP. This is a function of the + MySQL API and is not user-configurable. + + + + This directive is deprecated in favor of LogSQLDBParam + socketfile [socketfilename] + + + This is defined only once in the + httpd.conf + file. + + + + + + LogSQLTCPPort [Deprecated] + + + LogSQLTCPPort + + port-number + + + Example: LogSQLTCPPort 3309 + Default: (database specific) + Default (MySQL): 3306 + Context: main server config + + Your database may listen on a different port than the + default. If so, use this directive to instruct the + module which port to use. This directive only applies if + the database is on a different machine connected via + TCP/IP. + + + + This directive is deprecated in favor of LogSQLDBParam + tcpport [port-number] + + + This is defined only once in the + httpd.conf + file. + + + + + + LogSQLDatabase [Deprecated] + + + LogSQLDatabase + + database + + + Example: LogSQLDatabase loggingdb + Context: main server config + + Defines the database that is used for logging. + "database" must be a valid db on the MySQL host defined + in LogSQLLoginInfo + + + + This directive is deprecated in favor of the URI form + of LogSQLLoginInfo. + + + This is defined only once in the + httpd.conf + file. + + + + + +
+
+
+
+ FAQ + + + General module questions + + + Why log to an SQL database? + + + + To begin with, let's get it out of the way: logging to a + database is not a panacea. But while there are + complexities with this solution, the benefit can be + substantial for certain classes of administrator or people + with advanced requirements: + + + + + Chores like log rotation go away, as you can DELETE + records from the SQL database once they are no longer + useful. For example, the excellent and popular + log-analysis tool Webalizer (http://www.webalizer.com) + does not need historic logs after it has processed + them, enabling you to delete older logs. + + + + + People with clusters of web servers (for high + availability) will benefit the most - all their + webservers can log to a single SQL database. This + obviates the need to collate/interleave the many + separate logfiles, which can be / highly/ problematic. + + + + + People acquainted with the power of SQL SELECT + statements will know the flexibility of the extraction + possibilities at their fingertips. + + + + + For example, do you want to see all your 404's? Do this: + + SELECT remote_host, status, request_uri, bytes_sent, from_unixtime(time_stamp) +FROM acc_log_tbl WHERE status=404 ORDER BY time_stamp; + + + + + + + + + + + remote_host + status + request_uri + bytes_sent + from_unixtime(time_stamp) + + + + + marge.mmm.co.uk + 404 + /favicon.ico + 321 + 2001-11-20 02:30:56 + + + 62.180.239.251 + 404 + /favicon.ico + 333 + 2001-11-20 02:45:25 + + + 212.234.12.66 + 404 + /favicon.ico + 321 + 2001-11-20 03:01:00 + + + 212.210.78.254 + 404 + /favicon.ico + 333 + 2001-11-20 03:26:05 + + + +
+ + Or do you want to see how many bytes you've sent within a + certain directory or site? Do this: + + SELECT request_uri,sum(bytes_sent) AS bytes, count(request_uri) AS howmany +FROM acc_log_tbl +WHERE request_uri LIKE '%mod_log_sql%' +GROUP BY request_uri ORDER BY howmany DESC; + + + + + + + + + request_uri + bytes + howmany + + + + + /mod_log_sql/style_1.css + 157396 + 1288 + + + /mod_log_sql/ + 2514337 + 801 + + + + /mod_log_sql/mod_log_sql.tar.gz + + 9769312 + 456 + + + /mod_log_sql/faq.html + 5038728 + 436 + + + +
+ + Or maybe you want to see who's linking to you? Do this: + + SELECT count(referer) AS num,referer +FROM acc_log_tbl +WHERE request_uri='/mod_log_sql/' +GROUP BY referer ORDER BY num DESC; + + + + + + + + num + referer + + + + + 271 + + http://freshmeat.net/projects/mod_log_sql/ + + + + 96 + + http://modules.apache.org/search?id=339 + + + + 48 + http://freshmeat.net/ + + + 8 + http://freshmeat.net + + + +
+ + As you can see, there are myriad possibilities that can be + constructed with the wonderful SQL SELECT statement. + Logging to an SQL database can be really quite useful! + + + + + + Why use MySQL? Are there alternatives? + + + + MySQL is a robust, free, and very powerful + production-quality database engine. It is well supported + and comes with detailed documentation. Many 3rd-party + software pacakges (e.g. Slashcode, the engine that powers + Slashdot) run exclusively with MySQL. In other words, you + will belong to a very robust and well-supported community + by choosing MySQL. + + + That being said, there are alternatives. PostgreSQL is + probably MySQL's leading "competitor" in the free database + world. There is also an excellent module available for + Apache to permit logging to a PostgreSQL database, called + + pgLOGd + + + + + + + Currently a database abstraction system is in the works + to allow any database to be used with mod_log_sql. + + + + + + + Is this code production-ready? + + + + By all accounts it is. It is known to work without a + problem on many-thousands-of-hits-per-day webservers. Does + that mean it is 100% bug free? Well, no software is, but + it is well-tested and believed to be fully compatible with + production environments. (The usual disclaimers apply. + This software is provided without warranty of any kind.) + + + + + + Who's using mod_log_sql? + + + + Good question! It would be great to find out! If you are a + production-level mod_log_sql user, please contact eddie at + &EmailContact; + so that you can be mentioned here. + + + + + + + Why doesn't the module also replace the Apache ErrorLog? + + + + + There are circumstances when that would be quite unwise -- + for example, if Apache could not reach the MySQL server + for some reason and needed to log that fact. Without a + text-based error log you'd never know anything was wrong, + because Apache would be trying to log a database + connection error to the database... you get the point. + + + + + Error logs are usually not very high-traffic and are + really best left as text files on a web server machine. + + + + + The Error log is free format text.. (no specified + formatting what, so ever) which is rather difficult to + nicely format for storing in a database. + + + + + + Does mod_log_sql work with Apache 2.x? + + + + Yes. A port of mod_log_sql is available for Apache 2.x as + of mod_log_sql 1.90 + + + + + + + Does mod_log_sql connect to MySQL via TCP/IP or a socket? + + + + Quick answer, Yes. + + + + It depends! This is not determined by mod_log_sql. + mod_log_sql relies on a connection command that is + supplied in the MySQL API, and that command is somewhat + intelligent. How it works: + + + + + if the specified MySQL database is on the same + machine, the connection command uses a socket to + communicate with MySQL + + + + + if the specified MySQL database is on a different + machine, mod_log_sql connects using TCP/IP. + + + + + You don't have any control of which methodology is used. + You can fine-tune some of the configuration, however. The + LogSQLSocketFile runtime configuration directive overrides + the default of "/var/lib/mysql/mysql.sock" for + socket-based connections, whereas the LogSQLTCPPort + command allows to you override the default TCP port of + 3306 for TCP/IP connections. + + + + + + I have discovered a bug. Who can I contact? + + + + Please contact Edward Rudd at + &EmailContact; + , or post a message to the mod_log_sql + + . Your comments, suggestions, bugfixes, bug catches, and + usage testimonials are always welcome. As free software, + mod_log_sql is intended to be a community effort -- any + code contributions or other ideas will be fully and openly + credited, of course. + + + + + + Problems + + + + Apache segfaults or has other problems when using PHP and + mod_log_sql + + + + + This occurs if you compiled PHP with MySQL database + support. PHP utilizes its internal, bundled MySQL + libraries by default. These conflict with the "real" MySQL + libraries linked by mod_log_sql, causing the segmentation + fault. + + + PHP and mod_log_sql can be configured to happily coexist. + The solution is to configure PHP to link against the real + MySQL libraries: recompile PHP using + --with-mysql=/your/path. Apache will run properly once the + modules are all using the same version of the MySQL + libraries. + + + + + + + Apache appears to start up fine, but nothing is getting + logged in the database + + + + + If you do not see any entries in the access_log, then + something is preventing the inserts from happening. This + could be caused by several things: + + + + + Improper privileges set up in the MySQL database + + + + + You are not hitting a VirtualHost that has a + LogSQLTransferLogTable entry + + + + + You did not specify the right database host or login + information + + + + + Another factor is preventing a connection to the + database + + + + + + It is improper to ask for help before you have followed + these steps. + + + + First examine the MySQL log that you established in step + + of section + + . Ensure that the INSERT statements are not being rejected + because of a malformed table name or other typographical + error. By enabling that log, you instructed MySQL to log + every connection and command it receives -- if you see no + INSERT attempts in the log, the module isn't successfully + connecting to the database. If you see nothing at all in + the log -- not even a record of your administrative + connection attempts, then you did not enable the log + correctly. If you do see INSERT attempts but they are + failing, the log should tell you why. + + + Second, confirm that your LogSQL* directives are all + correct. + + + Third, examine the Apache error logs for messages from + mod_log_sql; the module will offer hints as to why it + cannot connect, etc. + + + The next thing to do is to change the LogLevel directive + + in the main server config as well as in each VirtualHost + config: + + + LogLevel debug +ErrorLog /var/log/httpd/server-messages + + + + + + Why do I get the message "insufficient configuration info + to establish database link" in my Apache error log? + + + + + At a minimum, LogSQLLoginInfo in the URl form and either + LogSQLTableName or LogSQLMassVirtualHosting must be + defined in order for the module to be able to establish a + database link. If these are not defined or are incomplete + you will receive this error message. + + + + + + + My database cannot handle all the open connections from + mod_log_sql, is there anything I can do? + + + + + The rule of thumb: if you have n webservers each + configured to support y MaxClients, then your database + must be able to handle n times y simultaneous connections + in the worst case. Certainly you must use common sense, + consider reasonable traffic expectations and structure + things accordingly. + + + + + Tweaking my.cnf to scale to high connection loads is + imperative. But if hardware limitations prevent your MySQL + server from gracefully handling the number of incoming + connections, it would be beneficial to upgrade the memory + or CPU on that server in order to handle the load. + + + + + Jeremy Zawodny, a highly respected MySQL user and + contributor to Linux Magazine, has this very helpful and + highly appropriate article on tuning MySQL: + + MySQL, Linux, and Thread Caching + + + + + + Please remember that mod_log_sql's overriding principle is + performance -- that is what the target audience demands + and expects. Other database logging solutions do not open + and maintain many database connections, but their + performance suffers drastically. For example, pgLOGd + funnels all log connections through a separate daemon that + connects to the database, but that bottlenecks the entire + process. mod_log_sql achieves performance numbers an order + of magnitude greater than the alternatives because it + dispenses with the overhead associated with rapid + connection cycling, and it does not attempt to shoehorn + all the database traffic through a single extra daemon or + proxy process. + + + + + + Currently connection pooling is being implemented as + part of the Database Abstraction layer to allow multiple + httpd processes to share connections. + + + + + + + + Why do I occasionally see a "lost connection to MySQL + server" message in my Apache error log? + + + + + This message may appear every now and then in your Apache + error log, especially on very lightly loaded servers. This + does not mean that anything is necessarily wrong. Within + each httpd child process, mod_log_sql will open (and keep + open) a connection to the MySQL server. MySQL, however, + will close connections that have not been used in a while; + the default timeout is 8 hours. When this occurs, + mod_log_sql will notice and re-open the connection. That + event is what is being logged, and looks like this: + + [Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed, + API said: error 2013, Lost connection to MySQL server during query +[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful +[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful + + Reference: + + MySQL documentation + + + + + + + + Sometimes a single VirtualHost gets logged to two + different tables (e.g. access_foo_com, + access_www_foo_com). Or, accesses to an unqualified + hostname (e.g. "http://intranet/index.html") get logged in + separate tables. + + + + + Proper usage of the Apache runtime ServerName directive + and the directive UseCanonicalName On (or DNS) are + necessary to prevent this problem. "On" is the default for + UseCanonicalName, and specifies that self-referential URLs + are generated from the ServerName part of your + VirtualHost: + + + With UseCanonicalName on (and in all versions prior to + 1.3) Apache will use the ServerName and Port directives to + construct the canonical name for the server. With + UseCanonicalName off Apache will form self-referential + URLs using the hostname and port supplied by the client if + any are supplied (otherwise it will use the canonical + name, as defined above). [From + + the Apache documentation + + ] + + + The module inherits Apache's "knowledge" about the server + name being accessed. As long as those two directives are + properly configured, mod_log_sql will log to only one + table per virtual host while using + LogSQLMassVirtualHosting. + + + + + + Performance and Tuning + + + How well does it perform? + + + + mod_log_sql scales to very high loads. Apache 1.3.22 + + mod_log_sql was benchmarked using the "ab" (Apache Bench) + program that comes with the Apache distribution; here are + the results. + + + Overall configuration + + Machine A: Apache webserver + + + Machine B: MySQL server + + + + Machines A and B connected with 100Mbps Ethernet + + + + + Webserver: Celeron 400, 128MB RAM, IDE storage + + + + + Apache configuration + Timeout 300 +KeepAlive On +MaxKeepAliveRequests 100 +KeepAliveTimeout 15 +MinSpareServers 5 +StartServers 10 +MaxSpareServers 15 +MaxClients 256 +MaxRequestsPerChild 5000 +LogSQLTransferLogFormat AbHhmRSsTUuvc +LogSQLWhichCookie Clicks +CookieTracking on +CookieName Clicks + + + "ab" commandline + ./ab -c 10 -t 20 -v 2 -C Clicks=ab_run http://www.hostname.com/target + + + ( 10 concurrent requests; 20 second test; setting a cookie + "Clicks=ab_run"; target = the mod_log_sql homepage. ) + + + Ten total ab runs were conducted: five with MySQL logging + enabled, and five with all MySQL directives commented out + of httpd.conf. Then each five were averaged. The results: + + + + + Average of five runs employing MySQL and standard text + logging: + + 139.01 requests per second, zero errors. + + + + + + Average of five runs employing only standard text + logging: + + 139.96 requests per second, zero errors. + + + + + + In other words, any rate-limiting effects on this + particular hardware setup are not caused by MySQL. Note + that although this very simple webserver setup is hardly + cutting-edge -- it is, after all, a fairly small machine + -- 139 requests per second equal over twelve million hits + per day. + + + + If you run this benchmark yourself, take note of three + things: + + + + Use a target URL that is on your own webserver :-). + + + + + Wait until all your connections are closed out between + runs; after several thousand requests your TCP/IP + stack will be filled with hundreds of connections in + TIME_WAIT that need to close. Do a "netstat -t|wc -l" + on the webserver to see. If you don't wait, you can + expect to see a lot of messages like "ip_conntrack: + table full, dropping packet" in your logs. (This has + nothing to do with mod_log_sql, this is simply the + nature of the TCP/IP stack in the Linux kernel.) + + + + + When done with your runs, clean these many thousands + of requests out of your database: + + mysql> delete from access_log where agent like 'ApacheBench%'; +mysql> optimize table access_log; + + + + + + + + Do I need to be worried about all the running MySQL + children? Will holding open n Apache-to-MySQL connections + consume a lot of memory? + + + + Short answer: you shouldn't be worried. + + + + Long answer: you might be evaluating at the output of "ps + -aufxw" and becoming alarmed at all the 7MB httpd + processes or 22MB mysqld children that you see. Don't be + alarmed. It's true that mod_log_sql opens and holds open + many MySQL connections: each httpd child maintains one + open database connection (and holds it open for + performance reasons). Four webservers, each running 20 + Apache children, will hold open 80 MySQL connections, + which means that your MySQL server needs to handle 80 + simultaneous connections. In truth, your MySQL server + needs to handle far more than that if traffic to your + website spikes and the Apache webservers spawn off an + additional 30 children each... + + + Fortunately the cost reported by 'ps -aufxw' is deceptive. + This is due to an OS memory-management feature called + "copy-on-write." When you have a number of identical child + processes (e.g. Apache, MySQL), it would appear in "ps" as + though each one occupies a great deal of RAM -- as much as + 7MB per httpd child! In actuality each additional child + only occupies a small bit of extra memory -- most of the + memory pages are common to each child and therefore shared + in a "read-only" fashion. The OS can get away with this + because the majority of memory pages for one child are + identical across all children. Instead of thinking of each + child as a rubber stamp of the others, think of each child + as a basket of links to a common memory area. + + + A memory page is only duplicated when it needs to be + written to, hence "copy-on-write." The result is + efficiency and decreased memory consumption. "ps" may + report 7MB per child, but it might really only "cost" 900K + of extra memory to add one more child. It is not correct + to assume that 20 Apache children with a VSZ of 7MB each + equals (2 x 7MB) of memory consumption -- the real answer + is much, much lower. The same "copy-on-write" rules apply + to all your MySQL children: 40 mysqld children @ 22MB each + do not occupy 880MB of RAM. + + + The bottom line: although there is a cost to spawn extra + httpd or mysqld children, that cost is not as great as + "ps" would lead you to believe. + + + + + + + My webserver cannot handle all the traffic that my site + receives, is there anything I can do? + + + + + If you have exhausted all the tuning possibilities on your + existing server, it is probably time you evaluated the + benefits of clustering two or more webservers together in + a load-balanced fashion. In fact, users of such a setup + are mod_log_sql's target audience! + + + + + + + What is the issue with activating delayed inserts? + + + + + INSERT DELAYED is a specific syntax to MySQL and is not + supported by any other database. Ergo, why is it needed, + and what MySQL deficiency is it working around? INSERT + DELAYED is a kluge. + + + + + The MySQL documentation is unclear whether INSERT DELAYED + is even necessary for an optimized database. It says, "The + DELAYED option for the INSERT statement is a + MySQL-specific option that is very useful if you have + clients that can't wait for the INSERT to complete." But + then it goes on to say, "Note that as MyISAM tables + supports concurrent SELECT and INSERT, if there is no free + blocks in the middle of the data file, you very seldom + need to use INSERT DELAYED with MyISAM." + + + + + Because INSERT DELAYED returns without waiting for the + data to be written, a hard kill of your MySQL database at + the right (wrong?) moment could lose those logfile + entries. + + + + + As of MySQL version 3.23.52, the error return functions + disagree after a failed INSERT DELAYED: mysql_errno() + always returns 0, even if mysql_error() returns a textual + error. I have reported this bug to the MySQL folks. + However, we have no way of knowing what solution they will + adopt to fix this, and with the worst case solution + mod_log_sql would not be able to tell if anything went + wrong with a delayed insert. + + + + + Instead of delayed inserts, you may wish to utilize InnoDB + tables (instead of the standard MyISAM tables). InnoDB + tables suppot row-level locking and are recommended for + high-volume databases. + + + + + If after understanding these problems you still wish to + enable delayed inserts, section + + discusses how. + + + + + + "How do I...?" -- accomplishing certain tasks + + + + How do I extract the data in a format that my analysis + tool can understand? + + + + + mod_log_sql would be virtually useless if there weren't a + way for you to extract the data from your database in a + somewhat meaningful fashion. To that end there's a Perl + script enclosed with the distribution. That script + (make_combined_log.pl) is designed to extract N-many days + worth of access logs and provide them in a Combined Log + Format output. You can use this very tool right in + /etc/crontab to extract logs on a regular basis so that + your favorite web analysis tool can read them. Or you can + examine the Perl code to construct your own custom tool. + + + For example, let's say that you want your web statistics + updated once per day in the wee hours of the morning. A + good way to accomplish that could be the following entries + in /etc/crontab: + + # Generate the temporary apache logs from the MySQL database (for webalizer) +05 04 * * * root make_combined_log.pl 1 www.grubbybaby.com > /var/log/temp01 +# Run webalizer on httpd log +30 04 * * * root webalizer -c /etc/webalizer.conf; rm -f /var/log/temp01 + + Or if you have a newer system that puts files in + /etc/cron.daily etc., create a file called "webalizer" in + the cron.daily subdirectory. Use the following as the + contents of your file, and make sure to chmod 755 it when + done. + + #!/bin/sh +/usr/local/sbin/make_combined_log.pl 1 www.yourdomain.com > /var/log/httpd/templog +/usr/local/bin/webalizer -q -c /etc/webalizer.conf +rm -f /var/log/httpd/templog + See? Easy. + + + + + How can I log mod_usertrack cookies? + + + + A number of people like to log mod_usertrack cookies in + their Apache TransferLog to aid in understanding their + visitors' clickstreams. This is accomplished, for example, + with a statement as follows: + + LogFormat "%h %l %u %t \"%r\" %s %b \"%{Referer}i\" \"%{User-Agent}i\"" \"%{cookie}n\"" + + Naturally it would be nice for mod_log_sql to permit the + admin to log the cookie data as well, so as of version + 1.10 you can do this. You need to have already compiled + mod_usertrack into httpd -- it's one of the standard + Apache modules. + + + First make sure you have a column called "cookie" in the + MySQL database to hold the cookies, which can be done as + follows if you already have a working database: + + mysql> alter table acc_log_tbl add column cookie varchar(255); + + Next configure your server to set usertracking cookies as + follows, and make sure you include the new 'c' directive + in your LogSQLTransferLogFormat, which activates cookie + logging. Here's an example: + + <VirtualHost 1.2.3.4> + CookieTracking on + CookieStyle Cookie + CookieName Foobar + LogSQLTransferLogFormat huSUsbTvRAc + LogSQLWhichCookie Foobar +</VirtualHost> + + The first three lines configure mod_usertrack to create a + COOKIE (RFC 2109) format cookie called Foobar. The last + two lines tell mod_log_sql to log cookies named Foobar. + You have to choose which cookie to log because more than + one cookie can/will be sent to the server by the client. + + + Recap: the 'c' character activates cookie logging, and the + LogSQLWhichCookie directive chooses which cookie to log. + + + FYI, you are advised NOT to use CookieStyle Cookie2 -- it + seems that even newer browsers (IE 5.5, etc.) have trouble + with the new COOKIE2 (RFC 2965) format. Just stick with + the standard COOKIE format and you'll be fine. + + + Perform some hits on your server and run a select + + SELECT request_uri,cookie +FROM access_log +WHERE cookie IS NOT NULL; + + + + + + + + request_uri + cookie + + + + + /mod_log_sql/ + + ool-18e4.dyn.optonline.net.130051007102700823 + + + + /mod_log_sql/usa.gif + + ool-18e4.dyn.optonline.net.130051007102700823 + + + + /mod_log_sql/style_1.css + + ool-18e4.dyn.optonline.net.130051007102700823 + + + + +
+ + + + + + What if I want to log more than one cookie? What is the + difference between LogSQLWhichCookie and + LogSQLWhichCookies? + + + + + As of version 1.17, you have a choice in how you want + cookie logging handled. + + + If you are interested in logging only one cookie per + request, follow the instructions in FAQ entry + + above. That cookie will be logged to a column in the + regular access_log table, and the actual cookie you want + to log is specified with LogSQLWhichCookie. Don't forget + to specify the 'c' character in LogSQLTransferLogFormat. + + + If, however, you need to log multiple cookies per request, + you must employ the LogSQLWhichCookies (note the plural) + directive. The cookies you specify will be logged to a + separate table (as discussed in section + + ), and entries in that table will be linked to the regular + access_log entries via the unique ID that is supplied by + mod_unique_id. Without mod_unique_id the information will + still be logged but you will be unable to correlate which + cookies go with which access-requests. Furthermore, with + LogSQLWhichCookies, you do not need to include the 'c' + character in LogSQLTransferLogFormat. + + + LogSQLWhichCookie and LogSQLWhichCookies can coexist + without conflict because they operate on entireley + different tables, but you're better off choosing the one + you need. + + + + + + + What are the SSL logging features, and how do I activate + them? + + + + + + You do not need to compile SSL support into mod_log_sql + in order to simply use it with a secure site. You only + need to compile SSL support into mod_log_sql if you want + to log SSL-specific data such as the cipher type used, + or the keysize that was negotiated. If that information + is unimportant to you, you can ignore this FAQ. + + + + By adding certain characters to your + LogSQLTransferLogFormat string you can tell mod_log_sql to + log the SSL cipher, the SSL keysize of the connection, and + the maximum keysize that was available. This would let you + tell, for example, which clients were using only + export-grade security to access your secure software area. + + + You can compile mod_log_sql with SSL logging support if + you have the right packages installed. If you already have + an SSL-enabled Apache then you by definition have the + correct packages already installed: OpenSSL and mod_ssl. + + + You need to ensure that your database is set up to log the + SSL data. Issue the following commands to MySQL if your + access table does not already have them: + + mysql> alter table access_log add column ssl_cipher varchar(25); +mysql> alter table access_log add column ssl_keysize smallint unsigned; +mysql> alter table access_log add column ssl_maxkeysize smallint unsigned; + + Finally configure httpd.conf to activate the SSL fields. + Note that this is only meaningful in a VirtualHost that is + set up for SSL. + + <VirtualHost 1.2.3.4:443> + LogSQLTransferLogFormat AbHhmRSsTUuvcQqz +</VirtualHost> + + You also need to make sure you have the mod_log_sql_ssl + module loaded as well. + + + The last three characters (Qqz) in the directive are the + SSL ones; see section + + in the directives documentation for details of the + LogSQLTransferLogFormat directive. + + + Restart Apache, then perform some hits on your server. + Then run the following select statement: + + SELECT remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize +FROM access_log +WHERE ssl_cipher IS NOT NULL; + + + + + + + + + + + remote_host + request_uri + ssl_cipher + ssl_keysize + ssl_maxkeysize + + + + + 216.192.52.4 + /dir/somefile.html + RC4-MD5 + 128 + 128 + + + 216.192.52.4 + /dir/somefile.gif + RC4-MD5 + 128 + 128 + + + 216.192.52.4 + /dir/somefile.jpg + RC4-MD5 + 128 + 128 + + + +
+ + + + +
+
-- cgit v0.9.2