%% LyX 1.1 created this file. For more info, see http://www.lyx.org/. %% Do not edit unless you really know what you are doing. \documentclass[10pt,english]{article} \usepackage[T1]{fontenc} \usepackage{geometry} \geometry{verbose,letterpaper,tmargin=0.5in,bmargin=1in,lmargin=1in,rmargin=1in} \usepackage{babel} \IfFileExists{url.sty}{\usepackage{url}} {\newcommand{\url}{\texttt}} \makeatletter %%%%%%%%%%%%%%%%%%%%%%%%%%%%%% LyX specific LaTeX commands. \providecommand{\LyX}{L\kern-.1667em\lower.25em\hbox{Y}\kern-.125emX\@} %%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Textclass specific LaTeX commands. \newenvironment{lyxcode} {\begin{list}{}{ \setlength{\rightmargin}{\leftmargin} \raggedright \setlength{\itemsep}{0pt} \setlength{\parsep}{0pt} \normalfont\ttfamily}% \item[]} {\end{list}} \makeatother \begin{document} \vfill{} \title{Installing and Running mod\_log\_sql} \vfill{} \author{Christopher Powell } \date{11/13/02} \maketitle \tableofcontents{} \newpage \section{Introduction} \subsection{Homepage } \begin{lyxcode} http://www.grubbybaby.com/mod\_log\_sql/ \end{lyxcode} \subsection{Summary} This Apache module will permit you to log Apache accesses (and a lot of related information) to a SQL database. Unlike logging to a flat text file (which is standard in Apache), there are a lot of powerful advantages to logging to SQL. This module can either replace or happily coexist with Apache's text file logging facility. \subsection{Approach} This project was formerly known as mod\_log\_mysql. It has been renamed to mod\_log\_sql in order to reflect the project goal of database-inspecificity. The module currently supports MySQL, and development for other database backends is underway. In order to save speed and overhead, links are kept alive in between queries. This module uses one SQL link per httpd child. 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. Virtual hosts are supported in the same manner they are in the regular logging modules. You define some basic 'global' directives in the main server config, then you define more specific 'local' directives inside each virtualhost stanza. SQL links are opened by each child process when it is born. Error reporting is robust throughout and will let you know about database issues in the standard Apache error-log for the server or virtual server. A robust \char`\"{}preserve\char`\"{} 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, you reboot the db host, etc. -- 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. \char`\"{}nobody\char`\"{} on many Linux installations). When your database becomes available again, 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: \begin{lyxcode} \#~mysql~-uadminuser~-p~mydbname~<~/tmp/mysql-preserve \end{lyxcode} \subsection{Supported directives} Please see the web-based documentation for full explanation of all supported run-time directives. http://www.grubbybaby.com/mod\_log\_sql/directives.html See the FAQ for some handy examples: http://www.grubbybaby.com/mod\_log\_sql/faq.html \subsection{What gets logged by default? } All the data that would be contained in the \char`\"{}Combined Log Format\char`\"{} is logged by default, plus a little extra. Your best bet is to begin by accepting this default, then later customize the log configuration based on your needs. The online documentation of the run-time directives includes a full explanation of what you can log, including examples. \subsection{Minor Notes} \begin{itemize} \item 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. \item The 'time\_stamp' field is stored in an UNSIGNED INTEGER column, in the standard unix \char`\"{}seconds since 1/1/1970 12:00:00\char`\"{} format. This is superior to storing the access time as a string due to size requirements: an UNSIGNED INT type fits in 4 bytes, whereas the Apache date string (e.g. \char`\"{}18/Nov/2001:13:59:52 -0800\char`\"{}) requires 26 bytes -- significantly larger, and those extra 22 bytes will add up over the thousands of accesses that a busy server will experience. Besides, an INT type is far more flexible for comparisons, etc. In MySQL 3.21 and above you can easily convert this to a human readable format using from\_unixtime(), e.g.: \begin{lyxcode} select~remote\_host,request\_uri,from\_unixtime(time\_stamp)~from~access\_log; \end{lyxcode} The enclosed perl program ''make\_combined\_log.pl'' shows how you can extract your access records in a format that is completely Combined Log Format compliant. You can then feed this to your favorite web log analysis tool. \item The table's string values can be CHAR or VARCHAR, at a length of your choice. VARCHAR is superior because it truncates long strings; CHAR types are fixed-length and will be padded with spaces. Just like the time\_stamp described above, that kind of space waste will add up over thousands of records. \item 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 or the INSERT will fail, sometimes in a really mysterious way to the frustrated sysad. \item Apache normally logs numeric fields with a '-' character to mean \char`\"{}not applicable,\char`\"{} e.g. bytes\_sent on a request with a 304 response code. 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. \end{itemize} \subsection{Author / Maintainer} The actual logging code was taken from the already existing flat file text modules, so all that credit goes to the Apache Server group. The MySQL routines and directives were added by Zeev Suraski . All changes from 1.06+ and the new documentation were added by Chris Powell . It seems that the module had fallen into the \char`\"{}unmaintained\char`\"{} category -- it hadn't been updated since 1998 -- so Chris adopted it as the new maintainer. \section{Installation} \subsection{Requirements} \begin{itemize} \item A compatible system. I have run mod\_log\_sql on Red Hat based systems (Red Hat, Mandrake). These instructions should easily adapt to any modern distro. \item Apache 1.2 or 1.3 installed. (I run 1.3.22 and it works fine). You should have already successfully compiled Apache and know what you're doing there. \item The MySQL development headers. This is called different things on different distros. For example, Red Hat 6.x called this RPM \char`\"{}MySQL-devel\char`\"{} whereas Mandrake calls it \char`\"{}libmysql10-devel\char`\"{}. \item 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. \item Again, basic administrative skills with Apache and MySQL. I try to make things as easy as possible in this file, but its purpose is not to be an administrative tutorial. \item Additionally, if you want to be able to log SSL information such as keysize or cipher, you need OpenSSL and glibc-devel installed. Both are available as RPMs. \end{itemize} \subsection{Do I want a DSO?} 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. 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. \subsection{Installation as an Apache DSO (Preferred) } \begin{enumerate} \item Perform all the following steps as root so that you have install privs, etc. Unpack the archive into a working directory. \begin{lyxcode} \#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql \end{lyxcode} \item Edit Makefile for your system.\\ NECESSARY: \begin{itemize} \item The location where you installed Apache -- usually /usr/local/apache, 'locate apxs' can help you find it. \item The location of your MySQL libraries, find using 'locate libmysqlclient' \item The location of your MySQL header files, find using 'locate mysql.h' \end{itemize} OPTIONAL if you have included mod\_ssl in Apache and want to log SSL data such as keysize and cipher type: \begin{itemize} \item The location of your SSL header files, find using 'locate mod\_ssl.h' \end{itemize} Now that you know these things, edit Makefile and replace the stock values with your own. IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting a \# character in front of it: \begin{lyxcode} \#MODSSLHDRS=/usr/include/... \end{lyxcode} \item Instruct apxs to compile the module as a DSO. \begin{lyxcode} \#~make~dso \end{lyxcode} \item You should see output similar to the following: \begin{lyxcode} /usr/local/Apache/bin/apxs~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-c~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-L/usr/lib~-lmysqlclient~-lz~mod\_log\_sql.c~ gcc~-DLINUX=22~-DNO\_DBM\_REWRITEMAP~-DMOD\_SSL=208111~-DUSE\_HSREGEX~-DEAPI~-DUSE\_EXPAT~-I../lib/expat-lite~-fpic~-DSHARED\_CORE~-DSHARED\_MODULE~-I/usr/local/Apache/include~-O2~-Wall~-DEAPI~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-c~mod\_log\_sql.c~ gcc~-shared~-o~mod\_log\_sql.so~mod\_log\_sql.o~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-L/usr/lib~-lmysqlclient~-lz~-lm~-lcrypt~-ldb \end{lyxcode} You should see no errors and have a file called \char`\"{}mod\_log\_sql.so\char`\"{} in your directory. \item Instruct apxs to install the DSO. \begin{lyxcode} \#~make~dsoinstall \end{lyxcode} You should see output similar to the following: \begin{lyxcode} /usr/local/Apache/bin/apxs~-i~mod\_log\_sql.so cp~mod\_log\_sql.so~/usr/local/Apache/libexec/mod\_log\_sql.so chmod~755~/usr/local/Apache/libexec/mod\_log\_sql.so \end{lyxcode} \item Module ordering within httpd.conf is important. If you are logging SSL, you must make sure that \begin{lyxcode} LoadModule~ssl\_module~libexec/libssl.so \end{lyxcode} comes before \begin{lyxcode} LoadModule~sql\_log\_module~libexec/mod\_log\_sql.so \end{lyxcode} If you don't, you will get this error when you start Apache: \begin{lyxcode} /usr/local/apache/libexec/mod\_log\_mysql.so:~undefined~symbol:~ssl\_var\_lookup /usr/local/apache/bin/apachectl~startssl:~httpd~could~not~be~started \end{lyxcode} (Because mod\_log\_sql doesn't yet have the required symbols that mod\_ssl provides.) Now skip below to the \char`\"{}Configuration\char`\"{} section. \end{enumerate} \subsection{Installation as a static module compiled into httpd} \begin{enumerate} \item Perform all the following steps as root so that you have install privs, etc. \item Unpack the archive into a working directory. \begin{lyxcode} \#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql \end{lyxcode} \item \label{step:editMF}Edit Makefile for your system.\\ NECESSARY: \begin{itemize} \item The location where you installed Apache -- usually /usr/local/apache, 'locate apxs' can help you find it. \item The location of your Apache {*}sources{*}, find using 'locate ABOUT\_APACHE' \item The location of your MySQL header files, find using 'locate mysql.h' \item The location of your MySQL libraries, find using 'locate libmysqlclient' \end{itemize} OPTIONAL if you have included mod\_ssl in Apache and want to log SSL data such as keysize and cipher type: \begin{itemize} \item The location of your mod\_ssl header files, find using 'locate mod\_ssl.h' \item The location of your OpenSSL header files, find using 'locate x509.h' \item The location of your db1 header files, find using 'locate mpool.h' \end{itemize} Now that you know these things, edit Makefile and replace the stock values with your own. IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, OPNSSLHDRS and DB1HDRS by putting a \# character in front of each one, e.g. \#OPNSSLHDRS=/usr/include/... \item \# make static \item \# make statinstall \item Change to your Apache source dir. \begin{lyxcode} \#~cd~/usr/local/src/apache-1.3.22/src \end{lyxcode} \item Re-make your httpd binary as follows. \begin{enumerate} \item Edit Configuration.apaci as follows... \begin{itemize} \item Append the following string to the EXTRA\_LIBS= line. (\char`\"{}/usr/lib/mysql\char`\"{} is from step \ref{step:editMF}, where your MySQL libraries live): \end{itemize} \begin{lyxcode} -L/usr/lib/mysql~-lmysqlclient~-lm~-lz \end{lyxcode} \begin{itemize} \item Find the mod\_log\_config.o line, and add this line immediately after it: \end{itemize} \begin{lyxcode} AddModule~modules/sql/mod\_log\_sql.o \end{lyxcode} \item \# cp Configuration.apaci Configuration \item \# ./Configure \item \# make \item \# strip httpd \end{enumerate} \item Test your new apache binary: \begin{lyxcode} \#~./httpd~-l \end{lyxcode} \item You should see something like: \begin{lyxcode} Compiled-in~modules:~ http\_core.c mod\_log\_sql.c~<-{}-~That's~the~line~you're~looking~for. mod\_env.c~ mod\_log\_config.c~ mod\_mime.c~ mod\_negotiation.c~... etc... \end{lyxcode} \item 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{lyxcode} \#~/etc/rc.d/init.d/httpd~stop~ \#~mv~/usr/local/Apache/bin/httpd~\textasciitilde{}/httpd-save~ \#~cp~-f~./httpd~/usr/local/Apache/bin/ \end{lyxcode} \end{enumerate} \section{Configuration} \subsection{Preparing MySQL} You have to prepare the database to receive data from mod\_log\_sql, and set up run-time directives in httpd.conf to control how and what mod\_log\_sql logs. This section will discuss how to get started with a basic config. Full documentation of the run-time directives is available here: http://www.grubbybaby.com/mod\_log\_sql/directives.html \begin{enumerate} \item 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 overhead. In this basic setup we'll let the module create tables for us. \item We still need to have a logging database created and ready, so run the MySQL command line client and create a database: \begin{lyxcode} \#~mysql~-uadmin~-pmypassword~mysql>~create~database~apachelogs; \end{lyxcode} \item If you want to hand-create the tables, run the enclosed 'create-tables' SQL script as follows: \begin{lyxcode} mysql>~source~create\_tables.sql \end{lyxcode} \item 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, \char`\"{}apachelogs\char`\"{} is the database, \char`\"{}loguser\char`\"{} is the userid to create, \char`\"{}my.apachemachine.com\char`\"{} is the name of the Apache machine, and \char`\"{}l0gger\char`\"{} is the password to assign. Choose values that are different from these examples. \begin{lyxcode} mysql>~grant~insert,create~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger'; \end{lyxcode} \item You may be especially security-paranoid and not want \char`\"{}loguser\char`\"{} to have \char`\"{}create\char`\"{} capability within the \char`\"{}apachelogs\char`\"{} databse. You can disable that but the cost is that you cannot use the module's automatic-table-creation feature. If that's an acceptable cost, hand-create the tables as described in step 1 and use the following GRANT statement instead of the one above: \begin{lyxcode} mysql>~grant~insert~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger'; \end{lyxcode} \item 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{lyxcode} log=/var/log/mysql-messages \end{lyxcode} Then restart MySQL. \begin{lyxcode} \#~/etc/rc.d/init.d/mysql~restart \end{lyxcode} \end{enumerate} \subsection{Preparing Apache} \begin{enumerate} \item Tell the module what database to use and the appropriate authentication information. \begin{description} \item [EXAMPLE:]Use the MySQL database called \char`\"{}apachelogs\char`\"{} running on \char`\"{}dbmachine.foo.com\char`\"{}. The module uses username \char`\"{}loguser\char`\"{} and password \char`\"{}l0gger\char`\"{} to authenticate to the database. The log entries will be INSERTed into the table called \char`\"{}access\_log\char`\"{}. \end{description} So, edit httpd.conf and insert the following lines somewhere AFTER any LoadModule / AddModule statements. Make sure these statements are \char`\"{}global,\char`\"{} i.e. not inside any VirtualHost stanza. \begin{lyxcode} LogSQLDatabase~apachelogs LogSQLLoginInfo~dbmachine.foo.com~loguser~l0gger~ LogSQLCreateTables~on \end{lyxcode} \item If your database resides on localhost instead of another host, specify the MySQL server's socket file as follows: \begin{lyxcode} LogSQLSocketFile~/your/path/to/mysql.sock \end{lyxcode} \item The actual logging is set up on a virtual-host-by-host basis. So, skip down to the virtual host you want to set up. Instruct this virtual host to log entries to the table ''access\_log'' by inserting a LogSQLTransferLogTable directive. (The LogSQLTransferLogTable directive is the minimum required to log -- other directives simply tune the module's behavior.) \begin{lyxcode} {[}snip{]} LogSQLTransferLogTable~access\_log {[}snip{]} \end{lyxcode} \item Restart apache. \begin{lyxcode} \#~/etc/rc.d/init.d/httpd~start \end{lyxcode} \end{enumerate} \subsection{Testing the module} \begin{enumerate} \item Load your web site in a browser to trigger some hits, then confirm that the entries are being successfully logged: \begin{lyxcode} \#~mysql~-hmysql.host.com~-umysqladmin~-p~-e~\char`\"{}select~{*}~from~access\_log\char`\"{}~apachelogs~ Enter~password: (Several~lines~of~output~should~follow,~corresponding~to~your~hits~on~the~site.) \end{lyxcode} 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. \item If you do not see any entries in the access\_log, then something is preventing the inserts from happening. This problem could be caused by several things: \begin{itemize} \item Improper privileges set up in the MySQL database \item You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry \item You didn't specify the right host \end{itemize} If you have confirmed your LogSQL{*} directives and know them to be correct, you should examine the httpd server logs for mod\_log\_sql messages; the module will offer hints as to why it cannot connect, etc. Also examine the MySQL log that you established in step 3. Ensure that the INSERTs are not being rejected because of a malformed table entry or other clerical error. If you see no INSERT attempts in the log, the module isn't successfully connecting to the database. The next thing to do is recompile the module with debugging output activated. change the \char`\"{}\#undef DEBUG\char`\"{} on line 8 of mod\_log\_sql.c to \char`\"{}\#define DEBUG\char`\"{} 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. \end{enumerate} You can now activate the advanced features of mod\_log\_sql. These are all described in the next section. \subsection{Configuration directive reference} \subsubsection{LogSQLLoginInfo } \begin{lyxcode} \textbf{MANDATORY}~ Syntax:~LogSQLLoginInfo~host~user~password Example:~LogSQLLoginInfo~foobar.baz.com~logwriter~passw0rd~ Context:~server~config \end{lyxcode} \begin{quote} Defines the general parameters of the MySQL host to which you will be logging. host is the hostname or IP address of the MySQL machine. user is the MySQL userid (not a Unix userid!) with INSERT privileges on the table defined in LogSQLTransferLogTable. password is that user's password. This is defined only once in the httpd.conf file. \end{quote} \subsubsection{LogSQLDatabase } \begin{lyxcode} \textbf{MANDATORY} Syntax:~LogSQLDatabase~database~ Example:~LogSQLDatabase~loggingdb~ Context:~server~config \end{lyxcode} \begin{quote} Defines the database that is used for logging. database must be operating on the MySQL host defined in LogSQLLoginInfo. This is defined only once in the httpd.conf file. \end{quote} \subsubsection{LogSQLTransferLogTable} \begin{lyxcode} \textbf{MANDATORY~} Syntax:~LogSQLTransferLogTable~table-name~ Example:~LogSQLTransferLogTable~access\_log\_table~ Context:~virtual~host \end{lyxcode} \begin{quote} 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 LogSQLDatabase. This directive is not necessary if you declare LogSQLMassVirtualHosting On, since that directive activates dynamically-named tables. If you attempt to use LogSQLTransferLogTable at the same time a warning will be logged and it will be ignored, since LogSQLMassVirtualHosting takes priority. \end{quote} \subsubsection{LogSQLRequestIgnore} \begin{lyxcode} Syntax:~LogSQLRequestIgnore~req1~req2~req3~...~reqN~ Example:~LogSQLRequestIgnore~root.exe~cmd.exe~default.ida~favicon.ico~ Context:~virtual~host \end{lyxcode} \begin{quote} Lists a series of strings that, if present in the URI, will cause that request NOT to be logged. This directive is useful for cutting down on log clutter when you KNOW that you do not want to log requests for certain objects. (The example above is a genuinely useful one; it will prevent logging of many common Microsoft-based worm intrusion attempts, as well as those ridiculous requests for the favicon.) 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(). \end{quote} \subsubsection{LogSQLRemhostIgnore} \begin{lyxcode} Syntax:~LogSQLRemhostIgnore~host1~host2~host3~...~hostN~ Example:~LogSQLRemhostIgnore~localnet.com~ Context:~virtual~host \end{lyxcode} \begin{quote} Lists a series of strings that, if present in the REMOTE\_HOST, will cause that request NOT to be logged. This directive is useful for cutting down on log clutter when you KNOW that you do not want to log requests from certain hosts, such as your own internal network machines. 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(). \end{quote} \subsubsection{LogSQLRefererIgnore} \begin{lyxcode} Syntax:~LogSQLRefererIgnore~ref1~ref2~ref3~...~refN~ Example:~LogSQLRefererIgnore~google.com~ Context:~virtual~host \end{lyxcode} \begin{quote} Lists a series of strings that, if present in the HTTP\_REFERER, will cause that request NOT to be logged. This directive is useful for cutting down on log clutter when you know that you do not want to log requests from certain referers. Each string is separated by a space, and NO regular expressions or globbing are allowed. Each string is evaluated as a substring of the HTTP\_REFERER using strstr(). \end{quote} \subsubsection{LogSQLWhichCookie} \begin{lyxcode} Syntax:~LogSQLWhichCookie~cookiename~ Example:~LogSQLWhichCookie~Clicks Default:~None.~You~must~set~this~if~you~wish~to~capture~cookies. Context:~virtual~host \end{lyxcode} \begin{quote} 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. If you have activated cookie logging in LogSQLTransferLogFormat, then LogSQLWhichCookie tells mod\_log\_sql which cookie to log. This is useful because many times you will be setting and receiving more than one cookie from a client; without this directive you'd be unable to choose which cookie is your mod\_usertrack cookie. Note: although this was intended for people who are using mod\_usertrack to set user-tracking cookies, you aren't restricted in any way. You can choose which cookie you wish to log to the database, and it doesn't necessarily have to have anything to do with mod\_usertrack. \end{quote} \subsubsection{LogSQLCreateTables} \begin{lyxcode} Syntax:~LogSQLCreateTables~flag Example:~LogSQLCreateTables~On~ Default:~Off~ Context:~server~config \end{lyxcode} \begin{quote} mod\_log\_sql now has the ability to create its tables on-the-fly. The advantage to this is convenience: you don't have to execute any SQL by hand to prepare the table. This is especially helpful for people with lots of virtual hosts (who should also see the LogSQLMassVirtualHosting directive). There is a slight disadvantage: if you wish to activate this feature, then the user specified by LogSQLLoginInfo must have CREATE privileges on the database. In an absolutely paranoid, locked-down situation you may only want to grant your mod\_log\_sql user INSERT privileges on the database; in that situation you are unable to take advantage of LogSQLCreateTables. But most people -- even the very security-conscious -- will find that granting CREATE on the logging database is reasonable. This is defined only once in the httpd.conf file. \end{quote} \subsubsection{LogSQLMassVirtualHosting} \begin{lyxcode} Syntax:~LogSQLMassVirtualHosting~flag~ Example:~LogSQLMassVirtualHosting~On~ Default:~Off~ Context:~server~config \end{lyxcode} \begin{quote} If you administer a site hosting many, many virtual hosts then this option will appeal to you. If you activate LogSQLMassVirtualHosting then several things happen: \begin{itemize} \item the on-the-fly table creation feature is activated automatically \item the transfer log table name is dynamically set from the virtual host's name (example: a virtual host www.grubbybaby.com gets logged to table access\_www\_grubbybaby\_com) \item 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. \end{itemize} This is a huge boost in convenience for sites with many virtual servers. Activating LogSQLMassVirtualHosting obviates the need to create every virtual server's table and provides more granular security possibilities. This is defined only once in the httpd.conf file. \end{quote} \subsubsection{LogSQLPreserveFile} \begin{lyxcode} Syntax:~LogSQLPreserveFile~filename~ Example:~LogSQLPreserveFile~offline-preserve~ Default:~mysql-preserve~(in~/tmp)~ Context:~virtual~host \end{lyxcode} \begin{quote} 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. For security purposes the name you supply will be prepended with \char`\"{}/tmp/\char`\"{} to force creation of the file in /tmp. If you do not define LogSQLPreserveFile then all virtual servers will log to the same default preserve file (/tmp/mysql-preserve). You can redefine this on a virtual-host basis in order to segregate your preserve files if you desire. Note that segregation is not really necessary, as the SQL statements that are written to the preserve file already distinguish between different virtual hosts. The module will log to error-log if/when it notices a database outage, and upon database return. You will therefore know when the preserve file is being used, although it is your responsibility to import the file. The file does not need to be created in advance. It is safe to remove or rename the file without interrupting Apache, as the module closes the filehandle immediately after completing the write. The file is created with the user \& group ID of the running Apache process (e.g. 'nobody' on many Linux distributions). \end{quote} \subsubsection{LogSQLSocketFile } \begin{lyxcode} Syntax:~LogSQLSocketFile~filename~ Example:~LogSQLSocketFile~/tmp/mysql.sock~ Default:~/var/lib/mysql/mysql.sock~ Context:~server~config \end{lyxcode} \begin{quote} At Apache runtime you can specify the MySQL socket file to use. Set this once in your main server config to override the default value. This value is irrelevant if your database resides on a separate machine. mod\_log\_sql will automatically employ the socket for db communications if the database resides on the local host. If the db resides on a separate host the module will automatically use TCP/IP. This is a function of the MySQL API and is not user-configurable. \end{quote} \subsubsection{LogSQLTransferLogFormat } \begin{lyxcode} Syntax:~LogSQLTransferLogFormat~format-string~ Example:~LogSQLTransferLogFormat~huSUTv~ Default:~AbHhmRSsTUuv~ Context:~virtual~host \end{lyxcode} \begin{quote} 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: \begin{tabular}{|c|l|l|l|l|} \hline \textbf{Character}& \textbf{What is this?}& \textbf{MySQL DB field}& \textbf{Recommended column type}& \textbf{Example}\\ \hline \hline A& User agent& agent& varchar(255)& Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0)\\ \hline b& Bytes transfered& bytes\_sent& int unsigned& 32561\\ \hline c& Text of cookie& cookie& varchar(255)& Apache=sdyn.fooonline.net.130051007102700823\\ \hline f& & request\_file& varchar(255)& \\ \hline H& HTTP request protocol& request\_protocol& varchar(10)& HTTP/1.1\\ \hline h& Name of remote host& remote\_host& varchar(50)& blah.foobar.com\\ \hline I& Ident user info& remote\_logname& varchar(50)& bobby\\ \hline m& HTTP request method& request\_method& varchar(6)& GET\\ \hline P& httpd child PID& child\_pid& smallint unsigned& 3215\\ \hline p& httpd port& server\_port& smallint unsigned& 80\\ \hline R& Referer& referer& varchar(255)& http://www.biglinkstoyou.com/linkpage.html\\ \hline r& Request in full form& request\_line& varchar(255)& GET /books-cycroad.html HTTP/1.1\\ \hline S& Time of request in UNIX format& time\_stamp& int unsigned& 1005598029\\ \hline s& HTTP status of request& status& smallint unsigned& 404\\ \hline T& Seconds to service request& request\_duration& smallint unsigned& 2\\ \hline t& Time of request in human format& request\_time& char(28)& {[}02/Dec/2001:15:01:26 -0800{]}\\ \hline U& Request in simple form& request\_uri& varchar(255)& /books-cycroad.html\\ \hline u& User info from HTTP authentication& remote\_user& varchar(50)& bobby\\ \hline v& Virtual host servicing the request& virtual\_host& varchar(50)& www.foobar.com\\ \hline \end{tabular} If you have compiled mod\_log\_sql with WANT\_SSL\_LOGGING, you also get these: \begin{tabular}{|c|l|l|l|} \hline \textbf{Character}& \textbf{MySQL DB field it activates}& \textbf{MySQL DB field it activatesRecommended column type}& \textbf{Example}\\ \hline \hline z& ssl\_cipher& varchar(25)& RC4-MD5\\ \hline q& ssl\_keysize& smallint unsigned& 56\\ \hline Q& ssl\_maxkeysize& smallint unsigned& 128\\ \hline \end{tabular} \end{quote} \section{FAQ} \subsection{Why log to an SQL database?} To begin with, let's get it out of the way: logging to a database is not a panacea. But while there are complexities with this solution, the benefit can be substantial for certain classes of administrator or people with advanced requirements: \begin{itemize} \item Chores like log rotation go away, as you can DELETE records from the SQL database once they are no longer useful. For example, the excellent and popular log-analysis tool Webalizer (http://www.webalizer.com/) does not need historic logs after it has processed them, enabling you to delete older logs. \item 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. \item People acquainted with the power of SQL SELECT statements will know the flexibility of the extraction possibilities at their fingertips. \end{itemize} For example, do you want to see all your 404's? Do this: \begin{lyxcode} select~remote\_host,status,request\_uri,bytes\_sent,from\_unixtime(time\_stamp)~from~acc\_log\_tbl~where~status=404~order~by~time\_stamp; +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- +~~~~~~~~~~~~~~~~~~~~~~~remote\_host~|~status~|~request\_uri~~~~~~~~~~~~~~|~bytes\_sent~|~from\_unixtime(time\_stamp)| +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- |~marge.mmm.co.uk~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~02:30:56~~~~~~| |~62.180.239.251~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~02:45:25~~~~~~| |~212.234.12.66~~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~03:01:00~~~~~~|~ |~212.210.78.254~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~03:26:05~~~~~~|~ +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ \end{lyxcode} Or do you want to see how many bytes you've sent within a certain directory or site? Do this: \begin{lyxcode} select~request\_uri,sum(bytes\_sent)~as~bytes,count(request\_uri)~as~howmany~from~acc\_log\_tbl~where~request\_uri~like~'\%mod\_log\_sql\%'~group~by~request\_uri~order~by~howmany~desc;~ +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~ |~request\_uri~~~~~~~~~~~~~~~~~~~~~~~|~bytes~~~|~howmany~|~ +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~ |~/mod\_log\_sql/style\_1.css~~~~~~~~~~|~157396~~|~~~~1288~|~ |~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~|~2514337~|~~~~~801~|~ |~/mod\_log\_sql/mod\_log\_sql.tar.gz~~~|~9769312~|~~~~~456~|~ |~/mod\_log\_sql/faq.html~~~~~~~~~~~~~|~5038728~|~~~~~436~|~ |~/mod\_log\_sql/INSTALL~~~~~~~~~~~~~~|~1196161~|~~~~~202~|~ |~/mod\_log\_sql/directives.html~~~~~~|~1096821~|~~~~~171~|~ |~/mod\_log\_sql/CHANGELOG~~~~~~~~~~~~|~424481~~|~~~~~107~|~ |~/mod\_log\_sql/README~~~~~~~~~~~~~~~|~796072~~|~~~~~~97~|~ |~/mod\_log\_sql/directives-old.html~~|~42480~~~|~~~~~~~6~| |~/mod\_log\_sql/index.html~~~~~~~~~~~|~9531~~~~|~~~~~~~3~|~ +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+ \end{lyxcode} Or maybe you want to see who's linking to you? Do this: \begin{lyxcode} select~count(referer)~as~num,referer~from~acc\_log\_tbl~where~request\_uri='/mod\_log\_sql/'~group~by~referer~order~by~num~desc; +-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ |~num~|~referer~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ +-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ |~271~|~http://freshmeat.net/projects/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ |~96~~|~http://modules.apache.org/search?id=339~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ |~48~~|~http://freshmeat.net/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ |~8~~~|~http://freshmeat.net~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| |~7~~~|~http://freshmeat.net/daily/2001/11/30/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ |~6~~~|~http://freshmeat.net/daily/2001/11/20/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| +-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ \end{lyxcode} 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! \subsection{Why use MySQL? Are there alternatives?} MySQL is a robust, free, and very powerful production-quality database engine. It is well supported and comes with detailed documentation. Many 3rd-party software pacakges (e.g. Slashcode, the engine that powers Slashdot) run exclusively with MySQL. In other words, you will belong to a very robust and well-supported community by choosing MySQL. That being said, there are alternatives. PostgreSQL is probably MySQL's leading \char`\"{}competitor\char`\"{} in the free database world. There is also an excellent module available for Apache to permit logging to a PostgreSQL database, called pgLOGd \url{(http://www.digitalstratum.com/pglogd/)}. \subsection{Is this code production-ready?} By all accounts it is. It is known to work without a problem on many-thousands-of-hits-per-day webservers. Does that mean it is 100\% bug free? Well, no software is. But it is well-tested and /believed/ to be fully compatible with production environments. (The usual disclaimers apply. This software is provided without warranty of any kind.) \subsection{How well does it perform?} mod\_log\_sql scales to very high loads. Apache 1.3.22 + mod\_log\_sql was benchmarked using the \char`\"{}ab\char`\"{} (Apache Bench) program that comes with the Apache distribution; here are the results. Overall configuration: \begin{itemize} \item Machine A: Apache webserver \item Machine B: MySQL server \item Machines A and B connected with 100Mbps Ethernet \end{itemize} Webserver configuration: \begin{itemize} \item Celeron 400 128 MB RAM IDE storage \end{itemize} Apache configuration: \begin{lyxcode} Timeout~300~ KeepAlive~On~ MaxKeepAliveRequests~100~ KeepAliveTimeout~15~ MinSpareServers~5~ StartServers~10~ MaxSpareServers~15~ MaxClients~256~ MaxRequestsPerChild~5000~ LogSQLTransferLogFormat~AbHhmRSsTUuvc~ LogSQLWhichCookie~Clicks~ CookieTracking~on~ CookieName~Clicks \end{lyxcode} \char`\"{}ab\char`\"{} commandline: \begin{lyxcode} ./ab~-c~10~-t~20~-v~2~-C~Clicks=ab\_run~http://www.hostname.com/target~ \end{lyxcode} ( 10 concurrent requests; 20 second test; setting a cookie \char`\"{}Clicks=ab\_run\char`\"{}; target = the mod\_log\_sql homepage. ) Ten total ab runs were conducted: five with MySQL logging enabled, and five with all MySQL directives commented out of httpd.conf. Then each five were averaged. The results: \begin{itemize} \item Average of five runs employing MySQL \emph{and} standard text logging: 139.01 requests per second, zero errors. \item Average of five runs employing \emph{only} standard text logging: 139.96 requests per second, zero errors. \end{itemize} In other words, any rate-limiting effects on this particular hardware setup are not caused by MySQL. Note that although this very simple webserver setup is hardly cutting-edge -- it is, after all, a fairly small machine -- 139 requests per second equal over twelve million hits per day\emph{.} If you run this benchmark yourself, take note of three things: \begin{enumerate} \item Use a target URL that is on your own webserver :-). \item 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 \char`\"{}netstat -t|wc -l\char`\"{} on the webserver to see. If you don't wait, you can expect to see a lot of messages like \char`\"{}ip\_conntrack: table full, dropping packet\char`\"{} 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.) \item When done with your runs, clean these many thousands of requests out of your database: \end{enumerate} \begin{lyxcode} mysql>~delete~from~access\_log~where~agent~like~'ApacheBench\%';~mysql>~optimize~table~access\_log;~ \end{lyxcode} \subsection{Who's using mod\_log\_sql?} Good question! It would be great to find out! If you are a production-level mod\_log\_sql user, please contact the maintainer, Chris Powell \url{chris@grubbybaby.com}so that you can be mentioned here. \subsection{How do I extract the data in a format that my analysis tool can understand?} mod\_log\_sql would be virtually useless if there weren't a way for you to extract the data from your database in a somewhat meaningful fashion. To that end there's a Perl script enclosed with the distribution. That script (make\_combined\_log.pl) is designed to extract N-many days worth of access logs and provide them in a Combined Log Format output. You can use this very tool right in /etc/crontab to extract logs on a regular basis so that your favorite web analysis tool can read them. Or you can examine the Perl code to construct your own custom tool. For example, let's say that you want your web statistics updated once per day in the wee hours of the morning. A good way to accomplish that would be the following entries in /etc/crontab: \begin{lyxcode} \#~Generate~the~temporary~apache~logs~from~the~MySQL~database~(for~webalizer)~ 05~04~{*}~{*}~{*}~root~/usr/local/sbin/make\_combined\_log.pl~1~www.grubbybaby.com~>~/var/log/httpd/mysql-grubbybaby \#~Run~webalizer~on~httpd~log~ 30~04~{*}~{*}~{*}~root~/usr/local/bin/webalizer~-c~/etc/webalizer.conf;~rm~-f~/var/log/httpd/mysql-grubbybaby \end{lyxcode} Or if you have a newer system that puts files in /etc/cron.daily etc., create a file called ''webalizer'' in the cron.\_\_\_\_ subdir of your choice. Use the following as the contents of your file, and make sure to chmod 755 it when done. \begin{lyxcode} \#!/bin/sh /usr/local/sbin/make\_combined\_log.pl~1~www.yourdomain.com~>~/var/log/httpd/templog /usr/local/bin/webalizer~-q~-c~/etc/webalizer.conf~ rm~-f~/var/log/httpd/templog \end{lyxcode} See? Easy. \subsection{Why doesn't the module also replace the Apache ErrorLog?} There are circumstances when that would be quite unwise -- for example, if Apache could not reach the MySQL server for some reason and needed to log that fact. Without a text-based error log you'd never know anything was wrong, because Apache would be trying to log a database connection error to the database... you get the point. Error logs are usually not very high-traffic and are really best left as text files on a web server machine. \subsection{\label{sec:cookie}How can I log mod\_usertrack cookies?} A number of people like to log mod\_usertrack cookies in their Apache TransferLog to aid in understanding their visitors' clickstreams. This is accomplished, for example, with a statement as follows: \begin{lyxcode} LogFormat~\char`\"{}\%h~\%l~\%u~\%t~\textbackslash{}\char`\"{}\%r\textbackslash{}\char`\"{}~\%s~\%b~\textbackslash{}\char`\"{}\%\{Referer\}i\textbackslash{}\char`\"{}~\textbackslash{}\char`\"{}\%\{User-Agent\}i\textbackslash{}\char`\"{}\char`\"{}~\textbackslash{}\char`\"{}\%\{cookie\}n\textbackslash{}\char`\"{}\char`\"{} \end{lyxcode} Naturally it would be nice for mod\_log\_sql to permit the admin to log the cookie data as well, so as of version 1.10 you can do this. You need to have already compiled mod\_usertrack into httpd -- it's one of the standard Apache modules. First make sure you have a column called \char`\"{}cookie\char`\"{} in the MySQL database to hold the cookies, which can be done as follows if you already have a working database: \begin{lyxcode} alter~table~acc\_log\_tbl~add~column~cookie~varchar(255); \end{lyxcode} Next configure your server to set usertracking cookies as follows, and make sure you include the new 'c' directive in your LogSQLTransferLogFormat, which activates cookie logging. Here's an example: \begin{lyxcode} ~ ~CookieTracking~on~ ~CookieStyle~Cookie~ ~CookieName~Foobar~ ~LogSQLTransferLogFormat~huSUsbTvRAc~ ~LogSQLWhichCookie~Foobar~ \end{lyxcode} 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. FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that even newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2 (RFC 2965) format. Just stick with the standard COOKIE format and you'll be fine. Perform some hits on your server and run a select: \begin{lyxcode} mysql>~select~request\_uri,cookie~from~access\_log~where~cookie~is~not~null; +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ |~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~cookie~|~ +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~ |~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~ |~/mod\_log\_sql/usa.gif~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~ |~/mod\_log\_sql/style\_1.css~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~ ...etc... \end{lyxcode} \subsection{What if I want to log more than one cookie?} No problem. As of version 1.17, you have a choice. If you are just interested in a single cookie, follow the instructions in section \ref{sec:cookie} above. That cookie will be logged to a column in the regular access\_log table. However, if you need to log multiple cookies, you'll employ the LogSQLWhichCookies (note the plural) directive. The cookies you specify will be logged to a separate table, 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. LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict, but you're better off choosing the one you need. \subsection{What are the SSL logging features, and how do I activate them?} If you run an SSL-enabled server you may benefit from logging some SSL details. mod\_log\_sql now supports this ability. By adding certain characters to your LogSQLTransferLogFormat string you can tell mod\_log\_sql to log the SSL cipher, the SSL keysize of the connection, and the Max-keysize that was available. This would let you tell, for example, which clients were using only export-grade security to access your secure software area. You can compile mod\_log\_sql with SSL logging support if you have the right packages installed. If you already have an SSL-enabled Apache then you by definition have the correct packages already installed: OpenSSL and mod\_ssl. You need to ensure that your database is set up to log the SSL data. Issue the following commands to MySQL once you have your basic access\_log table built: \begin{lyxcode} alter~table~access\_log~add~column~ssl\_cipher~varchar(25); alter~table~access\_log~add~column~ssl\_keysize~smallint~unsigned; alter~table~access\_log~add~column~ssl\_maxkeysize~smallint~unsigned; \end{lyxcode} Finally configure httpd.conf to activate the SSL fields. Note that this is only meaningful in a VirtualHost that is set up for SSL. \begin{lyxcode} ~ ~LogSQLTransferLogFormat~AbHhmRSsTUuvcQqz~ \end{lyxcode} The last three characters (Qqz) in the directive are the SSL ones; see the directives documentation for details. Perform some hits on your server and run a select: \begin{lyxcode} mysql>~select~remote\_host,request\_uri,ssl\_cipher,ssl\_keysize,ssl\_maxkeysize~from~access\_log~where~ssl\_cipher~is~not~null; +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~ |~remote\_host~~~~~~~~~~~~~~|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ssl\_cipher~|~ssl\_keysize~|~ssl\_maxkeysize~| +-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~ |~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.html~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~ |~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.gif~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~ |~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.jpg~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~ ...etc... \end{lyxcode} \subsection{Does mod\_log\_sql connect to MySQL via TCP/IP or a socket?} It depends! Actually this isn't 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. When mod\_log\_sql issues the connect command to MySQL, this intelligent connect command uses sockets to communicate with MySQL if the specified MySQL database is on the same machine (because sockets are more efficient than TCP/IP). However, if the specified MySQL db is on a different machine, mod\_log\_sql connects using TCP/IP. You don't have any control of which methodology is used. You do have control over where mod\_log\_sql looks for the socket. The LogSQLSocketFile runtime configuration directive overrides the default of \char`\"{}/var/lib/mysql/mysql.sock\char`\"{} to whatever you wish. (Applies to mod\_log\_sql 1.16 or later only.) \subsection{Why do I occasionally see a \char`\"{}connection lost, attempting reconnect\char`\"{} message in my error-log?} 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: \begin{lyxcode} {[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~connection~lost,~attempting~reconnect {[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~reconnect~successful \end{lyxcode} Reference: MySQL documentation \url{http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away} \subsection{Does mod\_log\_sql work with Apache 2.x?} 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. 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. 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. \subsection{I have discovered a bug. Who can I contact?} Please contact the maintainer \url{chris@grubbybaby.com}! 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. \end{document}