summaryrefslogtreecommitdiffstats
path: root/Documentation
diff options
context:
space:
mode:
Diffstat (limited to 'Documentation')
-rw-r--r--Documentation/documentation.lyx2969
-rw-r--r--Documentation/documentation.tex1534
2 files changed, 4503 insertions, 0 deletions
diff --git a/Documentation/documentation.lyx b/Documentation/documentation.lyx
new file mode 100644
index 0000000..48c3968
--- /dev/null
+++ b/Documentation/documentation.lyx
@@ -0,0 +1,2969 @@
1#LyX 1.1 created this file. For more info see http://www.lyx.org/
2\lyxformat 218
3\textclass article
4\language english
5\inputencoding default
6\fontscheme default
7\graphics default
8\paperfontsize 10
9\spacing single
10\papersize letterpaper
11\paperpackage a4
12\use_geometry 1
13\use_amsmath 0
14\paperorientation portrait
15\paperwidth 8.5in
16\paperheight 11in
17\leftmargin 1in
18\topmargin 0.5in
19\rightmargin 1in
20\bottommargin 1in
21\secnumdepth 3
22\tocdepth 3
23\paragraph_separation indent
24\defskip medskip
25\quotes_language swedish
26\quotes_times 2
27\papercolumns 1
28\papersides 1
29\paperpagestyle default
30
31\layout Title
32\added_space_top vfill \added_space_bottom vfill
33Installing and Running mod_log_sql
34\layout Author
35
36Christopher Powell
37\layout Date
38
3911/13/02
40\layout Standard
41\pagebreak_bottom
42
43\begin_inset LatexCommand \tableofcontents{}
44
45\end_inset
46
47
48\layout Section
49
50Introduction
51\layout Subsection
52
53Homepage
54\layout LyX-Code
55
56http://www.grubbybaby.com/mod_log_sql/
57\layout Subsection
58
59Summary
60\layout Standard
61
62This Apache module will permit you to log Apache accesses (and a lot of
63 related information) to a SQL database.
64 Unlike logging to a flat text file (which is standard in Apache), there
65 are a lot of powerful advantages to logging to SQL.
66 This module can either replace or happily coexist with Apache's text file
67 logging facility.
68\layout Subsection
69
70Approach
71\layout Standard
72
73This project was formerly known as mod_log_mysql.
74 It has been renamed to mod_log_sql in order to reflect the project goal
75 of database-inspecificity.
76 The module currently supports MySQL, and development for other database
77 backends is underway.
78\layout Standard
79
80In order to save speed and overhead, links are kept alive in between queries.
81 This module uses one SQL link per httpd child.
82 Among other things, this means that this module supports logging into only
83 one MySQL server, and for now, also, only one SQL database.
84 But that's a small tradeoff compared to the blinding speed of this module.
85\layout Standard
86
87Virtual hosts are supported in the same manner they are in the regular logging
88 modules.
89 You define some basic 'global' directives in the main server config, then
90 you define more specific 'local' directives inside each virtualhost stanza.
91\layout Standard
92
93SQL links are opened by each child process when it is born.
94 Error reporting is robust throughout and will let you know about database
95 issues in the standard Apache error-log for the server or virtual server.
96\layout Standard
97
98A robust "preserve" capability has now been implemented.
99 This permits the module to preserve any failed INSERT commands to a local
100 file on its machine.
101 In any situation that the database is unavailable -- e.g.
102 the network fails, you reboot the db host, etc.
103 -- mod_log_sql will note this in the error log and begin appending its
104 log entries to the preserve file (which is created with the user & group
105 ID of the running Apache process, e.g.
106 "nobody" on many Linux installations).
107 When your database becomes available again, mod_log_sql seamlessly resumes
108 logging to it.
109 When convenient for the sysadmin, he/she can easily import the preserve
110 file into the database because it is simply a series of SQL insert statements:
111\layout LyX-Code
112
113# mysql -uadminuser -p mydbname < /tmp/mysql-preserve
114\layout Subsection
115
116Supported directives
117\layout Standard
118
119Please see the web-based documentation for full explanation of all supported
120 run-time directives.
121\layout Standard
122
123http://www.grubbybaby.com/mod_log_sql/directives.html
124\layout Standard
125
126See the FAQ for some handy examples:
127\layout Standard
128
129http://www.grubbybaby.com/mod_log_sql/faq.html
130\layout Subsection
131
132What gets logged by default?
133\layout Standard
134
135All the data that would be contained in the "Combined Log Format" is logged
136 by default, plus a little extra.
137 Your best bet is to begin by accepting this default, then later customize
138 the log configuration based on your needs.
139\layout Standard
140
141The online documentation of the run-time directives includes a full explanation
142 of what you can log, including examples.
143\layout Subsection
144
145Minor Notes
146\layout Itemize
147
148Note which directives go in the 'main server config' and which directives
149 apply to the 'virtual host config'.
150 This is made clear in the directive documentation.
151\layout Itemize
152
153The 'time_stamp' field is stored in an UNSIGNED INTEGER column, in the standard
154 unix "seconds since 1/1/1970 12:00:00" format.
155 This is superior to storing the access time as a string due to size requirement
156s: an UNSIGNED INT type fits in 4 bytes, whereas the Apache date string
157 (e.g.
158 "18/Nov/2001:13:59:52 -0800") requires 26 bytes -- significantly larger,
159 and those extra 22 bytes will add up over the thousands of accesses that
160 a busy server will experience.
161 Besides, an INT type is far more flexible for comparisons, etc.
162\begin_deeper
163\layout Standard
164
165In MySQL 3.21 and above you can easily convert this to a human readable format
166 using from_unixtime(), e.g.:
167\layout LyX-Code
168
169select remote_host,request_uri,from_unixtime(time_stamp) from access_log;
170\layout Standard
171
172The enclosed perl program
173\begin_inset Quotes sld
174\end_inset
175
176make_combined_log.pl
177\begin_inset Quotes srd
178\end_inset
179
180 shows how you can extract your access records in a format that is completely
181 Combined Log Format compliant.
182 You can then feed this to your favorite web log analysis tool.
183\end_deeper
184\layout Itemize
185
186The table's string values can be CHAR or VARCHAR, at a length of your choice.
187 VARCHAR is superior because it truncates long strings; CHAR types are fixed-len
188gth and will be padded with spaces.
189 Just like the time_stamp described above, that kind of space waste will
190 add up over thousands of records.
191\layout Itemize
192
193Be careful not to go overboard setting fields to NOT NULL.
194 If a field is marked NOT NULL then it must contain data in the INSERT or
195 the INSERT will fail, sometimes in a really mysterious way to the frustrated
196 sysad.
197\layout Itemize
198
199Apache normally logs numeric fields with a '-' character to mean "not applicable
200," e.g.
201 bytes_sent on a request with a 304 response code.
202 Since '-' is an illegal character in an SQL numeric field, such fields
203 are assigned the value 0 instead of '-' which, of course, makes perfect
204 sense anyway.
205\layout Subsection
206
207Author / Maintainer
208\layout Standard
209
210The actual logging code was taken from the already existing flat file text
211 modules, so all that credit goes to the Apache Server group.
212\layout Standard
213
214The MySQL routines and directives were added by Zeev Suraski <bourbon@netvision.n
215et.il>.
216\layout Standard
217
218All changes from 1.06+ and the new documentation were added by Chris Powell
219 <chris@grubbybaby.com>.
220 It seems that the module had fallen into the "unmaintained" category --
221 it hadn't been updated since 1998 -- so Chris adopted it as the new maintainer.
222\layout Section
223
224Installation
225\layout Subsection
226
227Requirements
228\layout Itemize
229
230A compatible system.
231 I have run mod_log_sql on Red Hat based systems (Red Hat, Mandrake).
232 These instructions should easily adapt to any modern distro.
233\layout Itemize
234
235Apache 1.2 or 1.3 installed.
236 (I run 1.3.22 and it works fine).
237 You should have already successfully compiled Apache and know what you're
238 doing there.
239\layout Itemize
240
241The MySQL development headers.
242 This is called different things on different distros.
243 For example, Red Hat 6.x called this RPM "MySQL-devel" whereas Mandrake
244 calls it "libmysql10-devel".
245\layout Itemize
246
247MySQL >= 3.23.15 configured, installed and running on either localhost or
248 an accessible networked machine.
249 You should already have a basic understanding of MySQL and how it functions.
250\layout Itemize
251
252Again, basic administrative skills with Apache and MySQL.
253 I try to make things as easy as possible in this file, but its purpose
254 is not to be an administrative tutorial.
255\layout Itemize
256
257Additionally, if you want to be able to log SSL information such as keysize
258 or cipher, you need OpenSSL and glibc-devel installed.
259 Both are available as RPMs.
260\layout Subsection
261
262Do I want a DSO?
263\layout Standard
264
265You need to know the answer to this question before you proceed.
266 The answer is pretty straightforward: what have you done in the past? If
267 you like all your Apache modules to be dynamic, then you should keep doing
268 that.
269 If you're more of an old-school type and prefer to compile the modules
270 right into apache, do that.
271 Both methods work equally well.
272\layout Standard
273
274FWIW, the DSO method is more modern and increasing in popularity because
275 apxs takes care of a lot of dirty little details for you.
276 As you'll see below, the static-module method is a little more complex.
277\layout Subsection
278
279Installation as an Apache DSO (Preferred)
280\layout Enumerate
281
282Perform all the following steps as root so that you have install privs,
283 etc.
284 Unpack the archive into a working directory.
285\begin_deeper
286\layout LyX-Code
287
288# tar zxf mod_log_sql.tar.gz -C /usr/local/src # cd /usr/local/src/mod_log_sql
289\end_deeper
290\layout Enumerate
291
292Edit Makefile for your system.
293\newline
294
295\begin_deeper
296\layout Standard
297
298NECESSARY:
299\layout Itemize
300
301The location where you installed Apache -- usually /usr/local/apache, 'locate
302 apxs' can help you find it.
303\layout Itemize
304
305The location of your MySQL libraries, find using 'locate libmysqlclient'
306\layout Itemize
307
308The location of your MySQL header files, find using 'locate mysql.h'
309\layout Standard
310
311OPTIONAL if you have included mod_ssl in Apache and want to log SSL data
312 such as keysize and cipher type:
313\layout Itemize
314
315The location of your SSL header files, find using 'locate mod_ssl.h'
316\layout Standard
317
318Now that you know these things, edit Makefile and replace the stock values
319 with your own.
320\layout Standard
321
322IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting
323 a # character in front of it:
324\layout LyX-Code
325
326#MODSSLHDRS=/usr/include/...
327\end_deeper
328\layout Enumerate
329
330Instruct apxs to compile the module as a DSO.
331\begin_deeper
332\layout LyX-Code
333
334# make dso
335\end_deeper
336\layout Enumerate
337
338You should see output similar to the following:
339\begin_deeper
340\layout LyX-Code
341
342/usr/local/Apache/bin/apxs -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -c -I/usr/include/mysql
343 -I/usr/local/src/apache_1.3.27-dso/src/modules/ssl -L/usr/lib -lmysqlclient
344 -lz mod_log_sql.c
345\layout LyX-Code
346
347gcc -DLINUX=22 -DNO_DBM_REWRITEMAP -DMOD_SSL=208111 -DUSE_HSREGEX -DEAPI
348 -DUSE_EXPAT -I../lib/expat-lite -fpic -DSHARED_CORE -DSHARED_MODULE -I/usr/local/
349Apache/include -O2 -Wall -DEAPI -I/usr/include/mysql -I/usr/local/src/apache_1.3.2
3507-dso/src/modules/ssl -c mod_log_sql.c
351\layout LyX-Code
352
353gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -L/usr/li
354b -lmysqlclient -lz -lm -lcrypt -ldb
355\layout Standard
356
357You should see no errors and have a file called "mod_log_sql.so" in your
358 directory.
359\end_deeper
360\layout Enumerate
361
362Instruct apxs to install the DSO.
363\begin_deeper
364\layout LyX-Code
365
366# make dsoinstall
367\layout Standard
368
369You should see output similar to the following:
370\layout LyX-Code
371
372/usr/local/Apache/bin/apxs -i mod_log_sql.so
373\layout LyX-Code
374
375cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so
376\layout LyX-Code
377
378chmod 755 /usr/local/Apache/libexec/mod_log_sql.so
379\end_deeper
380\layout Enumerate
381
382Module ordering within httpd.conf is important.
383 If you are logging SSL, you must make sure that
384\begin_deeper
385\layout LyX-Code
386
387LoadModule ssl_module libexec/libssl.so
388\layout Standard
389
390comes before
391\layout LyX-Code
392
393LoadModule sql_log_module libexec/mod_log_sql.so
394\layout Standard
395
396If you don't, you will get this error when you start Apache:
397\layout LyX-Code
398
399/usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup
400\layout LyX-Code
401
402/usr/local/apache/bin/apachectl startssl: httpd could not be started
403\layout Standard
404
405(Because mod_log_sql doesn't yet have the required symbols that mod_ssl
406 provides.)
407\layout Standard
408
409Now skip below to the "Configuration" section.
410\end_deeper
411\layout Subsection
412
413Installation as a static module compiled into httpd
414\layout Enumerate
415
416Perform all the following steps as root so that you have install privs,
417 etc.
418\layout Enumerate
419
420Unpack the archive into a working directory.
421\begin_deeper
422\layout LyX-Code
423
424# tar zxf mod_log_sql.tar.gz -C /usr/local/src # cd /usr/local/src/mod_log_sql
425\end_deeper
426\layout Enumerate
427
428
429\begin_inset LatexCommand \label{step:editMF}
430
431\end_inset
432
433Edit Makefile for your system.
434\newline
435
436\begin_deeper
437\layout Standard
438
439NECESSARY:
440\layout Itemize
441
442The location where you installed Apache -- usually /usr/local/apache, 'locate
443 apxs' can help you find it.
444
445\layout Itemize
446
447The location of your Apache *sources*, find using 'locate ABOUT_APACHE'
448
449\layout Itemize
450
451The location of your MySQL header files, find using 'locate mysql.h'
452\layout Itemize
453
454The location of your MySQL libraries, find using 'locate libmysqlclient'
455\layout Standard
456
457OPTIONAL if you have included mod_ssl in Apache and want to log SSL data
458 such as keysize and cipher type:
459\layout Itemize
460
461The location of your mod_ssl header files, find using 'locate mod_ssl.h'
462\layout Itemize
463
464The location of your OpenSSL header files, find using 'locate x509.h'
465\layout Itemize
466
467The location of your db1 header files, find using 'locate mpool.h'
468\layout Standard
469
470Now that you know these things, edit Makefile and replace the stock values
471 with your own.
472\layout Standard
473
474IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, OPNSSLHDRS
475 and DB1HDRS by putting a # character in front of each one, e.g.
476 #OPNSSLHDRS=/usr/include/...
477\end_deeper
478\layout Enumerate
479
480# make static
481\layout Enumerate
482
483# make statinstall
484\layout Enumerate
485
486Change to your Apache source dir.
487\begin_deeper
488\layout LyX-Code
489
490# cd /usr/local/src/apache-1.3.22/src
491\end_deeper
492\layout Enumerate
493
494Re-make your httpd binary as follows.
495\begin_deeper
496\layout Enumerate
497
498Edit Configuration.apaci as follows...
499\begin_deeper
500\layout Itemize
501
502Append the following string to the EXTRA_LIBS= line.
503 ("/usr/lib/mysql" is from step
504\begin_inset LatexCommand \ref{step:editMF}
505
506\end_inset
507
508, where your MySQL libraries live):
509\layout LyX-Code
510
511-L/usr/lib/mysql -lmysqlclient -lm -lz
512\layout Itemize
513
514Find the mod_log_config.o line, and add this line immediately after it:
515\layout LyX-Code
516
517AddModule modules/sql/mod_log_sql.o
518\end_deeper
519\layout Enumerate
520
521# cp Configuration.apaci Configuration
522\layout Enumerate
523
524# ./Configure
525\layout Enumerate
526
527# make
528\layout Enumerate
529
530# strip httpd
531\end_deeper
532\layout Enumerate
533
534Test your new apache binary:
535\begin_deeper
536\layout LyX-Code
537
538# ./httpd -l
539\end_deeper
540\layout Enumerate
541
542You should see something like:
543\begin_deeper
544\layout LyX-Code
545
546Compiled-in modules:
547\layout LyX-Code
548
549http_core.c
550\layout LyX-Code
551
552mod_log_sql.c <-- That's the line you're looking for.
553\layout LyX-Code
554
555mod_env.c
556\layout LyX-Code
557
558mod_log_config.c
559\layout LyX-Code
560
561mod_mime.c
562\layout LyX-Code
563
564mod_negotiation.c ...
565\layout LyX-Code
566
567etc...
568\end_deeper
569\layout Enumerate
570
571Install your httpd binary.
572 Copy it over your old httpd binary, wherever it lives.
573 You can and should rename your old httpd first so that you can easily revert
574 to that working version in case of bugs with the new version.
575\begin_deeper
576\layout LyX-Code
577
578# /etc/rc.d/init.d/httpd stop
579\layout LyX-Code
580
581# mv /usr/local/Apache/bin/httpd ~/httpd-save
582\layout LyX-Code
583
584# cp -f ./httpd /usr/local/Apache/bin/
585\end_deeper
586\layout Section
587
588Configuration
589\layout Subsection
590
591Preparing MySQL
592\layout Standard
593
594You have to prepare the database to receive data from mod_log_sql, and set
595 up run-time directives in httpd.conf to control how and what mod_log_sql
596 logs.
597\layout Standard
598
599This section will discuss how to get started with a basic config.
600 Full documentation of the run-time directives is available here: http://www.grub
601bybaby.com/mod_log_sql/directives.html
602\layout Enumerate
603
604mod_log_sql can make its own tables on-the-fly, or you can pre-make the
605 tables by hand.
606 The advantage of letting the module make the tables is ease-of-use, but
607 for raw performance you will want to pre-make the tables in order to save
608 overhead.
609 In this basic setup we'll let the module create tables for us.
610\layout Enumerate
611
612We still need to have a logging database created and ready, so run the MySQL
613 command line client and create a database:
614\begin_deeper
615\layout LyX-Code
616
617# mysql -uadmin -pmypassword mysql> create database apachelogs;
618\end_deeper
619\layout Enumerate
620
621If you want to hand-create the tables, run the enclosed 'create-tables'
622 SQL script as follows:
623\begin_deeper
624\layout LyX-Code
625
626mysql> source create_tables.sql
627\end_deeper
628\layout Enumerate
629
630Create a specific MySQL userid that httpd will use to authenticate and enter
631 data.
632 This userid need not be an actual Unix user.
633 It is a userid internal to MySQL with specific privileges.
634 In the following example command, "apachelogs" is the database, "loguser"
635 is the userid to create, "my.apachemachine.com" is the name of the Apache
636 machine, and "l0gger" is the password to assign.
637 Choose values that are different from these examples.
638\begin_deeper
639\layout LyX-Code
640
641mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com
642 identified by 'l0gger';
643\end_deeper
644\layout Enumerate
645
646You may be especially security-paranoid and not want "loguser" to have "create"
647 capability within the "apachelogs" databse.
648 You can disable that but the cost is that you cannot use the module's automatic
649-table-creation feature.
650 If that's an acceptable cost, hand-create the tables as described in step
651 1 and use the following GRANT statement instead of the one above:
652\begin_deeper
653\layout LyX-Code
654
655mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com identified
656 by 'l0gger';
657\end_deeper
658\layout Enumerate
659
660Enable full logging of your MySQL daemon (at least temporarily for debugging
661 purposes) if you don't do this already.
662 Edit /etc/my.cnf and add the following line to your [mysqld] section:
663\begin_deeper
664\layout LyX-Code
665
666log=/var/log/mysql-messages
667\layout Standard
668
669Then restart MySQL.
670\layout LyX-Code
671
672# /etc/rc.d/init.d/mysql restart
673\end_deeper
674\layout Subsection
675
676Preparing Apache
677\layout Enumerate
678
679Tell the module what database to use and the appropriate authentication
680 information.
681\begin_deeper
682\layout Description
683
684EXAMPLE: Use the MySQL database called "apachelogs" running on "dbmachine.foo.com".
685 The module uses username "loguser" and password "l0gger" to authenticate
686 to the database.
687 The log entries will be INSERTed into the table called "access_log".
688\layout Standard
689
690So, edit httpd.conf and insert the following lines somewhere AFTER any LoadModule
691 / AddModule statements.
692 Make sure these statements are "global," i.e.
693 not inside any VirtualHost stanza.
694\layout LyX-Code
695
696LogSQLDatabase apachelogs
697\layout LyX-Code
698
699LogSQLLoginInfo dbmachine.foo.com loguser l0gger
700\layout LyX-Code
701
702LogSQLCreateTables on
703\end_deeper
704\layout Enumerate
705
706If your database resides on localhost instead of another host, specify the
707 MySQL server's socket file as follows:
708\begin_deeper
709\layout LyX-Code
710
711LogSQLSocketFile /your/path/to/mysql.sock
712\end_deeper
713\layout Enumerate
714
715The actual logging is set up on a virtual-host-by-host basis.
716 So, skip down to the virtual host you want to set up.
717 Instruct this virtual host to log entries to the table
718\begin_inset Quotes sld
719\end_inset
720
721access_log
722\begin_inset Quotes srd
723\end_inset
724
725 by inserting a LogSQLTransferLogTable directive.
726 (The LogSQLTransferLogTable directive is the minimum required to log --
727 other directives simply tune the module's behavior.)
728\begin_deeper
729\layout LyX-Code
730
731<VirtualHost 1.2.3.4>
732\layout LyX-Code
733
734[snip]
735\layout LyX-Code
736
737LogSQLTransferLogTable access_log
738\layout LyX-Code
739
740[snip]
741\layout LyX-Code
742
743</VirtualHost>
744\end_deeper
745\layout Enumerate
746
747Restart apache.
748\begin_deeper
749\layout LyX-Code
750
751# /etc/rc.d/init.d/httpd start
752\end_deeper
753\layout Subsection
754
755Testing the module
756\layout Enumerate
757
758Load your web site in a browser to trigger some hits, then confirm that
759 the entries are being successfully logged:
760\begin_deeper
761\layout LyX-Code
762
763# mysql -hmysql.host.com -umysqladmin -p -e "select * from access_log" apachelogs
764
765\layout LyX-Code
766
767Enter password:
768\layout LyX-Code
769
770(Several lines of output should follow, corresponding to your hits on the
771 site.)
772\layout Standard
773
774You now have basic functionality.
775 Don't disable your regular Apache logs until you feel comfortable that
776 the database is behaving as you'd like and that things are going well.
777\end_deeper
778\layout Enumerate
779
780If you do not see any entries in the access_log, then something is preventing
781 the inserts from happening.
782 This problem could be caused by several things:
783\begin_deeper
784\layout Itemize
785
786Improper privileges set up in the MySQL database
787\layout Itemize
788
789You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry
790
791\layout Itemize
792
793You didn't specify the right host
794\layout Standard
795
796If you have confirmed your LogSQL* directives and know them to be correct,
797 you should examine the httpd server logs for mod_log_sql messages; the
798 module will offer hints as to why it cannot connect, etc.
799 Also examine the MySQL log that you established in step 3.
800 Ensure that the INSERTs are not being rejected because of a malformed table
801 entry or other clerical error.
802 If you see no INSERT attempts in the log, the module isn't successfully
803 connecting to the database.
804\layout Standard
805
806The next thing to do is recompile the module with debugging output activated.
807 change the "#undef DEBUG" on line 8 of mod_log_sql.c to "#define DEBUG"
808 and recompile/reinstall.
809 The module will now output copious notes about what it is doing, and this
810 will help you (and the maintainer) solve the problem.
811\end_deeper
812\layout Standard
813
814You can now activate the advanced features of mod_log_sql.
815 These are all described in the next section.
816\layout Subsection
817
818Configuration directive reference
819\layout Subsubsection
820
821LogSQLLoginInfo
822\layout LyX-Code
823
824
825\series bold
826MANDATORY
827\series default
828
829\layout LyX-Code
830
831Syntax: LogSQLLoginInfo host user password
832\layout LyX-Code
833
834Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd
835\layout LyX-Code
836
837Context: server config
838\layout Quote
839
840Defines the general parameters of the MySQL host to which you will be logging.
841 host is the hostname or IP address of the MySQL machine.
842 user is the MySQL userid (not a Unix userid!) with INSERT privileges on
843 the table defined in LogSQLTransferLogTable.
844 password is that user's password.
845 This is defined only once in the httpd.conf file.
846\layout Subsubsection
847
848LogSQLDatabase
849\layout LyX-Code
850
851
852\series bold
853MANDATORY
854\layout LyX-Code
855
856Syntax: LogSQLDatabase database
857\layout LyX-Code
858
859Example: LogSQLDatabase loggingdb
860\layout LyX-Code
861
862Context: server config
863\layout Quote
864
865Defines the database that is used for logging.
866 database must be operating on the MySQL host defined in LogSQLLoginInfo.
867 This is defined only once in the httpd.conf file.
868\layout Subsubsection
869
870LogSQLTransferLogTable
871\layout LyX-Code
872
873
874\series bold
875MANDATORY
876\layout LyX-Code
877
878Syntax: LogSQLTransferLogTable table-name
879\layout LyX-Code
880
881Example: LogSQLTransferLogTable access_log_table
882\layout LyX-Code
883
884Context: virtual host
885\layout Quote
886
887Defines which table is used for logging of Apache's transfers; this is analogous
888 to Apache's TransferLog directive.
889 table-name must be a valid table within the database defined in LogSQLDatabase.
890\layout Quote
891
892This directive is not necessary if you declare LogSQLMassVirtualHosting
893 On, since that directive activates dynamically-named tables.
894 If you attempt to use LogSQLTransferLogTable at the same time a warning
895 will be logged and it will be ignored, since LogSQLMassVirtualHosting takes
896 priority.
897\layout Subsubsection
898
899LogSQLRequestIgnore
900\layout LyX-Code
901
902Syntax: LogSQLRequestIgnore req1 req2 req3 ...
903 reqN
904\layout LyX-Code
905
906Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico
907\layout LyX-Code
908
909Context: virtual host
910\layout Quote
911
912Lists a series of strings that, if present in the URI, will cause that request
913 NOT to be logged.
914 This directive is useful for cutting down on log clutter when you KNOW
915 that you do not want to log requests for certain objects.
916 (The example above is a genuinely useful one; it will prevent logging of
917 many common Microsoft-based worm intrusion attempts, as well as those ridiculou
918s requests for the favicon.)
919\layout Quote
920
921Each string is separated by a space, and NO regular expressions or globbing
922 are allowed.
923 Each string is evaluated as a substring of the URI using strstr().
924\layout Subsubsection
925
926LogSQLRemhostIgnore
927\layout LyX-Code
928
929Syntax: LogSQLRemhostIgnore host1 host2 host3 ...
930 hostN
931\layout LyX-Code
932
933Example: LogSQLRemhostIgnore localnet.com
934\layout LyX-Code
935
936Context: virtual host
937\layout Quote
938
939Lists a series of strings that, if present in the REMOTE_HOST, will cause
940 that request NOT to be logged.
941 This directive is useful for cutting down on log clutter when you KNOW
942 that you do not want to log requests from certain hosts, such as your own
943 internal network machines.
944\layout Quote
945
946Each string is separated by a space, and NO regular expressions or globbing
947 are allowed.
948 Each string is evaluated as a substring of the REMOTE_HOST using strstr().
949\layout Subsubsection
950
951LogSQLRefererIgnore
952\layout LyX-Code
953
954Syntax: LogSQLRefererIgnore ref1 ref2 ref3 ...
955 refN
956\layout LyX-Code
957
958Example: LogSQLRefererIgnore google.com
959\layout LyX-Code
960
961Context: virtual host
962\layout Quote
963
964Lists a series of strings that, if present in the HTTP_REFERER, will cause
965 that request NOT to be logged.
966 This directive is useful for cutting down on log clutter when you know
967 that you do not want to log requests from certain referers.
968\layout Quote
969
970Each string is separated by a space, and NO regular expressions or globbing
971 are allowed.
972 Each string is evaluated as a substring of the HTTP_REFERER using strstr().
973\layout Subsubsection
974
975LogSQLWhichCookie
976\layout LyX-Code
977
978Syntax: LogSQLWhichCookie cookiename
979\layout LyX-Code
980
981Example: LogSQLWhichCookie Clicks
982\layout LyX-Code
983
984Default: None.
985 You must set this if you wish to capture cookies.
986\layout LyX-Code
987
988Context: virtual host
989\layout Quote
990
991In HTTP, cookies have names to distinguish them from each other.
992 Using mod_usertrack, for example, you can give your user-tracking cookies
993 a name with the CookieName directive.
994\layout Quote
995
996If you have activated cookie logging in LogSQLTransferLogFormat, then LogSQLWhic
997hCookie tells mod_log_sql which cookie to log.
998 This is useful because many times you will be setting and receiving more
999 than one cookie from a client; without this directive you'd be unable to
1000 choose which cookie is your mod_usertrack cookie.
1001\layout Quote
1002
1003Note: although this was intended for people who are using mod_usertrack
1004 to set user-tracking cookies, you aren't restricted in any way.
1005 You can choose which cookie you wish to log to the database, and it doesn't
1006 necessarily have to have anything to do with mod_usertrack.
1007\layout Subsubsection
1008
1009LogSQLCreateTables
1010\layout LyX-Code
1011
1012Syntax: LogSQLCreateTables flag
1013\layout LyX-Code
1014
1015Example: LogSQLCreateTables On
1016\layout LyX-Code
1017
1018Default: Off
1019\layout LyX-Code
1020
1021Context: server config
1022\layout Quote
1023
1024mod_log_sql now has the ability to create its tables on-the-fly.
1025 The advantage to this is convenience: you don't have to execute any SQL
1026 by hand to prepare the table.
1027 This is especially helpful for people with lots of virtual hosts (who should
1028 also see the LogSQLMassVirtualHosting directive).
1029\layout Quote
1030
1031There is a slight disadvantage: if you wish to activate this feature, then
1032 the user specified by LogSQLLoginInfo must have CREATE privileges on the
1033 database.
1034 In an absolutely paranoid, locked-down situation you may only want to grant
1035 your mod_log_sql user INSERT privileges on the database; in that situation
1036 you are unable to take advantage of LogSQLCreateTables.
1037 But most people -- even the very security-conscious -- will find that granting
1038 CREATE on the logging database is reasonable.
1039\layout Quote
1040
1041This is defined only once in the httpd.conf file.
1042\layout Subsubsection
1043
1044LogSQLMassVirtualHosting
1045\layout LyX-Code
1046
1047Syntax: LogSQLMassVirtualHosting flag
1048\layout LyX-Code
1049
1050Example: LogSQLMassVirtualHosting On
1051\layout LyX-Code
1052
1053Default: Off
1054\layout LyX-Code
1055
1056Context: server config
1057\layout Quote
1058
1059If you administer a site hosting many, many virtual hosts then this option
1060 will appeal to you.
1061 If you activate LogSQLMassVirtualHosting then several things happen:
1062\begin_deeper
1063\layout Itemize
1064
1065the on-the-fly table creation feature is activated automatically
1066\layout Itemize
1067
1068the transfer log table name is dynamically set from the virtual host's name
1069 (example: a virtual host www.grubbybaby.com gets logged to table access_www_grubb
1070ybaby_com)
1071\layout Itemize
1072
1073which, in turn, means that each virtual host logs to its own segregated
1074 table.
1075 Because there is no data shared between virtual servers you can grant your
1076 users access to the tables they need; they will be unable to view others'
1077 data.
1078\end_deeper
1079\layout Quote
1080
1081This is a huge boost in convenience for sites with many virtual servers.
1082 Activating LogSQLMassVirtualHosting obviates the need to create every virtual
1083 server's table and provides more granular security possibilities.
1084\layout Quote
1085
1086This is defined only once in the httpd.conf file.
1087\layout Subsubsection
1088
1089LogSQLPreserveFile
1090\layout LyX-Code
1091
1092Syntax: LogSQLPreserveFile filename
1093\layout LyX-Code
1094
1095Example: LogSQLPreserveFile offline-preserve
1096\layout LyX-Code
1097
1098Default: mysql-preserve (in /tmp)
1099\layout LyX-Code
1100
1101Context: virtual host
1102\layout Quote
1103
1104mod_log_sql writes queries to this local preserve file in the event that
1105 it cannot reach the database, and thus ensures that your high-availability
1106 web frontend does not lose logs during a temporary database outage.
1107 This could happen for a number of reasons: the database goes offline, the
1108 network breaks, etc.
1109 You will not lose entries since the module has this backup.
1110 The file consists of a series of SQL statements that can be imported into
1111 your database at your convenience; furthermore, because the SQL queries
1112 contain the access timestamps you do not need to worry about out-of-order
1113 data after the import.
1114\layout Quote
1115
1116For security purposes the name you supply will be prepended with "/tmp/"
1117 to force creation of the file in /tmp.
1118\layout Quote
1119
1120If you do not define LogSQLPreserveFile then all virtual servers will log
1121 to the same default preserve file (/tmp/mysql-preserve).
1122 You can redefine this on a virtual-host basis in order to segregate your
1123 preserve files if you desire.
1124 Note that segregation is not really necessary, as the SQL statements that
1125 are written to the preserve file already distinguish between different
1126 virtual hosts.
1127\layout Quote
1128
1129The module will log to error-log if/when it notices a database outage, and
1130 upon database return.
1131 You will therefore know when the preserve file is being used, although
1132 it is your responsibility to import the file.
1133\layout Quote
1134
1135The file does not need to be created in advance.
1136 It is safe to remove or rename the file without interrupting Apache, as
1137 the module closes the filehandle immediately after completing the write.
1138 The file is created with the user & group ID of the running Apache process
1139 (e.g.
1140 'nobody' on many Linux distributions).
1141\layout Subsubsection
1142
1143LogSQLSocketFile
1144\layout LyX-Code
1145
1146Syntax: LogSQLSocketFile filename
1147\layout LyX-Code
1148
1149Example: LogSQLSocketFile /tmp/mysql.sock
1150\layout LyX-Code
1151
1152Default: /var/lib/mysql/mysql.sock
1153\layout LyX-Code
1154
1155Context: server config
1156\layout Quote
1157
1158At Apache runtime you can specify the MySQL socket file to use.
1159 Set this once in your main server config to override the default value.
1160 This value is irrelevant if your database resides on a separate machine.
1161\layout Quote
1162
1163mod_log_sql will automatically employ the socket for db communications if
1164 the database resides on the local host.
1165 If the db resides on a separate host the module will automatically use
1166 TCP/IP.
1167 This is a function of the MySQL API and is not user-configurable.
1168\layout Subsubsection
1169
1170LogSQLTransferLogFormat
1171\layout LyX-Code
1172
1173Syntax: LogSQLTransferLogFormat format-string
1174\layout LyX-Code
1175
1176Example: LogSQLTransferLogFormat huSUTv
1177\layout LyX-Code
1178
1179Default: AbHhmRSsTUuv
1180\layout LyX-Code
1181
1182Context: virtual host
1183\layout Quote
1184
1185Each character in the format-string defines an attribute of the request
1186 that you wish to log.
1187 The default logs the information required to create Combined Log Format
1188 logs, plus several extras.
1189 Here is the full list of allowable keys, which sometimes resemble their
1190 Apache counterparts, but do not always:
1191\layout Quote
1192
1193
1194\begin_inset Tabular
1195<lyxtabular version="2" rows="20" columns="5">
1196<features rotate="false" islongtable="false" endhead="1" endfirsthead="1" endfoot="0" endlastfoot="0">
1197<column alignment="center" valignment="top" leftline="true" rightline="true" width="" special="">
1198<column alignment="left" valignment="top" leftline="false" rightline="false" width="" special="">
1199<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
1200<column alignment="left" valignment="top" leftline="false" rightline="false" width="" special="">
1201<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
1202<row topline="true" bottomline="true" newpage="false">
1203<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="true" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1204\begin_inset Text
1205
1206\layout Standard
1207
1208
1209\series bold
1210Character
1211\end_inset
1212</cell>
1213<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1214\begin_inset Text
1215
1216\layout Standard
1217
1218
1219\series bold
1220What is this?
1221\end_inset
1222</cell>
1223<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1224\begin_inset Text
1225
1226\layout Standard
1227
1228
1229\series bold
1230MySQL DB field
1231\end_inset
1232</cell>
1233<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1234\begin_inset Text
1235
1236\layout Standard
1237
1238
1239\series bold
1240Recommended column type
1241\end_inset
1242</cell>
1243<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1244\begin_inset Text
1245
1246\layout Standard
1247
1248
1249\series bold
1250Example
1251\end_inset
1252</cell>
1253</row>
1254<row topline="true" bottomline="false" newpage="false">
1255<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1256\begin_inset Text
1257
1258\layout Standard
1259
1260A
1261\end_inset
1262</cell>
1263<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1264\begin_inset Text
1265
1266\layout Standard
1267
1268User agent
1269\end_inset
1270</cell>
1271<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1272\begin_inset Text
1273
1274\layout Standard
1275
1276agent
1277\end_inset
1278</cell>
1279<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1280\begin_inset Text
1281
1282\layout Standard
1283
1284varchar(255)
1285\end_inset
1286</cell>
1287<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1288\begin_inset Text
1289
1290\layout Standard
1291
1292Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0)
1293\end_inset
1294</cell>
1295</row>
1296<row topline="true" bottomline="false" newpage="false">
1297<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1298\begin_inset Text
1299
1300\layout Standard
1301
1302b
1303\end_inset
1304</cell>
1305<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1306\begin_inset Text
1307
1308\layout Standard
1309
1310Bytes transfered
1311\end_inset
1312</cell>
1313<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1314\begin_inset Text
1315
1316\layout Standard
1317
1318bytes_sent
1319\end_inset
1320</cell>
1321<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1322\begin_inset Text
1323
1324\layout Standard
1325
1326int unsigned
1327\end_inset
1328</cell>
1329<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1330\begin_inset Text
1331
1332\layout Standard
1333
133432561
1335\end_inset
1336</cell>
1337</row>
1338<row topline="true" bottomline="false" newpage="false">
1339<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1340\begin_inset Text
1341
1342\layout Standard
1343
1344c
1345\end_inset
1346</cell>
1347<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1348\begin_inset Text
1349
1350\layout Standard
1351
1352Text of cookie
1353\end_inset
1354</cell>
1355<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1356\begin_inset Text
1357
1358\layout Standard
1359
1360cookie
1361\end_inset
1362</cell>
1363<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1364\begin_inset Text
1365
1366\layout Standard
1367
1368varchar(255)
1369\end_inset
1370</cell>
1371<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1372\begin_inset Text
1373
1374\layout Standard
1375
1376Apache=sdyn.fooonline.net.130051007102700823
1377\end_inset
1378</cell>
1379</row>
1380<row topline="true" bottomline="false" newpage="false">
1381<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1382\begin_inset Text
1383
1384\layout Standard
1385
1386f
1387\end_inset
1388</cell>
1389<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1390\begin_inset Text
1391
1392\layout Standard
1393
1394\end_inset
1395</cell>
1396<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1397\begin_inset Text
1398
1399\layout Standard
1400
1401request_file
1402\end_inset
1403</cell>
1404<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1405\begin_inset Text
1406
1407\layout Standard
1408
1409varchar(255)
1410\end_inset
1411</cell>
1412<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1413\begin_inset Text
1414
1415\layout Standard
1416
1417\end_inset
1418</cell>
1419</row>
1420<row topline="true" bottomline="false" newpage="false">
1421<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1422\begin_inset Text
1423
1424\layout Standard
1425
1426H
1427\end_inset
1428</cell>
1429<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1430\begin_inset Text
1431
1432\layout Standard
1433
1434HTTP request protocol
1435\end_inset
1436</cell>
1437<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1438\begin_inset Text
1439
1440\layout Standard
1441
1442request_protocol
1443\end_inset
1444</cell>
1445<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1446\begin_inset Text
1447
1448\layout Standard
1449
1450varchar(10)
1451\end_inset
1452</cell>
1453<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1454\begin_inset Text
1455
1456\layout Standard
1457
1458HTTP/1.1
1459\end_inset
1460</cell>
1461</row>
1462<row topline="true" bottomline="false" newpage="false">
1463<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1464\begin_inset Text
1465
1466\layout Standard
1467
1468h
1469\end_inset
1470</cell>
1471<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1472\begin_inset Text
1473
1474\layout Standard
1475
1476Name of remote host
1477\end_inset
1478</cell>
1479<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1480\begin_inset Text
1481
1482\layout Standard
1483
1484remote_host
1485\end_inset
1486</cell>
1487<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1488\begin_inset Text
1489
1490\layout Standard
1491
1492varchar(50)
1493\end_inset
1494</cell>
1495<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1496\begin_inset Text
1497
1498\layout Standard
1499
1500blah.foobar.com
1501\end_inset
1502</cell>
1503</row>
1504<row topline="true" bottomline="false" newpage="false">
1505<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1506\begin_inset Text
1507
1508\layout Standard
1509
1510I
1511\end_inset
1512</cell>
1513<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1514\begin_inset Text
1515
1516\layout Standard
1517
1518Ident user info
1519\end_inset
1520</cell>
1521<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1522\begin_inset Text
1523
1524\layout Standard
1525
1526remote_logname
1527\end_inset
1528</cell>
1529<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1530\begin_inset Text
1531
1532\layout Standard
1533
1534varchar(50)
1535\end_inset
1536</cell>
1537<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1538\begin_inset Text
1539
1540\layout Standard
1541
1542bobby
1543\end_inset
1544</cell>
1545</row>
1546<row topline="true" bottomline="false" newpage="false">
1547<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1548\begin_inset Text
1549
1550\layout Standard
1551
1552m
1553\end_inset
1554</cell>
1555<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1556\begin_inset Text
1557
1558\layout Standard
1559
1560HTTP request method
1561\end_inset
1562</cell>
1563<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1564\begin_inset Text
1565
1566\layout Standard
1567
1568request_method
1569\end_inset
1570</cell>
1571<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1572\begin_inset Text
1573
1574\layout Standard
1575
1576varchar(6)
1577\end_inset
1578</cell>
1579<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1580\begin_inset Text
1581
1582\layout Standard
1583
1584GET
1585\end_inset
1586</cell>
1587</row>
1588<row topline="true" bottomline="false" newpage="false">
1589<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1590\begin_inset Text
1591
1592\layout Standard
1593
1594P
1595\end_inset
1596</cell>
1597<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1598\begin_inset Text
1599
1600\layout Standard
1601
1602httpd child PID
1603\end_inset
1604</cell>
1605<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1606\begin_inset Text
1607
1608\layout Standard
1609
1610child_pid
1611\end_inset
1612</cell>
1613<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1614\begin_inset Text
1615
1616\layout Standard
1617
1618smallint unsigned
1619\end_inset
1620</cell>
1621<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1622\begin_inset Text
1623
1624\layout Standard
1625
16263215
1627\end_inset
1628</cell>
1629</row>
1630<row topline="true" bottomline="false" newpage="false">
1631<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1632\begin_inset Text
1633
1634\layout Standard
1635
1636p
1637\end_inset
1638</cell>
1639<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1640\begin_inset Text
1641
1642\layout Standard
1643
1644httpd port
1645\end_inset
1646</cell>
1647<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1648\begin_inset Text
1649
1650\layout Standard
1651
1652server_port
1653\end_inset
1654</cell>
1655<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1656\begin_inset Text
1657
1658\layout Standard
1659
1660smallint unsigned
1661\end_inset
1662</cell>
1663<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1664\begin_inset Text
1665
1666\layout Standard
1667
166880
1669\end_inset
1670</cell>
1671</row>
1672<row topline="true" bottomline="false" newpage="false">
1673<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1674\begin_inset Text
1675
1676\layout Standard
1677
1678R
1679\end_inset
1680</cell>
1681<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1682\begin_inset Text
1683
1684\layout Standard
1685
1686Referer
1687\end_inset
1688</cell>
1689<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1690\begin_inset Text
1691
1692\layout Standard
1693
1694referer
1695\end_inset
1696</cell>
1697<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1698\begin_inset Text
1699
1700\layout Standard
1701
1702varchar(255)
1703\end_inset
1704</cell>
1705<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1706\begin_inset Text
1707
1708\layout Standard
1709
1710http://www.biglinkstoyou.com/linkpage.html
1711\end_inset
1712</cell>
1713</row>
1714<row topline="true" bottomline="false" newpage="false">
1715<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1716\begin_inset Text
1717
1718\layout Standard
1719
1720r
1721\end_inset
1722</cell>
1723<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1724\begin_inset Text
1725
1726\layout Standard
1727
1728Request in full form
1729\end_inset
1730</cell>
1731<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1732\begin_inset Text
1733
1734\layout Standard
1735
1736request_line
1737\end_inset
1738</cell>
1739<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1740\begin_inset Text
1741
1742\layout Standard
1743
1744varchar(255)
1745\end_inset
1746</cell>
1747<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1748\begin_inset Text
1749
1750\layout Standard
1751
1752GET /books-cycroad.html HTTP/1.1
1753\end_inset
1754</cell>
1755</row>
1756<row topline="true" bottomline="false" newpage="false">
1757<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1758\begin_inset Text
1759
1760\layout Standard
1761
1762S
1763\end_inset
1764</cell>
1765<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1766\begin_inset Text
1767
1768\layout Standard
1769
1770Time of request in UNIX format
1771\end_inset
1772</cell>
1773<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1774\begin_inset Text
1775
1776\layout Standard
1777
1778time_stamp
1779\end_inset
1780</cell>
1781<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1782\begin_inset Text
1783
1784\layout Standard
1785
1786int unsigned
1787\end_inset
1788</cell>
1789<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1790\begin_inset Text
1791
1792\layout Standard
1793
17941005598029
1795\end_inset
1796</cell>
1797</row>
1798<row topline="true" bottomline="false" newpage="false">
1799<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1800\begin_inset Text
1801
1802\layout Standard
1803
1804s
1805\end_inset
1806</cell>
1807<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1808\begin_inset Text
1809
1810\layout Standard
1811
1812HTTP status of request
1813\end_inset
1814</cell>
1815<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1816\begin_inset Text
1817
1818\layout Standard
1819
1820status
1821\end_inset
1822</cell>
1823<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1824\begin_inset Text
1825
1826\layout Standard
1827
1828smallint unsigned
1829\end_inset
1830</cell>
1831<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1832\begin_inset Text
1833
1834\layout Standard
1835
1836404
1837\end_inset
1838</cell>
1839</row>
1840<row topline="true" bottomline="false" newpage="false">
1841<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1842\begin_inset Text
1843
1844\layout Standard
1845
1846T
1847\end_inset
1848</cell>
1849<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1850\begin_inset Text
1851
1852\layout Standard
1853
1854Seconds to service request
1855\end_inset
1856</cell>
1857<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1858\begin_inset Text
1859
1860\layout Standard
1861
1862request_duration
1863\end_inset
1864</cell>
1865<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1866\begin_inset Text
1867
1868\layout Standard
1869
1870smallint unsigned
1871\end_inset
1872</cell>
1873<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1874\begin_inset Text
1875
1876\layout Standard
1877
18782
1879\end_inset
1880</cell>
1881</row>
1882<row topline="true" bottomline="false" newpage="false">
1883<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1884\begin_inset Text
1885
1886\layout Standard
1887
1888t
1889\end_inset
1890</cell>
1891<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1892\begin_inset Text
1893
1894\layout Standard
1895
1896Time of request in human format
1897\end_inset
1898</cell>
1899<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1900\begin_inset Text
1901
1902\layout Standard
1903
1904request_time
1905\end_inset
1906</cell>
1907<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1908\begin_inset Text
1909
1910\layout Standard
1911
1912char(28)
1913\end_inset
1914</cell>
1915<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1916\begin_inset Text
1917
1918\layout Standard
1919
1920[02/Dec/2001:15:01:26 -0800]
1921\end_inset
1922</cell>
1923</row>
1924<row topline="true" bottomline="false" newpage="false">
1925<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1926\begin_inset Text
1927
1928\layout Standard
1929
1930U
1931\end_inset
1932</cell>
1933<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1934\begin_inset Text
1935
1936\layout Standard
1937
1938Request in simple form
1939\end_inset
1940</cell>
1941<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1942\begin_inset Text
1943
1944\layout Standard
1945
1946request_uri
1947\end_inset
1948</cell>
1949<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1950\begin_inset Text
1951
1952\layout Standard
1953
1954varchar(255)
1955\end_inset
1956</cell>
1957<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1958\begin_inset Text
1959
1960\layout Standard
1961
1962/books-cycroad.html
1963\end_inset
1964</cell>
1965</row>
1966<row topline="true" bottomline="false" newpage="false">
1967<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1968\begin_inset Text
1969
1970\layout Standard
1971
1972u
1973\end_inset
1974</cell>
1975<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1976\begin_inset Text
1977
1978\layout Standard
1979
1980User info from HTTP authentication
1981\end_inset
1982</cell>
1983<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1984\begin_inset Text
1985
1986\layout Standard
1987
1988remote_user
1989\end_inset
1990</cell>
1991<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1992\begin_inset Text
1993
1994\layout Standard
1995
1996varchar(50)
1997\end_inset
1998</cell>
1999<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2000\begin_inset Text
2001
2002\layout Standard
2003
2004bobby
2005\end_inset
2006</cell>
2007</row>
2008<row topline="true" bottomline="true" newpage="false">
2009<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2010\begin_inset Text
2011
2012\layout Standard
2013
2014v
2015\end_inset
2016</cell>
2017<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2018\begin_inset Text
2019
2020\layout Standard
2021
2022Virtual host servicing the request
2023\end_inset
2024</cell>
2025<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2026\begin_inset Text
2027
2028\layout Standard
2029
2030virtual_host
2031\end_inset
2032</cell>
2033<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2034\begin_inset Text
2035
2036\layout Standard
2037
2038varchar(50)
2039\end_inset
2040</cell>
2041<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2042\begin_inset Text
2043
2044\layout Standard
2045
2046www.foobar.com
2047\end_inset
2048</cell>
2049</row>
2050</lyxtabular>
2051
2052\end_inset
2053
2054
2055\layout Quote
2056
2057If you have compiled mod_log_sql with WANT_SSL_LOGGING, you also get these:
2058\layout Quote
2059
2060
2061\begin_inset Tabular
2062<lyxtabular version="2" rows="4" columns="4">
2063<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
2064<column alignment="center" valignment="top" leftline="true" rightline="false" width="" special="">
2065<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
2066<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
2067<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
2068<row topline="true" bottomline="true" newpage="false">
2069<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2070\begin_inset Text
2071
2072\layout Standard
2073
2074
2075\series bold
2076Character
2077\end_inset
2078</cell>
2079<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2080\begin_inset Text
2081
2082\layout Standard
2083
2084
2085\series bold
2086MySQL DB field it activates
2087\end_inset
2088</cell>
2089<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2090\begin_inset Text
2091
2092\layout Standard
2093
2094
2095\series bold
2096MySQL DB field it activatesRecommended column type
2097\end_inset
2098</cell>
2099<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
2100\begin_inset Text
2101
2102\layout Standard
2103
2104
2105\series bold
2106Example
2107\end_inset
2108</cell>
2109</row>
2110<row topline="true" bottomline="false" newpage="false">
2111<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2112\begin_inset Text
2113
2114\layout Standard
2115
2116z
2117\end_inset
2118</cell>
2119<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2120\begin_inset Text
2121
2122\layout Standard
2123
2124ssl_cipher
2125\end_inset
2126</cell>
2127<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2128\begin_inset Text
2129
2130\layout Standard
2131
2132varchar(25)
2133\end_inset
2134</cell>
2135<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
2136\begin_inset Text
2137
2138\layout Standard
2139
2140RC4-MD5
2141\end_inset
2142</cell>
2143</row>
2144<row topline="true" bottomline="false" newpage="false">
2145<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2146\begin_inset Text
2147
2148\layout Standard
2149
2150q
2151\end_inset
2152</cell>
2153<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2154\begin_inset Text
2155
2156\layout Standard
2157
2158ssl_keysize
2159\end_inset
2160</cell>
2161<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2162\begin_inset Text
2163
2164\layout Standard
2165
2166smallint unsigned
2167\end_inset
2168</cell>
2169<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
2170\begin_inset Text
2171
2172\layout Standard
2173
217456
2175\end_inset
2176</cell>
2177</row>
2178<row topline="true" bottomline="true" newpage="false">
2179<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2180\begin_inset Text
2181
2182\layout Standard
2183
2184Q
2185\end_inset
2186</cell>
2187<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2188\begin_inset Text
2189
2190\layout Standard
2191
2192ssl_maxkeysize
2193\end_inset
2194</cell>
2195<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2196\begin_inset Text
2197
2198\layout Standard
2199
2200smallint unsigned
2201\end_inset
2202</cell>
2203<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
2204\begin_inset Text
2205
2206\layout Standard
2207
2208128
2209\end_inset
2210</cell>
2211</row>
2212</lyxtabular>
2213
2214\end_inset
2215
2216
2217\layout Section
2218
2219FAQ
2220\layout Subsection
2221
2222Why log to an SQL database?
2223\layout Standard
2224
2225To begin with, let's get it out of the way: logging to a database is not
2226 a panacea.
2227 But while there are complexities with this solution, the benefit can be
2228 substantial for certain classes of administrator or people with advanced
2229 requirements:
2230\layout Itemize
2231
2232Chores like log rotation go away, as you can DELETE records from the SQL
2233 database once they are no longer useful.
2234 For example, the excellent and popular log-analysis tool Webalizer (http://www.w
2235ebalizer.com/) does not need historic logs after it has processed them, enabling
2236 you to delete older logs.
2237
2238\layout Itemize
2239
2240People with clusters of web servers (for high availability) will benefit
2241 the most -- all their webservers can log to a single SQL database.
2242 This obviates the need to collate/interleave the many separate logfiles,
2243 which can be / highly/ problematic.
2244
2245\layout Itemize
2246
2247People acquainted with the power of SQL SELECT statements will know the
2248 flexibility of the extraction possibilities at their fingertips.
2249\layout Standard
2250
2251For example, do you want to see all your 404's? Do this:
2252\layout LyX-Code
2253
2254select remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp)
2255 from acc_log_tbl where status=404 order by time_stamp;
2256\layout LyX-Code
2257
2258+-----------------------------------+--------+--------------------------+-------
2259-----+---------------------------
2260\layout LyX-Code
2261
2262+ remote_host | status | request_uri
2263 | bytes_sent | from_unixtime(time_stamp)|
2264\layout LyX-Code
2265
2266+-----------------------------------+--------+--------------------------+-------
2267-----+---------------------------
2268\layout LyX-Code
2269
2270| marge.mmm.co.uk | 404 | /favicon.ico | 321
2271 | 2001-11-20 02:30:56 |
2272\layout LyX-Code
2273
2274| 62.180.239.251 | 404 | /favicon.ico | 333
2275 | 2001-11-20 02:45:25 |
2276\layout LyX-Code
2277
2278| 212.234.12.66 | 404 | /favicon.ico | 321
2279 | 2001-11-20 03:01:00 |
2280\layout LyX-Code
2281
2282| 212.210.78.254 | 404 | /favicon.ico | 333
2283 | 2001-11-20 03:26:05 |
2284\layout LyX-Code
2285
2286+-----------------------------------+--------+--------------------------+-------
2287-----+---------------------------+
2288\layout Standard
2289
2290Or do you want to see how many bytes you've sent within a certain directory
2291 or site? Do this:
2292\layout LyX-Code
2293
2294select request_uri,sum(bytes_sent) as bytes,count(request_uri) as howmany
2295 from acc_log_tbl where request_uri like '%mod_log_sql%' group by request_uri
2296 order by howmany desc;
2297\layout LyX-Code
2298
2299+-----------------------------------+---------+---------+
2300\layout LyX-Code
2301
2302| request_uri | bytes | howmany |
2303\layout LyX-Code
2304
2305+-----------------------------------+---------+---------+
2306\layout LyX-Code
2307
2308| /mod_log_sql/style_1.css | 157396 | 1288 |
2309\layout LyX-Code
2310
2311| /mod_log_sql/ | 2514337 | 801 |
2312\layout LyX-Code
2313
2314| /mod_log_sql/mod_log_sql.tar.gz | 9769312 | 456 |
2315\layout LyX-Code
2316
2317| /mod_log_sql/faq.html | 5038728 | 436 |
2318\layout LyX-Code
2319
2320| /mod_log_sql/INSTALL | 1196161 | 202 |
2321\layout LyX-Code
2322
2323| /mod_log_sql/directives.html | 1096821 | 171 |
2324\layout LyX-Code
2325
2326| /mod_log_sql/CHANGELOG | 424481 | 107 |
2327\layout LyX-Code
2328
2329| /mod_log_sql/README | 796072 | 97 |
2330\layout LyX-Code
2331
2332| /mod_log_sql/directives-old.html | 42480 | 6 |
2333\layout LyX-Code
2334
2335| /mod_log_sql/index.html | 9531 | 3 |
2336\layout LyX-Code
2337
2338+-----------------------------------+---------+---------+
2339\layout Standard
2340
2341Or maybe you want to see who's linking to you? Do this:
2342\layout LyX-Code
2343
2344select count(referer) as num,referer from acc_log_tbl where request_uri='/mod_lo
2345g_sql/' group by referer order by num desc;
2346\layout LyX-Code
2347
2348+-----+-------------------------------------------------------------------------
2349------+
2350\layout LyX-Code
2351
2352| num | referer
2353 |
2354\layout LyX-Code
2355
2356+-----+-------------------------------------------------------------------------
2357------+
2358\layout LyX-Code
2359
2360| 271 | http://freshmeat.net/projects/mod_log_sql/
2361 |
2362\layout LyX-Code
2363
2364| 96 | http://modules.apache.org/search?id=339
2365 |
2366\layout LyX-Code
2367
2368| 48 | http://freshmeat.net/
2369 |
2370\layout LyX-Code
2371
2372| 8 | http://freshmeat.net
2373 |
2374\layout LyX-Code
2375
2376| 7 | http://freshmeat.net/daily/2001/11/30/
2377 |
2378\layout LyX-Code
2379
2380| 6 | http://freshmeat.net/daily/2001/11/20/
2381 |
2382\layout LyX-Code
2383
2384+-----+-------------------------------------------------------------------------
2385------+
2386\layout Standard
2387
2388As you can see, there are myriad possibilities that can be constructed with
2389 the wonderful SQL SELECT statement.
2390 Logging to an SQL database can be really quite useful!
2391\layout Subsection
2392
2393Why use MySQL? Are there alternatives?
2394\layout Standard
2395
2396MySQL is a robust, free, and very powerful production-quality database engine.
2397 It is well supported and comes with detailed documentation.
2398 Many 3rd-party software pacakges (e.g.
2399 Slashcode, the engine that powers Slashdot) run exclusively with MySQL.
2400 In other words, you will belong to a very robust and well-supported community
2401 by choosing MySQL.
2402\layout Standard
2403
2404That being said, there are alternatives.
2405 PostgreSQL is probably MySQL's leading "competitor" in the free database
2406 world.
2407 There is also an excellent module available for Apache to permit logging
2408 to a PostgreSQL database, called
2409\begin_inset LatexCommand \url[pgLOGd]{(http://www.digitalstratum.com/pglogd/)}
2410
2411\end_inset
2412
2413.
2414\layout Subsection
2415
2416Is this code production-ready?
2417\layout Standard
2418
2419By all accounts it is.
2420 It is known to work without a problem on many-thousands-of-hits-per-day
2421 webservers.
2422 Does that mean it is 100% bug free? Well, no software is.
2423 But it is well-tested and /believed/ to be fully compatible with production
2424 environments.
2425 (The usual disclaimers apply.
2426 This software is provided without warranty of any kind.)
2427\layout Subsection
2428
2429How well does it perform?
2430\layout Standard
2431
2432mod_log_sql scales to very high loads.
2433 Apache 1.3.22 + mod_log_sql was benchmarked using the "ab" (Apache Bench)
2434 program that comes with the Apache distribution; here are the results.
2435\layout Standard
2436
2437Overall configuration:
2438\layout Itemize
2439
2440Machine A: Apache webserver
2441\layout Itemize
2442
2443Machine B: MySQL server
2444\layout Itemize
2445
2446Machines A and B connected with 100Mbps Ethernet
2447\layout Standard
2448
2449Webserver configuration:
2450\layout Itemize
2451
2452Celeron 400 128 MB RAM IDE storage
2453\layout Standard
2454
2455Apache configuration:
2456\layout LyX-Code
2457
2458Timeout 300
2459\layout LyX-Code
2460
2461KeepAlive On
2462\layout LyX-Code
2463
2464MaxKeepAliveRequests 100
2465\layout LyX-Code
2466
2467KeepAliveTimeout 15
2468\layout LyX-Code
2469
2470MinSpareServers 5
2471\layout LyX-Code
2472
2473StartServers 10
2474\layout LyX-Code
2475
2476MaxSpareServers 15
2477\layout LyX-Code
2478
2479MaxClients 256
2480\layout LyX-Code
2481
2482MaxRequestsPerChild 5000
2483\layout LyX-Code
2484
2485LogSQLTransferLogFormat AbHhmRSsTUuvc
2486\layout LyX-Code
2487
2488LogSQLWhichCookie Clicks
2489\layout LyX-Code
2490
2491CookieTracking on
2492\layout LyX-Code
2493
2494CookieName Clicks
2495\layout Standard
2496
2497"ab" commandline:
2498\layout LyX-Code
2499
2500./ab -c 10 -t 20 -v 2 -C Clicks=ab_run http://www.hostname.com/target
2501\layout Standard
2502
2503( 10 concurrent requests; 20 second test; setting a cookie "Clicks=ab_run";
2504 target = the mod_log_sql homepage.
2505 )
2506\layout Standard
2507
2508Ten total ab runs were conducted: five with MySQL logging enabled, and five
2509 with all MySQL directives commented out of httpd.conf.
2510 Then each five were averaged.
2511 The results:
2512\layout Itemize
2513
2514Average of five runs employing MySQL
2515\emph on
2516and
2517\emph default
2518 standard text logging: 139.01 requests per second, zero errors.
2519\layout Itemize
2520
2521Average of five runs employing
2522\emph on
2523only
2524\emph default
2525 standard text logging: 139.96 requests per second, zero errors.
2526\layout Standard
2527
2528In other words, any rate-limiting effects on this particular hardware setup
2529 are not caused by MySQL.
2530 Note that although this very simple webserver setup is hardly cutting-edge
2531 -- it is, after all, a fairly small machine -- 139 requests per second
2532 equal over twelve million hits per day
2533\emph on
2534.
2535\layout Standard
2536
2537If you run this benchmark yourself, take note of three things:
2538\layout Enumerate
2539
2540Use a target URL that is on your own webserver :-).
2541
2542\layout Enumerate
2543
2544Wait until all your connections are closed out between runs; after several
2545 thousand requests your TCP/IP stack will be filled with hundreds of connections
2546 in TIME_WAIT that need to close.
2547 Do a "netstat -t|wc -l" on the webserver to see.
2548 If you don't wait, you can expect to see a lot of messages like "ip_conntrack:
2549 table full, dropping packet" in your logs.
2550 (This has nothing to do with mod_log_sql, this is simply the nature of
2551 the TCP/IP stack in the Linux kernel.)
2552\layout Enumerate
2553
2554When done with your runs, clean these many thousands of requests out of
2555 your database:
2556\layout LyX-Code
2557
2558mysql> delete from access_log where agent like 'ApacheBench%'; mysql> optimize
2559 table access_log;
2560\layout Subsection
2561
2562Who's using mod_log_sql?
2563\layout Standard
2564
2565Good question! It would be great to find out! If you are a production-level
2566 mod_log_sql user, please contact
2567\begin_inset LatexCommand \url[the maintainer, Chris Powell]{chris@grubbybaby.com}
2568
2569\end_inset
2570
2571so that you can be mentioned here.
2572\layout Subsection
2573
2574How do I extract the data in a format that my analysis tool can understand?
2575\layout Standard
2576
2577mod_log_sql would be virtually useless if there weren't a way for you to
2578 extract the data from your database in a somewhat meaningful fashion.
2579 To that end there's a Perl script enclosed with the distribution.
2580 That script (make_combined_log.pl) is designed to extract N-many days worth
2581 of access logs and provide them in a Combined Log Format output.
2582 You can use this very tool right in /etc/crontab to extract logs on a regular
2583 basis so that your favorite web analysis tool can read them.
2584 Or you can examine the Perl code to construct your own custom tool.
2585\layout Standard
2586
2587For example, let's say that you want your web statistics updated once per
2588 day in the wee hours of the morning.
2589 A good way to accomplish that would be the following entries in /etc/crontab:
2590\layout LyX-Code
2591
2592# Generate the temporary apache logs from the MySQL database (for webalizer)
2593
2594\layout LyX-Code
2595
259605 04 * * * root /usr/local/sbin/make_combined_log.pl 1 www.grubbybaby.com
2597 > /var/log/httpd/mysql-grubbybaby
2598\layout LyX-Code
2599
2600# Run webalizer on httpd log
2601\layout LyX-Code
2602
260330 04 * * * root /usr/local/bin/webalizer -c /etc/webalizer.conf; rm -f /var/log/
2604httpd/mysql-grubbybaby
2605\layout Standard
2606
2607Or if you have a newer system that puts files in /etc/cron.daily etc., create
2608 a file called
2609\begin_inset Quotes sld
2610\end_inset
2611
2612webalizer
2613\begin_inset Quotes srd
2614\end_inset
2615
2616 in the cron.____ subdir of your choice.
2617 Use the following as the contents of your file, and make sure to chmod
2618 755 it when done.
2619\layout LyX-Code
2620
2621#!/bin/sh
2622\layout LyX-Code
2623
2624/usr/local/sbin/make_combined_log.pl 1 www.yourdomain.com > /var/log/httpd/templog
2625\layout LyX-Code
2626
2627/usr/local/bin/webalizer -q -c /etc/webalizer.conf
2628\layout LyX-Code
2629
2630rm -f /var/log/httpd/templog
2631\layout Standard
2632
2633See? Easy.
2634\layout Subsection
2635
2636Why doesn't the module also replace the Apache ErrorLog?
2637\layout Standard
2638
2639There are circumstances when that would be quite unwise -- for example,
2640 if Apache could not reach the MySQL server for some reason and needed to
2641 log that fact.
2642 Without a text-based error log you'd never know anything was wrong, because
2643 Apache would be trying to log a database connection error to the database...
2644 you get the point.
2645\layout Standard
2646
2647Error logs are usually not very high-traffic and are really best left as
2648 text files on a web server machine.
2649\layout Subsection
2650
2651
2652\begin_inset LatexCommand \label{sec:cookie}
2653
2654\end_inset
2655
2656How can I log mod_usertrack cookies?
2657\layout Standard
2658
2659A number of people like to log mod_usertrack cookies in their Apache TransferLog
2660 to aid in understanding their visitors' clickstreams.
2661 This is accomplished, for example, with a statement as follows:
2662\layout LyX-Code
2663
2664LogFormat "%h %l %u %t
2665\backslash
2666"%r
2667\backslash
2668" %s %b
2669\backslash
2670"%{Referer}i
2671\backslash
2672"
2673\backslash
2674"%{User-Agent}i
2675\backslash
2676""
2677\backslash
2678"%{cookie}n
2679\backslash
2680""
2681\layout Standard
2682
2683Naturally it would be nice for mod_log_sql to permit the admin to log the
2684 cookie data as well, so as of version 1.10 you can do this.
2685 You need to have already compiled mod_usertrack into httpd -- it's one
2686 of the standard Apache modules.
2687\layout Standard
2688
2689First make sure you have a column called "cookie" in the MySQL database
2690 to hold the cookies, which can be done as follows if you already have a
2691 working database:
2692\layout LyX-Code
2693
2694alter table acc_log_tbl add column cookie varchar(255);
2695\layout Standard
2696
2697Next configure your server to set usertracking cookies as follows, and make
2698 sure you include the new 'c' directive in your LogSQLTransferLogFormat,
2699 which activates cookie logging.
2700 Here's an example:
2701\layout LyX-Code
2702
2703<VirtualHost 1.2.3.4>
2704\layout LyX-Code
2705
2706 CookieTracking on
2707\layout LyX-Code
2708
2709 CookieStyle Cookie
2710\layout LyX-Code
2711
2712 CookieName Foobar
2713\layout LyX-Code
2714
2715 LogSQLTransferLogFormat huSUsbTvRAc
2716\layout LyX-Code
2717
2718 LogSQLWhichCookie Foobar
2719\layout LyX-Code
2720
2721</VirtualHost>
2722\layout Standard
2723
2724The first three lines configure mod_usertrack to create a COOKIE (RFC 2109)
2725 format cookie called Foobar.
2726 The last two lines tell mod_log_sql to log cookies named Foobar.
2727 You have to choose which cookie to log because more than one cookie can/will
2728 be sent to the server by the client.
2729\layout Standard
2730
2731FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that even
2732 newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2 (RFC 2965)
2733 format.
2734 Just stick with the standard COOKIE format and you'll be fine.
2735\layout Standard
2736
2737Perform some hits on your server and run a select:
2738\layout LyX-Code
2739
2740mysql> select request_uri,cookie from access_log where cookie is not null;
2741\layout LyX-Code
2742
2743+--------------------------------------------+----------------------------------
2744------------------+
2745\layout LyX-Code
2746
2747| request_uri |
2748 cookie |
2749\layout LyX-Code
2750
2751+--------------------------------------------+----------------------------------
2752------------------+
2753\layout LyX-Code
2754
2755| /mod_log_sql/ | ool-18e4.dyn.optonline.net.1300510071
275602700823 |
2757\layout LyX-Code
2758
2759| /mod_log_sql/usa.gif | ool-18e4.dyn.optonline.net.13005100710
27602700823 |
2761\layout LyX-Code
2762
2763| /mod_log_sql/style_1.css | ool-18e4.dyn.optonline.net.13005100710
27642700823 |
2765\layout LyX-Code
2766
2767...etc...
2768\layout Subsection
2769
2770What if I want to log more than one cookie?
2771\layout Standard
2772
2773No problem.
2774 As of version 1.17, you have a choice.
2775 If you are just interested in a single cookie, follow the instructions
2776 in section
2777\begin_inset LatexCommand \ref{sec:cookie}
2778
2779\end_inset
2780
2781 above.
2782 That cookie will be logged to a column in the regular access_log table.
2783\layout Standard
2784
2785However, if you need to log multiple cookies, you'll employ the LogSQLWhichCooki
2786es (note the plural) directive.
2787 The cookies you specify will be logged to a separate table, and entries
2788 in that table will be linked to the regular access_log entries via the
2789 unique ID that is supplied by mod_unique_id.
2790 Without mod_unique_id the information will still be logged but you will
2791 be unable to correlate which cookies go with which access-requests.
2792\layout Standard
2793
2794LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict, but
2795 you're better off choosing the one you need.
2796\layout Subsection
2797
2798What are the SSL logging features, and how do I activate them?
2799\layout Standard
2800
2801If you run an SSL-enabled server you may benefit from logging some SSL details.
2802 mod_log_sql now supports this ability.
2803 By adding certain characters to your LogSQLTransferLogFormat string you
2804 can tell mod_log_sql to log the SSL cipher, the SSL keysize of the connection,
2805 and the Max-keysize that was available.
2806 This would let you tell, for example, which clients were using only export-grad
2807e security to access your secure software area.
2808\layout Standard
2809
2810You can compile mod_log_sql with SSL logging support if you have the right
2811 packages installed.
2812 If you already have an SSL-enabled Apache then you by definition have the
2813 correct packages already installed: OpenSSL and mod_ssl.
2814\layout Standard
2815
2816You need to ensure that your database is set up to log the SSL data.
2817 Issue the following commands to MySQL once you have your basic access_log
2818 table built:
2819\layout LyX-Code
2820
2821alter table access_log add column ssl_cipher varchar(25);
2822\layout LyX-Code
2823
2824alter table access_log add column ssl_keysize smallint unsigned;
2825\layout LyX-Code
2826
2827alter table access_log add column ssl_maxkeysize smallint unsigned;
2828\layout Standard
2829
2830Finally configure httpd.conf to activate the SSL fields.
2831 Note that this is only meaningful in a VirtualHost that is set up for SSL.
2832\layout LyX-Code
2833
2834<VirtualHost 1.2.3.4:443>
2835\layout LyX-Code
2836
2837 LogSQLTransferLogFormat AbHhmRSsTUuvcQqz
2838\layout LyX-Code
2839
2840</VirtualHost>
2841\layout Standard
2842
2843The last three characters (Qqz) in the directive are the SSL ones; see the
2844 directives documentation for details.
2845\layout Standard
2846
2847Perform some hits on your server and run a select:
2848\layout LyX-Code
2849
2850mysql> select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize
2851 from access_log where ssl_cipher is not null;
2852\layout LyX-Code
2853
2854+--------------------------+----------------------------------------------------
2855+------------+-------------+----------------+
2856\layout LyX-Code
2857
2858| remote_host | request_uri
2859 | ssl_cipher | ssl_keysize | ssl_maxkeysize |
2860\layout LyX-Code
2861
2862+--------------------------+----------------------------------------------------
2863+------------+-------------+----------------+
2864\layout LyX-Code
2865
2866| 216.190.52.4 | /dir/somefile.html
2867 | RC4-MD5 | 128 | 128 |
2868\layout LyX-Code
2869
2870| 216.190.52.4 | /dir/somefile.gif
2871 | RC4-MD5 | 128 | 128 |
2872\layout LyX-Code
2873
2874| 216.190.52.4 | /dir/somefile.jpg
2875 | RC4-MD5 | 128 | 128 |
2876\layout LyX-Code
2877
2878...etc...
2879\layout Subsection
2880
2881Does mod_log_sql connect to MySQL via TCP/IP or a socket?
2882\layout Standard
2883
2884It depends! Actually this isn't determined by mod_log_sql.
2885 mod_log_sql relies on a connection command that is supplied in the MySQL
2886 API, and that command is somewhat intelligent.
2887 When mod_log_sql issues the connect command to MySQL, this intelligent
2888 connect command uses sockets to communicate with MySQL if the specified
2889 MySQL database is on the same machine (because sockets are more efficient
2890 than TCP/IP).
2891 However, if the specified MySQL db is on a different machine, mod_log_sql
2892 connects using TCP/IP.
2893 You don't have any control of which methodology is used.
2894\layout Standard
2895
2896You do have control over where mod_log_sql looks for the socket.
2897 The LogSQLSocketFile runtime configuration directive overrides the default
2898 of "/var/lib/mysql/mysql.sock" to whatever you wish.
2899 (Applies to mod_log_sql 1.16 or later only.)
2900\layout Subsection
2901
2902Why do I occasionally see a "connection lost, attempting reconnect" message
2903 in my error-log?
2904\layout Standard
2905
2906This message may appear every now and then in your Apache error log, especially
2907 on very lightly loaded servers.
2908 This doesn't mean that anything is necessarily wrong.
2909 Within each httpd child process, mod_log_sql will open (and keep open)
2910 a connection to the MySQL server.
2911 MySQL, however, will close connections that haven't been used in a while;
2912 the default timeout is 8 hours.
2913 When this occurs, mod_log_sql will notice and re-open the connection.
2914 That event is what is being logged, and looks like this:
2915\layout LyX-Code
2916
2917[Thu Dec 13 05:42:18 2001] [error] mod_log_sql: connection lost, attempting
2918 reconnect
2919\layout LyX-Code
2920
2921[Thu Dec 13 05:42:18 2001] [error] mod_log_sql: reconnect successful
2922\layout Standard
2923
2924Reference:
2925\begin_inset LatexCommand \url[MySQL documentation]{http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away}
2926
2927\end_inset
2928
2929
2930\layout Subsection
2931
2932Does mod_log_sql work with Apache 2.x?
2933\layout Standard
2934
2935As of this writing, no.
2936 The Apache Group significantly altered the module API with the release
2937 of Apache 2.0.
2938 All modules written for 1.3, including mod_log_sql, will not work with 2.0.
2939\layout Standard
2940
2941mod_log_sql will eventually be ported to Apache 2.x, but not immediately.
2942 It is going to take some time, and there are other features that have higher
2943 priority.
2944 Please sign up for the announcements list (on the main website) or monitor
2945 the website for updates to learn when the port (and other releases) are
2946 available.
2947\layout Standard
2948
2949<OPINION>If you're a *NIX user, stick with Apache 1.3.x for now.
2950 Major modules like mod_ssl and PHP are not even ready for 2.0 yet, and the
2951 main benefits in 2.0 are for Win32 users anyway.
2952 Apache 1.3.x is rock-stable and performs equally well on *NIX as 2.0.</OPINION>
2953\layout Subsection
2954
2955I have discovered a bug.
2956 Who can I contact?
2957\layout Standard
2958
2959Please contact
2960\begin_inset LatexCommand \url[the maintainer]{chris@grubbybaby.com}
2961
2962\end_inset
2963
2964! Your comments, suggestions, bugfixes, bug catches, and usage testimonials
2965 are always welcome.
2966 As free software, mod_log_sql is intended to be a community effort -- any
2967 code contributions or other ideas will be fully and openly credited, of
2968 course.
2969\the_end
diff --git a/Documentation/documentation.tex b/Documentation/documentation.tex
new file mode 100644
index 0000000..0eb6359
--- /dev/null
+++ b/Documentation/documentation.tex
@@ -0,0 +1,1534 @@
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}