summaryrefslogtreecommitdiffstats
path: root/Documentation/documentation.tex
diff options
context:
space:
mode:
Diffstat (limited to 'Documentation/documentation.tex')
-rw-r--r--Documentation/documentation.tex1534
1 files changed, 0 insertions, 1534 deletions
diff --git a/Documentation/documentation.tex b/Documentation/documentation.tex
deleted file mode 100644
index 0eb6359..0000000
--- a/Documentation/documentation.tex
+++ /dev/null
@@ -1,1534 +0,0 @@
1%% LyX 1.1 created this file. For more info, see http://www.lyx.org/.
2%% Do not edit unless you really know what you are doing.
3\documentclass[10pt,english]{article}
4\usepackage[T1]{fontenc}
5\usepackage{geometry}
6\geometry{verbose,letterpaper,tmargin=0.5in,bmargin=1in,lmargin=1in,rmargin=1in}
7\usepackage{babel}
8\IfFileExists{url.sty}{\usepackage{url}}
9 {\newcommand{\url}{\texttt}}
10
11\makeatletter
12
13%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% LyX specific LaTeX commands.
14\providecommand{\LyX}{L\kern-.1667em\lower.25em\hbox{Y}\kern-.125emX\@}
15
16%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Textclass specific LaTeX commands.
17 \newenvironment{lyxcode}
18 {\begin{list}{}{
19 \setlength{\rightmargin}{\leftmargin}
20 \raggedright
21 \setlength{\itemsep}{0pt}
22 \setlength{\parsep}{0pt}
23 \normalfont\ttfamily}%
24 \item[]}
25 {\end{list}}
26
27\makeatother
28\begin{document}
29
30\vfill{}
31\title{Installing and Running mod\_log\_sql}
32\vfill{}
33
34
35\author{Christopher Powell }
36
37
38\date{11/13/02}
39
40\maketitle
41\tableofcontents{}
42\newpage
43
44
45\section{Introduction}
46
47
48\subsection{Homepage }
49
50\begin{lyxcode}
51http://www.grubbybaby.com/mod\_log\_sql/
52\end{lyxcode}
53
54\subsection{Summary}
55
56This Apache module will permit you to log Apache accesses (and a lot
57of related information) to a SQL database. Unlike logging to a flat
58text file (which is standard in Apache), there are a lot of powerful
59advantages to logging to SQL. This module can either replace or happily
60coexist with Apache's text file logging facility.
61
62
63\subsection{Approach}
64
65This project was formerly known as mod\_log\_mysql. It has been renamed
66to mod\_log\_sql in order to reflect the project goal of database-inspecificity.
67The module currently supports MySQL, and development for other database
68backends is underway.
69
70In order to save speed and overhead, links are kept alive in between
71queries. This module uses one SQL link per httpd child. Among other
72things, this means that this module supports logging into only one
73MySQL server, and for now, also, only one SQL database. But that's
74a small tradeoff compared to the blinding speed of this module.
75
76Virtual hosts are supported in the same manner they are in the regular
77logging modules. You define some basic 'global' directives in the
78main server config, then you define more specific 'local' directives
79inside each virtualhost stanza.
80
81SQL links are opened by each child process when it is born. Error
82reporting is robust throughout and will let you know about database
83issues in the standard Apache error-log for the server or virtual
84server.
85
86A robust \char`\"{}preserve\char`\"{} capability has now been implemented.
87This permits the module to preserve any failed INSERT commands to
88a local file on its machine. In any situation that the database is
89unavailable -- e.g. the network fails, you reboot the db host, etc.
90-- mod\_log\_sql will note this in the error log and begin appending
91its log entries to the preserve file (which is created with the user
92\& group ID of the running Apache process, e.g. \char`\"{}nobody\char`\"{}
93on many Linux installations). When your database becomes available
94again, mod\_log\_sql seamlessly resumes logging to it. When convenient
95for the sysadmin, he/she can easily import the preserve file into
96the database because it is simply a series of SQL insert statements:
97
98\begin{lyxcode}
99\#~mysql~-uadminuser~-p~mydbname~<~/tmp/mysql-preserve
100\end{lyxcode}
101
102\subsection{Supported directives}
103
104Please see the web-based documentation for full explanation of all
105supported run-time directives.
106
107http://www.grubbybaby.com/mod\_log\_sql/directives.html
108
109See the FAQ for some handy examples:
110
111http://www.grubbybaby.com/mod\_log\_sql/faq.html
112
113
114\subsection{What gets logged by default? }
115
116All the data that would be contained in the \char`\"{}Combined Log
117Format\char`\"{} is logged by default, plus a little extra. Your best
118bet is to begin by accepting this default, then later customize the
119log configuration based on your needs.
120
121The online documentation of the run-time directives includes a full
122explanation of what you can log, including examples.
123
124
125\subsection{Minor Notes}
126
127\begin{itemize}
128\item Note which directives go in the 'main server config' and which directives
129apply to the 'virtual host config'. This is made clear in the directive
130documentation.
131\item The 'time\_stamp' field is stored in an UNSIGNED INTEGER column, in
132the standard unix \char`\"{}seconds since 1/1/1970 12:00:00\char`\"{}
133format. This is superior to storing the access time as a string due
134to size requirements: an UNSIGNED INT type fits in 4 bytes, whereas
135the Apache date string (e.g. \char`\"{}18/Nov/2001:13:59:52 -0800\char`\"{})
136requires 26 bytes -- significantly larger, and those extra 22 bytes
137will add up over the thousands of accesses that a busy server will
138experience. Besides, an INT type is far more flexible for comparisons,
139etc.
140
141
142In MySQL 3.21 and above you can easily convert this to a human readable
143format using from\_unixtime(), e.g.:
144
145\begin{lyxcode}
146select~remote\_host,request\_uri,from\_unixtime(time\_stamp)~from~access\_log;
147\end{lyxcode}
148The enclosed perl program ''make\_combined\_log.pl'' shows how you
149can extract your access records in a format that is completely Combined
150Log Format compliant. You can then feed this to your favorite web
151log analysis tool.
152
153\item The table's string values can be CHAR or VARCHAR, at a length of your
154choice. VARCHAR is superior because it truncates long strings; CHAR
155types are fixed-length and will be padded with spaces. Just like the
156time\_stamp described above, that kind of space waste will add up
157over thousands of records.
158\item Be careful not to go overboard setting fields to NOT NULL. If a field
159is marked NOT NULL then it must contain data in the INSERT or the
160INSERT will fail, sometimes in a really mysterious way to the frustrated
161sysad.
162\item Apache normally logs numeric fields with a '-' character to mean \char`\"{}not
163applicable,\char`\"{} e.g. bytes\_sent on a request with a 304 response
164code. Since '-' is an illegal character in an SQL numeric field, such
165fields are assigned the value 0 instead of '-' which, of course, makes
166perfect sense anyway.
167\end{itemize}
168
169\subsection{Author / Maintainer}
170
171The actual logging code was taken from the already existing flat file
172text modules, so all that credit goes to the Apache Server group.
173
174The MySQL routines and directives were added by Zeev Suraski <bourbon@netvision.net.il>.
175
176All changes from 1.06+ and the new documentation were added by Chris
177Powell <chris@grubbybaby.com>. It seems that the module had fallen
178into the \char`\"{}unmaintained\char`\"{} category -- it hadn't been
179updated since 1998 -- so Chris adopted it as the new maintainer.
180
181
182\section{Installation}
183
184
185\subsection{Requirements}
186
187\begin{itemize}
188\item A compatible system. I have run mod\_log\_sql on Red Hat based systems
189(Red Hat, Mandrake). These instructions should easily adapt to any
190modern distro.
191\item Apache 1.2 or 1.3 installed. (I run 1.3.22 and it works fine). You
192should have already successfully compiled Apache and know what you're
193doing there.
194\item The MySQL development headers. This is called different things on
195different distros. For example, Red Hat 6.x called this RPM \char`\"{}MySQL-devel\char`\"{}
196whereas Mandrake calls it \char`\"{}libmysql10-devel\char`\"{}.
197\item MySQL >= 3.23.15 configured, installed and running on either localhost
198or an accessible networked machine. You should already have a basic
199understanding of MySQL and how it functions.
200\item Again, basic administrative skills with Apache and MySQL. I try to
201make things as easy as possible in this file, but its purpose is not
202to be an administrative tutorial.
203\item Additionally, if you want to be able to log SSL information such as
204keysize or cipher, you need OpenSSL and glibc-devel installed. Both
205are available as RPMs.
206\end{itemize}
207
208\subsection{Do I want a DSO?}
209
210You need to know the answer to this question before you proceed. The
211answer is pretty straightforward: what have you done in the past?
212If you like all your Apache modules to be dynamic, then you should
213keep doing that. If you're more of an old-school type and prefer to
214compile the modules right into apache, do that. Both methods work
215equally well.
216
217FWIW, the DSO method is more modern and increasing in popularity because
218apxs takes care of a lot of dirty little details for you. As you'll
219see below, the static-module method is a little more complex.
220
221
222\subsection{Installation as an Apache DSO (Preferred) }
223
224\begin{enumerate}
225\item Perform all the following steps as root so that you have install privs,
226etc. Unpack the archive into a working directory.
227
228\begin{lyxcode}
229\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql
230\end{lyxcode}
231\item Edit Makefile for your system.\\
232
233
234
235NECESSARY:
236
237\begin{itemize}
238\item The location where you installed Apache -- usually /usr/local/apache,
239'locate apxs' can help you find it.
240\item The location of your MySQL libraries, find using 'locate libmysqlclient'
241\item The location of your MySQL header files, find using 'locate mysql.h'
242\end{itemize}
243OPTIONAL if you have included mod\_ssl in Apache and want to log SSL
244data such as keysize and cipher type:
245
246\begin{itemize}
247\item The location of your SSL header files, find using 'locate mod\_ssl.h'
248\end{itemize}
249Now that you know these things, edit Makefile and replace the stock
250values with your own.
251
252IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS
253by putting a \# character in front of it:
254
255\begin{lyxcode}
256\#MODSSLHDRS=/usr/include/...
257\end{lyxcode}
258\item Instruct apxs to compile the module as a DSO.
259
260\begin{lyxcode}
261\#~make~dso
262\end{lyxcode}
263\item You should see output similar to the following:
264
265\begin{lyxcode}
266/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~
267
268gcc~-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~
269
270gcc~-shared~-o~mod\_log\_sql.so~mod\_log\_sql.o~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-L/usr/lib~-lmysqlclient~-lz~-lm~-lcrypt~-ldb
271\end{lyxcode}
272You should see no errors and have a file called \char`\"{}mod\_log\_sql.so\char`\"{}
273in your directory.
274
275\item Instruct apxs to install the DSO.
276
277\begin{lyxcode}
278\#~make~dsoinstall
279\end{lyxcode}
280You should see output similar to the following:
281
282\begin{lyxcode}
283/usr/local/Apache/bin/apxs~-i~mod\_log\_sql.so
284
285cp~mod\_log\_sql.so~/usr/local/Apache/libexec/mod\_log\_sql.so
286
287chmod~755~/usr/local/Apache/libexec/mod\_log\_sql.so
288\end{lyxcode}
289\item Module ordering within httpd.conf is important. If you are logging
290SSL, you must make sure that
291
292\begin{lyxcode}
293LoadModule~ssl\_module~libexec/libssl.so
294\end{lyxcode}
295comes before
296
297\begin{lyxcode}
298LoadModule~sql\_log\_module~libexec/mod\_log\_sql.so
299\end{lyxcode}
300If you don't, you will get this error when you start Apache:
301
302\begin{lyxcode}
303/usr/local/apache/libexec/mod\_log\_mysql.so:~undefined~symbol:~ssl\_var\_lookup
304
305/usr/local/apache/bin/apachectl~startssl:~httpd~could~not~be~started
306\end{lyxcode}
307(Because mod\_log\_sql doesn't yet have the required symbols that
308mod\_ssl provides.)
309
310Now skip below to the \char`\"{}Configuration\char`\"{} section.
311
312\end{enumerate}
313
314\subsection{Installation as a static module compiled into httpd}
315
316\begin{enumerate}
317\item Perform all the following steps as root so that you have install privs,
318etc.
319\item Unpack the archive into a working directory.
320
321\begin{lyxcode}
322\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql
323\end{lyxcode}
324\item \label{step:editMF}Edit Makefile for your system.\\
325
326
327
328NECESSARY:
329
330\begin{itemize}
331\item The location where you installed Apache -- usually /usr/local/apache,
332'locate apxs' can help you find it.
333\item The location of your Apache {*}sources{*}, find using 'locate ABOUT\_APACHE'
334\item The location of your MySQL header files, find using 'locate mysql.h'
335\item The location of your MySQL libraries, find using 'locate libmysqlclient'
336\end{itemize}
337OPTIONAL if you have included mod\_ssl in Apache and want to log SSL
338data such as keysize and cipher type:
339
340\begin{itemize}
341\item The location of your mod\_ssl header files, find using 'locate mod\_ssl.h'
342\item The location of your OpenSSL header files, find using 'locate x509.h'
343\item The location of your db1 header files, find using 'locate mpool.h'
344\end{itemize}
345Now that you know these things, edit Makefile and replace the stock
346values with your own.
347
348IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS,
349OPNSSLHDRS and DB1HDRS by putting a \# character in front of each
350one, e.g. \#OPNSSLHDRS=/usr/include/...
351
352\item \# make static
353\item \# make statinstall
354\item Change to your Apache source dir.
355
356\begin{lyxcode}
357\#~cd~/usr/local/src/apache-1.3.22/src
358\end{lyxcode}
359\item Re-make your httpd binary as follows.
360
361\begin{enumerate}
362\item Edit Configuration.apaci as follows...
363
364\begin{itemize}
365\item Append the following string to the EXTRA\_LIBS= line. (\char`\"{}/usr/lib/mysql\char`\"{}
366is from step \ref{step:editMF}, where your MySQL libraries live):
367\end{itemize}
368\begin{lyxcode}
369-L/usr/lib/mysql~-lmysqlclient~-lm~-lz
370\end{lyxcode}
371\begin{itemize}
372\item Find the mod\_log\_config.o line, and add this line immediately after
373it:
374\end{itemize}
375\begin{lyxcode}
376AddModule~modules/sql/mod\_log\_sql.o
377\end{lyxcode}
378\item \# cp Configuration.apaci Configuration
379\item \# ./Configure
380\item \# make
381\item \# strip httpd
382\end{enumerate}
383\item Test your new apache binary:
384
385\begin{lyxcode}
386\#~./httpd~-l
387\end{lyxcode}
388\item You should see something like:
389
390\begin{lyxcode}
391Compiled-in~modules:~
392
393http\_core.c
394
395mod\_log\_sql.c~<-{}-~That's~the~line~you're~looking~for.
396
397mod\_env.c~
398
399mod\_log\_config.c~
400
401mod\_mime.c~
402
403mod\_negotiation.c~...
404
405etc...
406\end{lyxcode}
407\item Install your httpd binary. Copy it over your old httpd binary, wherever
408it lives. You can and should rename your old httpd first so that you
409can easily revert to that working version in case of bugs with the
410new version.
411
412\begin{lyxcode}
413\#~/etc/rc.d/init.d/httpd~stop~
414
415\#~mv~/usr/local/Apache/bin/httpd~\textasciitilde{}/httpd-save~
416
417\#~cp~-f~./httpd~/usr/local/Apache/bin/
418\end{lyxcode}
419\end{enumerate}
420
421\section{Configuration}
422
423
424\subsection{Preparing MySQL}
425
426You have to prepare the database to receive data from mod\_log\_sql,
427and set up run-time directives in httpd.conf to control how and what
428mod\_log\_sql logs.
429
430This section will discuss how to get started with a basic config.
431Full documentation of the run-time directives is available here: http://www.grubbybaby.com/mod\_log\_sql/directives.html
432
433\begin{enumerate}
434\item mod\_log\_sql can make its own tables on-the-fly, or you can pre-make
435the tables by hand. The advantage of letting the module make the tables
436is ease-of-use, but for raw performance you will want to pre-make
437the tables in order to save overhead. In this basic setup we'll let
438the module create tables for us.
439\item We still need to have a logging database created and ready, so run
440the MySQL command line client and create a database:
441
442\begin{lyxcode}
443\#~mysql~-uadmin~-pmypassword~mysql>~create~database~apachelogs;
444\end{lyxcode}
445\item If you want to hand-create the tables, run the enclosed 'create-tables'
446SQL script as follows:
447
448\begin{lyxcode}
449mysql>~source~create\_tables.sql
450\end{lyxcode}
451\item Create a specific MySQL userid that httpd will use to authenticate
452and enter data. This userid need not be an actual Unix user. It is
453a userid internal to MySQL with specific privileges. In the following
454example command, \char`\"{}apachelogs\char`\"{} is the database, \char`\"{}loguser\char`\"{}
455is the userid to create, \char`\"{}my.apachemachine.com\char`\"{}
456is the name of the Apache machine, and \char`\"{}l0gger\char`\"{}
457is the password to assign. Choose values that are different from these
458examples.
459
460\begin{lyxcode}
461mysql>~grant~insert,create~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger';
462\end{lyxcode}
463\item You may be especially security-paranoid and not want \char`\"{}loguser\char`\"{}
464to have \char`\"{}create\char`\"{} capability within the \char`\"{}apachelogs\char`\"{}
465databse. You can disable that but the cost is that you cannot use
466the module's automatic-table-creation feature. If that's an acceptable
467cost, hand-create the tables as described in step 1 and use the following
468GRANT statement instead of the one above:
469
470\begin{lyxcode}
471mysql>~grant~insert~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger';
472\end{lyxcode}
473\item Enable full logging of your MySQL daemon (at least temporarily for
474debugging purposes) if you don't do this already. Edit /etc/my.cnf
475and add the following line to your {[}mysqld{]} section:
476
477\begin{lyxcode}
478log=/var/log/mysql-messages
479\end{lyxcode}
480Then restart MySQL.
481
482\begin{lyxcode}
483\#~/etc/rc.d/init.d/mysql~restart
484\end{lyxcode}
485\end{enumerate}
486
487\subsection{Preparing Apache}
488
489\begin{enumerate}
490\item Tell the module what database to use and the appropriate authentication
491information.
492
493\begin{description}
494\item [EXAMPLE:]Use the MySQL database called \char`\"{}apachelogs\char`\"{}
495running on \char`\"{}dbmachine.foo.com\char`\"{}. The module uses
496username \char`\"{}loguser\char`\"{} and password \char`\"{}l0gger\char`\"{}
497to authenticate to the database. The log entries will be INSERTed
498into the table called \char`\"{}access\_log\char`\"{}.
499\end{description}
500So, edit httpd.conf and insert the following lines somewhere AFTER
501any LoadModule / AddModule statements. Make sure these statements
502are \char`\"{}global,\char`\"{} i.e. not inside any VirtualHost stanza.
503
504\begin{lyxcode}
505LogSQLDatabase~apachelogs
506
507LogSQLLoginInfo~dbmachine.foo.com~loguser~l0gger~
508
509LogSQLCreateTables~on
510\end{lyxcode}
511\item If your database resides on localhost instead of another host, specify
512the MySQL server's socket file as follows:
513
514\begin{lyxcode}
515LogSQLSocketFile~/your/path/to/mysql.sock
516\end{lyxcode}
517\item The actual logging is set up on a virtual-host-by-host basis. So,
518skip down to the virtual host you want to set up. Instruct this virtual
519host to log entries to the table ''access\_log'' by inserting a
520LogSQLTransferLogTable directive. (The LogSQLTransferLogTable directive
521is the minimum required to log -- other directives simply tune the
522module's behavior.)
523
524\begin{lyxcode}
525<VirtualHost~1.2.3.4>
526
527{[}snip{]}
528
529LogSQLTransferLogTable~access\_log
530
531{[}snip{]}
532
533</VirtualHost>
534\end{lyxcode}
535\item Restart apache.
536
537\begin{lyxcode}
538\#~/etc/rc.d/init.d/httpd~start
539\end{lyxcode}
540\end{enumerate}
541
542\subsection{Testing the module}
543
544\begin{enumerate}
545\item Load your web site in a browser to trigger some hits, then confirm
546that the entries are being successfully logged:
547
548\begin{lyxcode}
549\#~mysql~-hmysql.host.com~-umysqladmin~-p~-e~\char`\"{}select~{*}~from~access\_log\char`\"{}~apachelogs~
550
551Enter~password:
552
553(Several~lines~of~output~should~follow,~corresponding~to~your~hits~on~the~site.)
554\end{lyxcode}
555You now have basic functionality. Don't disable your regular Apache
556logs until you feel comfortable that the database is behaving as you'd
557like and that things are going well.
558
559\item If you do not see any entries in the access\_log, then something is
560preventing the inserts from happening. This problem could be caused
561by several things:
562
563\begin{itemize}
564\item Improper privileges set up in the MySQL database
565\item You aren't hitting a VirtualHost that has a LogSQLTransferLogTable
566entry
567\item You didn't specify the right host
568\end{itemize}
569If you have confirmed your LogSQL{*} directives and know them to be
570correct, you should examine the httpd server logs for mod\_log\_sql
571messages; the module will offer hints as to why it cannot connect,
572etc. Also examine the MySQL log that you established in step 3. Ensure
573that the INSERTs are not being rejected because of a malformed table
574entry or other clerical error. If you see no INSERT attempts in the
575log, the module isn't successfully connecting to the database.
576
577The next thing to do is recompile the module with debugging output
578activated. change the \char`\"{}\#undef DEBUG\char`\"{} on line 8
579of mod\_log\_sql.c to \char`\"{}\#define DEBUG\char`\"{} and recompile/reinstall.
580The module will now output copious notes about what it is doing, and
581this will help you (and the maintainer) solve the problem.
582
583\end{enumerate}
584You can now activate the advanced features of mod\_log\_sql. These
585are all described in the next section.
586
587
588\subsection{Configuration directive reference}
589
590
591\subsubsection{LogSQLLoginInfo }
592
593\begin{lyxcode}
594\textbf{MANDATORY}~
595
596Syntax:~LogSQLLoginInfo~host~user~password
597
598Example:~LogSQLLoginInfo~foobar.baz.com~logwriter~passw0rd~
599
600Context:~server~config
601\end{lyxcode}
602\begin{quote}
603Defines the general parameters of the MySQL host to which you will
604be logging. host is the hostname or IP address of the MySQL machine.
605user is the MySQL userid (not a Unix userid!) with INSERT privileges
606on the table defined in LogSQLTransferLogTable. password is that user's
607password. This is defined only once in the httpd.conf file.
608\end{quote}
609
610\subsubsection{LogSQLDatabase }
611
612\begin{lyxcode}
613\textbf{MANDATORY}
614
615Syntax:~LogSQLDatabase~database~
616
617Example:~LogSQLDatabase~loggingdb~
618
619Context:~server~config
620\end{lyxcode}
621\begin{quote}
622Defines the database that is used for logging. database must be operating
623on the MySQL host defined in LogSQLLoginInfo. This is defined only
624once in the httpd.conf file.
625\end{quote}
626
627\subsubsection{LogSQLTransferLogTable}
628
629\begin{lyxcode}
630\textbf{MANDATORY~}
631
632Syntax:~LogSQLTransferLogTable~table-name~
633
634Example:~LogSQLTransferLogTable~access\_log\_table~
635
636Context:~virtual~host
637\end{lyxcode}
638\begin{quote}
639Defines which table is used for logging of Apache's transfers; this
640is analogous to Apache's TransferLog directive. table-name must be
641a valid table within the database defined in LogSQLDatabase.
642
643This directive is not necessary if you declare LogSQLMassVirtualHosting
644On, since that directive activates dynamically-named tables. If you
645attempt to use LogSQLTransferLogTable at the same time a warning will
646be logged and it will be ignored, since LogSQLMassVirtualHosting takes
647priority.
648\end{quote}
649
650\subsubsection{LogSQLRequestIgnore}
651
652\begin{lyxcode}
653Syntax:~LogSQLRequestIgnore~req1~req2~req3~...~reqN~
654
655Example:~LogSQLRequestIgnore~root.exe~cmd.exe~default.ida~favicon.ico~
656
657Context:~virtual~host
658\end{lyxcode}
659\begin{quote}
660Lists a series of strings that, if present in the URI, will cause
661that request NOT to be logged. This directive is useful for cutting
662down on log clutter when you KNOW that you do not want to log requests
663for certain objects. (The example above is a genuinely useful one;
664it will prevent logging of many common Microsoft-based worm intrusion
665attempts, as well as those ridiculous requests for the favicon.)
666
667Each string is separated by a space, and NO regular expressions or
668globbing are allowed. Each string is evaluated as a substring of the
669URI using strstr().
670\end{quote}
671
672\subsubsection{LogSQLRemhostIgnore}
673
674\begin{lyxcode}
675Syntax:~LogSQLRemhostIgnore~host1~host2~host3~...~hostN~
676
677Example:~LogSQLRemhostIgnore~localnet.com~
678
679Context:~virtual~host
680\end{lyxcode}
681\begin{quote}
682Lists a series of strings that, if present in the REMOTE\_HOST, will
683cause that request NOT to be logged. This directive is useful for
684cutting down on log clutter when you KNOW that you do not want to
685log requests from certain hosts, such as your own internal network
686machines.
687
688Each string is separated by a space, and NO regular expressions or
689globbing are allowed. Each string is evaluated as a substring of the
690REMOTE\_HOST using strstr().
691\end{quote}
692
693\subsubsection{LogSQLRefererIgnore}
694
695\begin{lyxcode}
696Syntax:~LogSQLRefererIgnore~ref1~ref2~ref3~...~refN~
697
698Example:~LogSQLRefererIgnore~google.com~
699
700Context:~virtual~host
701\end{lyxcode}
702\begin{quote}
703Lists a series of strings that, if present in the HTTP\_REFERER, will
704cause that request NOT to be logged. This directive is useful for
705cutting down on log clutter when you know that you do not want to
706log requests from certain referers.
707
708Each string is separated by a space, and NO regular expressions or
709globbing are allowed. Each string is evaluated as a substring of the
710HTTP\_REFERER using strstr().
711\end{quote}
712
713\subsubsection{LogSQLWhichCookie}
714
715\begin{lyxcode}
716Syntax:~LogSQLWhichCookie~cookiename~
717
718Example:~LogSQLWhichCookie~Clicks
719
720Default:~None.~You~must~set~this~if~you~wish~to~capture~cookies.
721
722Context:~virtual~host
723\end{lyxcode}
724\begin{quote}
725In HTTP, cookies have names to distinguish them from each other. Using
726mod\_usertrack, for example, you can give your user-tracking cookies
727a name with the CookieName directive.
728
729If you have activated cookie logging in LogSQLTransferLogFormat, then
730LogSQLWhichCookie tells mod\_log\_sql which cookie to log. This is
731useful because many times you will be setting and receiving more than
732one cookie from a client; without this directive you'd be unable to
733choose which cookie is your mod\_usertrack cookie.
734
735Note: although this was intended for people who are using mod\_usertrack
736to set user-tracking cookies, you aren't restricted in any way. You
737can choose which cookie you wish to log to the database, and it doesn't
738necessarily have to have anything to do with mod\_usertrack.
739\end{quote}
740
741\subsubsection{LogSQLCreateTables}
742
743\begin{lyxcode}
744Syntax:~LogSQLCreateTables~flag
745
746Example:~LogSQLCreateTables~On~
747
748Default:~Off~
749
750Context:~server~config
751\end{lyxcode}
752\begin{quote}
753mod\_log\_sql now has the ability to create its tables on-the-fly.
754The advantage to this is convenience: you don't have to execute any
755SQL by hand to prepare the table. This is especially helpful for people
756with lots of virtual hosts (who should also see the LogSQLMassVirtualHosting
757directive).
758
759There is a slight disadvantage: if you wish to activate this feature,
760then the user specified by LogSQLLoginInfo must have CREATE privileges
761on the database. In an absolutely paranoid, locked-down situation
762you may only want to grant your mod\_log\_sql user INSERT privileges
763on the database; in that situation you are unable to take advantage
764of LogSQLCreateTables. But most people -- even the very security-conscious
765-- will find that granting CREATE on the logging database is reasonable.
766
767This is defined only once in the httpd.conf file.
768\end{quote}
769
770\subsubsection{LogSQLMassVirtualHosting}
771
772\begin{lyxcode}
773Syntax:~LogSQLMassVirtualHosting~flag~
774
775Example:~LogSQLMassVirtualHosting~On~
776
777Default:~Off~
778
779Context:~server~config
780\end{lyxcode}
781\begin{quote}
782If you administer a site hosting many, many virtual hosts then this
783option will appeal to you. If you activate LogSQLMassVirtualHosting
784then several things happen:
785\begin{itemize}
786\item the on-the-fly table creation feature is activated automatically
787\item the transfer log table name is dynamically set from the virtual host's
788name (example: a virtual host www.grubbybaby.com gets logged to table
789access\_www\_grubbybaby\_com)
790\item which, in turn, means that each virtual host logs to its own segregated
791table. Because there is no data shared between virtual servers you
792can grant your users access to the tables they need; they will be
793unable to view others' data.
794\end{itemize}
795This is a huge boost in convenience for sites with many virtual servers.
796Activating LogSQLMassVirtualHosting obviates the need to create every
797virtual server's table and provides more granular security possibilities.
798
799This is defined only once in the httpd.conf file.
800\end{quote}
801
802\subsubsection{LogSQLPreserveFile}
803
804\begin{lyxcode}
805Syntax:~LogSQLPreserveFile~filename~
806
807Example:~LogSQLPreserveFile~offline-preserve~
808
809Default:~mysql-preserve~(in~/tmp)~
810
811Context:~virtual~host
812\end{lyxcode}
813\begin{quote}
814mod\_log\_sql writes queries to this local preserve file in the event
815that it cannot reach the database, and thus ensures that your high-availability
816web frontend does not lose logs during a temporary database outage.
817This could happen for a number of reasons: the database goes offline,
818the network breaks, etc. You will not lose entries since the module
819has this backup. The file consists of a series of SQL statements that
820can be imported into your database at your convenience; furthermore,
821because the SQL queries contain the access timestamps you do not need
822to worry about out-of-order data after the import.
823
824For security purposes the name you supply will be prepended with \char`\"{}/tmp/\char`\"{}
825to force creation of the file in /tmp.
826
827If you do not define LogSQLPreserveFile then all virtual servers will
828log to the same default preserve file (/tmp/mysql-preserve). You can
829redefine this on a virtual-host basis in order to segregate your preserve
830files if you desire. Note that segregation is not really necessary,
831as the SQL statements that are written to the preserve file already
832distinguish between different virtual hosts.
833
834The module will log to error-log if/when it notices a database outage,
835and upon database return. You will therefore know when the preserve
836file is being used, although it is your responsibility to import the
837file.
838
839The file does not need to be created in advance. It is safe to remove
840or rename the file without interrupting Apache, as the module closes
841the filehandle immediately after completing the write. The file is
842created with the user \& group ID of the running Apache process (e.g.
843'nobody' on many Linux distributions).
844\end{quote}
845
846\subsubsection{LogSQLSocketFile }
847
848\begin{lyxcode}
849Syntax:~LogSQLSocketFile~filename~
850
851Example:~LogSQLSocketFile~/tmp/mysql.sock~
852
853Default:~/var/lib/mysql/mysql.sock~
854
855Context:~server~config
856\end{lyxcode}
857\begin{quote}
858At Apache runtime you can specify the MySQL socket file to use. Set
859this once in your main server config to override the default value.
860This value is irrelevant if your database resides on a separate machine.
861
862mod\_log\_sql will automatically employ the socket for db communications
863if the database resides on the local host. If the db resides on a
864separate host the module will automatically use TCP/IP. This is a
865function of the MySQL API and is not user-configurable.
866\end{quote}
867
868\subsubsection{LogSQLTransferLogFormat }
869
870\begin{lyxcode}
871Syntax:~LogSQLTransferLogFormat~format-string~
872
873Example:~LogSQLTransferLogFormat~huSUTv~
874
875Default:~AbHhmRSsTUuv~
876
877Context:~virtual~host
878\end{lyxcode}
879\begin{quote}
880Each character in the format-string defines an attribute of the request
881that you wish to log. The default logs the information required to
882create Combined Log Format logs, plus several extras. Here is the
883full list of allowable keys, which sometimes resemble their Apache
884counterparts, but do not always:
885
886\begin{tabular}{|c|l|l|l|l|}
887\hline
888\textbf{Character}&
889\textbf{What is this?}&
890\textbf{MySQL DB field}&
891\textbf{Recommended column type}&
892\textbf{Example}\\
893\hline
894\hline
895A&
896User agent&
897agent&
898varchar(255)&
899Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0)\\
900\hline
901b&
902Bytes transfered&
903bytes\_sent&
904int unsigned&
90532561\\
906\hline
907c&
908Text of cookie&
909cookie&
910varchar(255)&
911Apache=sdyn.fooonline.net.130051007102700823\\
912\hline
913f&
914&
915request\_file&
916varchar(255)&
917\\
918\hline
919H&
920HTTP request protocol&
921request\_protocol&
922varchar(10)&
923HTTP/1.1\\
924\hline
925h&
926Name of remote host&
927remote\_host&
928varchar(50)&
929blah.foobar.com\\
930\hline
931I&
932Ident user info&
933remote\_logname&
934varchar(50)&
935bobby\\
936\hline
937m&
938HTTP request method&
939request\_method&
940varchar(6)&
941GET\\
942\hline
943P&
944httpd child PID&
945child\_pid&
946smallint unsigned&
9473215\\
948\hline
949p&
950httpd port&
951server\_port&
952smallint unsigned&
95380\\
954\hline
955R&
956Referer&
957referer&
958varchar(255)&
959http://www.biglinkstoyou.com/linkpage.html\\
960\hline
961r&
962Request in full form&
963request\_line&
964varchar(255)&
965GET /books-cycroad.html HTTP/1.1\\
966\hline
967S&
968Time of request in UNIX format&
969time\_stamp&
970int unsigned&
9711005598029\\
972\hline
973s&
974HTTP status of request&
975status&
976smallint unsigned&
977404\\
978\hline
979T&
980Seconds to service request&
981request\_duration&
982smallint unsigned&
9832\\
984\hline
985t&
986Time of request in human format&
987request\_time&
988char(28)&
989{[}02/Dec/2001:15:01:26 -0800{]}\\
990\hline
991U&
992Request in simple form&
993request\_uri&
994varchar(255)&
995/books-cycroad.html\\
996\hline
997u&
998User info from HTTP authentication&
999remote\_user&
1000varchar(50)&
1001bobby\\
1002\hline
1003v&
1004Virtual host servicing the request&
1005virtual\_host&
1006varchar(50)&
1007www.foobar.com\\
1008\hline
1009\end{tabular}
1010
1011If you have compiled mod\_log\_sql with WANT\_SSL\_LOGGING, you also
1012get these:
1013
1014\begin{tabular}{|c|l|l|l|}
1015\hline
1016\textbf{Character}&
1017\textbf{MySQL DB field it activates}&
1018\textbf{MySQL DB field it activatesRecommended column type}&
1019\textbf{Example}\\
1020\hline
1021\hline
1022z&
1023ssl\_cipher&
1024varchar(25)&
1025RC4-MD5\\
1026\hline
1027q&
1028ssl\_keysize&
1029smallint unsigned&
103056\\
1031\hline
1032Q&
1033ssl\_maxkeysize&
1034smallint unsigned&
1035128\\
1036\hline
1037\end{tabular}
1038\end{quote}
1039
1040\section{FAQ}
1041
1042
1043\subsection{Why log to an SQL database?}
1044
1045To begin with, let's get it out of the way: logging to a database
1046is not a panacea. But while there are complexities with this solution,
1047the benefit can be substantial for certain classes of administrator
1048or people with advanced requirements:
1049
1050\begin{itemize}
1051\item Chores like log rotation go away, as you can DELETE records from the
1052SQL database once they are no longer useful. For example, the excellent
1053and popular log-analysis tool Webalizer (http://www.webalizer.com/)
1054does not need historic logs after it has processed them, enabling
1055you to delete older logs.
1056\item People with clusters of web servers (for high availability) will benefit
1057the most -- all their webservers can log to a single SQL database.
1058This obviates the need to collate/interleave the many separate logfiles,
1059which can be / highly/ problematic.
1060\item People acquainted with the power of SQL SELECT statements will know
1061the flexibility of the extraction possibilities at their fingertips.
1062\end{itemize}
1063For example, do you want to see all your 404's? Do this:
1064
1065\begin{lyxcode}
1066select~remote\_host,status,request\_uri,bytes\_sent,from\_unixtime(time\_stamp)~from~acc\_log\_tbl~where~status=404~order~by~time\_stamp;
1067
1068+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-
1069
1070+~~~~~~~~~~~~~~~~~~~~~~~remote\_host~|~status~|~request\_uri~~~~~~~~~~~~~~|~bytes\_sent~|~from\_unixtime(time\_stamp)|
1071
1072+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-
1073
1074|~marge.mmm.co.uk~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~02:30:56~~~~~~|
1075
1076|~62.180.239.251~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~02:45:25~~~~~~|
1077
1078|~212.234.12.66~~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~03:01:00~~~~~~|~
1079
1080|~212.210.78.254~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~03:26:05~~~~~~|~
1081
1082+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1083\end{lyxcode}
1084Or do you want to see how many bytes you've sent within a certain
1085directory or site? Do this:
1086
1087\begin{lyxcode}
1088select~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;~
1089
1090+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~
1091
1092|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~|~bytes~~~|~howmany~|~
1093
1094+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~
1095
1096|~/mod\_log\_sql/style\_1.css~~~~~~~~~~|~157396~~|~~~~1288~|~
1097
1098|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~|~2514337~|~~~~~801~|~
1099
1100|~/mod\_log\_sql/mod\_log\_sql.tar.gz~~~|~9769312~|~~~~~456~|~
1101
1102|~/mod\_log\_sql/faq.html~~~~~~~~~~~~~|~5038728~|~~~~~436~|~
1103
1104|~/mod\_log\_sql/INSTALL~~~~~~~~~~~~~~|~1196161~|~~~~~202~|~
1105
1106|~/mod\_log\_sql/directives.html~~~~~~|~1096821~|~~~~~171~|~
1107
1108|~/mod\_log\_sql/CHANGELOG~~~~~~~~~~~~|~424481~~|~~~~~107~|~
1109
1110|~/mod\_log\_sql/README~~~~~~~~~~~~~~~|~796072~~|~~~~~~97~|~
1111
1112|~/mod\_log\_sql/directives-old.html~~|~42480~~~|~~~~~~~6~|
1113
1114|~/mod\_log\_sql/index.html~~~~~~~~~~~|~9531~~~~|~~~~~~~3~|~
1115
1116+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+
1117\end{lyxcode}
1118Or maybe you want to see who's linking to you? Do this:
1119
1120\begin{lyxcode}
1121select~count(referer)~as~num,referer~from~acc\_log\_tbl~where~request\_uri='/mod\_log\_sql/'~group~by~referer~order~by~num~desc;
1122
1123+-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1124
1125|~num~|~referer~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1126
1127+-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1128
1129|~271~|~http://freshmeat.net/projects/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1130
1131|~96~~|~http://modules.apache.org/search?id=339~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1132
1133|~48~~|~http://freshmeat.net/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1134
1135|~8~~~|~http://freshmeat.net~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
1136
1137|~7~~~|~http://freshmeat.net/daily/2001/11/30/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1138
1139|~6~~~|~http://freshmeat.net/daily/2001/11/20/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
1140
1141+-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1142\end{lyxcode}
1143As you can see, there are myriad possibilities that can be constructed
1144with the wonderful SQL SELECT statement. Logging to an SQL database
1145can be really quite useful!
1146
1147
1148\subsection{Why use MySQL? Are there alternatives?}
1149
1150MySQL is a robust, free, and very powerful production-quality database
1151engine. It is well supported and comes with detailed documentation.
1152Many 3rd-party software pacakges (e.g. Slashcode, the engine that
1153powers Slashdot) run exclusively with MySQL. In other words, you will
1154belong to a very robust and well-supported community by choosing MySQL.
1155
1156That being said, there are alternatives. PostgreSQL is probably MySQL's
1157leading \char`\"{}competitor\char`\"{} in the free database world.
1158There is also an excellent module available for Apache to permit logging
1159to a PostgreSQL database, called pgLOGd \url{(http://www.digitalstratum.com/pglogd/)}.
1160
1161
1162\subsection{Is this code production-ready?}
1163
1164By all accounts it is. It is known to work without a problem on many-thousands-of-hits-per-day
1165webservers. Does that mean it is 100\% bug free? Well, no software
1166is. But it is well-tested and /believed/ to be fully compatible with
1167production environments. (The usual disclaimers apply. This software
1168is provided without warranty of any kind.)
1169
1170
1171\subsection{How well does it perform?}
1172
1173mod\_log\_sql scales to very high loads. Apache 1.3.22 + mod\_log\_sql
1174was benchmarked using the \char`\"{}ab\char`\"{} (Apache Bench) program
1175that comes with the Apache distribution; here are the results.
1176
1177Overall configuration:
1178
1179\begin{itemize}
1180\item Machine A: Apache webserver
1181\item Machine B: MySQL server
1182\item Machines A and B connected with 100Mbps Ethernet
1183\end{itemize}
1184Webserver configuration:
1185
1186\begin{itemize}
1187\item Celeron 400 128 MB RAM IDE storage
1188\end{itemize}
1189Apache configuration:
1190
1191\begin{lyxcode}
1192Timeout~300~
1193
1194KeepAlive~On~
1195
1196MaxKeepAliveRequests~100~
1197
1198KeepAliveTimeout~15~
1199
1200MinSpareServers~5~
1201
1202StartServers~10~
1203
1204MaxSpareServers~15~
1205
1206MaxClients~256~
1207
1208MaxRequestsPerChild~5000~
1209
1210LogSQLTransferLogFormat~AbHhmRSsTUuvc~
1211
1212LogSQLWhichCookie~Clicks~
1213
1214CookieTracking~on~
1215
1216CookieName~Clicks
1217\end{lyxcode}
1218\char`\"{}ab\char`\"{} commandline:
1219
1220\begin{lyxcode}
1221./ab~-c~10~-t~20~-v~2~-C~Clicks=ab\_run~http://www.hostname.com/target~
1222\end{lyxcode}
1223( 10 concurrent requests; 20 second test; setting a cookie \char`\"{}Clicks=ab\_run\char`\"{};
1224target = the mod\_log\_sql homepage. )
1225
1226Ten total ab runs were conducted: five with MySQL logging enabled,
1227and five with all MySQL directives commented out of httpd.conf. Then
1228each five were averaged. The results:
1229
1230\begin{itemize}
1231\item Average of five runs employing MySQL \emph{and} standard text logging:
1232139.01 requests per second, zero errors.
1233\item Average of five runs employing \emph{only} standard text logging:
1234139.96 requests per second, zero errors.
1235\end{itemize}
1236In other words, any rate-limiting effects on this particular hardware
1237setup are not caused by MySQL. Note that although this very simple
1238webserver setup is hardly cutting-edge -- it is, after all, a fairly
1239small machine -- 139 requests per second equal over twelve million
1240hits per day\emph{.}
1241
1242If you run this benchmark yourself, take note of three things:
1243
1244\begin{enumerate}
1245\item Use a target URL that is on your own webserver :-).
1246\item Wait until all your connections are closed out between runs; after
1247several thousand requests your TCP/IP stack will be filled with hundreds
1248of connections in TIME\_WAIT that need to close. Do a \char`\"{}netstat
1249-t|wc -l\char`\"{} on the webserver to see. If you don't wait, you
1250can expect to see a lot of messages like \char`\"{}ip\_conntrack:
1251table full, dropping packet\char`\"{} in your logs. (This has nothing
1252to do with mod\_log\_sql, this is simply the nature of the TCP/IP
1253stack in the Linux kernel.)
1254\item When done with your runs, clean these many thousands of requests out
1255of your database:
1256\end{enumerate}
1257\begin{lyxcode}
1258mysql>~delete~from~access\_log~where~agent~like~'ApacheBench\%';~mysql>~optimize~table~access\_log;~
1259\end{lyxcode}
1260
1261\subsection{Who's using mod\_log\_sql?}
1262
1263Good question! It would be great to find out! If you are a production-level
1264mod\_log\_sql user, please contact the maintainer, Chris Powell \url{chris@grubbybaby.com}so
1265that you can be mentioned here.
1266
1267
1268\subsection{How do I extract the data in a format that my analysis tool can understand?}
1269
1270mod\_log\_sql would be virtually useless if there weren't a way for
1271you to extract the data from your database in a somewhat meaningful
1272fashion. To that end there's a Perl script enclosed with the distribution.
1273That script (make\_combined\_log.pl) is designed to extract N-many
1274days worth of access logs and provide them in a Combined Log Format
1275output. You can use this very tool right in /etc/crontab to extract
1276logs on a regular basis so that your favorite web analysis tool can
1277read them. Or you can examine the Perl code to construct your own
1278custom tool.
1279
1280For example, let's say that you want your web statistics updated once
1281per day in the wee hours of the morning. A good way to accomplish
1282that would be the following entries in /etc/crontab:
1283
1284\begin{lyxcode}
1285\#~Generate~the~temporary~apache~logs~from~the~MySQL~database~(for~webalizer)~
1286
128705~04~{*}~{*}~{*}~root~/usr/local/sbin/make\_combined\_log.pl~1~www.grubbybaby.com~>~/var/log/httpd/mysql-grubbybaby
1288
1289\#~Run~webalizer~on~httpd~log~
1290
129130~04~{*}~{*}~{*}~root~/usr/local/bin/webalizer~-c~/etc/webalizer.conf;~rm~-f~/var/log/httpd/mysql-grubbybaby
1292\end{lyxcode}
1293Or if you have a newer system that puts files in /etc/cron.daily etc.,
1294create a file called ''webalizer'' in the cron.\_\_\_\_ subdir of
1295your choice. Use the following as the contents of your file, and make
1296sure to chmod 755 it when done.
1297
1298\begin{lyxcode}
1299\#!/bin/sh
1300
1301/usr/local/sbin/make\_combined\_log.pl~1~www.yourdomain.com~>~/var/log/httpd/templog
1302
1303/usr/local/bin/webalizer~-q~-c~/etc/webalizer.conf~
1304
1305rm~-f~/var/log/httpd/templog
1306\end{lyxcode}
1307See? Easy.
1308
1309
1310\subsection{Why doesn't the module also replace the Apache ErrorLog?}
1311
1312There are circumstances when that would be quite unwise -- for example,
1313if Apache could not reach the MySQL server for some reason and needed
1314to log that fact. Without a text-based error log you'd never know
1315anything was wrong, because Apache would be trying to log a database
1316connection error to the database... you get the point.
1317
1318Error logs are usually not very high-traffic and are really best left
1319as text files on a web server machine.
1320
1321
1322\subsection{\label{sec:cookie}How can I log mod\_usertrack cookies?}
1323
1324A number of people like to log mod\_usertrack cookies in their Apache
1325TransferLog to aid in understanding their visitors' clickstreams.
1326This is accomplished, for example, with a statement as follows:
1327
1328\begin{lyxcode}
1329LogFormat~\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`\"{}
1330\end{lyxcode}
1331Naturally it would be nice for mod\_log\_sql to permit the admin to
1332log the cookie data as well, so as of version 1.10 you can do this.
1333You need to have already compiled mod\_usertrack into httpd -- it's
1334one of the standard Apache modules.
1335
1336First make sure you have a column called \char`\"{}cookie\char`\"{}
1337in the MySQL database to hold the cookies, which can be done as follows
1338if you already have a working database:
1339
1340\begin{lyxcode}
1341alter~table~acc\_log\_tbl~add~column~cookie~varchar(255);
1342\end{lyxcode}
1343Next configure your server to set usertracking cookies as follows,
1344and make sure you include the new 'c' directive in your LogSQLTransferLogFormat,
1345which activates cookie logging. Here's an example:
1346
1347\begin{lyxcode}
1348<VirtualHost~1.2.3.4>~
1349
1350~CookieTracking~on~
1351
1352~CookieStyle~Cookie~
1353
1354~CookieName~Foobar~
1355
1356~LogSQLTransferLogFormat~huSUsbTvRAc~
1357
1358~LogSQLWhichCookie~Foobar~
1359
1360</VirtualHost>
1361\end{lyxcode}
1362The first three lines configure mod\_usertrack to create a COOKIE
1363(RFC 2109) format cookie called Foobar. The last two lines tell mod\_log\_sql
1364to log cookies named Foobar. You have to choose which cookie to log
1365because more than one cookie can/will be sent to the server by the
1366client.
1367
1368FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that
1369even newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2
1370(RFC 2965) format. Just stick with the standard COOKIE format and
1371you'll be fine.
1372
1373Perform some hits on your server and run a select:
1374
1375\begin{lyxcode}
1376mysql>~select~request\_uri,cookie~from~access\_log~where~cookie~is~not~null;
1377
1378+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1379
1380|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~cookie~|~
1381
1382+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~
1383
1384|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~
1385
1386|~/mod\_log\_sql/usa.gif~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~
1387
1388|~/mod\_log\_sql/style\_1.css~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~
1389
1390...etc...
1391\end{lyxcode}
1392
1393\subsection{What if I want to log more than one cookie?}
1394
1395No problem. As of version 1.17, you have a choice. If you are just
1396interested in a single cookie, follow the instructions in section
1397\ref{sec:cookie} above. That cookie will be logged to a column in
1398the regular access\_log table.
1399
1400However, if you need to log multiple cookies, you'll employ the LogSQLWhichCookies
1401(note the plural) directive. The cookies you specify will be logged
1402to a separate table, and entries in that table will be linked to the
1403regular access\_log entries via the unique ID that is supplied by
1404mod\_unique\_id. Without mod\_unique\_id the information will still
1405be logged but you will be unable to correlate which cookies go with
1406which access-requests.
1407
1408LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict,
1409but you're better off choosing the one you need.
1410
1411
1412\subsection{What are the SSL logging features, and how do I activate them?}
1413
1414If you run an SSL-enabled server you may benefit from logging some
1415SSL details. mod\_log\_sql now supports this ability. By adding certain
1416characters to your LogSQLTransferLogFormat string you can tell mod\_log\_sql
1417to log the SSL cipher, the SSL keysize of the connection, and the
1418Max-keysize that was available. This would let you tell, for example,
1419which clients were using only export-grade security to access your
1420secure software area.
1421
1422You can compile mod\_log\_sql with SSL logging support if you have
1423the right packages installed. If you already have an SSL-enabled Apache
1424then you by definition have the correct packages already installed:
1425OpenSSL and mod\_ssl.
1426
1427You need to ensure that your database is set up to log the SSL data.
1428Issue the following commands to MySQL once you have your basic access\_log
1429table built:
1430
1431\begin{lyxcode}
1432alter~table~access\_log~add~column~ssl\_cipher~varchar(25);
1433
1434alter~table~access\_log~add~column~ssl\_keysize~smallint~unsigned;
1435
1436alter~table~access\_log~add~column~ssl\_maxkeysize~smallint~unsigned;
1437\end{lyxcode}
1438Finally configure httpd.conf to activate the SSL fields. Note that
1439this is only meaningful in a VirtualHost that is set up for SSL.
1440
1441\begin{lyxcode}
1442<VirtualHost~1.2.3.4:443>~
1443
1444~LogSQLTransferLogFormat~AbHhmRSsTUuvcQqz~
1445
1446</VirtualHost>
1447\end{lyxcode}
1448The last three characters (Qqz) in the directive are the SSL ones;
1449see the directives documentation for details.
1450
1451Perform some hits on your server and run a select:
1452
1453\begin{lyxcode}
1454mysql>~select~remote\_host,request\_uri,ssl\_cipher,ssl\_keysize,ssl\_maxkeysize~from~access\_log~where~ssl\_cipher~is~not~null;
1455
1456+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~
1457
1458|~remote\_host~~~~~~~~~~~~~~|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ssl\_cipher~|~ssl\_keysize~|~ssl\_maxkeysize~|
1459
1460+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~
1461
1462|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.html~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~
1463
1464|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.gif~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~
1465
1466|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.jpg~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~
1467
1468...etc...
1469\end{lyxcode}
1470
1471\subsection{Does mod\_log\_sql connect to MySQL via TCP/IP or a socket?}
1472
1473It depends! Actually this isn't determined by mod\_log\_sql. mod\_log\_sql
1474relies on a connection command that is supplied in the MySQL API,
1475and that command is somewhat intelligent. When mod\_log\_sql issues
1476the connect command to MySQL, this intelligent connect command uses
1477sockets to communicate with MySQL if the specified MySQL database
1478is on the same machine (because sockets are more efficient than TCP/IP).
1479However, if the specified MySQL db is on a different machine, mod\_log\_sql
1480connects using TCP/IP. You don't have any control of which methodology
1481is used.
1482
1483You do have control over where mod\_log\_sql looks for the socket.
1484The LogSQLSocketFile runtime configuration directive overrides the
1485default of \char`\"{}/var/lib/mysql/mysql.sock\char`\"{} to whatever
1486you wish. (Applies to mod\_log\_sql 1.16 or later only.)
1487
1488
1489\subsection{Why do I occasionally see a \char`\"{}connection lost, attempting
1490reconnect\char`\"{} message in my error-log?}
1491
1492This message may appear every now and then in your Apache error log,
1493especially on very lightly loaded servers. This doesn't mean that
1494anything is necessarily wrong. Within each httpd child process, mod\_log\_sql
1495will open (and keep open) a connection to the MySQL server. MySQL,
1496however, will close connections that haven't been used in a while;
1497the default timeout is 8 hours. When this occurs, mod\_log\_sql will
1498notice and re-open the connection. That event is what is being logged,
1499and looks like this:
1500
1501\begin{lyxcode}
1502{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~connection~lost,~attempting~reconnect
1503
1504{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~reconnect~successful
1505\end{lyxcode}
1506Reference: MySQL documentation \url{http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away}
1507
1508
1509\subsection{Does mod\_log\_sql work with Apache 2.x?}
1510
1511As of this writing, no. The Apache Group significantly altered the
1512module API with the release of Apache 2.0. All modules written for
15131.3, including mod\_log\_sql, will not work with 2.0.
1514
1515mod\_log\_sql will eventually be ported to Apache 2.x, but not immediately.
1516It is going to take some time, and there are other features that have
1517higher priority. Please sign up for the announcements list (on the
1518main website) or monitor the website for updates to learn when the
1519port (and other releases) are available.
1520
1521<OPINION>If you're a {*}NIX user, stick with Apache 1.3.x for now.
1522Major modules like mod\_ssl and PHP are not even ready for 2.0 yet,
1523and the main benefits in 2.0 are for Win32 users anyway. Apache 1.3.x
1524is rock-stable and performs equally well on {*}NIX as 2.0.</OPINION>
1525
1526
1527\subsection{I have discovered a bug. Who can I contact?}
1528
1529Please contact the maintainer \url{chris@grubbybaby.com}! Your comments,
1530suggestions, bugfixes, bug catches, and usage testimonials are always
1531welcome. As free software, mod\_log\_sql is intended to be a community
1532effort -- any code contributions or other ideas will be fully and
1533openly credited, of course.
1534\end{document}