From 360b6956ce9ecba3d671cf579f38aa5bcfbb657c Mon Sep 17 00:00:00 2001 From: Edward Rudd Date: Wed, 12 May 2004 23:31:12 +0000 Subject: renamed directory Documentation to docs updated configure and makefile to use new m4 files --- (limited to 'docs/documentation.lyx') diff --git a/docs/documentation.lyx b/docs/documentation.lyx new file mode 100644 index 0000000..eeb6af5 --- /dev/null +++ b/docs/documentation.lyx @@ -0,0 +1,7202 @@ +#LyX 1.3 created this file. For more info see http://www.lyx.org/ +\lyxformat 221 +\textclass article +\language english +\inputencoding default +\fontscheme default +\graphics default +\float_placement !htbp +\paperfontsize 10 +\spacing single +\papersize letterpaper +\paperpackage a4 +\use_geometry 1 +\use_amsmath 0 +\use_natbib 0 +\use_numerical_citations 0 +\paperorientation portrait +\leftmargin 1in +\topmargin 0.5in +\rightmargin 1in +\bottommargin 0.65in +\secnumdepth 3 +\tocdepth 3 +\paragraph_separation indent +\defskip medskip +\quotes_language english +\quotes_times 2 +\papercolumns 1 +\papersides 1 +\paperpagestyle default + +\layout Title +\added_space_top vfill \added_space_bottom vfill +Installing and Running mod_log_sql +\layout Author + +Christopher Powell, +\layout Standard +\pagebreak_bottom + +\begin_inset LatexCommand \tableofcontents{} + +\end_inset + + +\layout Section + +Introduction +\layout Subsection + +Homepage +\layout LyX-Code + +http://www.grubbybaby.com/mod_log_sql/ +\layout Subsection + +Summary +\layout Standard + +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 section +\begin_inset LatexCommand \ref{sub:why} + +\end_inset + + in the FAQ for further discussion and examples of the advantages to SQL.) +\layout Standard + +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. +\layout Subsection + +Approach +\layout Standard + +This project was formerly known as +\begin_inset Quotes eld +\end_inset + +mod_log_mysql. +\begin_inset Quotes erd +\end_inset + + It was renamed +\begin_inset Quotes eld +\end_inset + +mod_log_sql +\begin_inset Quotes erd +\end_inset + + in order to reflect the project goal of database-inspecificity. + The module currently supports MySQL, but support for other database backends + is underway. +\layout Standard + +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 administrat +or of database issues in the Apache +\noun on +ErrorLog +\noun default + for the server/virtual server. +\layout Standard + +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. +\layout Standard + +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 & group ID of the running Apache process, + e.g. + "nobody/nobody" on many Linux installations). + When database availablity 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. +\layout Subsection + +What gets logged by default? +\layout Standard + +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. +\layout Standard + +The documentation of the run-time directives includes a full explanation + of what you can log, including examples -- see section +\begin_inset LatexCommand \ref{sec:ConfRef} + +\end_inset + +. +\layout Subsection + +Miscellaneous Notes +\layout Itemize + +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. +\layout Itemize + +The 'time_stamp' field is stored in an UNSIGNED INTEGER format, in the standard + unix +\begin_inset Quotes eld +\end_inset + +seconds since the epoch +\begin_inset Quotes erd +\end_inset + + format. + This is superior to storing the access time as a string due to size requirement +s: 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. +\begin_deeper +\layout Standard + +In MySQL 3.21 and above you can easily convert this to a human readable format + using from_unixtime(), e.g.: +\layout LyX-Code + +select remote_host,request_uri,from_unixtime(time_stamp) from access_log; +\layout Standard + +The enclosed perl program +\begin_inset Quotes eld +\end_inset + +make_combined_log.pl +\begin_inset Quotes srd +\end_inset + + 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. +\end_deeper +\layout Itemize + +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-len +gth 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. +\layout Itemize + +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. +\layout Itemize + +When Apache logs a numeric field, it uses a '-' character to mean +\begin_inset Quotes eld +\end_inset + +not applicable, +\begin_inset Quotes erd +\end_inset + + 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. +\layout Subsection + +Author / Maintainer +\layout Standard + +The actual logging code was taken from the already existing flat file text + modules, so all that credit goes to the Apache Server group. +\layout Standard + +The MySQL routines and directives were added by Zeev Suraski . +\layout Standard + +All changes from 1.06+ and the new documentation were added by Chris Powell + . + It seems that the module had fallen into the "unmaintained" category -- + it hadn't been updated since 1998 -- so Chris adopted it as the new maintainer. +\layout Section + +Installation +\layout Subsection + +Requirements +\layout Itemize + +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. +\layout Itemize + +Apache 1.2 or 1.3. + Ideally you should already have successfully compiled Apache and understand + the process, but this document tries to make it simple for beginners. +\layout Itemize + +The MySQL development headers. + This package is called different things on different distros. + For example, Red Hat 6.x calls this RPM +\begin_inset Quotes eld +\end_inset + +MySQL-devel +\begin_inset Quotes erd +\end_inset + + whereas Mandrake calls it +\begin_inset Quotes eld +\end_inset + +libmysql10-devel. +\begin_inset Quotes erd +\end_inset + + +\layout Itemize + +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. +\layout Itemize + +Optionally, if you want to be able to log SSL information such as keysize + or cipher, you need OpenSSL and mod_ssl installed. +\layout Subsection + +Platform-specific notes +\layout Standard + +These installation documents assume a relatively modern GNU/Linux scenario. + mod_log_sql has been ported to other platforms; following are notes on + compiling the module for those platforms. +\layout Subsubsection + + +\begin_inset LatexCommand \label{sub:Solaris} + +\end_inset + +Solaris +\layout Standard + +The nanosleep() function used in mod_log_sql relies on linking aginst the + librt library. + Make the following alterations before proceeding: +\layout Enumerate + +In Makefile, search for the string +\begin_inset Quotes eld +\end_inset + +-lmysqlclient -lz +\begin_inset Quotes erd +\end_inset + + and change it to read +\begin_inset Quotes eld +\end_inset + +-lmysqlclient -lz -lrt +\begin_inset Quotes erd +\end_inset + + +\layout Enumerate + +In part +\begin_inset LatexCommand \ref{step:Linking} + +\end_inset + + of section +\begin_inset LatexCommand \ref{sec:Static} + +\end_inset + + below, change +\begin_inset Quotes eld +\end_inset + +-lmysqlclient -lm -lz +\begin_inset Quotes erd +\end_inset + + to read +\begin_inset Quotes eld +\end_inset + +-lmysqlclient -lm -lz -lrt +\begin_inset Quotes erd +\end_inset + + +\layout Subsubsection + +BSD +\layout Standard + +No notes are available at present, but they are desired. + If you have successfully ported mod_log_sql to BSD, +\emph on +please +\emph default +contact +\begin_inset LatexCommand \url[the maintaniner, Chris Powell]{(chris@grubbybaby.com)} + +\end_inset + + and help fill in this section. +\layout Subsubsection + +Win32 +\layout Standard + +No notes are available at present, but they are desired. + If you have successfully ported mod_log_sql to Win32, +\emph on +please +\emph default +contact +\begin_inset LatexCommand \url[the maintaniner, Chris Powell]{(chris@grubbybaby.com)} + +\end_inset + + and help fill in this section. +\layout Subsubsection + +OS X +\layout Standard + +mod_log_sql should compile and work out-of-the-box on this platform. + Here are some notes from a user successfully running the module on OS X: +\layout Quote + + +\emph on +The only changes I had to make were to where I had the various libraries + installed. +\layout Quote + + +\emph on +Here are the changes I made to the head of the Makefile: +\layout LyX-Code + +APACHESOURCE = /usr/local/src/apache_1.3.27 +\layout Quote + + +\emph on +(Wasn't sure if this was really needed or not, so I downloaded the Apache + source just in case) +\layout LyX-Code + +APACHEINSTALLED = /usr/sbin +\layout LyX-Code + +APACHEHEADERS = /usr/include/httpd +\layout LyX-Code + +APXS = $(APACHEINSTALLED)/apxs +\layout LyX-Code + +MYSQLLIBRARIES = /usr/local/mysql/lib +\layout LyX-Code + +MYSQLHEADERS = /usr/local/mysql/include +\layout Quote + + +\emph on +I'm using a binary installation of MySQL and the default apache installation + on OS X Client 10.2.3, the locations of these files may vary depending on + how you've installed MySQL and will almost certainly be different if you're + using OS X Server. +\layout Standard + +My thanks to Tom Wiebe for being the first (to my knowlege) mod_log_sql + user on OS X and for providing these notes. +\layout Subsubsection + +Digital Unix +\layout Standard + +Digital Unix, like Solaris, needs to be linked against librt; see section + +\begin_inset LatexCommand \ref{sub:Solaris} + +\end_inset + +. + Here are further notes from a user successfully running the module on Digital + Unix: +\layout Quote + + +\emph on +Instead of trying to get the module to remember where the MySQL libraries + were, I instead compiled apache with the information: +\layout Quote + + +\emph on +LDFLAGS='-rpath /isp/mysql/lib/mysql' ./configure ... +\layout Quote + + +\emph on +Everything worked as expected after that. + (The error I got without this was "/sbin/loader: Fatal Error: cannot map + libmysqlclient.so" ) +\layout Quote + + +\emph on +Digital Unix (v4.0f, at least ) appears to follow the same requirements needed + by Solaris, so simply adding librt to the module made it compile without + errors. +\layout Quote + + +\emph on +As for the warnings, here's the text: +\layout LyX-Code + + +\emph on +mod_log_sql.c: In function `extract_request_duration': +\layout LyX-Code + + +\emph on +mod_log_sql.c:292: warning: long int format, different type arg (arg 4) +\layout LyX-Code + + +\emph on +mod_log_sql.c: In function `extract_request_timestamp': +\layout LyX-Code + + +\emph on +mod_log_sql.c:497: warning: long int format, different type arg (arg 4) +\layout Quote + + +\emph on +Poking around in the code, it looks like the compiler was complaining that + what time() is returning doesn't play nicely with %ld by default. + I just typecast them as (long)'s and the warnings went away ( not that + the module wasn't working correctly without them ). +\layout Quote + + +\emph on +The module works very well so far in testing... + hasn't dropped a single log entry yet. + +\layout Standard + +My thanks to Jim Turner for permitting me to quote him here, and for being + the first known user of mod_log_sql on Digital Unix. +\layout Subsection + +Do I want a DSO or a static module? +\layout Standard + +You need to know the answer to this question before you proceed. + The answer is pretty straightforward: what have you done in the past? If + you like all your Apache modules to be dynamic, then you should keep doing + that. + If you're more of an old-school type and prefer to compile the modules + right into apache, do that. + Both methods work equally well. +\layout Standard + +FWIW, the DSO method is more modern and increasing in popularity because + apxs takes care of a lot of dirty little details for you. + As you'll see below, the static-module method is a little more complex. +\layout Subsection + +Installation as an Apache DSO (Preferred) +\layout Enumerate + +Perform all the following steps as root so that you have install privs, + etc. + Unpack the archive into a working directory. +\begin_deeper +\layout LyX-Code + +# tar zxf mod_log_sql.tar.gz -C /usr/local/src +\layout LyX-Code + +# cd /usr/local/src/mod_log_sql +\end_deeper +\layout Enumerate + +Edit Makefile and change the values of the variables in the first section. + +\begin_deeper +\layout Enumerate + +These paths are +\series bold +necessary: +\begin_deeper +\layout Description + +APACHEINSTALLED: the location where you installed Apache -- usually /usr/local/a +pache, 'locate apxs' can help you find it. +\layout Description + +APACHEHEADERS: The location of your Apache header files, find using 'locate + httpd.h' +\layout Description + +MYSQLLIBRARIES: The location of your MySQL libraries, find using 'locate + libmysqlclient.so' +\layout Description + +MYSQLHEADERS: The location of your MySQL header files, find using 'locate + mysql.h' +\end_deeper +\layout Enumerate + + +\series bold +Optional +\series default +: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize' + and 'cipher type': +\begin_deeper +\layout Description + +MODSSLHEADERS: the location of your mod_ssl header files, find using 'locate + mod_ssl.h' +\layout Description + +DB1HEADERS: the location of your db1 header files, find using 'locate ndbm.h' +\end_deeper +\layout Standard + +You do +\series bold +not +\series default +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 +\series bold +if you want to log SSL-specific data +\series default +such as the cipher type. +\end_deeper +\layout Enumerate + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting + a # character in front of it: +\begin_deeper +\layout LyX-Code + +#MODSSLHDRS=/usr/include/... +\end_deeper +\layout Enumerate + +Instruct apxs to compile the module as a DSO. +\begin_deeper +\layout LyX-Code + +# make dso +\layout Standard + +You should see output similar to the following: +\layout LyX-Code + +/usr/local/Apache/bin/apxs -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -c -I/usr/... +\layout LyX-Code + +gcc -DLINUX=22 -DNO_DBM_REWRITEMAP -DMOD_SSL=208111 -DUSE_HS... + +\layout LyX-Code + +gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc... +\layout Standard + +You should see no errors and have a new file called "mod_log_sql.so" in your + directory. +\end_deeper +\layout Enumerate + +Instruct apxs to install the DSO. +\begin_deeper +\layout LyX-Code + +# make dsoinstall +\layout Standard + +You should see output similar to the following: +\layout LyX-Code + +/usr/local/Apache/bin/apxs -i mod_log_sql.so +\layout LyX-Code + +cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so +\layout LyX-Code + +chmod 755 /usr/local/Apache/libexec/mod_log_sql.so +\end_deeper +\layout Enumerate + +Load and activate the module in httpd.conf: +\begin_deeper +\layout Enumerate + +Insert this line in the same area as other logging modules, e.g. + near +\begin_inset Quotes eld +\end_inset + +LoadModule config_log_module +\begin_inset Quotes erd +\end_inset + +: +\begin_deeper +\layout LyX-Code + +LoadModule sql_log_module libexec/mod_log_sql.so +\end_deeper +\layout Enumerate + +Insert this line in the same area as other logging modules, e.g. + near +\begin_inset Quotes eld +\end_inset + +AddModule mod_log_config.c +\begin_inset Quotes erd +\end_inset + +: +\begin_deeper +\layout LyX-Code + +AddModule mod_log_sql.c +\end_deeper +\end_deeper +\layout Enumerate + +Module ordering within httpd.conf is important if you are logging SSL information. + Please ensure that +\begin_deeper +\layout LyX-Code + +LoadModule ssl_module libexec/libssl.so +\layout Standard + +comes before +\layout LyX-Code + +LoadModule sql_log_module libexec/mod_log_sql.so +\layout Standard + +in your httpd.conf file. + If they are out of order, simply cut-and-paste the +\begin_inset Quotes eld +\end_inset + +ssl_module +\begin_inset Quotes erd +\end_inset + + section so that it is at the top. + If you do not, you will get this error when you start Apache: +\layout LyX-Code + +/usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup +\layout LyX-Code + +/usr/local/apache/bin/apachectl startssl: httpd could not be started +\layout Standard + +(mod_log_sql has a dependency on mod_ssl for SSL symbols. + If the statements are out of order, mod_log_sql cannot recognize those + symbols.) +\layout Standard + +Now skip below to section +\begin_inset LatexCommand \ref{sec:Configuration} + +\end_inset + +, +\series bold +Configuration +\series default +. +\end_deeper +\layout Subsection + + +\begin_inset LatexCommand \label{sec:Static} + +\end_inset + +Installation as a static module compiled into httpd +\layout Enumerate + +Perform all the following steps as root so that you have install privs, + etc. +\layout Enumerate + +Unpack the archive into a working directory. +\begin_deeper +\layout LyX-Code + +# tar zxf mod_log_sql.tar.gz -C /usr/local/src +\layout LyX-Code + +# cd /usr/local/src/mod_log_sql +\end_deeper +\layout Enumerate + + +\begin_inset LatexCommand \label{step:editMF} + +\end_inset + +Edit Makefile and change the values of the variables in the first section. + +\begin_deeper +\layout Enumerate + +These are +\series bold +necessary: +\begin_deeper +\layout Description + +APACHEINSTALLED: the location where you installed Apache -- usually /usr/local/a +pache, 'locate apxs' can help you find it. +\layout Description + +APACHESOURCE: the location of your Apache +\series bold +sources +\series default +, find using 'locate ABOUT_APACHE' +\layout Description + +APACHEHEADERS: the location of your Apache header files, find using 'locate + httpd.h' +\layout Description + +MYSQLLIBRARIES: the location of your MySQL libraries, find using 'locate + libmysqlclient.so' +\layout Description + +MYSQLHEADERS: the location of your MySQL header files, find using 'locate + mysql.h' +\end_deeper +\layout Enumerate + + +\series bold +Optional +\series default +: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize' + and 'cipher type': +\begin_deeper +\layout Description + +MODSSLHEADERS: the location of your mod_ssl header files, find using 'locate + mod_ssl.h' +\layout Description + +DB1HEADERS: the location of your db1 header files, find using 'locate ndbm.h' +\end_deeper +\layout Standard + +You do +\series bold +not +\series default +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 +\series bold +if you want to log SSL-specific data +\series default + such as the cipher type. +\end_deeper +\layout Enumerate + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting + a # character in front of it: +\begin_deeper +\layout LyX-Code + +#MODSSLHDRS=/usr/include/... +\end_deeper +\layout Enumerate + +Compile the module. +\begin_deeper +\layout LyX-Code + +# make static +\layout Standard + +You should see output similar to the following: +\layout LyX-Code + +gcc -fpic -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo... +\layout Standard + +You should see no errors and have a new file called "mod_log_sql.o" in your + directory. +\end_deeper +\layout Enumerate + +Install the module. +\begin_deeper +\layout LyX-Code + +# make statinstall +\end_deeper +\layout Enumerate + +Change to your Apache source directory. +\begin_deeper +\layout LyX-Code + +# cd /usr/local/src/apache-1.3.22/src +\end_deeper +\layout Enumerate + +Re-compile your httpd binary as follows. +\begin_deeper +\layout Enumerate + + +\begin_inset LatexCommand \label{step:Linking} + +\end_inset + +Make these changes to Configuration.apaci: +\begin_deeper +\layout Itemize + +Append the following string to the EXTRA_LIBS= line. + ("/usr/lib/mysql" is from step +\begin_inset LatexCommand \ref{step:editMF} + +\end_inset + +, and is where your MySQL libraries live): +\layout LyX-Code + +-L/usr/lib/mysql -lmysqlclient -lm -lz +\layout Itemize + +Find the mod_log_config.o line, and insert this line immediately after it: +\layout LyX-Code + +AddModule modules/sql/mod_log_sql.o +\end_deeper +\layout Enumerate + +# cp Configuration.apaci Configuration +\layout Enumerate + +# ./Configure +\layout Enumerate + +# make +\layout Enumerate + +# strip httpd +\end_deeper +\layout Enumerate + +Test your new apache binary: +\begin_deeper +\layout LyX-Code + +# ./httpd -l +\layout Standard + +You should see something like: +\layout LyX-Code + +Compiled-in modules: +\layout LyX-Code + +http_core.c +\layout LyX-Code + +mod_log_sql.c <-- That's the line you're looking for. +\layout LyX-Code + +mod_env.c +\layout LyX-Code + +mod_log_config.c +\layout LyX-Code + +mod_mime.c +\layout LyX-Code + +mod_negotiation.c +\layout LyX-Code + +etc... +\end_deeper +\layout Enumerate + +Install your httpd binary. + Copy it over your old httpd binary, wherever it lives. + You can and should rename your old httpd first so that you can easily revert + to that working version in case of bugs with the new version. +\begin_deeper +\layout LyX-Code + +# /etc/rc.d/init.d/httpd stop +\layout LyX-Code + +# mv /usr/local/Apache/bin/httpd ~/httpd-save +\layout LyX-Code + +# cp -f ./httpd /usr/local/Apache/bin/ +\end_deeper +\layout Section + + +\begin_inset LatexCommand \label{sec:Configuration} + +\end_inset + +Configuration +\layout Subsection + + +\begin_inset LatexCommand \label{sub:PrepDb} + +\end_inset + +Preparing MySQL for logging +\layout Standard + +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. +\layout Standard + +This section will discuss how to get started with a basic config. + Full documentation of all available run-time directives is available in + section +\begin_inset LatexCommand \ref{sec:ConfRef} + +\end_inset + +. +\layout Enumerate + +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. +\layout Enumerate + +We still need to have a logging database created and ready, so run the MySQL + command line client and create a database: +\begin_deeper +\layout LyX-Code + +# mysql -uadmin -pmypassword +\layout LyX-Code + +Enter password: +\layout LyX-Code + +mysql> create database apachelogs; +\end_deeper +\layout Enumerate + + +\begin_inset LatexCommand \label{part:CrTbl} + +\end_inset + +If you want to hand-create the tables, run the enclosed 'create-tables' + SQL script as follows ( +\begin_inset Quotes eld +\end_inset + +create_tables.sql +\begin_inset Quotes erd +\end_inset + + needs to be in your current working directory). +\begin_deeper +\layout LyX-Code + +mysql> use apachelogs +\layout LyX-Code + +Database changed +\layout LyX-Code + +mysql> source create_tables.sql +\end_deeper +\layout Enumerate + +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. +\begin_deeper +\layout LyX-Code + +mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com + identified by 'l0gger'; +\end_deeper +\layout Enumerate + +You may be especially security-paranoid and want "loguser" to +\emph on +not +\emph default +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 + +\begin_inset LatexCommand \ref{part:CrTbl} + +\end_inset + + and use the following GRANT statement instead of the one above: +\begin_deeper +\layout LyX-Code + +mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com +\layout LyX-Code + +identified by 'l0gger'; +\end_deeper +\layout Enumerate + + +\begin_inset LatexCommand \label{step:EnaLog} + +\end_inset + +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: +\begin_deeper +\layout LyX-Code + +log=/var/log/mysql-messages +\layout Standard + +Then restart MySQL. +\layout LyX-Code + +# /etc/rc.d/init.d/mysql restart +\end_deeper +\layout Subsection + +A very basic logging setup in Apache +\layout Enumerate + +Tell the module what database to use and the appropriate authentication + information. +\begin_deeper +\layout Standard + +So, edit httpd.conf and insert the following lines somewhere after any LoadModule + / AddModule statements. + +\emph on +Make sure these statements are +\begin_inset Quotes eld +\end_inset + +global, +\begin_inset Quotes erd +\end_inset + + i.e. + not inside any VirtualHost stanza +\emph default +. + You will also note that you are embedding a password in the file. + Therefore you are advised to +\begin_inset Quotes eld +\end_inset + +chmod 660 httpd.conf +\begin_inset Quotes erd +\end_inset + + to prevent unauthorized regular users from viewing your database user and + password. +\layout Standard + + +\series bold +Example +\series default +: 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. +\layout LyX-Code + +LogSQLLoginInfo dbmachine.foo.com loguser l0gg3r +\layout LyX-Code + +LogSQLDatabase apachelogs +\layout LyX-Code + +LogSQLCreateTables on +\layout Standard + +If your database resides on localhost instead of another host, specify the + MySQL server's socket file as follows: +\layout LyX-Code + +LogSQLSocketFile /your/path/to/mysql.sock +\layout Standard + +If your database is listening on a port other than 3306, specify the correct + TCP port as follows: +\layout LyX-Code + +LogSQLTCPPort 1234 +\end_deeper +\layout Enumerate + +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 +\begin_inset Quotes eld +\end_inset + +access_log +\begin_inset Quotes srd +\end_inset + + by inserting a +\noun on +LogSQLTransferLogTable +\noun default + directive. + (The +\noun on +LogSQLTransferLogTable +\noun default + directive is the minimum required to log -- other directives that you'll + learn about later simply tune the module's behavior.) +\begin_deeper +\layout LyX-Code + + +\layout LyX-Code + + [snip] +\layout LyX-Code + + LogSQLTransferLogTable access_log +\layout LyX-Code + + [snip] +\layout LyX-Code + + +\end_deeper +\layout Enumerate + +Restart apache. +\begin_deeper +\layout LyX-Code + +# /etc/rc.d/init.d/httpd stop +\layout LyX-Code + +# /etc/rc.d/init.d/httpd start +\end_deeper +\layout Subsection + +Testing the basic setup +\layout Enumerate + +Visit your web site in a browser to trigger some hits, then confirm that + the entries are being successfully logged: +\begin_deeper +\layout LyX-Code + +# mysql -hdbmachine.foo.com -umysqladmin -p -e "select * from access_log" + apachelogs +\layout LyX-Code + +Enter password: +\layout Standard + +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 + +\begin_inset LatexCommand \ref{faq:NothingLogged} + +\end_inset + + of the FAQ on how to debug and fix the situation. +\end_deeper +\layout Enumerate + +You can now activate the advanced features of mod_log_sql, which are described + in the next section. +\layout Subsection + +How to tune logging with run-time directives +\layout Subsubsection + +Instructing the module what to log +\layout Standard + +The most basic directive for the module is +\noun on +LogSQLTransferLogFormat +\noun default +, which tells the module which information to send to the database; logging + to the database will not take place without it. + Place a +\noun on +LogSQLTransferLogFormat +\noun default + directive in the VirtualHost stanza of each virtual host that you want + to activate. +\layout Standard + +After +\noun on +LogSQLTransferLogFormat +\noun default + you supply a string of characters that tell the module what information + to log. + In the configuration directive reference (section +\begin_inset LatexCommand \ref{sub:Frmat} + +\end_inset + +) there is a table which clearly defines all the possible things to log. + Let's say you want to log only the +\begin_inset Quotes eld +\end_inset + +request time, +\begin_inset Quotes erd +\end_inset + + the +\begin_inset Quotes eld +\end_inset + +remote host, +\begin_inset Quotes erd +\end_inset + + and the +\begin_inset Quotes eld +\end_inset + +request +\begin_inset Quotes erd +\end_inset + +; you'd use: +\layout LyX-Code + +LogSQLTransferLogFormat hUS +\layout Standard + +But a more appropriate string to use is +\layout LyX-Code + +LogSQLTransferLogFormat AbHhmRSsTUuv +\layout Standard + +which logs all the information required to be compatible with the Combined + Log Format (CLF). +\layout Standard + +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. +\layout Standard + +Some of the +\noun on +LogSQLTransferLogFormat +\noun default + characters require a little extra configuration: +\layout Itemize + +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 +\noun on +LogSQLWhichCookie +\noun default + -- after all, there could be many cookies associated with a given request. + Fail to specify +\noun on +LogSQLWhichCookie +\noun default +, and no cookie information at all will be logged. + +\layout Itemize + +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 +\noun on +LogSQLMachineID +\noun default + directive. + Fail to specify +\noun on +LogSQLMachineID +\noun default +, and a simple '-' character will be logged in the machine_id column. +\layout Subsubsection + + +\begin_inset LatexCommand \label{sub:Ignore} + +\end_inset + +Instructing the module what NOT to log using filtering directives +\layout Standard + +One +\begin_inset Quotes eld +\end_inset + +accept +\begin_inset Quotes erd +\end_inset + + and two +\begin_inset Quotes eld +\end_inset + +ignore +\begin_inset Quotes srd +\end_inset + + 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. +\layout Standard + + +\emph on +It is important to remember that each of these three directives is purely + optional. + mod_log_sql's default is to log everything. + +\layout Standard + +When a request comes in, the contents of +\noun on +LogSQLRequestAccept +\noun default +are evaluated first. + This optional, +\begin_inset Quotes eld +\end_inset + +blanket +\begin_inset Quotes erd +\end_inset + + directive lets you specify that only certain things are to be accepted + for logging, and everything else discarded. + Because it is evaluated before +\noun on +LogSQLRequestIgnore +\noun default +and +\noun on +LogSQLRemhostIgnore +\noun default +it can halt logging before those two filtering directives +\begin_inset Quotes eld +\end_inset + +get their chance. +\begin_inset Quotes erd +\end_inset + + +\layout Standard + +Once a request makes it past +\noun on +LogSQLRequestAccept +\noun default +, it still can be excluded based on +\noun on +LogSQLRemhostIgnore +\noun default + and +\noun on +LogSQLRequestIgnore +\noun default +. + A good way to use +\noun on +LogSQLRemhostIgnore +\noun default + is to prevent the module from logging the traffic that your internal hosts + generate. + +\noun on +LogSQLRequestIgnore +\noun default + is great for preventing things like requests for +\begin_inset Quotes eld +\end_inset + +favicon.ico +\begin_inset Quotes erd +\end_inset + + from cluttering up your database, as well as excluding the various requests + that worms make, etc. +\layout Standard + +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 +\emph on + if it is a substring of the larger request or remote-host; the comarison + is case-sensitive. + +\emph default + This means that +\noun on + +\begin_inset Quotes eld +\end_inset + +LogSQLRemhostIgnore +\noun default + micro +\begin_inset Quotes erd +\end_inset + + will ignore requests from +\begin_inset Quotes eld +\end_inset + +microsoft.com, +\begin_inset Quotes erd +\end_inset + + +\begin_inset Quotes eld +\end_inset + +microworld.net, +\begin_inset Quotes erd +\end_inset + + +\begin_inset Quotes eld +\end_inset + +mymicroscope.org, +\begin_inset Quotes erd +\end_inset + + etc. + +\begin_inset Quotes eld +\end_inset + + +\noun on +LogSQLRequestIgnore +\noun default + gif +\begin_inset Quotes erd +\end_inset + + will instruct the module to ignore requests for +\begin_inset Quotes eld +\end_inset + +leftbar.gif, +\begin_inset Quotes erd +\end_inset + + +\begin_inset Quotes eld +\end_inset + +bluedot.gif +\begin_inset Quotes erd +\end_inset + + and even +\begin_inset Quotes eld +\end_inset + +giftwrap.jpg +\begin_inset Quotes erd +\end_inset + + -- but +\begin_inset Quotes eld +\end_inset + +RED.GIF +\begin_inset Quotes erd +\end_inset + + and +\begin_inset Quotes eld +\end_inset + +Tree.Gif +\begin_inset Quotes erd +\end_inset + + would still get logged because of case sensitivity. +\layout Standard + +A summary of the decision flow: +\layout Enumerate + +If +\noun on +LogSQLRequestAccept +\noun default + exists and a request does not match anything in that list, it is discarded. +\layout Enumerate + +If a request matches anything in the +\noun on +LogSQLRequestIgnore +\noun default +list, it is discarded. +\layout Enumerate + +If a reqiest matches anything in the +\noun on +LogSQLRemhostIgnore +\noun default + list, it is discarded. +\layout Enumerate + +Otherwise the request is logged. +\layout Standard + +This means that you can have a series of directives similar to the following: +\layout LyX-Code + +LogSQLRequestAccept *.html *.gif *.jpg +\layout LyX-Code + +LogSQLRequestIgnore statistics.html bluedot.jpg +\layout Standard + +So the first line instructs the module to +\series bold +only +\series default + log files with html, gif and jpg suffixes; requests for +\begin_inset Quotes eld +\end_inset + +formail.cgi +\begin_inset Quotes erd +\end_inset + + and +\begin_inset Quotes eld +\end_inset + +shopping-cart.pl +\begin_inset Quotes erd +\end_inset + + will never be considered for logging. + ( +\begin_inset Quotes eld +\end_inset + +LeftArrow.JPG +\begin_inset Quotes erd +\end_inset + + will also never be considered for logging -- remember, the comparison is + +\series bold +case sensitive +\series default +.) The second line prunes the list further -- you never want to log requests + for those two objects. +\layout Standard + +Tip: if you want to match all the hosts in your domain such as +\begin_inset Quotes eld +\end_inset + +host1.corp.foo.com +\begin_inset Quotes srd +\end_inset + + and +\begin_inset Quotes eld +\end_inset + +server.dmz.foo.com +\begin_inset Quotes srd +\end_inset + +, simply specify: +\layout LyX-Code + +LogSQLRemhostIgnore foo.com +\layout Standard + +Tip: a great way to catch the vast majority of worm-attack requests and + prevent them from being logged is to specify: +\layout LyX-Code + +LogSQLRequestIgnore root.exe cmd.exe default.ida +\layout Standard + +Tip: 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: +\layout LyX-Code + +LogSQLRequestIgnore .gif .jpg +\layout Subsection + +Advanced logging scenarios +\layout Subsubsection + +Using the module in an ISP environment +\layout Standard + +mod_log_sql has three basic tiers of operation: +\layout Enumerate + +The administrator creates all necessary tables by hand and configures each + Apache VirtualHost by hand. + ( +\noun on +LogSQLCreateTables Off +\noun default +) +\layout Enumerate + +The module is permitted to create necessary tables on-the-fly, but the administr +ator configures each Apache VirtualHost by hand. + ( +\noun on +LogSQLCreateTables On +\noun default +) +\layout Enumerate + +The module is permitted to create all necessary tables and to make intelligent, + on-the-fly configuration of each VirtualHost. + ( +\noun on +LogSQLMassVirtualHosting On +\noun default +) +\layout Standard + +Many users are happy to use the module in its most minimal form: they hand-creat +e any necessary tables (using +\begin_inset Quotes eld +\end_inset + +create_tables.sql +\begin_inset Quotes erd +\end_inset + +), 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 +\noun on +LogSQLMassVirtualHosting +\noun default + 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: +\layout Itemize + +the on-the-fly table creation feature is activated automatically +\layout Itemize + +the transfer log table name is dynamically set from the virtual host's name + (example: a virtual host +\begin_inset Quotes eld +\end_inset + +www.grubbybaby.com +\begin_inset Quotes erd +\end_inset + + gets logged to table +\begin_inset Quotes eld +\end_inset + +access_www_grubbybaby_com +\begin_inset Quotes erd +\end_inset + +) +\layout Standard + +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 +\noun on +LogSQLTransferLogTable +\noun default + 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. +\layout Standard + +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 +\noun on +LogSQLMachineID +\noun default + directive. + The administrator uses this directive to assign a unique identifier to + each machine in the web cluster, e.g. + +\begin_inset Quotes eld +\end_inset + + +\noun on +LogSQLMachineID +\noun default + web01, +\begin_inset Quotes erd +\end_inset + + +\begin_inset Quotes eld +\end_inset + + +\noun on +LogSQLMachineID +\noun default + web02, +\begin_inset Quotes erd +\end_inset + + etc. + Used in conjunction with the 'M' character in +\noun on +LogSQLTransferLogFormat +\noun default +, 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. +\layout Subsubsection + + +\begin_inset LatexCommand \label{secMulTable} + +\end_inset + +Logging many-to-one data in separate tables +\layout Standard + +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 + +\begin_inset Quotes eld +\end_inset + +mod_gzip +\begin_inset Quotes erd +\end_inset + + notes associated with it. + mod_log_sql is capable of logging these relationships due to the elegance + of SQL relational data. +\layout Standard + +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 +\noun on +LogSQLTransferLogFormat +\noun default + 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. + +\layout Standard + + +\begin_inset Float table +wide false +collapsed false + +\layout Caption + + +\begin_inset LatexCommand \label{tblAcc} + +\end_inset + +access_log +\layout Standard +\align center + +\begin_inset Tabular + + + + + + + + + + +\begin_inset Text + +\layout Standard + +id +\end_inset + + +\begin_inset Text + +\layout Standard + +remote_host +\end_inset + + +\begin_inset Text + +\layout Standard + +request_uri +\end_inset + + +\begin_inset Text + +\layout Standard + +time_stamp +\end_inset + + +\begin_inset Text + +\layout Standard + +status +\end_inset + + +\begin_inset Text + +\layout Standard + +bytes_sent +\end_inset + + + + +\begin_inset Text + +\layout Standard + +PPIDskBRH30AAGPtAsg +\end_inset + + +\begin_inset Text + +\layout Standard + +zerberus.aiacs.net +\end_inset + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/index.html +\end_inset + + +\begin_inset Text + +\layout Standard + +1022493617 +\end_inset + + +\begin_inset Text + +\layout Standard + +200 +\end_inset + + +\begin_inset Text + +\layout Standard + +2215 +\end_inset + + + + +\end_inset + + +\end_inset + + +\begin_inset Float table +wide false +collapsed false + +\layout Caption + + +\begin_inset LatexCommand \label{tblNotes} + +\end_inset + +notes_log +\layout Standard +\align center + +\begin_inset Tabular + + + + + + + +\begin_inset Text + +\layout Standard + +id +\end_inset + + +\begin_inset Text + +\layout Standard + +item +\end_inset + + +\begin_inset Text + +\layout Standard + +val +\end_inset + + + + +\begin_inset Text + +\layout Standard + +PPIDskBRH30AAGPtAsg +\end_inset + + +\begin_inset Text + +\layout Standard + +mod_gzip_result +\end_inset + + +\begin_inset Text + +\layout Standard + +OK +\end_inset + + + + +\begin_inset Text + +\layout Standard + +PPIDskBRH30AAGPtAsg +\end_inset + + +\begin_inset Text + +\layout Standard + +mod_gzip_compression_ratio +\end_inset + + +\begin_inset Text + +\layout Standard + +69 +\end_inset + + + + +\end_inset + + +\end_inset + + +\layout Standard + + +\begin_inset Float table +wide false +collapsed false + +\layout Caption + + +\begin_inset LatexCommand \label{tblHdr} + +\end_inset + +headers_log +\layout Standard +\align center + +\begin_inset Tabular + + + + + + + +\begin_inset Text + +\layout Standard + +id +\end_inset + + +\begin_inset Text + +\layout Standard + +item +\end_inset + + +\begin_inset Text + +\layout Standard + +val +\end_inset + + + + +\begin_inset Text + +\layout Standard + +PPIDskBRH30AAGPtAsg +\end_inset + + +\begin_inset Text + +\layout Standard + +Content-Type +\end_inset + + +\begin_inset Text + +\layout Standard + +text/html +\end_inset + + + + +\begin_inset Text + +\layout Standard + +PPIDskBRH30AAGPtAsg +\end_inset + + +\begin_inset Text + +\layout Standard + +Accept-Encoding +\end_inset + + +\begin_inset Text + +\layout Standard + +gzip, deflate +\end_inset + + + + +\begin_inset Text + +\layout Standard + +PPIDskBRH30AAGPtAsg +\end_inset + + +\begin_inset Text + +\layout Standard + +Expires +\end_inset + + +\begin_inset Text + +\layout Standard + +Tue, 28 May 2002 10:00:18 GMT +\end_inset + + + + +\begin_inset Text + +\layout Standard + +PPIDskBRH30AAGPtAsg +\end_inset + + +\begin_inset Text + +\layout Standard + +Cache-Control +\end_inset + + +\begin_inset Text + +\layout Standard + +max-age=86400 +\end_inset + + + + +\end_inset + + +\end_inset + + +\layout Standard + +We have a certain request, and its unique ID is +\begin_inset Quotes eld +\end_inset + +PPIDskBRH30AAGPtAsg +\begin_inset Quotes erd +\end_inset + +. + 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 +\begin_inset LatexCommand \ref{tblAcc} + +\end_inset + +, +\begin_inset LatexCommand \ref{tblNotes} + +\end_inset + + and +\begin_inset LatexCommand \ref{tblHdr} + +\end_inset + +, 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 +\begin_inset Quotes eld +\end_inset + +select +\begin_inset Quotes erd +\end_inset + + statement and table joins. + To see the notes associated with a particular request: +\layout LyX-Code + +select a.remote_host, a.request_uri, n.item, n.val from access_log a, notes_log + n +\layout LyX-Code + +where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg'; +\layout LyX-Code + +\layout Standard +\align center + +\begin_inset Tabular + + + + + + + + +\begin_inset Text + +\layout Standard + +remote_host +\end_inset + + +\begin_inset Text + +\layout Standard + +request_uri +\end_inset + + +\begin_inset Text + +\layout Standard + +item +\end_inset + + +\begin_inset Text + +\layout Standard + +val +\end_inset + + + + +\begin_inset Text + +\layout Standard + +zerberus.aiacs.net +\end_inset + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/index.html +\end_inset + + +\begin_inset Text + +\layout Standard + +mod_gzip_result +\end_inset + + +\begin_inset Text + +\layout Standard + +OK +\end_inset + + + + +\begin_inset Text + +\layout Standard + +zerberus.aiacs.net +\end_inset + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/index.html +\end_inset + + +\begin_inset Text + +\layout Standard + +mod_gzip_compression_ratio +\end_inset + + +\begin_inset Text + +\layout Standard + +69 +\end_inset + + + + +\end_inset + + +\layout LyX-Code + +\layout Standard + +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. +\layout Standard + +In order to use this capability of mod_log_sql, you must do several things: +\layout Itemize + +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. +\layout Itemize + +Create the appropriate tables. + This will be done for you if you permit mod_log_sql to create its own tables + using +\noun on +LogSQLCreateTables On +\noun default +, or if you use the enclosed +\begin_inset Quotes eld +\end_inset + +create_tables.sql +\begin_inset Quotes erd +\end_inset + + script. +\layout Itemize + +Create a SQL index on the +\begin_inset Quotes eld +\end_inset + +id +\begin_inset Quotes erd +\end_inset + + 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. +\layout Itemize + +Within each appropriate VirtualHost stanza, use the +\noun on + LogSQLWhich* +\noun default + and +\noun on +LogSQL*LogTable +\noun default + 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 +\noun on +LogSQLTransferLogTable.) +\layout LyX-Code + + +\layout LyX-Code + + (snip) +\layout LyX-Code + + LogSQLNotesLogTable notestable +\layout LyX-Code + + LogSQLWhichCookies bluecookie redcookie greencookie +\layout LyX-Code + + LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio +\layout LyX-Code + + LogSQLWhichHeadersOut Expires Content-Type Cache-Control +\layout LyX-Code + + LogSQLWhichHeadersIn UserAgent Accept-Encoding Host +\layout LyX-Code + + (snip) +\layout LyX-Code + + +\layout Subsubsection + +Using the same database for production and test +\layout Standard + +Although suboptimal, it is not uncommon to use the same backend database + for the +\begin_inset Quotes eld +\end_inset + +production +\begin_inset Quotes erd +\end_inset + + webservers as well as the +\begin_inset Quotes eld +\end_inset + +test +\begin_inset Quotes erd +\end_inset + + webservers (budgetary constraints, rackspace limits, etc.). + Furthermore, an administrator in this situation may be unable to use +\noun on +LogSQLRemhostIgnore +\noun default +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. +\layout Standard + +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 +\noun on +LogSQLMachineID +\noun default +directive. + Assume a scenario where the production webservers have IDs like +\begin_inset Quotes eld +\end_inset + +web01, +\begin_inset Quotes erd +\end_inset + + +\begin_inset Quotes eld +\end_inset + +web02, +\begin_inset Quotes erd +\end_inset + + and so on -- and the test webservers have IDs like +\begin_inset Quotes eld +\end_inset + +test01, +\begin_inset Quotes erd +\end_inset + + +\begin_inset Quotes eld +\end_inset + +test02, +\begin_inset Quotes erd +\end_inset + + 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: +\layout LyX-Code + +delete from access_log where machine_id like 'test%'; +\layout Subsubsection + + +\begin_inset LatexCommand \label{sub:DelayedIns} + +\end_inset + +Optimizing for a busy database +\layout Standard + +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 compile mod_log_sql for +\begin_inset Quotes eld +\end_inset + +delayed inserts, +\begin_inset Quotes erd +\end_inset + + which are described as follows in the MySQL documentation: +\layout Quote + +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 periodical +ly 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. +\layout Quote + +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. + +\layout Quote + +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. +\layout Quote + +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. + +\layout Standard + +The general disadvantages of delayed inserts are: +\layout Enumerate + +The queued rows are only stored in memory until they are inserted into the + table. + If mysqld dies unexpectedly, any queued rows that weren't written to disk + are lost. +\layout Enumerate + +There is additional overhead for the server to handle a separate thread + for each table on which you use INSERT DELAYED. +\layout Standard + + +\series bold +The MySQL documentation concludes, +\begin_inset Quotes eld +\end_inset + +This means that you should only use INSERT DELAYED when you are really sure + you need it! +\begin_inset Quotes erd +\end_inset + + 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 section +\begin_inset LatexCommand \ref{sub:DelayedInsFAQ} + +\end_inset + + in the FAQ -- you have been warned. +\layout Standard + +If you are experiencing issues which could be solved by delayed inserts, + uncomment the #MYSQLDELAYED line in the Makefile by removing the # that + is in front of it. + Recompile and reinstall your module. + All regular INSERT statements are now INSERT DELAYED, and you should see + no more blocking of the module. +\layout Subsection + + +\begin_inset LatexCommand \label{sec:ConfRef} + +\end_inset + +Configuration directive reference +\layout Standard + +It is imperative that you understand which directives are used +\emph on +only once +\emph default +in the main server config, and which are used inside VirtualHost stanzas + and therefore multiple times within httpd.conf. + The +\begin_inset Quotes eld +\end_inset + +context +\begin_inset Quotes srd +\end_inset + + listed with each entry informs you of this. +\layout Subsubsection + +LogSQLCookieLogTable +\layout LyX-Code + +Syntax: LogSQLCookieLogTable table-name +\layout LyX-Code + +Example: LogSQLCookieLogTable cookie_log +\layout LyX-Code + +Default: cookies +\layout LyX-Code + +Context: virtual host +\layout Standard + +Defines which table is used for logging of cookies. + Working in conjunction with +\noun on +LogSQLWhichCookies +\noun default +, 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. +\layout Standard + +Note that you must create the table (see create-tables.sql, included in the + package), or +\noun on +LogSQLCreateTables +\noun default + must be set to +\begin_inset Quotes eld +\end_inset + +on +\begin_inset Quotes erd +\end_inset + +. +\layout Subsubsection + +LogSQLCreateTables +\layout LyX-Code + +Syntax: LogSQLCreateTables flag +\layout LyX-Code + +Example: LogSQLCreateTables On +\layout LyX-Code + +Default: Off +\layout LyX-Code + +Context: main server config +\layout Standard + +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 +\noun on +LogSQLMassVirtualHosting +\noun default + directive). +\layout Standard + +There is a slight disadvantage: if you wish to activate this feature, then + the userid specified in +\noun on +LogSQLLoginInfo +\noun default + 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 +\noun on +LogSQLCreateTables +\noun default +. + But most people -- even the very security-conscious -- will find that granting + CREATE on the logging database is reasonable. +\layout Standard + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLDatabase +\layout LyX-Code + + +\series bold +MANDATORY +\layout LyX-Code + +Syntax: LogSQLDatabase database +\layout LyX-Code + +Example: LogSQLDatabase loggingdb +\layout LyX-Code + +Context: main server config +\layout Standard + +Defines the database that is used for logging. + +\begin_inset Quotes eld +\end_inset + +database +\begin_inset Quotes erd +\end_inset + + must be a valid db on the MySQL host defined in +\noun on +LogSQLLoginInfo +\noun default +. + +\layout Standard + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLForcePreserve +\layout LyX-Code + +Syntax: LogSQLForcePreserve Flag +\layout LyX-Code + +Example: LogSQLPreserveFile on +\layout LyX-Code + +Default: off +\layout LyX-Code + +Context: main server config +\layout Standard + +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. +\layout Standard + +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. +\layout Standard + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLHeadersInLogTable +\layout LyX-Code + +Syntax: LogSQLHeadersInLogTable table-name +\layout LyX-Code + +Example: LogSQLHeadersInLogTable headers +\layout LyX-Code + +Default: headers_in +\layout LyX-Code + +Context: virtual host +\layout Standard + +Defines which table is used for logging of inbound headers. + Working in conjunction with +\noun on +LogSQLWhichHeadersIn +\noun default +, 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_uniqu +e_id. +\layout Standard + +Note that you must create the table (see create-tables.sql, included in the + package), or +\noun on +LogSQLCreateTables +\noun default + must be set to +\begin_inset Quotes eld +\end_inset + +on +\begin_inset Quotes erd +\end_inset + +. +\layout Subsubsection + +LogSQLHeadersOutLogTable +\layout LyX-Code + +Syntax: LogSQLHeadersOutLogTable table-name +\layout LyX-Code + +Example: LogSQLHeadersOutLogTable headers +\layout LyX-Code + +Default: headers_out +\layout LyX-Code + +Context: virtual host +\layout Standard + +Defines which table is used for logging of outbound headers. + Working in conjunction with +\noun on +LogSQLWhichHeadersOut +\noun default +, 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_uniqu +e_id. +\layout Standard + +Note that you must create the table (see create-tables.sql, included in the + package), or +\noun on +LogSQLCreateTables +\noun default + must be set to +\begin_inset Quotes eld +\end_inset + +on +\begin_inset Quotes erd +\end_inset + +. +\layout Subsubsection + +LogSQLLoginInfo +\layout LyX-Code + + +\series bold +MANDATORY +\series default + +\layout LyX-Code + +Syntax: LogSQLLoginInfo host user password +\layout LyX-Code + +Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd +\layout LyX-Code + +Context: main server config +\layout Standard + +Defines the general parameters of the MySQL host to which you will be logging. + +\begin_inset Quotes eld +\end_inset + +host +\begin_inset Quotes erd +\end_inset + + is the hostname or IP address of the MySQL machine, and is simply +\begin_inset Quotes eld +\end_inset + +localhost +\begin_inset Quotes erd +\end_inset + + if the database lives on the same machine as Apache. + +\begin_inset Quotes eld +\end_inset + +user +\begin_inset Quotes erd +\end_inset + + is the MySQL userid (not a Unix userid!) with INSERT privileges on the + table defined in +\noun on +LogSQLTransferLogTable +\noun default +. + +\begin_inset Quotes eld +\end_inset + +password +\begin_inset Quotes erd +\end_inset + + is that user's password. + +\layout Standard + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLMachineID +\layout LyX-Code + +Syntax: LogSQLMachineID somename +\layout LyX-Code + +Example: LogSQLMachineID web01 +\layout LyX-Code + +Context: main server config +\layout Standard + +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 loadbalancing + methodology. + +\noun on +LogSQLMachineID +\noun default + permits you to distinguish each machine's entries if you assign each machine + its own +\noun on +LogSQLMachineID +\noun default +: for example, the first webserver gets +\begin_inset Quotes eld +\end_inset + + +\noun on +LogSQLMachineID +\noun default + web01, +\begin_inset Quotes erd +\end_inset + + the second gets +\begin_inset Quotes eld +\end_inset + + +\noun on +LogSQLMachineID +\noun default + web02, +\begin_inset Quotes erd +\end_inset + + etc. +\layout Standard + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLMassVirtualHosting +\layout LyX-Code + +Syntax: LogSQLMassVirtualHosting flag +\layout LyX-Code + +Example: LogSQLMassVirtualHosting On +\layout LyX-Code + +Default: Off +\layout LyX-Code + +Context: main server config +\layout Standard + +If you administer a site hosting many, many virtual hosts then this option + will appeal to you. + If you turn on +\noun on +LogSQLMassVirtualHosting +\noun default + then several things happen: +\layout Itemize + +the on-the-fly table creation feature is activated automatically +\layout Itemize + +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) +\layout Itemize + +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. +\layout Standard + +This is a huge boost in convenience for sites with many virtual servers. + Activating +\noun on +LogSQLMassVirtualHosting +\noun default + obviates the need to create every virtual server's table and provides more + granular security possibilities. +\layout Standard + +You are advised to investigate the use of Apache's +\noun on +UseCanonicalName On +\noun default +directive with this directive in order to ensure that each virtual host + maps to one table namespace. +\layout Standard + +This is defined only once in the httpd.conf file. + +\layout Subsubsection + +LogSQLNotesLogTable +\layout LyX-Code + +Syntax: LogSQLNotesLogTable table-name +\layout LyX-Code + +Example: LogSQLNotesLogTable notes_log +\layout LyX-Code + +Default: notes +\layout LyX-Code + +Context: virtual host +\layout Standard + +Defines which table is used for logging of notes. + Working in conjunction with +\noun on +LogSQLWhichNotes +\noun default +, 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. +\layout Standard + +Note that you must create the table (see create-tables.sql, included in the + package), or +\noun on +LogSQLCreateTables +\noun default + must be set to ``on''. + +\layout Subsubsection + +LogSQLPreserveFile +\layout LyX-Code + +Syntax: LogSQLPreserveFile filename +\layout LyX-Code + +Example: LogSQLPreserveFile offline-preserve +\layout LyX-Code + +Default: /tmp/sql-preserve +\layout LyX-Code + +Context: virtual host +\layout Standard + +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: +\layout LyX-Code + +# mysql -uadminuser -p mydbname < /tmp/sql-preserve +\layout Standard + +If you do not define +\noun on +LogSQLPreserveFile +\noun default + then all virtual servers will log to the same default preserve file (/tmp/sql-p +reserve). + 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 +\noun on +LogSQLTransferLogFormat +\noun default + directive. + It is only necessary to segregate preserve-files by virualhost if you also + segregate access logs by virtualhost. +\layout Standard + +The module will log to Apache's +\noun on +ErrorLog +\noun default + 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. +\layout Standard + +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). +\layout Subsubsection + +LogSQLRemhostIgnore +\layout LyX-Code + +Syntax: LogSQLRemhostIgnore host1 host2 host3 ... + hostN +\layout LyX-Code + +Example: LogSQLRemhostIgnore localnet.com +\layout LyX-Code + +Context: virtual host +\layout Standard + +Lists a series of strings that, if present in the REMOTE_HOST, will cause + that request to +\series bold +not +\series default + 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 +\begin_inset LatexCommand \ref{sub:Ignore} + +\end_inset + + for some tips for using this directive. +\layout Standard + +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. +\layout Subsubsection + +LogSQLRequestAccept +\layout LyX-Code + +Syntax: LogSQLRequestAccept req1 req2 req3 ... + reqN +\layout LyX-Code + +Example: LogSQLRequestAccept .html .php .jpg +\layout LyX-Code + +Default: if not specified, all requests are +\begin_inset Quotes eld +\end_inset + +accepted +\begin_inset Quotes erd +\end_inset + + +\layout LyX-Code + +Context: virtual host +\layout Standard + +Lists a series of strings that, if present in the URI, will permit that + request to be +\series bold + +\series default +considered for logging (depending on additional filtering by the +\begin_inset Quotes eld +\end_inset + +ignore +\begin_inset Quotes erd +\end_inset + + directives). + Any request that fails to match one of the +\noun on +LogSQLRequestAccept +\noun default +entries will be discarded. +\layout Standard + +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 +\begin_inset LatexCommand \ref{sub:Ignore} + +\end_inset + + for some tips for using this directive. +\layout Standard + +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. +\layout Standard + +This directive is completely optional. + It is more general than +\noun on +LogSQLRequestIgnore +\noun default +and +\noun on + +\noun default +is evaluated before +\noun on + LogSQLRequestIgnore +\noun default +. + If this directive is not used, +\series bold +all +\series default + 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. +\layout Subsubsection + +LogSQLRequestIgnore +\layout LyX-Code + +Syntax: LogSQLRequestIgnore req1 req2 req3 ... + reqN +\layout LyX-Code + +Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico +\layout LyX-Code + +Context: virtual host +\layout Standard + +Lists a series of strings that, if present in the URI, will cause that request + to +\series bold +NOT +\series default + be +\series bold + +\series default +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 +\begin_inset LatexCommand \ref{sub:Ignore} + +\end_inset + + for some tips for using this directive. +\layout Standard + +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. +\layout Subsubsection + +LogSQLSocketFile +\layout LyX-Code + +Syntax: LogSQLSocketFile filename +\layout LyX-Code + +Example: LogSQLSocketFile /tmp/mysql.sock +\layout LyX-Code + +Default: /var/lib/mysql/mysql.sock +\layout LyX-Code + +Context: main server config +\layout Standard + +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. +\layout Standard + +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. +\layout Standard + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLTCPPort +\layout LyX-Code + +Syntax: LogSQLTCPPort portnumber +\layout LyX-Code + +Example: LogSQLTCPPort 3309 +\layout LyX-Code + +Default: 3306 +\layout LyX-Code + +Context: main server config +\layout Standard + +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. +\layout Standard + +This is defined only once in the httpd.conf file. +\layout Subsubsection + + +\begin_inset LatexCommand \label{sub:Frmat} + +\end_inset + +LogSQLTransferLogFormat +\layout LyX-Code + +Syntax: LogSQLTransferLogFormat format-string +\layout LyX-Code + +Example: LogSQLTransferLogFormat huSUTv +\layout LyX-Code + +Default: AbHhmRSsTUuv +\layout LyX-Code + +Context: virtual host +\layout Standard + +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: +\layout Quote + + +\size footnotesize + +\begin_inset Tabular + + + + + + + + + +\begin_inset Text + +\layout Standard + +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +\size footnotesize +What is this? +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +\size footnotesize +Data field +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +\size footnotesize +Column type +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +\size footnotesize +Example +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +A +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +User agent +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +agent +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Mozilla/4.0 (compat; MSIE 6.0; Windows) +\end_inset + + + + +\begin_inset Text + +\layout Standard + +a +\end_inset + + +\begin_inset Text + +\layout Standard + +CGI request arguments +\end_inset + + +\begin_inset Text + +\layout Standard + +request_args +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +user=Smith&cart=1231&item=532 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +b +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Bytes transfered +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +bytes_sent +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +int unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +32561 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +c +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Text of cookie +\begin_inset Formula $^{\textrm{1}}$ +\end_inset + + +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +cookie +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Apache=sdyn.fooonline.net.1300102700823 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +H +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +HTTP request protocol +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +request_protocol +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(10) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +HTTP/1.1 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +h +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Name of remote host +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +remote_host +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +blah.foobar.com +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +I +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Request ID (from mod_unique_id) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +id +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +char(19) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +POlFcUBRH30AAALdBG8 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +l +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Ident user info +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +remote_logname +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +bobby +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +M +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Machine ID +\begin_inset Formula $^{\textrm{2}}$ +\end_inset + + +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +machine_id +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(25) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +web01 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +m +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +HTTP request method +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +request_method +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(6) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +GET +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +P +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +httpd child PID +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +child_pid +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +3215 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +p +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +httpd port +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +server_port +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +80 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +R +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Referer +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +referer +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +http://www.biglinks4u.com/linkpage.html +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +r +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Request in full form +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +request_line +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +GET /books-cycroad.html HTTP/1.1 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +S +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Time of request in UNIX format +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +time_stamp +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +int unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +1005598029 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +s +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +HTTP status of request +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +status +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +404 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +T +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Seconds to service request +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +request_duration +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +2 +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +t +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Time of request in human format +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +request_time +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +char(28) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +[02/Dec/2001:15:01:26 -0800] +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +U +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Request in simple form +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +request_uri +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +/books-cycroad.html +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +u +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +User info from HTTP auth +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +remote_user +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +bobby +\end_inset + + + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +v +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +Virtual host servicing the request +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +virtual_host +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + + +\size footnotesize +www.foobar.com +\end_inset + + + + +\end_inset + + +\layout Quote + + +\begin_inset Formula $^{\textrm{1}}$ +\end_inset + + You must also specify +\noun on +LogSQLWhichCookie +\noun default +for this to take effect. +\layout Quote + + +\begin_inset Formula $^{\textrm{2}}$ +\end_inset + + You must also specify +\noun on +LogSQLMachineID +\noun default + for this to take effect. +\layout Standard + +If you have compiled mod_log_sql with SSL logging capability, you also can + use these: +\layout Quote + + +\begin_inset Tabular + + + + + + + + + +\begin_inset Text + +\layout Standard + +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +What is this? +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +Data field +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +Column Type +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +Example +\end_inset + + + + +\begin_inset Text + +\layout Standard + +z +\end_inset + + +\begin_inset Text + +\layout Standard + +SSL cipher used +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_cipher +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(25) +\end_inset + + +\begin_inset Text + +\layout Standard + +RC4-MD5 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +q +\end_inset + + +\begin_inset Text + +\layout Standard + +Keysize of the SSL connection +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_keysize +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +56 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +Q +\end_inset + + +\begin_inset Text + +\layout Standard + +Maximum keysize supported +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_maxkeysize +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +128 +\end_inset + + + + +\end_inset + + +\layout Subsubsection + +LogSQLTransferLogTable +\layout LyX-Code + + +\series bold +MANDATORY (unless +\noun on +LogSQLMassVirtualHosting +\noun default + is +\begin_inset Quotes eld +\end_inset + +on +\begin_inset Quotes erd +\end_inset + +) +\layout LyX-Code + +Syntax: LogSQLTransferLogTable table-name +\layout LyX-Code + +Example: LogSQLTransferLogTable access_log_table +\layout LyX-Code + +Context: virtual host +\layout Standard + +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 +\noun on +LogSQLDatabase +\noun default +. +\layout Standard + +This directive is not necessary if you declare +\noun on +LogSQLMassVirtualHosting On +\noun default +, since that directive activates dynamically-named tables. + If you attempt to use +\noun on +LogSQLTransferLogTable +\noun default + at the same time a warning will be logged and it will be ignored, since + +\noun on +LogSQLMassVirtualHosting +\noun default + takes priority. +\layout Subsubsection + +LogSQLWhichCookie +\layout LyX-Code + +Syntax: LogSQLWhichCookie cookiename +\layout LyX-Code + +Example: LogSQLWhichCookie Clicks +\layout LyX-Code + +Default: None +\layout LyX-Code + +Context: virtual host +\layout Standard + +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. + +\layout Standard + +mod_log_sql allows you to log cookie information. + +\noun on + LogSQLWhichCookie +\noun default + 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. +\layout Standard + +You must include a 'c' character in +\noun on +LogSQLTransferLogFormat +\noun default + for this directive to take effect. +\layout Standard + +Note: although this was origintally intended for people using mod_usertrack + to create user-tracking cookies, you aren't restricted in any way. + You can choose which cookie you wish to log to the database -- any cookie + at all -- and it doesn't necessarily have to have anything to do with mod_usert +rack. +\layout Subsubsection + +LogSQLWhichCookies +\layout LyX-Code + +Syntax: LogSQLWhichCookies cookie1 cookie2 ... + cookieN +\layout LyX-Code + +Example: LogSQLWhichCookies userlogin foobar foobaz +\layout LyX-Code + +Default: None +\layout LyX-Code + +Context: virtual host +\layout Standard + +Defines the list of cookies you would like logged. + This works in conjunction with +\noun on +LogSQLCookieLogTable +\noun default +. + This directive does not require any additional characters to be added to + the +\noun on +LogSQLTransferLogFormat +\noun default + string. + The feature is activated simply by including this directive, upon which + you will begin populating the separate cookie table with data. +\layout Standard + +Note that you must have already created the table (see create-tables.sql, + included in the package), or +\noun on +LogSQLCreateTables +\noun default + must be set to +\begin_inset Quotes eld +\end_inset + +on +\begin_inset Quotes erd +\end_inset + +. +\layout Subsubsection + +LogSQLWhichHeadersIn +\layout LyX-Code + +Syntax: LogSQLWhichHeadersIn item1 item2 ... + itemN +\layout LyX-Code + +Example: LogSQLWhichHeadersIn UserAgent Accept-Encoding Host +\layout LyX-Code + +Default: None +\layout LyX-Code + +Context: virtual host +\layout Standard + +Defines the list of inbound headers you would like logged. + This works in conjunction with +\noun on + LogSQLHeadersInLogTable +\noun default +. + This directive does not require any additional characters to be added to + the +\noun on +LogSQLTransferLogFormat +\noun default + string. + The feature is activated simply by including this directive, upon which + you will begin populating the separate inbound-headers table with data. +\layout Standard + +Note that you must have already created the table (see create-tables.sql, + included in the package), or +\noun on +LogSQLCreateTables +\noun default + must be set to +\begin_inset Quotes eld +\end_inset + +on +\begin_inset Quotes erd +\end_inset + +. +\layout Subsubsection + +LogSQLWhichHeadersOut +\layout LyX-Code + +Syntax: LogSQLWhichHeadersOut item1 item2 ... + itemN +\layout LyX-Code + +Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control +\layout LyX-Code + +Default: None +\layout LyX-Code + +Context: virtual host +\layout Standard + +Defines the list of outbound headers you would like logged. + This works in conjunction with +\noun on +LogSQLHeadersOutLogTable +\noun default +. + This directive does not require any additional characters to be added to + the +\noun on +LogSQLTransferLogFormat +\noun default + string. + The feature is activated simply by including this directive, upon which + you will begin populating the separate outbound-headers table with data. +\layout Standard + +Note that you must have already created the table (see create-tables.sql, + included in the package), or +\noun on +LogSQLCreateTables +\noun default + must be set to +\begin_inset Quotes eld +\end_inset + +on +\begin_inset Quotes erd +\end_inset + +. +\layout Subsubsection + +LogSQLWhichNotes +\layout LyX-Code + +Syntax: LogSQLWhichNotes item1 item2 ... + itemN +\layout LyX-Code + +Example: LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio +\layout LyX-Code + +Default: None +\layout LyX-Code + +Context: virtual host +\layout Standard + +Defines the list of notes you would like logged. + This works in conjunction with +\noun on +LogSQLNotesLogTable +\noun default +. + This directive does not require any additional characters to be added to + the +\noun on +LogSQLTransferLogFormat +\noun default + string. + The feature is activated simply by including this directive, upon which + you will begin populating the separate notes table with data. +\layout Standard + +Note that you must have already created the table (see create-tables.sql, + included in the package), or +\noun on +LogSQLCreateTables +\noun default + must be set to +\begin_inset Quotes eld +\end_inset + +on +\begin_inset Quotes erd +\end_inset + +. +\layout Section + +FAQ +\layout Subsection + +General module questions +\layout Subsubsection + + +\begin_inset LatexCommand \label{sub:why} + +\end_inset + +Why log to an SQL database? +\layout Standard + +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: +\layout Itemize + +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 +\begin_inset LatexCommand \url[Webalizer]{(http://www.webalizer.com)} + +\end_inset + + does not need historic logs after it has processed them, enabling you to + delete older logs. +\layout Itemize + +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. + +\layout Itemize + +People acquainted with the power of SQL SELECT statements will know the + flexibility of the extraction possibilities at their fingertips. +\layout Standard + +For example, do you want to see all your 404's? Do this: +\layout LyX-Code + +select remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp) +\layout LyX-Code + +from acc_log_tbl where status=404 order by time_stamp; +\layout LyX-Code + +\layout Standard +\align center + +\begin_inset Tabular + + + + + + + + + +\begin_inset Text + +\layout Standard + +remote_host +\end_inset + + +\begin_inset Text + +\layout Standard + +status +\end_inset + + +\begin_inset Text + +\layout Standard + +request_uri +\end_inset + + +\begin_inset Text + +\layout Standard + +bytes_sent +\end_inset + + +\begin_inset Text + +\layout Standard + +from_unixtime(time_stamp) +\end_inset + + + + +\begin_inset Text + +\layout Standard + +marge.mmm.co.uk +\end_inset + + +\begin_inset Text + +\layout Standard + +404 +\end_inset + + +\begin_inset Text + +\layout Standard + +/favicon.ico +\end_inset + + +\begin_inset Text + +\layout Standard + +321 +\end_inset + + +\begin_inset Text + +\layout Standard + +2001-11-20 02:30:56 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +62.180.239.251 +\end_inset + + +\begin_inset Text + +\layout Standard + +404 +\end_inset + + +\begin_inset Text + +\layout Standard + +/favicon.ico +\end_inset + + +\begin_inset Text + +\layout Standard + +333 +\end_inset + + +\begin_inset Text + +\layout Standard + +2001-11-20 02:45:25 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +212.234.12.66 +\end_inset + + +\begin_inset Text + +\layout Standard + +404 +\end_inset + + +\begin_inset Text + +\layout Standard + +/favicon.ico +\end_inset + + +\begin_inset Text + +\layout Standard + +321 +\end_inset + + +\begin_inset Text + +\layout Standard + +2001-11-20 03:01:00 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +212.210.78.254 +\end_inset + + +\begin_inset Text + +\layout Standard + +404 +\end_inset + + +\begin_inset Text + +\layout Standard + +/favicon.ico +\end_inset + + +\begin_inset Text + +\layout Standard + +333 +\end_inset + + +\begin_inset Text + +\layout Standard + +2001-11-20 03:26:05 +\end_inset + + + + +\end_inset + + +\layout LyX-Code + +\layout Standard + +Or do you want to see how many bytes you've sent within a certain directory + or site? Do this: +\layout LyX-Code + +select request_uri,sum(bytes_sent) as bytes,count(request_uri) as howmany + from +\layout LyX-Code + +acc_log_tbl where request_uri like '%mod_log_sql%' group by request_uri + order +\layout LyX-Code + +by howmany desc; +\layout LyX-Code + +\layout Standard +\align center + +\begin_inset Tabular + + + + + + + +\begin_inset Text + +\layout Standard + +request_uri +\end_inset + + +\begin_inset Text + +\layout Standard + +bytes +\end_inset + + +\begin_inset Text + +\layout Standard + +howmany +\end_inset + + + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/style_1.css +\end_inset + + +\begin_inset Text + +\layout Standard + +157396 +\end_inset + + +\begin_inset Text + +\layout Standard + +1288 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/ +\end_inset + + +\begin_inset Text + +\layout Standard + +2514337 +\end_inset + + +\begin_inset Text + +\layout Standard + +801 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/mod_log_sql.tar.gz +\end_inset + + +\begin_inset Text + +\layout Standard + +9769312 +\end_inset + + +\begin_inset Text + +\layout Standard + +456 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/faq.html +\end_inset + + +\begin_inset Text + +\layout Standard + +5038728 +\end_inset + + +\begin_inset Text + +\layout Standard + +436 +\end_inset + + + + +\end_inset + + +\layout LyX-Code + +\layout Standard + +Or maybe you want to see who's linking to you? Do this: +\layout LyX-Code + +select count(referer) as num,referer from acc_log_tbl where +\layout LyX-Code + +request_uri='/mod_log_sql/' group by referer order by num desc; +\layout LyX-Code + +\layout Standard +\align center + +\begin_inset Tabular + + + + + + +\begin_inset Text + +\layout Standard + +num +\end_inset + + +\begin_inset Text + +\layout Standard + +referer +\end_inset + + + + +\begin_inset Text + +\layout Standard + +271 +\end_inset + + +\begin_inset Text + +\layout Standard + +http://freshmeat.net/projects/mod_log_sql/ +\end_inset + + + + +\begin_inset Text + +\layout Standard + +96 +\end_inset + + +\begin_inset Text + +\layout Standard + +http://modules.apache.org/search?id=339 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +48 +\end_inset + + +\begin_inset Text + +\layout Standard + +http://freshmeat.net/ +\end_inset + + + + +\begin_inset Text + +\layout Standard + +8 +\end_inset + + +\begin_inset Text + +\layout Standard + +http://freshmeat.net +\end_inset + + + + +\end_inset + + +\layout LyX-Code + +\layout Standard + +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! +\layout Subsubsection + +Why use MySQL? Are there alternatives? +\layout Standard + +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. +\layout Standard + +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 +\begin_inset LatexCommand \url[pgLOGd]{(http://www.digitalstratum.com/pglogd/)} + +\end_inset + +. +\layout Subsubsection + +Is this code production-ready? +\layout Standard + +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.) +\layout Subsubsection + +Who's using mod_log_sql? +\layout Standard + +Good question! It would be great to find out! If you are a production-level + mod_log_sql user, please contact +\begin_inset LatexCommand \url[the maintainer, Chris Powell]{(chris@grubbybaby.com)} + +\end_inset + + so that you can be mentioned here. +\layout Subsubsection + +Why doesn't the module also replace the Apache ErrorLog? +\layout Standard + +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. +\layout Standard + +Error logs are usually not very high-traffic and are really best left as + text files on a web server machine. +\layout Subsubsection + +Does mod_log_sql work with Apache 2.x? +\layout Standard + +As of this writing, no. + The Apache Group significantly altered the module API with the release + of Apache 2.0. + All modules written for 1.3, including mod_log_sql, will not work with 2.0. +\layout Standard + +mod_log_sql will eventually be ported to Apache 2.x, but not immediately. + It is going to take some time, and there are other features that have higher + priority. + Please sign up for the announcements list (on the main website) or monitor + the website for updates to learn when the port (and other releases) are + available. +\layout Standard + +If you're a *NIX user, stick with Apache 1.3.x for now. + Major modules like mod_ssl and PHP are not even ready for 2.0 yet, and the + main benefits in 2.0 are for Win32 users anyway. + Apache 1.3.x is rock-stable and performs equally well on *NIX as 2.0. +\layout Subsubsection + +Does mod_log_sql connect to MySQL via TCP/IP or a socket? +\layout Standard + +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: +\layout Itemize + +if the specified MySQL database is on the same machine, the connection command + uses a socket to communicate with MySQL +\layout Itemize + +if the specified MySQL database is on a different machine, mod_log_sql connects + using TCP/IP. + +\layout Standard + +You don't have any control of which methodology is used. + You can fine-tune some of the configuration, however. + The +\noun on +LogSQLSocketFile +\noun default + runtime configuration directive overrides the default of +\begin_inset Quotes eld +\end_inset + +/var/lib/mysql/mysql.sock +\begin_inset Quotes erd +\end_inset + + for socket-based connections, whereas the +\noun on +LogSQLTCPPort +\noun default + command allows to you override the default TCP port of 3306 for TCP/IP + connections. +\layout Subsubsection + +I have discovered a bug. + Who can I contact? +\layout Standard + +Please contact +\begin_inset LatexCommand \url[the maintainer]{(chris@grubbybaby.com)} + +\end_inset + +! 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. +\layout Subsection + +Problems +\layout Subsubsection + +Apache segfaults or has other problems when using PHP and mod_log_sql +\layout Standard + +This occurs if you compiled PHP with MySQL database support. + PHP utilizes its internal, bundled MySQL libraries by default. + These conflict with the +\begin_inset Quotes eld +\end_inset + +real +\begin_inset Quotes erd +\end_inset + + MySQL libraries linked by mod_log_sql, causing the segmentation fault. + +\layout Standard + +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. +\layout Subsubsection + + +\begin_inset LatexCommand \label{faq:NothingLogged} + +\end_inset + +Apache appears to start up fine, but nothing is getting logged in the database +\layout Standard + +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: +\layout Itemize + +Improper privileges set up in the MySQL database +\layout Itemize + +You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry + +\layout Itemize + +You didn't specify the right database host or login information +\layout Itemize + +Another factor is preventing a connection to the database +\layout Standard + +Important: it is improper to ask for help before you have followed these + steps. +\layout Standard + +First examine the MySQL log that you established in step +\begin_inset LatexCommand \ref{step:EnaLog} + +\end_inset + + of section +\begin_inset LatexCommand \ref{sub:PrepDb} + +\end_inset + +. + 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 administrativ +e 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. +\layout Standard + +Second, confirm that your +\noun on +LogSQL* +\noun default + directives are all correct. +\layout Standard + +Third, examine the Apache +\noun on + +\noun default +error logs for messages from mod_log_sql; the module will offer hints as + to why it cannot connect, etc. + +\layout Standard + +The next thing to do is recompile the module with debugging output activated. + change the "#undef DEBUG" on line 8 of mod_log_sql.c to "#define DEBUG" + and recompile/reinstall. + The module will now output copious notes about what it is doing, and this + will help you (and the maintainer) solve the problem. + In order to see the debugging messages, ensure that you make them visible + using the +\noun on +LogLevel +\noun default +directive +\series bold +in the main server config as well as in each +\noun on +VirtualHost +\noun default +config: +\layout LyX-Code + +LogLevel debug +\layout LyX-Code + +ErrorLog /var/log/httpd/server-messages +\layout Subsubsection + +Why do I get the message +\begin_inset Quotes eld +\end_inset + +insufficient configuration info to establish database link +\begin_inset Quotes erd +\end_inset + + in my Apache error log? +\layout Standard + +At a minimum, +\noun on +LogSQLDatabase +\noun default + and +\noun on +LogSQLLoginInfo +\noun default +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. +\layout Subsubsection + +My database cannot handle all the open connections from mod_log_sql, is + there anything I can do? +\layout Standard + +The rule of thumb: if you have +\emph on +n +\emph default +webservers each configured to support +\emph on +y +\emph default + +\noun on +MaxClients +\noun default +, then your database must be able to handle +\begin_inset Formula $n\times y$ +\end_inset + + simultenous connections +\emph on +in the worst case. + +\emph default +Certainly you must use common sense, consider reasonable traffic expectations + and structure things accordingly. +\layout Standard + +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. + +\layout Standard + +Jeremy Zawodny, a highly respected MySQL user and contributor to Linux Magazine, + has this very helpful and highly appropriate article on tuning MySQL: +\begin_inset LatexCommand \url{http://jeremy.zawodny.com/blog/archives/000173.html} + +\end_inset + + +\layout Standard + +Please remember that mod_log_sql's overriding principle is +\series bold +performance +\series default + -- 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 doesn't attempt to shoehorn all the + database traffic through a single extra daemon or proxy process. +\layout Subsubsection + +Why do I occasionally see a +\begin_inset Quotes eld +\end_inset + +lost connection to MySQL server +\begin_inset Quotes erd +\end_inset + + message in my Apache error log? +\layout Standard + +This message may appear every now and then in your Apache error log, especially + on very lightly loaded servers. + This doesn't 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 haven't 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: +\layout LyX-Code + +[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed, +\layout LyX-Code + + API said: error 2013, Lost connection to MySQL server during query +\layout LyX-Code + +[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful +\layout LyX-Code + +[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful +\layout Standard + +Reference: +\begin_inset LatexCommand \url[MySQL documentation]{(http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away)} + +\end_inset + + +\layout Subsubsection + +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. + +\begin_inset Quotes eld +\end_inset + +http://intranet/index.html +\begin_inset Quotes erd +\end_inset + +) get logged in separate tables. +\layout Standard + +Proper usage of the Apache runtime +\noun on +ServerName +\noun default + directive and the directive +\noun on +UseCanonicalName On +\noun default +(or +\noun on +DNS +\noun default +) are necessary to prevent this problem. + +\begin_inset Quotes eld +\end_inset + +On +\begin_inset Quotes erd +\end_inset + + is the default for +\noun on +UseCanonicalName +\noun default +, and specifies that self-referential URLs are generated from the +\noun on +ServerName +\noun default +part of your VirtualHost: +\layout Quote + +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 +\begin_inset LatexCommand \url[the Apache documentation]{http://httpd.apache.org/docs/mod/core.html#usecanonicalname} + +\end_inset + +] +\layout Standard + +The module inherits Apache's +\begin_inset Quotes eld +\end_inset + +knowledge +\begin_inset Quotes erd +\end_inset + + 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 +\noun on +LogSQLMassVirtualHosting +\noun default +. +\layout Subsection + +Performance and Tuning +\layout Subsubsection + +How well does it perform? +\layout Standard + +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. +\layout Standard + +Overall configuration: +\layout Itemize + +Machine A: Apache webserver +\layout Itemize + +Machine B: MySQL server +\layout Itemize + +Machines A and B connected with 100Mbps Ethernet +\layout Itemize + +Webserver: Celeron 400, 128 MB RAM, IDE storage +\layout Standard + +Apache configuration: +\layout LyX-Code + +Timeout 300 +\layout LyX-Code + +KeepAlive On +\layout LyX-Code + +MaxKeepAliveRequests 100 +\layout LyX-Code + +KeepAliveTimeout 15 +\layout LyX-Code + +MinSpareServers 5 +\layout LyX-Code + +StartServers 10 +\layout LyX-Code + +MaxSpareServers 15 +\layout LyX-Code + +MaxClients 256 +\layout LyX-Code + +MaxRequestsPerChild 5000 +\layout LyX-Code + +LogSQLTransferLogFormat AbHhmRSsTUuvc +\layout LyX-Code + +LogSQLWhichCookie Clicks +\layout LyX-Code + +CookieTracking on +\layout LyX-Code + +CookieName Clicks +\layout Standard + +"ab" commandline: +\layout LyX-Code + +./ab -c 10 -t 20 -v 2 -C Clicks=ab_run http://www.hostname.com/target +\layout Standard + +( 10 concurrent requests; 20 second test; setting a cookie "Clicks=ab_run"; + target = the mod_log_sql homepage. + ) +\layout Standard + +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: +\layout Itemize + +Average of five runs employing MySQL +\emph on +and +\emph default + standard text logging: +\series bold +139.01 requests per second, zero errors +\series default +. +\layout Itemize + +Average of five runs employing +\emph on +only +\emph default + standard text logging: +\series bold +139.96 requests per second, zero errors +\series default +. +\layout Standard + +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 +\emph on +twelve million hits per day. +\layout Standard + +If you run this benchmark yourself, take note of three things: +\layout Enumerate + +Use a target URL that is on your own webserver :-). + +\layout Enumerate + +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.) +\layout Enumerate + +When done with your runs, clean these many thousands of requests out of + your database: +\layout LyX-Code + +mysql> delete from access_log where agent like 'ApacheBench%'; +\layout LyX-Code + +mysql> optimize table access_log; +\layout Subsubsection + +Do I need to be worried about all the running MySQL children? Will holding + open +\emph on +n +\emph default + Apache-to-MySQL connections consume a lot of memory? +\layout Standard + +Short answer: you shouldn't be worried. +\layout Standard + +Long answer: you might be evaluating at the output of +\begin_inset Quotes eld +\end_inset + +ps -aufxw +\begin_inset Quotes erd +\end_inset + + and becoming alarmed at all the 7MB httpd processes or 22MB mysqld children + that you see. + Don't be alarmed +\emph on +. + +\emph default + 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... +\layout Standard + +Fortunately the cost reported by 'ps -aufxw' is deceptive. + This is due to an OS memory-management feature called +\begin_inset Quotes eld +\end_inset + +copy-on-write. +\begin_inset Quotes erd +\end_inset + + When you have a number of identical child processes (e.g. + Apache, MySQL), it would appear in +\begin_inset Quotes eld +\end_inset + +ps +\begin_inset Quotes erd +\end_inset + + 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 +\begin_inset Quotes eld +\end_inset + +read-only +\begin_inset Quotes erd +\end_inset + + 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. +\layout Standard + +A memory page is only duplicated when it needs to be written to, hence +\begin_inset Quotes eld +\end_inset + +copy-on-write. +\begin_inset Quotes erd +\end_inset + + The result is efficiency and decreased memory consumption. + +\begin_inset Quotes eld +\end_inset + +ps +\begin_inset Quotes erd +\end_inset + + may report 7MB per child, but it might really only +\begin_inset Quotes eld +\end_inset + +cost +\begin_inset Quotes erd +\end_inset + + 900K of extra memory to add one more child. + It is +\series bold +not +\emph on + +\emph default +correct +\series default + to assume that 20 Apache children with a VSZ of 7MB each equals +\begin_inset Formula $(20\times7MB)$ +\end_inset + + of memory consumption -- the real answer is much, much lower. + The same +\begin_inset Quotes eld +\end_inset + +copy-on-write +\begin_inset Quotes erd +\end_inset + + rules apply to all your MySQL children: 40 mysqld children @ 22MB each + +\series bold +do not +\series default + occupy 880MB of RAM. +\layout Standard + +The bottom line: although there is a cost to spawn extra httpd or mysqld + children, that cost is not as great as +\begin_inset Quotes eld +\end_inset + +ps +\begin_inset Quotes erd +\end_inset + + would lead you to believe. +\layout Subsubsection + +My webserver cannot handle all the traffic that my site receives, is there + anything I can do? +\layout Standard + +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! +\layout Subsubsection + + +\begin_inset LatexCommand \label{sub:DelayedInsFAQ} + +\end_inset + +What is the issue with activating delayed inserts? +\layout Standard + +There are several. +\layout Enumerate + +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. +\layout Enumerate + +The MySQL documentation is unclear whether INSERT DELAYED is even necessary + for an optimized database. + It says, +\begin_inset Quotes eld +\end_inset + +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. +\begin_inset Quotes erd +\end_inset + + But then it goes on to say, +\begin_inset Quotes eld +\end_inset + +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. +\begin_inset Quotes erd +\end_inset + + +\layout Enumerate + +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. +\layout Enumerate + +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. +\layout Standard + +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. +\layout Standard + +If after understanding these problems you still wish to enable delayed inserts, + section +\begin_inset LatexCommand \ref{sub:DelayedIns} + +\end_inset + + discusses how. +\layout Subsection + + +\begin_inset Quotes eld +\end_inset + +How do I...? +\begin_inset Quotes erd +\end_inset + + -- accomplishing certain tasks +\layout Subsubsection + +How do I extract the data in a format that my analysis tool can understand? +\layout Standard + +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. +\layout Standard + +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: +\layout LyX-Code + +# Generate the temporary apache logs from the MySQL database (for webalizer) + +\layout LyX-Code + +05 04 * * * root make_combined_log.pl 1 www.grubbybaby.com > /var/log/temp01 +\layout LyX-Code + +# Run webalizer on httpd log +\layout LyX-Code + +30 04 * * * root webalizer -c /etc/webalizer.conf; rm -f /var/log/temp01 +\layout Standard + +Or if you have a newer system that puts files in /etc/cron.daily etc., create + a file called +\begin_inset Quotes eld +\end_inset + +webalizer +\begin_inset Quotes srd +\end_inset + + in the cron.daily subdirectory. + Use the following as the contents of your file, and make sure to chmod + 755 it when done. +\layout LyX-Code + +#!/bin/sh +\layout LyX-Code + +/usr/local/sbin/make_combined_log.pl 1 www.yourdomain.com > /var/log/httpd/templog +\layout LyX-Code + +/usr/local/bin/webalizer -q -c /etc/webalizer.conf +\layout LyX-Code + +rm -f /var/log/httpd/templog +\layout Standard + +See? Easy. +\layout Subsubsection + + +\begin_inset LatexCommand \label{sec:cookie} + +\end_inset + +How can I log mod_usertrack cookies? +\layout Standard + +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: +\layout LyX-Code + +LogFormat "%h %l %u %t +\backslash +"%r +\backslash +" %s %b +\backslash +"%{Referer}i +\backslash +" +\backslash +"%{User-Agent}i +\backslash +"" +\backslash +"%{cookie}n +\backslash +"" +\layout Standard + +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. +\layout Standard + +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: +\layout LyX-Code + +alter table acc_log_tbl add column cookie varchar(255); +\layout Standard + +Next configure your server to set usertracking cookies as follows, and make + sure you include the new 'c' directive in your +\noun on +LogSQLTransferLogFormat +\noun default +, which activates cookie logging. + Here's an example: +\layout LyX-Code + + +\layout LyX-Code + + CookieTracking on +\layout LyX-Code + + CookieStyle Cookie +\layout LyX-Code + + CookieName Foobar +\layout LyX-Code + + LogSQLTransferLogFormat huSUsbTvRAc +\layout LyX-Code + + LogSQLWhichCookie Foobar +\layout LyX-Code + + +\layout Standard + +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. +\layout Standard + +Recap: the 'c' character +\emph on +activates +\emph default + cookie logging, and the +\noun on +LogSQLWhichCookie +\noun default + directive +\emph on +chooses +\emph default +which cookie to log. +\layout Standard + +FYI, you are advised NOT to use +\noun on +CookieStyle Cookie2 +\noun default + -- 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. +\layout Standard + +Perform some hits on your server and run a select: +\layout LyX-Code + +mysql> select request_uri,cookie from access_log where cookie is not null; +\layout LyX-Code + +\layout Standard +\align center + +\begin_inset Tabular + + + + + + +\begin_inset Text + +\layout Standard + +request_uri +\end_inset + + +\begin_inset Text + +\layout Standard + +cookie +\end_inset + + + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/ +\end_inset + + +\begin_inset Text + +\layout Standard + +ool-18e4.dyn.optonline.net.130051007102700823 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/usa.gif +\end_inset + + +\begin_inset Text + +\layout Standard + +ool-18e4.dyn.optonline.net.130051007102700823 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +/mod_log_sql/style_1.css +\end_inset + + +\begin_inset Text + +\layout Standard + +ool-18e4.dyn.optonline.net.130051007102700823 +\end_inset + + + + +\end_inset + + +\layout LyX-Code + +\layout Subsubsection + +What if I want to log more than one cookie? What is the difference between + LogSQLWhichCookie and LogSQLWhichCookies? +\layout Standard + +As of version 1.17, you have a choice in how you want cookie logging handled. +\layout Standard + +If you are interested in logging only one cookie per request, follow the + instructions in section +\begin_inset LatexCommand \ref{sec:cookie} + +\end_inset + + 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 +\noun on + LogSQLWhichCookie +\noun default +. + Don't forget to specify the 'c' character in +\noun on +LogSQLTransferLogFormat +\noun default +. +\layout Standard + +If, however, you need to log multiple cookies per request, you must employ + the +\noun on +LogSQLWhichCookies +\noun default + (note the plural) directive. + The cookies you specify will be logged to a separate table (as discussed + in section +\begin_inset LatexCommand \ref{secMulTable} + +\end_inset + +), 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 +\noun on +LogSQLWhichCookies +\noun default +, you do +\series bold +not +\series default + need to include the 'c' character in +\noun on +LogSQLTransferLogFormat +\noun default +. +\layout Standard + + +\noun on +LogSQLWhichCookie +\noun default + and +\noun on +LogSQLWhichCookies +\noun default + can coexist without conflict because they operate on entireley different + tables, but you're better off choosing the one you need. +\layout Subsubsection + +What are the SSL logging features, and how do I activate them? +\layout Standard + +Note: you do +\series bold +not +\series default +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. +\layout Standard + +By adding certain characters to your +\noun on +LogSQLTransferLogFormat +\noun default + 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-grad +e security to access your secure software area. +\layout Standard + +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. +\layout Standard + +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: +\layout LyX-Code + +alter table access_log add column ssl_cipher varchar(25); +\layout LyX-Code + +alter table access_log add column ssl_keysize smallint unsigned; +\layout LyX-Code + +alter table access_log add column ssl_maxkeysize smallint unsigned; +\layout Standard + +Finally configure httpd.conf to activate the SSL fields. + Note that this is only meaningful in a VirtualHost that is set up for SSL. +\layout LyX-Code + + +\layout LyX-Code + + LogSQLTransferLogFormat AbHhmRSsTUuvc +\series bold +Qqz +\series default + +\layout LyX-Code + + +\layout Standard + +The last three characters (Qqz) in the directive are the SSL ones; see section + +\begin_inset LatexCommand \ref{sub:Frmat} + +\end_inset + + in the directives documentation for details of the +\noun on +LogSQLTransferLogFormat +\noun default + directive. +\layout Standard + +Restart Apache, then perform some hits on your server. + Then run the following select statement: +\layout LyX-Code + +mysql> select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize +\layout LyX-Code + +from access_log where ssl_cipher is not null; +\layout LyX-Code + +\layout Standard +\align center + +\begin_inset Tabular + + + + + + + + + +\begin_inset Text + +\layout Standard + +remote_host +\end_inset + + +\begin_inset Text + +\layout Standard + +request_uri +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_cipher +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_keysize +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_maxkeysize +\end_inset + + + + +\begin_inset Text + +\layout Standard + +216.190.52.4 +\end_inset + + +\begin_inset Text + +\layout Standard + +/dir/somefile.html +\end_inset + + +\begin_inset Text + +\layout Standard + +RC4-MD5 +\end_inset + + +\begin_inset Text + +\layout Standard + +128 +\end_inset + + +\begin_inset Text + +\layout Standard + +128 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +216.190.52.4 +\end_inset + + +\begin_inset Text + +\layout Standard + +/dir/somefile.gif +\end_inset + + +\begin_inset Text + +\layout Standard + +RC4-MD5 +\end_inset + + +\begin_inset Text + +\layout Standard + +128 +\end_inset + + +\begin_inset Text + +\layout Standard + +128 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +216.190.52.4 +\end_inset + + +\begin_inset Text + +\layout Standard + +/dir/somefile.jpg +\end_inset + + +\begin_inset Text + +\layout Standard + +RC4-MD5 +\end_inset + + +\begin_inset Text + +\layout Standard + +128 +\end_inset + + +\begin_inset Text + +\layout Standard + +128 +\end_inset + + + + +\end_inset + + +\layout LyX-Code + +\the_end -- cgit v0.9.2