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 --- Documentation/documentation.lyx | 7202 --------------------------------------- 1 file changed, 7202 deletions(-) delete mode 100644 Documentation/documentation.lyx (limited to 'Documentation/documentation.lyx') diff --git a/Documentation/documentation.lyx b/Documentation/documentation.lyx deleted file mode 100644 index eeb6af5..0000000 --- a/Documentation/documentation.lyx +++ /dev/null @@ -1,7202 +0,0 @@ -#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