summaryrefslogtreecommitdiffstats
path: root/Documentation
diff options
context:
space:
mode:
Diffstat (limited to 'Documentation')
-rw-r--r--Documentation/documentation.lyx1488
1 files changed, 1177 insertions, 311 deletions
diff --git a/Documentation/documentation.lyx b/Documentation/documentation.lyx
index 4fc8020..d15053b 100644
--- a/Documentation/documentation.lyx
+++ b/Documentation/documentation.lyx
@@ -57,74 +57,83 @@ http://www.grubbybaby.com/mod_log_sql/
57Summary 57Summary
58\layout Standard 58\layout Standard
59 59
60This Apache module will permit you to log Apache accesses (and a lot of 60This Apache module will permit you to log to a SQL database; it can log
61 related information) to a SQL database. 61 each access request as well as data associated with each request: cookies,
62 Unlike logging to a flat text file (which is standard in Apache), there 62 notes, and inbound/outbound headers.
63 are a lot of powerful advantages to logging to SQL. 63 Unlike logging to a flat text file -- which is standard in Apache -- a
64 This module can either replace or happily coexist with Apache's text file 64 SQL-based log exhibits tremendous flexibility and power of data extraction.
65 logging facility. 65 (See section
66\begin_inset LatexCommand \ref{sub:why}
67
68\end_inset
69
70 in the FAQ for further discussion and examples of the advantages to SQL.)
71\layout Standard
72
73This module can either replace or happily coexist with mod_log_config, Apache's
74 text file logging facility.
75 In addition to being more configurable than the standard module, mod_log_sql
76 is much more flexible.
66\layout Subsection 77\layout Subsection
67 78
68Approach 79Approach
69\layout Standard 80\layout Standard
70 81
71This project was formerly known as mod_log_mysql. 82This project was formerly known as
72 It has been renamed to mod_log_sql in order to reflect the project goal 83\begin_inset Quotes eld
73 of database-inspecificity. 84\end_inset
74 The module currently supports MySQL, and development for other database 85
75 backends is underway. 86mod_log_mysql.
87\begin_inset Quotes erd
88\end_inset
89
90 It was renamed
91\begin_inset Quotes eld
92\end_inset
93
94mod_log_sql
95\begin_inset Quotes erd
96\end_inset
97
98 in order to reflect the project goal of database-inspecificity.
99 The module currently supports MySQL, but support for other database backends
100 is underway.
76\layout Standard 101\layout Standard
77 102
78In order to save speed and overhead, links are kept alive in between queries. 103In order to save speed and overhead, links are kept alive in between queries.
79 This module uses one SQL link per httpd child. 104 This module uses one dedicated SQL link per httpd child, opened by each
105 child process when it is born.
80 Among other things, this means that this module supports logging into only 106 Among other things, this means that this module supports logging into only
81 one MySQL server, and for now, also, only one SQL database. 107 one MySQL server, and for now, also, only one SQL database.
82 But that's a small tradeoff compared to the blinding speed of this module. 108 But that's a small tradeoff compared to the blinding speed of this module.
109 Error reporting is robust throughout the module and will inform the administrat
110or of database issues in the Apache
111\noun on
112ErrorLog
113\noun default
114 for the server/virtual server.
83\layout Standard 115\layout Standard
84 116
85Virtual hosts are supported in the same manner they are in the regular logging 117Virtual hosts are supported in the same manner they are in the regular logging
86 modules. 118 modules.
87 You define some basic 'global' directives in the main server config, then 119 The administrator defines some basic 'global' directives in the main server
88 you define more specific 'local' directives inside each virtualhost stanza. 120 config, then defines more specific 'local' directives inside each VirtualHost
89\layout Standard 121 stanza.
90
91SQL links are opened by each child process when it is born.
92 Error reporting is robust throughout and will let you know about database
93 issues in the standard Apache error-log for the server or virtual server.
94\layout Standard 122\layout Standard
95 123
96A robust "preserve" capability has now been implemented. 124A robust "preserve" capability has now been implemented.
97 This permits the module to preserve any failed INSERT commands to a local 125 This permits the module to preserve any failed INSERT commands to a local
98 file on its machine. 126 file on its machine.
99 In any situation that the database is unavailable -- e.g. 127 In any situation that the database is unavailable -- e.g.
100 the network fails, you reboot the db host, etc. 128 the network fails or the database host is rebooted -- mod_log_sql will
101 -- mod_log_sql will note this in the error log and begin appending its 129 note this in the error log and begin appending its log entries to the preserve
102 log entries to the preserve file (which is created with the user & group 130 file (which is created with the user & group ID of the running Apache process,
103 ID of the running Apache process, e.g. 131 e.g.
104 "nobody" on many Linux installations). 132 "nobody/nobody" on many Linux installations).
105 When your database becomes available again, mod_log_sql seamlessly resumes 133 When database availablity returns, mod_log_sql seamlessly resumes logging
106 logging to it. 134 to it.
107 When convenient for the sysadmin, he/she can easily import the preserve 135 When convenient for the sysadmin, he/she can easily import the preserve
108 file into the database because it is simply a series of SQL insert statements: 136 file into the database because it is simply a series of SQL insert statements.
109\layout LyX-Code
110
111# mysql -uadminuser -p mydbname < /tmp/mysql-preserve
112\layout Subsection
113
114Supported directives
115\layout Standard
116
117Please see the web-based documentation for full explanation of all supported
118 run-time directives.
119\layout Standard
120
121http://www.grubbybaby.com/mod_log_sql/directives.html
122\layout Standard
123
124See the FAQ for some handy examples:
125\layout Standard
126
127http://www.grubbybaby.com/mod_log_sql/faq.html
128\layout Subsection 137\layout Subsection
129 138
130What gets logged by default? 139What gets logged by default?
@@ -136,11 +145,16 @@ All the data that would be contained in the "Combined Log Format" is logged
136 the log configuration based on your needs. 145 the log configuration based on your needs.
137\layout Standard 146\layout Standard
138 147
139The online documentation of the run-time directives includes a full explanation 148The documentation of the run-time directives includes a full explanation
140 of what you can log, including examples. 149 of what you can log, including examples -- see section
150\begin_inset LatexCommand \ref{sec:ConfRef}
151
152\end_inset
153
154.
141\layout Subsection 155\layout Subsection
142 156
143Minor Notes 157Miscellaneous Notes
144\layout Itemize 158\layout Itemize
145 159
146Note which directives go in the 'main server config' and which directives 160Note which directives go in the 'main server config' and which directives
@@ -149,13 +163,19 @@ Note which directives go in the 'main server config' and which directives
149\layout Itemize 163\layout Itemize
150 164
151The 'time_stamp' field is stored in an UNSIGNED INTEGER column, in the standard 165The 'time_stamp' field is stored in an UNSIGNED INTEGER column, in the standard
152 unix "seconds since 1/1/1970 12:00:00" format. 166 unix
167\begin_inset Quotes eld
168\end_inset
169
170seconds since the epoch
171\begin_inset Quotes erd
172\end_inset
173
174 format.
153 This is superior to storing the access time as a string due to size requirement 175 This is superior to storing the access time as a string due to size requirement
154s: an UNSIGNED INT type fits in 4 bytes, whereas the Apache date string 176s: an UNSIGNED INT requires 4 bytes, whereas an Apache date string -- e.g.
155 (e.g. 177 "18/Nov/2001:13:59:52 -0800" -- requires 26 bytes: those extra 22 bytes
156 "18/Nov/2001:13:59:52 -0800") requires 26 bytes -- significantly larger, 178 become significant when multiplied by thousands of accesses on a busy server.
157 and those extra 22 bytes will add up over the thousands of accesses that
158 a busy server will experience.
159 Besides, an INT type is far more flexible for comparisons, etc. 179 Besides, an INT type is far more flexible for comparisons, etc.
160\begin_deeper 180\begin_deeper
161\layout Standard 181\layout Standard
@@ -168,35 +188,42 @@ select remote_host,request_uri,from_unixtime(time_stamp) from access_log;
168\layout Standard 188\layout Standard
169 189
170The enclosed perl program 190The enclosed perl program
171\begin_inset Quotes sld 191\begin_inset Quotes eld
172\end_inset 192\end_inset
173 193
174make_combined_log.pl 194make_combined_log.pl
175\begin_inset Quotes srd 195\begin_inset Quotes srd
176\end_inset 196\end_inset
177 197
178 shows how you can extract your access records in a format that is completely 198 extracts your access log in a format that is completely compatible with
179 Combined Log Format compliant. 199 the Combined Log Format.
180 You can then feed this to your favorite web log analysis tool. 200 You can then feed this to your favorite web log analysis tool.
181\end_deeper 201\end_deeper
182\layout Itemize 202\layout Itemize
183 203
184The table's string values can be CHAR or VARCHAR, at a length of your choice. 204The table's string values can be CHAR or VARCHAR, at a length of your choice.
185 VARCHAR is superior because it truncates long strings; CHAR types are fixed-len 205 VARCHAR is superior because it truncates long strings; CHAR types are fixed-len
186gth and will be padded with spaces. 206gth and will be padded with spaces, resulting in waste.
187 Just like the time_stamp described above, that kind of space waste will 207 Just like the time_stamp issue described above, that kind of space waste
188 add up over thousands of records. 208 multiplies over thousands of records.
189\layout Itemize 209\layout Itemize
190 210
191Be careful not to go overboard setting fields to NOT NULL. 211Be careful not to go overboard setting fields to NOT NULL.
192 If a field is marked NOT NULL then it must contain data in the INSERT or 212 If a field is marked NOT NULL then it must contain data in the INSERT statement
193 the INSERT will fail, sometimes in a really mysterious way to the frustrated 213, or the INSERT will fail.
194 sysad. 214 These mysterious failures can be quite frustrating and difficult to debug.
195\layout Itemize 215\layout Itemize
196 216
197Apache normally logs numeric fields with a '-' character to mean "not applicable 217When Apache logs a numeric field, it uses a '-' character to mean
198," e.g. 218\begin_inset Quotes eld
199 bytes_sent on a request with a 304 response code. 219\end_inset
220
221not applicable,
222\begin_inset Quotes erd
223\end_inset
224
225 e.g.
226 the number of bytes returned on a 304 (unchanged) request.
200 Since '-' is an illegal character in an SQL numeric field, such fields 227 Since '-' is an illegal character in an SQL numeric field, such fields
201 are assigned the value 0 instead of '-' which, of course, makes perfect 228 are assigned the value 0 instead of '-' which, of course, makes perfect
202 sense anyway. 229 sense anyway.
@@ -226,20 +253,35 @@ Requirements
226\layout Itemize 253\layout Itemize
227 254
228A compatible system. 255A compatible system.
229 I have run mod_log_sql on Red Hat based systems (Red Hat, Mandrake). 256 mod_log_sql was authored and tested on systems based on Red Hat Linux (Red
230 These instructions should easily adapt to any modern distro. 257 Hat, Mandrake), but the module should easily adapt to any modern distribution.
258 mod_log_sql has also been ported successfully to Solaris and FreeBSD.
231\layout Itemize 259\layout Itemize
232 260
233Apache 1.2 or 1.3 installed. 261Apache 1.2 or 1.3.
234 (I run 1.3.22 and it works fine). 262 Ideally you should already have successfully compiled Apache and understand
235 You should have already successfully compiled Apache and know what you're 263 the process, but this document tries to make it simple for beginners.
236 doing there.
237\layout Itemize 264\layout Itemize
238 265
239The MySQL development headers. 266The MySQL development headers.
240 This is called different things on different distros. 267 This package is called different things on different distros.
241 For example, Red Hat 6.x called this RPM "MySQL-devel" whereas Mandrake 268 For example, Red Hat 6.x calls this RPM
242 calls it "libmysql10-devel". 269\begin_inset Quotes eld
270\end_inset
271
272MySQL-devel
273\begin_inset Quotes erd
274\end_inset
275
276 whereas Mandrake calls it
277\begin_inset Quotes eld
278\end_inset
279
280libmysql10-devel.
281\begin_inset Quotes erd
282\end_inset
283
284
243\layout Itemize 285\layout Itemize
244 286
245MySQL >= 3.23.15 configured, installed and running on either localhost or 287MySQL >= 3.23.15 configured, installed and running on either localhost or
@@ -247,22 +289,16 @@ MySQL >= 3.23.15 configured, installed and running on either localhost or
247 You should already have a basic understanding of MySQL and how it functions. 289 You should already have a basic understanding of MySQL and how it functions.
248\layout Itemize 290\layout Itemize
249 291
250Again, basic administrative skills with Apache and MySQL. 292Optionally, if you want to be able to log SSL information such as keysize
251 I try to make things as easy as possible in this file, but its purpose 293 or cipher, you need OpenSSL and mod_ssl installed.
252 is not to be an administrative tutorial.
253\layout Itemize
254
255Additionally, if you want to be able to log SSL information such as keysize
256 or cipher, you need OpenSSL and glibc-devel installed.
257 Both are available as RPMs.
258\layout Subsection 294\layout Subsection
259 295
260Platform-specific notes 296Platform-specific notes
261\layout Standard 297\layout Standard
262 298
263These installation documents assume a relatively modern GNU/Linux scenario. 299These installation documents assume a relatively modern GNU/Linux scenario.
264 mod_log_sql has been ported to other platforms; notes on compiling the 300 mod_log_sql has been ported to other platforms; following are notes on
265 module on those platforms are noted here. 301 compiling the module for those platforms.
266\layout Subsubsection 302\layout Subsubsection
267 303
268Solaris 304Solaris
@@ -353,7 +389,7 @@ contact
353 and help fill in this section. 389 and help fill in this section.
354\layout Subsection 390\layout Subsection
355 391
356Do I want a DSO? 392Do I want a DSO or a static module?
357\layout Standard 393\layout Standard
358 394
359You need to know the answer to this question before you proceed. 395You need to know the answer to this question before you proceed.
@@ -387,8 +423,6 @@ Perform all the following steps as root so that you have install privs,
387\layout Enumerate 423\layout Enumerate
388 424
389Edit Makefile for your system. 425Edit Makefile for your system.
390\newline
391
392\begin_deeper 426\begin_deeper
393\layout Standard 427\layout Standard
394 428
@@ -429,11 +463,9 @@ Instruct apxs to compile the module as a DSO.
429\layout LyX-Code 463\layout LyX-Code
430 464
431# make dso 465# make dso
432\end_deeper 466\layout Standard
433\layout Enumerate
434 467
435You should see output similar to the following: 468You should see output similar to the following:
436\begin_deeper
437\layout LyX-Code 469\layout LyX-Code
438 470
439/usr/local/Apache/bin/apxs -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -c -I/usr/incl... 471/usr/local/Apache/bin/apxs -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -c -I/usr/incl...
@@ -447,7 +479,7 @@ gcc -DLINUX=22 -DNO_DBM_REWRITEMAP -DMOD_SSL=208111 -DUSE_HSREGEX -DEAPI...
447gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc,-DEAPI... 479gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc,-DEAPI...
448\layout Standard 480\layout Standard
449 481
450You should see no errors and have a file called "mod_log_sql.so" in your 482You should see no errors and have a new file called "mod_log_sql.so" in your
451 directory. 483 directory.
452\end_deeper 484\end_deeper
453\layout Enumerate 485\layout Enumerate
@@ -472,8 +504,8 @@ chmod 755 /usr/local/Apache/libexec/mod_log_sql.so
472\end_deeper 504\end_deeper
473\layout Enumerate 505\layout Enumerate
474 506
475Module ordering within httpd.conf is important. 507Module ordering within httpd.conf is important if you are logging SSL information.
476 If you are logging SSL, you must make sure that 508 Please ensure that
477\begin_deeper 509\begin_deeper
478\layout LyX-Code 510\layout LyX-Code
479 511
@@ -486,7 +518,8 @@ comes before
486LoadModule sql_log_module libexec/mod_log_sql.so 518LoadModule sql_log_module libexec/mod_log_sql.so
487\layout Standard 519\layout Standard
488 520
489If you don't, you will get this error when you start Apache: 521in your httpd.conf file.
522 If you do not, you will get this error when you start Apache:
490\layout LyX-Code 523\layout LyX-Code
491 524
492/usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup 525/usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup
@@ -495,8 +528,8 @@ If you don't, you will get this error when you start Apache:
495/usr/local/apache/bin/apachectl startssl: httpd could not be started 528/usr/local/apache/bin/apachectl startssl: httpd could not be started
496\layout Standard 529\layout Standard
497 530
498(Because mod_log_sql doesn't yet have the required symbols that mod_ssl 531(This is because mod_log_sql doesn't yet have the required symbols that
499 provides.) 532 mod_ssl provides.)
500\layout Standard 533\layout Standard
501 534
502Now skip below to the "Configuration" section. 535Now skip below to the "Configuration" section.
@@ -519,7 +552,10 @@ Unpack the archive into a working directory.
519\begin_deeper 552\begin_deeper
520\layout LyX-Code 553\layout LyX-Code
521 554
522# tar zxf mod_log_sql.tar.gz -C /usr/local/src # cd /usr/local/src/mod_log_sql 555# tar zxf mod_log_sql.tar.gz -C /usr/local/src
556\layout LyX-Code
557
558# cd /usr/local/src/mod_log_sql
523\end_deeper 559\end_deeper
524\layout Enumerate 560\layout Enumerate
525 561
@@ -573,13 +609,33 @@ IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, OPNSSLHDRS
573\end_deeper 609\end_deeper
574\layout Enumerate 610\layout Enumerate
575 611
612Compile the module.
613\begin_deeper
614\layout LyX-Code
615
576# make static 616# make static
617\layout Standard
618
619You should see output similar to the following:
620\layout LyX-Code
621
622gcc -fPIC -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo...
623\layout Standard
624
625You should see no errors and have a new file called "mod_log_sql.o" in your
626 directory.
627\end_deeper
577\layout Enumerate 628\layout Enumerate
578 629
630Install the module.
631\begin_deeper
632\layout LyX-Code
633
579# make statinstall 634# make statinstall
635\end_deeper
580\layout Enumerate 636\layout Enumerate
581 637
582Change to your Apache source dir. 638Change to your Apache source directory.
583\begin_deeper 639\begin_deeper
584\layout LyX-Code 640\layout LyX-Code
585 641
@@ -587,7 +643,7 @@ Change to your Apache source dir.
587\end_deeper 643\end_deeper
588\layout Enumerate 644\layout Enumerate
589 645
590Re-make your httpd binary as follows. 646Re-compile your httpd binary as follows.
591\begin_deeper 647\begin_deeper
592\layout Enumerate 648\layout Enumerate
593 649
@@ -596,7 +652,7 @@ Re-make your httpd binary as follows.
596 652
597\end_inset 653\end_inset
598 654
599Edit Configuration.apaci as follows... 655Make these changes to Configuration.apaci:
600\begin_deeper 656\begin_deeper
601\layout Itemize 657\layout Itemize
602 658
@@ -606,13 +662,13 @@ Append the following string to the EXTRA_LIBS= line.
606 662
607\end_inset 663\end_inset
608 664
609, where your MySQL libraries live): 665, and is where your MySQL libraries live):
610\layout LyX-Code 666\layout LyX-Code
611 667
612-L/usr/lib/mysql -lmysqlclient -lm -lz 668-L/usr/lib/mysql -lmysqlclient -lm -lz
613\layout Itemize 669\layout Itemize
614 670
615Find the mod_log_config.o line, and add this line immediately after it: 671Find the mod_log_config.o line, and insert this line immediately after it:
616\layout LyX-Code 672\layout LyX-Code
617 673
618AddModule modules/sql/mod_log_sql.o 674AddModule modules/sql/mod_log_sql.o
@@ -637,11 +693,9 @@ Test your new apache binary:
637\layout LyX-Code 693\layout LyX-Code
638 694
639# ./httpd -l 695# ./httpd -l
640\end_deeper 696\layout Standard
641\layout Enumerate
642 697
643You should see something like: 698You should see something like:
644\begin_deeper
645\layout LyX-Code 699\layout LyX-Code
646 700
647Compiled-in modules: 701Compiled-in modules:
@@ -650,7 +704,7 @@ Compiled-in modules:
650http_core.c 704http_core.c
651\layout LyX-Code 705\layout LyX-Code
652 706
653mod_log_sql.c <-- That's the line you're looking for. 707mod_log_sql.c <-- That's the line you're looking for.
654\layout LyX-Code 708\layout LyX-Code
655 709
656mod_env.c 710mod_env.c
@@ -662,7 +716,7 @@ mod_log_config.c
662mod_mime.c 716mod_mime.c
663\layout LyX-Code 717\layout LyX-Code
664 718
665mod_negotiation.c ... 719mod_negotiation.c
666\layout LyX-Code 720\layout LyX-Code
667 721
668etc... 722etc...
@@ -689,6 +743,11 @@ Install your httpd binary.
689Configuration 743Configuration
690\layout Subsection 744\layout Subsection
691 745
746
747\begin_inset LatexCommand \label{sub:PrepDb}
748
749\end_inset
750
692Preparing MySQL for logging 751Preparing MySQL for logging
693\layout Standard 752\layout Standard
694 753
@@ -698,24 +757,40 @@ You have to prepare the database to receive data from mod_log_sql, and set
698\layout Standard 757\layout Standard
699 758
700This section will discuss how to get started with a basic config. 759This section will discuss how to get started with a basic config.
701 Full documentation of the run-time directives is available here: http://www.grub 760 Full documentation of all available run-time directives is available in
702bybaby.com/mod_log_sql/directives.html 761 section
762\begin_inset LatexCommand \ref{sec:ConfRef}
763
764\end_inset
765
766.
703\layout Enumerate 767\layout Enumerate
704 768
705mod_log_sql can make its own tables on-the-fly, or you can pre-make the 769mod_log_sql can make its own tables on-the-fly, or you can pre-make the
706 tables by hand. 770 tables by hand.
707 The advantage of letting the module make the tables is ease-of-use, but 771 The advantage of letting the module make the tables is ease-of-use, but
708 for raw performance you will want to pre-make the tables in order to save 772 for raw performance you will want to pre-make the tables in order to save
709 overhead. 773 some overhead.
710 In this basic setup we'll let the module create tables for us. 774 In this basic setup we'll just let the module create tables for us.
711\layout Enumerate 775\layout Enumerate
712 776
777
778\begin_inset LatexCommand \label{part:CrTbl}
779
780\end_inset
781
713We still need to have a logging database created and ready, so run the MySQL 782We still need to have a logging database created and ready, so run the MySQL
714 command line client and create a database: 783 command line client and create a database:
715\begin_deeper 784\begin_deeper
716\layout LyX-Code 785\layout LyX-Code
717 786
718# mysql -uadmin -pmypassword mysql> create database apachelogs; 787# mysql -uadmin -pmypassword
788\layout LyX-Code
789
790Enter password:
791\layout LyX-Code
792
793mysql> create database apachelogs;
719\end_deeper 794\end_deeper
720\layout Enumerate 795\layout Enumerate
721 796
@@ -746,12 +821,20 @@ identified by 'l0gger';
746\end_deeper 821\end_deeper
747\layout Enumerate 822\layout Enumerate
748 823
749You may be especially security-paranoid and not want "loguser" to have "create" 824You may be especially security-paranoid and want "loguser" to
750 capability within the "apachelogs" databse. 825\emph on
751 You can disable that but the cost is that you cannot use the module's automatic 826not
752-table-creation feature. 827\emph default
753 If that's an acceptable cost, hand-create the tables as described in step 828have "create" capability within the "apachelogs" database.
754 1 and use the following GRANT statement instead of the one above: 829 You can disable that privilege, but the cost is that you will not be able
830 to use the module's on-the-fly table creation feature.
831 If that cost is acceptable, hand-create the tables as described in step
832
833\begin_inset LatexCommand \ref{part:CrTbl}
834
835\end_inset
836
837 and use the following GRANT statement instead of the one above:
755\begin_deeper 838\begin_deeper
756\layout LyX-Code 839\layout LyX-Code
757 840
@@ -762,6 +845,11 @@ identified by 'l0gger';
762\end_deeper 845\end_deeper
763\layout Enumerate 846\layout Enumerate
764 847
848
849\begin_inset LatexCommand \label{step:EnaLog}
850
851\end_inset
852
765Enable full logging of your MySQL daemon (at least temporarily for debugging 853Enable full logging of your MySQL daemon (at least temporarily for debugging
766 purposes) if you don't do this already. 854 purposes) if you don't do this already.
767 Edit /etc/my.cnf and add the following line to your [mysqld] section: 855 Edit /etc/my.cnf and add the following line to your [mysqld] section:
@@ -784,28 +872,39 @@ A very basic logging setup in Apache
784Tell the module what database to use and the appropriate authentication 872Tell the module what database to use and the appropriate authentication
785 information. 873 information.
786\begin_deeper 874\begin_deeper
787\layout Description 875\layout Quote
876
788 877
789EXAMPLE: Use the MySQL database called "apachelogs" running on "dbmachine.foo.com". 878\series bold
790 The module uses username "loguser" and password "l0gger" to authenticate 879Example:
791 to the database. 880\series default
792 The log entries will be INSERTed into the table called "access_log". 881 Use the MySQL database called "apachelogs" running on "dbmachine.foo.com".
882 Use username "loguser" and password "l0gger" to authenticate to the database.
883 Let the module create tables for us.
793\layout Standard 884\layout Standard
794 885
795So, edit httpd.conf and insert the following lines somewhere AFTER any LoadModule 886So, edit httpd.conf and insert the following lines somewhere after any LoadModule
796 / AddModule statements. 887 / AddModule statements.
797 888
798\emph on 889\emph on
799Make sure these statements are "global," i.e. 890Make sure these statements are
891\begin_inset Quotes eld
892\end_inset
893
894global,
895\begin_inset Quotes erd
896\end_inset
897
898 i.e.
800 not inside any VirtualHost stanza 899 not inside any VirtualHost stanza
801\emph default 900\emph default
802. 901.
803\layout LyX-Code 902\layout LyX-Code
804 903
805LogSQLDatabase apachelogs 904LogSQLLoginInfo dbmachine.foo.com loguser l0gger
806\layout LyX-Code 905\layout LyX-Code
807 906
808LogSQLLoginInfo dbmachine.foo.com loguser l0gger 907LogSQLDatabase apachelogs
809\layout LyX-Code 908\layout LyX-Code
810 909
811LogSQLCreateTables on 910LogSQLCreateTables on
@@ -829,17 +928,24 @@ LogSQLTCPPort 1234
829The actual logging is set up on a virtual-host-by-host basis. 928The actual logging is set up on a virtual-host-by-host basis.
830 So, skip down to the virtual host you want to set up. 929 So, skip down to the virtual host you want to set up.
831 Instruct this virtual host to log entries to the table 930 Instruct this virtual host to log entries to the table
832\begin_inset Quotes sld 931\begin_inset Quotes eld
833\end_inset 932\end_inset
834 933
835access_log 934access_log
836\begin_inset Quotes srd 935\begin_inset Quotes srd
837\end_inset 936\end_inset
838 937
839 by inserting a LogSQLTransferLogTable directive. 938 by inserting a
840 (The LogSQLTransferLogTable directive is the minimum required to log -- 939\noun on
841 other directives that you'll learn about later simply tune the module's 940LogSQLTransferLogTable
842 behavior.) 941\noun default
942 directive.
943 (The
944\noun on
945LogSQLTransferLogTable
946\noun default
947 directive is the minimum required to log -- other directives that you'll
948 learn about later simply tune the module's behavior.)
843\begin_deeper 949\begin_deeper
844\layout LyX-Code 950\layout LyX-Code
845 951
@@ -870,52 +976,83 @@ Restart apache.
870Testing the basic setup 976Testing the basic setup
871\layout Enumerate 977\layout Enumerate
872 978
873Load your web site in a browser to trigger some hits, then confirm that 979Visit your web site in a browser to trigger some hits, then confirm that
874 the entries are being successfully logged: 980 the entries are being successfully logged:
875\begin_deeper 981\begin_deeper
876\layout LyX-Code 982\layout LyX-Code
877 983
878# mysql -hmysql.host.com -umysqladmin -p -e "select * from access_log" apachelogs 984# mysql -hdbmachine.foo.com -umysqladmin -p -e "select * from access_log"
879 985 apachelogs
880\layout LyX-Code 986\layout LyX-Code
881 987
882Enter password: 988Enter password:
883\layout LyX-Code
884
885(Several lines of output should follow, corresponding to your hits on the
886 site.)
887\layout Standard 989\layout Standard
888 990
889You now have basic functionality. 991Several lines of output should follow, corresponding to your hits on the
992 site.
993 You now have basic functionality.
890 Don't disable your regular Apache logs until you feel comfortable that 994 Don't disable your regular Apache logs until you feel comfortable that
891 the database is behaving as you'd like and that things are going well. 995 the database is behaving as you'd like and that things are going well.
892\end_deeper 996\end_deeper
893\layout Enumerate 997\layout Enumerate
894 998
895If you do not see any entries in the access_log, then something is preventing 999You can now activate the advanced features of mod_log_sql, which are described
1000 in the next section.
1001 If you do not see any entries in the access_log, then something is preventing
896 the inserts from happening. 1002 the inserts from happening.
897 This problem could be caused by several things: 1003 This could be caused by several things:
898\begin_deeper 1004\begin_deeper
899\layout Itemize 1005\layout Itemize
900 1006
901Improper privileges set up in the MySQL database 1007Improper privileges set up in the MySQL database
902\layout Itemize 1008\layout Itemize
903 1009
904You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry 1010You aren't hitting a VirtualHost that has a
905 1011\noun on
1012LogSQLTransferLogTable
1013\noun default
1014 entry
906\layout Itemize 1015\layout Itemize
907 1016
908You didn't specify the right host 1017You didn't specify the right database host or login information
909\layout Standard 1018\layout Standard
910 1019
911If you have confirmed your LogSQL* directives and know them to be correct, 1020So, first examine the MySQL log that you established in step
912 you should examine the httpd server logs for mod_log_sql messages; the 1021\begin_inset LatexCommand \ref{step:EnaLog}
913 module will offer hints as to why it cannot connect, etc. 1022
914 Also examine the MySQL log that you established in step 3. 1023\end_inset
915 Ensure that the INSERTs are not being rejected because of a malformed table 1024
916 entry or other clerical error. 1025 of section
917 If you see no INSERT attempts in the log, the module isn't successfully 1026\begin_inset LatexCommand \ref{sub:PrepDb}
918 connecting to the database. 1027
1028\end_inset
1029
1030.
1031 Ensure that the INSERT statements are not being rejected because of a malformed
1032 table entry or other clerical error.
1033 By enabling that log, you instructed MySQL to log every connection and
1034 command it receives -- if you see no INSERT attempts in the log, the module
1035 isn't successfully connecting to the database.
1036 If you see nothing at all in the log -- not even a record of your administrativ
1037e connection attempts, then you did not enable the log correctly.
1038 If you do see INSERT attempts but they are failing, the log should tell
1039 you why.
1040\layout Standard
1041
1042Second, confirm that your
1043\noun on
1044LogSQL*
1045\noun default
1046 directives are all correct.
1047\layout Standard
1048
1049Third, examine the Apache
1050\noun on
1051
1052\noun default
1053error logs for messages from mod_log_sql; the module will offer hints as
1054 to why it cannot connect, etc.
1055
919\layout Standard 1056\layout Standard
920 1057
921The next thing to do is recompile the module with debugging output activated. 1058The next thing to do is recompile the module with debugging output activated.
@@ -924,10 +1061,6 @@ The next thing to do is recompile the module with debugging output activated.
924 The module will now output copious notes about what it is doing, and this 1061 The module will now output copious notes about what it is doing, and this
925 will help you (and the maintainer) solve the problem. 1062 will help you (and the maintainer) solve the problem.
926\end_deeper 1063\end_deeper
927\layout Standard
928
929You can now activate the advanced features of mod_log_sql.
930 These are all described in the next section.
931\layout Subsection 1064\layout Subsection
932 1065
933How to tune logging with run-time directives 1066How to tune logging with run-time directives
@@ -936,30 +1069,63 @@ How to tune logging with run-time directives
936Instructing the module what to log 1069Instructing the module what to log
937\layout Standard 1070\layout Standard
938 1071
939The LogSQLTransferLogFormat directive tells the module which information 1072The most basic directive for the module is
940 to send to the database. 1073\noun on
941 This directive resembles -- but is not identical to -- the Apache LogFormat 1074LogSQLTransferLogFormat
942 directive. 1075\noun default
943 For each virtual host that you want to enable SQL logging, insert a LogSQLTrans 1076, which tells the module which information to send to the database; logging
944ferLogFormat directive into the VirtualHost stanza. 1077 to the database will not take place without it.
945\layout Standard 1078 Place a
946 1079\noun on
947After LogSQLTransferLogFormat you supply a string of characters that tell 1080LogSQLTransferLogFormat
948 the module what to log. 1081\noun default
1082 directive in the VirtualHost stanza of each virtual host that you want
1083 to activate.
1084\layout Standard
1085
1086After
1087\noun on
1088LogSQLTransferLogFormat
1089\noun default
1090 you supply a string of characters that tell the module what information
1091 to log.
949 In the configuration directive reference (section 1092 In the configuration directive reference (section
950\begin_inset LatexCommand \ref{sec:ConfRef} 1093\begin_inset LatexCommand \ref{sub:Frmat}
951 1094
952\end_inset 1095\end_inset
953 1096
954) there is a table which clearly defines all the possible things to log. 1097) there is a table which clearly defines all the possible things to log.
955 Let's say you only wanted to log only the request time, the remote host, 1098 Let's say you want to log only the
956 and the request; you'd use: 1099\begin_inset Quotes eld
1100\end_inset
1101
1102request time,
1103\begin_inset Quotes erd
1104\end_inset
1105
1106 the
1107\begin_inset Quotes eld
1108\end_inset
1109
1110remote host,
1111\begin_inset Quotes erd
1112\end_inset
1113
1114 and the
1115\begin_inset Quotes eld
1116\end_inset
1117
1118request
1119\begin_inset Quotes erd
1120\end_inset
1121
1122; you'd use:
957\layout LyX-Code 1123\layout LyX-Code
958 1124
959LogSQLTransferLogFormat hUS 1125LogSQLTransferLogFormat hUS
960\layout Standard 1126\layout Standard
961 1127
962A more appropriate string to use is 1128But a more appropriate string to use is
963\layout LyX-Code 1129\layout LyX-Code
964 1130
965LogSQLTransferLogFormat AbHhmRSsTUuv 1131LogSQLTransferLogFormat AbHhmRSsTUuv
@@ -970,28 +1136,49 @@ which logs all the information required to be compatible with the Combined
970\layout Standard 1136\layout Standard
971 1137
972If you don't choose to log everything that is available, that's fine. 1138If you don't choose to log everything that is available, that's fine.
973 The table in your database will have unused columns that will simply contain 1139 The unused columns in your table will have simply contain NULL and do not
974 NULL and do not cause issues. 1140 cause issues.
975\layout Standard 1141\layout Standard
976 1142
977Some directives need a little extra configuration: 1143Some of the
1144\noun on
1145LogSQLTransferLogFormat
1146\noun default
1147 characters require a little extra configuration:
978\layout Itemize 1148\layout Itemize
979 1149
980If you specify 'c' to indicate that you want to log the cookie value, you 1150If you specify 'c' to indicate that you want to log the cookie value, you
981 need to also tell the module which cookie you want with LogSQLWhichCookie 1151 must also tell the module which cookie you mean by using
1152\noun on
1153LogSQLWhichCookie
1154\noun default
982 -- after all, there could be many cookies associated with a given request. 1155 -- after all, there could be many cookies associated with a given request.
983 Fail to specify LogSQLWhichCookie, and no cookie information at all will 1156 Fail to specify
984 be logged. 1157\noun on
1158LogSQLWhichCookie
1159\noun default
1160, and no cookie information at all will be logged.
985 1161
986\layout Itemize 1162\layout Itemize
987 1163
988If you specify 'M' to indicate that you want to log the machine ID, you 1164If you specify 'M' to indicate that you want to log the machine ID, you
989 need to also instruct the module this machine's identifier using the LogSQLMach 1165 must also tell the module this machine's identity using the
990ineID directive. 1166\noun on
991 Fail to specify LogSQLMachineID, and a simple '-' character will be logged 1167LogSQLMachineID
992 in the machine_id column. 1168\noun default
1169 directive.
1170 Fail to specify
1171\noun on
1172LogSQLMachineID
1173\noun default
1174, and a simple '-' character will be logged in the machine_id column.
993\layout Subsubsection 1175\layout Subsubsection
994 1176
1177
1178\begin_inset LatexCommand \label{sub:Ignore}
1179
1180\end_inset
1181
995Instructing the module what NOT to log 1182Instructing the module what NOT to log
996\layout Standard 1183\layout Standard
997 1184
@@ -1004,24 +1191,142 @@ ignore
1004\end_inset 1191\end_inset
1005 1192
1006 directives allow you to specify things that the module should not log. 1193 directives allow you to specify things that the module should not log.
1007 This can be very handy for a variety of reasons, such as keeping your database 1194 These are very handy for keeping your database as uncluttered as possible
1008 as uncluttered as possible, keeping your statistics free of unneeded numbers, 1195 and keeping your statistics free of unneeded numbers.
1009 etc.
1010\layout Standard 1196\layout Standard
1011 1197
1012You are able to tell the module to ignore based on LogSQLRemhostIgnore and 1198You are able to tell the module what to exclude based on
1013 LogSQLRequestIgnore. 1199\noun on
1014 A good way to use LogSQLRemhostIgnore is to prevent the module from logging 1200LogSQLRemhostIgnore
1015 the traffic that your internal hosts generate. 1201\noun default
1016 LogSQLRequestIgnore is great for preventing things like requests for favicon.ico 1202 and
1017 from cluttering up your database, as well as the various requests that 1203\noun on
1018 worms make, etc. 1204LogSQLRequestIgnore
1205\noun default
1206.
1207 A good way to use
1208\noun on
1209LogSQLRemhostIgnore
1210\noun default
1211 is to prevent the module from logging the traffic that your internal hosts
1212 generate.
1213
1214\noun on
1215LogSQLRequestIgnore
1216\noun default
1217 is great for preventing things like requests for
1218\begin_inset Quotes eld
1219\end_inset
1220
1221favicon.ico
1222\begin_inset Quotes erd
1223\end_inset
1224
1225 from cluttering up your database, as well as excluding the various requests
1226 that worms make, etc.
1019\layout Standard 1227\layout Standard
1020 1228
1021You can specify a series of strings after each directive. 1229You can specify a series of strings after each directive.
1022 Do not use any type of globbing or regular-expression syntax -- each string 1230 Do not use any type of globbing or regular-expression syntax -- each string
1023 is considered a match if it is a substring of the larger request or remote-host. 1231 is considered a match
1024 In other words, if you want to match 1232\emph on
1233 if it is a substring of the larger request or remote-host, and the comarison
1234 is case-sensitive.
1235
1236\emph default
1237 This means that
1238\noun on
1239
1240\begin_inset Quotes eld
1241\end_inset
1242
1243LogSQLRemhostIgnore
1244\noun default
1245 micro
1246\begin_inset Quotes erd
1247\end_inset
1248
1249 will ignore requests from
1250\begin_inset Quotes eld
1251\end_inset
1252
1253microsoft.com,
1254\begin_inset Quotes erd
1255\end_inset
1256
1257
1258\begin_inset Quotes eld
1259\end_inset
1260
1261microworld.net,
1262\begin_inset Quotes erd
1263\end_inset
1264
1265
1266\begin_inset Quotes eld
1267\end_inset
1268
1269mymicroscope.org,
1270\begin_inset Quotes erd
1271\end_inset
1272
1273 etc.
1274
1275\begin_inset Quotes eld
1276\end_inset
1277
1278
1279\noun on
1280LogSQLRequestIgnore
1281\noun default
1282 gif
1283\begin_inset Quotes erd
1284\end_inset
1285
1286 will instruct the module to ignore requests for
1287\begin_inset Quotes eld
1288\end_inset
1289
1290leftbar.gif,
1291\begin_inset Quotes erd
1292\end_inset
1293
1294
1295\begin_inset Quotes eld
1296\end_inset
1297
1298bluedot.gif
1299\begin_inset Quotes erd
1300\end_inset
1301
1302 and even
1303\begin_inset Quotes eld
1304\end_inset
1305
1306giftwrap.jpg
1307\begin_inset Quotes erd
1308\end_inset
1309
1310 -- but
1311\begin_inset Quotes eld
1312\end_inset
1313
1314RED.GIF
1315\begin_inset Quotes erd
1316\end_inset
1317
1318 and
1319\begin_inset Quotes eld
1320\end_inset
1321
1322Tree.Gif
1323\begin_inset Quotes erd
1324\end_inset
1325
1326 would still get logged.
1327\layout Standard
1328
1329Tip: if you want to match all the hosts in your domain such as
1025\begin_inset Quotes eld 1330\begin_inset Quotes eld
1026\end_inset 1331\end_inset
1027 1332
@@ -1033,7 +1338,7 @@ host1.corp.foo.com
1033\begin_inset Quotes eld 1338\begin_inset Quotes eld
1034\end_inset 1339\end_inset
1035 1340
1036host2.dmz.foo.com 1341server.dmz.foo.com
1037\begin_inset Quotes srd 1342\begin_inset Quotes srd
1038\end_inset 1343\end_inset
1039 1344
@@ -1043,16 +1348,155 @@ host2.dmz.foo.com
1043LogSQLRemhostIgnore foo.com 1348LogSQLRemhostIgnore foo.com
1044\layout Standard 1349\layout Standard
1045 1350
1046A great way to catch the vast majority of worm-attack requests and prevent 1351Tip: a great way to catch the vast majority of worm-attack requests and
1047 them from being logged is to specify: 1352 prevent them from being logged is to specify:
1048\layout LyX-Code 1353\layout LyX-Code
1049 1354
1050LogSQLRequestIgnore root.exe cmd.exe default.ida 1355LogSQLRequestIgnore root.exe cmd.exe default.ida
1356\layout Standard
1357
1358Tip: to prevent the logging of requests for common graphic types, make sure
1359 to put a '.' before the suffix to avoid matches that you didn't intend:
1360\layout LyX-Code
1361
1362LogSQLRequestIgnore .gif .jpg
1051\layout Subsection 1363\layout Subsection
1052 1364
1053Advanced logging scenarios 1365Advanced logging scenarios
1054\layout Subsubsection 1366\layout Subsubsection
1055 1367
1368Using the module in an ISP environment
1369\layout Standard
1370
1371mod_log_sql has three basic tiers of operation:
1372\layout Enumerate
1373
1374The administrator creates all necessary tables by hand and configures each
1375 Apache VirtualHost by hand.
1376 (
1377\noun on
1378LogSQLCreateTables Off
1379\noun default
1380)
1381\layout Enumerate
1382
1383The module is permitted to create necessary tables on-the-fly, but the administr
1384ator configures each Apache VirtualHost by hand.
1385 (
1386\noun on
1387LogSQLCreateTables On
1388\noun default
1389)
1390\layout Enumerate
1391
1392The module is permitted to create all necessary tables and to make intelligent,
1393 on-the-fly configuration of each VirtualHost.
1394\layout Standard
1395
1396Many users are happy to use the module in its most minimal form: they hand-creat
1397e any necessary tables (using
1398\begin_inset Quotes eld
1399\end_inset
1400
1401create_tables.sql
1402\begin_inset Quotes erd
1403\end_inset
1404
1405), and they configure each VirtualHost by hand to suit their needs.
1406 However, some administrators need extra features due to a large and growing
1407 number of VirtualHosts.
1408 The
1409\noun on
1410LogSQLMassVirtualHosting
1411\noun default
1412 directive activates module capabilities that make it far easier to manage
1413 an ISP environment, or any situation characterized by a large and varying
1414 number of virtual servers:
1415\layout Itemize
1416
1417the on-the-fly table creation feature is activated automatically
1418\layout Itemize
1419
1420the transfer log table name is dynamically set from the virtual host's name
1421 (example: a virtual host
1422\begin_inset Quotes eld
1423\end_inset
1424
1425www.grubbybaby.com
1426\begin_inset Quotes erd
1427\end_inset
1428
1429 gets logged to table
1430\begin_inset Quotes eld
1431\end_inset
1432
1433access_www_grubbybaby_com
1434\begin_inset Quotes erd
1435\end_inset
1436
1437)
1438\layout Standard
1439
1440There are numerous benefits.
1441 The admin will not need to create new tables for every new VirtualHost.
1442 (Although the admin will still need to drop the tables of virtual hosts
1443 that are removed.) The admin will not need to set
1444\noun on
1445LogSQLTransferLogTable
1446\noun default
1447 for each virtual host -- it will be configured automatically based on the
1448 host's name.
1449 Because each virtual host will log to its own segregated table, data about
1450 one virtual server will segregate from others; an admin can grant users
1451 access to the tables they need, and they will be unable to view data about
1452 another user's virtual host.
1453\layout Standard
1454
1455In an ISP scenario the admin is likely to have a cluster of many front-end
1456 webservers logging to a back-end database.
1457 mod_log_sql has a feature that permits analysis of how well the web servers
1458 are loadbalancing: the
1459\noun on
1460LogSQLMachineID
1461\noun default
1462 directive.
1463 The administrator uses this directive to assign a unique identifier to
1464 each machine in the web cluster, e.g.
1465
1466\begin_inset Quotes eld
1467\end_inset
1468
1469
1470\noun on
1471LogSQLMachineID
1472\noun default
1473 web01,
1474\begin_inset Quotes erd
1475\end_inset
1476
1477
1478\begin_inset Quotes eld
1479\end_inset
1480
1481
1482\noun on
1483LogSQLMachineID
1484\noun default
1485 web02,
1486\begin_inset Quotes erd
1487\end_inset
1488
1489 etc.
1490 Used in conjunction with the 'M' character in
1491\noun on
1492LogSQLTransferLogFormat
1493\noun default
1494, each entry in the SQL log will include the machine ID of the machine that
1495 created the entry.
1496 This permits the administrator to count the entries made by each particular
1497 machine and thereby analyze the front-end loadbalancing algorithm.
1498\layout Subsubsection
1499
1056 1500
1057\begin_inset LatexCommand \label{secMulTable} 1501\begin_inset LatexCommand \label{secMulTable}
1058 1502
@@ -1064,19 +1508,31 @@ Logging many-to-one data in separate tables
1064A given HTTP request can have a one-to-many relationship with certain kinds 1508A given HTTP request can have a one-to-many relationship with certain kinds
1065 of data. 1509 of data.
1066 For example, a single HTTP request can have 4 cookies, 3 headers and 5 1510 For example, a single HTTP request can have 4 cookies, 3 headers and 5
1067 mod_gzip notes associated with it. 1511
1512\begin_inset Quotes eld
1513\end_inset
1514
1515mod_gzip
1516\begin_inset Quotes erd
1517\end_inset
1518
1519 notes associated with it.
1068 mod_log_sql is capable of logging these relationships due to the elegance 1520 mod_log_sql is capable of logging these relationships due to the elegance
1069 of SQL relational data. 1521 of SQL relational data.
1070\layout Standard 1522\layout Standard
1071 1523
1072You already have a single table containing access requests. 1524You already have a single table containing access requests.
1073 One of the columns in that table is 'id' which contains the unique request 1525 One of the columns in that table is 'id' which is intended to contain the
1074 ID supplied by the standard Apache module mod_unique_id (assuming you compile 1526 unique request ID supplied by the standard Apache module mod_unique_id
1075 in that module and employ the LogSQLTransferLogFormat character 'I'). 1527 -- all you need to do is compile in that module and employ the
1076 Each request gets a unique ID that can be thought of as a primary key within 1528\noun on
1077 the database. 1529LogSQLTransferLogFormat
1078 So let's add several new tables: a notes table, a cookies table, and tables 1530\noun default
1079 for inbound and outbound headers. 1531 character 'I'.
1532 Thereafter, each request gets a unique ID that can be thought of as a primary
1533 key within the database, useful for joining multiple tables.
1534 So let's envision several new tables: a notes table, a cookies table, and
1535 a table for inbound and outbound headers.
1080 1536
1081\layout Standard 1537\layout Standard
1082 1538
@@ -1459,8 +1915,8 @@ XYZ123
1459 1915
1460\end_inset 1916\end_inset
1461 1917
1462, you have a many-to-one relationship for request XYZ123: one access has 1918, you have a one-to-many relationship for request XYZ123: that one access
1463 two associated notes and four associated headers. 1919 has two associated notes and four associated headers.
1464 You can extract this data easily using the power of SQL's 1920 You can extract this data easily using the power of SQL's
1465\begin_inset Quotes eld 1921\begin_inset Quotes eld
1466\end_inset 1922\end_inset
@@ -1599,8 +2055,6 @@ mod_gzip_compression_ratio
1599 2055
1600\layout LyX-Code 2056\layout LyX-Code
1601 2057
1602\layout LyX-Code
1603
1604\layout Standard 2058\layout Standard
1605 2059
1606In order to use this capability of mod_log_sql, you must do several things: 2060In order to use this capability of mod_log_sql, you must do several things:
@@ -1615,7 +2069,15 @@ Compile mod_unique_id into Apache (statically or as a DSO).
1615 2069
1616Create the appropriate tables. 2070Create the appropriate tables.
1617 This will be done for you if you permit mod_log_sql to create its own tables, 2071 This will be done for you if you permit mod_log_sql to create its own tables,
1618 or if you use the enclosed create_tables.sql script. 2072 or if you use the enclosed
2073\begin_inset Quotes eld
2074\end_inset
2075
2076create_tables.sql
2077\begin_inset Quotes erd
2078\end_inset
2079
2080 script.
1619\layout Itemize 2081\layout Itemize
1620 2082
1621Create a SQL index on the 2083Create a SQL index on the
@@ -1632,12 +2094,22 @@ id
1632 a column index if you are not familiar with this operation. 2094 a column index if you are not familiar with this operation.
1633\layout Itemize 2095\layout Itemize
1634 2096
1635Within each appropriate VirtualHost stanza, use the LogSQLWhich* and LogSQL*LogT 2097Within each appropriate VirtualHost stanza, use the
1636able directives to tell the module what and where to log the data. 2098\noun on
1637 In the following example, I have specified a name for the notes table that 2099 LogSQLWhich*
1638 is different from the default, whereas I have left the other table names 2100\noun default
1639 at their defaults. 2101 and
2102\noun on
2103LogSQL*LogTable
2104\noun default
2105 directives to tell the module what and where to log the data.
2106 In the following example, I have overridden the name for the notes table
2107 whereas I have left the other table names at their defaults.
1640 I then specified the cookies, headers and notes that interest me. 2108 I then specified the cookies, headers and notes that interest me.
2109 As you can see, these directives do not require me to add any characters
2110 to
2111\noun on
2112LogSQLTransferLogTable.
1641\layout LyX-Code 2113\layout LyX-Code
1642 2114
1643<VirtualHost 216.231.36.128> 2115<VirtualHost 216.231.36.128>
@@ -1665,8 +2137,83 @@ able directives to tell the module what and where to log the data.
1665\layout LyX-Code 2137\layout LyX-Code
1666 2138
1667</VirtualHost> 2139</VirtualHost>
2140\layout Subsubsection
2141
2142Using the same database for production and test
2143\layout Standard
2144
2145Although suboptimal, it is not uncommon to use the same backend database
2146 for the
2147\begin_inset Quotes eld
2148\end_inset
2149
2150production
2151\begin_inset Quotes erd
2152\end_inset
2153
2154 webservers as well as the
2155\begin_inset Quotes eld
2156\end_inset
2157
2158test
2159\begin_inset Quotes erd
2160\end_inset
2161
2162 webservers (budgetary constraints, rackspace limits, etc.).
2163 Furthermore, an administrator in this situation may be unable to use
2164\noun on
2165LogSQLRemhostIgnore
2166\noun default
2167to exclude requests from the test servers -- perhaps the generated entries
2168 are genuinely useful for analytical or QA purposes, but their value after
2169 analysis is minimal.
2170\layout Standard
2171
2172It is wasteful and potentially confusing to permit this internal test data
2173 to clutter the database, and a solution to the problem is the proper use
2174 of the
2175\noun on
2176LogSQLMachineID
2177\noun default
2178directive.
2179 Assume a scenario where the production webservers have IDs like
2180\begin_inset Quotes eld
2181\end_inset
2182
2183web01,
2184\begin_inset Quotes erd
2185\end_inset
2186
2187
2188\begin_inset Quotes eld
2189\end_inset
2190
2191web02,
2192\begin_inset Quotes erd
2193\end_inset
2194
2195 and so on -- and the test webservers have IDs like
2196\begin_inset Quotes eld
2197\end_inset
2198
2199test01,
2200\begin_inset Quotes erd
2201\end_inset
2202
2203
2204\begin_inset Quotes eld
2205\end_inset
2206
2207test02,
2208\begin_inset Quotes erd
2209\end_inset
2210
2211 etc.
2212 Because entries in the log database are distinguished by their source machine,
2213 an administrator may purge unneeded test data from the access log as follows:
1668\layout LyX-Code 2214\layout LyX-Code
1669 2215
2216delete from access_log where machine_id like 'test%';
1670\layout Subsection 2217\layout Subsection
1671 2218
1672 2219
@@ -1712,8 +2259,23 @@ Context: main server config
1712\layout Quote 2259\layout Quote
1713 2260
1714Defines the database that is used for logging. 2261Defines the database that is used for logging.
1715 database must be operating on the MySQL host defined in LogSQLLoginInfo. 2262
1716 This is defined only once in the httpd.conf file. 2263\begin_inset Quotes eld
2264\end_inset
2265
2266database
2267\begin_inset Quotes erd
2268\end_inset
2269
2270 must be a valid db on the MySQL host defined in
2271\noun on
2272LogSQLLoginInfo
2273\noun default
2274.
2275
2276\layout Quote
2277
2278This is defined only once in the httpd.conf file.
1717\layout Subsubsection 2279\layout Subsubsection
1718 2280
1719LogSQLLoginInfo 2281LogSQLLoginInfo
@@ -1736,11 +2298,42 @@ Context: main server config
1736\layout Quote 2298\layout Quote
1737 2299
1738Defines the general parameters of the MySQL host to which you will be logging. 2300Defines the general parameters of the MySQL host to which you will be logging.
1739 host is the hostname or IP address of the MySQL machine. 2301
1740 user is the MySQL userid (not a Unix userid!) with INSERT privileges on 2302\begin_inset Quotes eld
1741 the table defined in LogSQLTransferLogTable. 2303\end_inset
1742 password is that user's password. 2304
1743 This is defined only once in the httpd.conf file. 2305host
2306\begin_inset Quotes erd
2307\end_inset
2308
2309 is the hostname or IP address of the MySQL machine.
2310
2311\begin_inset Quotes eld
2312\end_inset
2313
2314user
2315\begin_inset Quotes erd
2316\end_inset
2317
2318 is the MySQL userid (not a Unix userid!) with INSERT privileges on the
2319 table defined in
2320\noun on
2321LogSQLTransferLogTable
2322\noun default
2323.
2324
2325\begin_inset Quotes eld
2326\end_inset
2327
2328password
2329\begin_inset Quotes erd
2330\end_inset
2331
2332 is that user's password.
2333
2334\layout Quote
2335
2336This is defined only once in the httpd.conf file.
1744\layout Subsubsection 2337\layout Subsubsection
1745 2338
1746LogSQLTCPPort 2339LogSQLTCPPort
@@ -1762,6 +2355,9 @@ Your database may listen on a different port than the default.
1762 If so, use this directive to instruct the module which port to use. 2355 If so, use this directive to instruct the module which port to use.
1763 This directive only applies if the database is on a different machine connected 2356 This directive only applies if the database is on a different machine connected
1764 via TCP/IP. 2357 via TCP/IP.
2358\layout Quote
2359
2360This is defined only once in the httpd.conf file.
1765\layout Subsubsection 2361\layout Subsubsection
1766 2362
1767LogSQLSocketFile 2363LogSQLSocketFile
@@ -1789,6 +2385,9 @@ mod_log_sql will automatically employ the socket for db communications if
1789 If the db resides on a separate host the module will automatically use 2385 If the db resides on a separate host the module will automatically use
1790 TCP/IP. 2386 TCP/IP.
1791 This is a function of the MySQL API and is not user-configurable. 2387 This is a function of the MySQL API and is not user-configurable.
2388\layout Quote
2389
2390This is defined only once in the httpd.conf file.
1792\layout Subsubsection 2391\layout Subsubsection
1793 2392
1794LogSQLPreserveFile 2393LogSQLPreserveFile
@@ -1815,21 +2414,36 @@ mod_log_sql writes queries to this local preserve file in the event that
1815 The file consists of a series of SQL statements that can be imported into 2414 The file consists of a series of SQL statements that can be imported into
1816 your database at your convenience; furthermore, because the SQL queries 2415 your database at your convenience; furthermore, because the SQL queries
1817 contain the access timestamps you do not need to worry about out-of-order 2416 contain the access timestamps you do not need to worry about out-of-order
1818 data after the import. 2417 data after the import, which is done in a simple manner:
2418\layout LyX-Code
2419
2420# mysql -uadminuser -p mydbname < /tmp/sql-preserve
1819\layout Quote 2421\layout Quote
1820 2422
1821If you do not define LogSQLPreserveFile then all virtual servers will log 2423If you do not define
1822 to the same default preserve file (/tmp/mysql-preserve). 2424\noun on
2425LogSQLPreserveFile
2426\noun default
2427 then all virtual servers will log to the same default preserve file (/tmp/sql-p
2428reserve).
1823 You can redefine this on a virtual-host basis in order to segregate your 2429 You can redefine this on a virtual-host basis in order to segregate your
1824 preserve files if you desire. 2430 preserve files if you desire.
1825 Note that segregation is not really necessary, as the SQL statements that 2431 Note that segregation is not usually necessary, as the SQL statements that
1826 are written to the preserve file already distinguish between different 2432 are written to the preserve file already distinguish between different
1827 virtual hosts if you include the 'v' character in your LogSQLTransferLogFormat 2433 virtual hosts if you include the 'v' character in your
2434\noun on
2435LogSQLTransferLogFormat
2436\noun default
1828 directive. 2437 directive.
2438 It is only necessary to segregate preserve-files by virualhost if you also
2439 segregate access logs by virtualhost.
1829\layout Quote 2440\layout Quote
1830 2441
1831The module will log to error-log if/when it notices a database outage, and 2442The module will log to Apache's
1832 upon database return. 2443\noun on
2444ErrorLog
2445\noun default
2446 when it notices a database outage, and upon database return.
1833 You will therefore know when the preserve file is being used, although 2447 You will therefore know when the preserve file is being used, although
1834 it is your responsibility to import the file. 2448 it is your responsibility to import the file.
1835\layout Quote 2449\layout Quote
@@ -1866,6 +2480,9 @@ You may need to perform debugging on your database and specifically want
1866This is presumably a directive for temporary use only; it could be dangerous 2480This is presumably a directive for temporary use only; it could be dangerous
1867 if you set it and forget it, as all your entries will simply pile up in 2481 if you set it and forget it, as all your entries will simply pile up in
1868 the preserve file. 2482 the preserve file.
2483\layout Quote
2484
2485This is defined only once in the httpd.conf file.
1869\layout Subsubsection 2486\layout Subsubsection
1870 2487
1871LogSQLMachineID 2488LogSQLMachineID
@@ -1880,11 +2497,46 @@ Example: LogSQLMachineID web01
1880Context: main server config 2497Context: main server config
1881\layout Quote 2498\layout Quote
1882 2499
1883If you have a farm of webservers you may wish to log which particular machine 2500If you have a farm of webservers then you may wish to know which particular
1884 made each entry; this is useful for analyzing your loadbalancing methodology. 2501 machine made each entry; this is useful for analyzing your loadbalancing
1885 LogSQLMachineID permits you to distinguish each machine's entries if you 2502 methodology.
1886 assign each machine its own LogSQLMachineID: for example, web01, web02, 2503
1887 web03, etc. 2504\noun on
2505LogSQLMachineID
2506\noun default
2507 permits you to distinguish each machine's entries if you assign each machine
2508 its own
2509\noun on
2510LogSQLMachineID
2511\noun default
2512: for example, the first webserver gets
2513\begin_inset Quotes eld
2514\end_inset
2515
2516
2517\noun on
2518LogSQLMachineID
2519\noun default
2520 web01,
2521\begin_inset Quotes erd
2522\end_inset
2523
2524 the second gets
2525\begin_inset Quotes eld
2526\end_inset
2527
2528
2529\noun on
2530LogSQLMachineID
2531\noun default
2532 web02,
2533\begin_inset Quotes erd
2534\end_inset
2535
2536 etc.
2537\layout Quote
2538
2539This is defined only once in the httpd.conf file.
1888\layout Subsubsection 2540\layout Subsubsection
1889 2541
1890LogSQLCreateTables 2542LogSQLCreateTables
@@ -1906,20 +2558,31 @@ mod_log_sql has the ability to create its tables on-the-fly.
1906 The advantage to this is convenience: you don't have to execute any SQL 2558 The advantage to this is convenience: you don't have to execute any SQL
1907 by hand to prepare the table. 2559 by hand to prepare the table.
1908 This is especially helpful for people with lots of virtual hosts (who should 2560 This is especially helpful for people with lots of virtual hosts (who should
1909 also see the LogSQLMassVirtualHosting directive). 2561 also see the
2562\noun on
2563LogSQLMassVirtualHosting
2564\noun default
2565 directive).
1910\layout Quote 2566\layout Quote
1911 2567
1912There is a slight disadvantage: if you wish to activate this feature, then 2568There is a slight disadvantage: if you wish to activate this feature, then
1913 the user specified by LogSQLLoginInfo must have CREATE privileges on the 2569 the userid specified in
1914 database. 2570\noun on
2571LogSQLLoginInfo
2572\noun default
2573 must have CREATE privileges on the database.
1915 In an absolutely paranoid, locked-down situation you may only want to grant 2574 In an absolutely paranoid, locked-down situation you may only want to grant
1916 your mod_log_sql user INSERT privileges on the database; in that situation 2575 your mod_log_sql user INSERT privileges on the database; in that situation
1917 you are unable to take advantage of LogSQLCreateTables. 2576 you are unable to take advantage of
2577\noun on
2578LogSQLCreateTables
2579\noun default
2580.
1918 But most people -- even the very security-conscious -- will find that granting 2581 But most people -- even the very security-conscious -- will find that granting
1919 CREATE on the logging database is reasonable. 2582 CREATE on the logging database is reasonable.
1920\layout Quote 2583\layout Quote
1921 2584
1922Defined this only once in the httpd.conf file. 2585This is defined only once in the httpd.conf file.
1923\layout Subsubsection 2586\layout Subsubsection
1924 2587
1925LogSQLMassVirtualHosting 2588LogSQLMassVirtualHosting
@@ -1939,7 +2602,11 @@ Context: main server config
1939 2602
1940If you administer a site hosting many, many virtual hosts then this option 2603If you administer a site hosting many, many virtual hosts then this option
1941 will appeal to you. 2604 will appeal to you.
1942 If you activate LogSQLMassVirtualHosting then several things happen: 2605 If you turn on
2606\noun on
2607LogSQLMassVirtualHosting
2608\noun default
2609 then several things happen:
1943\begin_deeper 2610\begin_deeper
1944\layout Itemize 2611\layout Itemize
1945 2612
@@ -1947,8 +2614,8 @@ the on-the-fly table creation feature is activated automatically
1947\layout Itemize 2614\layout Itemize
1948 2615
1949the transfer log table name is dynamically set from the virtual host's name 2616the transfer log table name is dynamically set from the virtual host's name
1950 (example: a virtual host www.grubbybaby.com gets logged to table access_www_grubb 2617 after stripping out SQL-unfriendly characters (example: a virtual host
1951ybaby_com) 2618 www.grubbybaby.com gets logged to table access_www_grubbybaby_com)
1952\layout Itemize 2619\layout Itemize
1953 2620
1954which, in turn, means that each virtual host logs to its own segregated 2621which, in turn, means that each virtual host logs to its own segregated
@@ -1960,8 +2627,12 @@ which, in turn, means that each virtual host logs to its own segregated
1960\layout Quote 2627\layout Quote
1961 2628
1962This is a huge boost in convenience for sites with many virtual servers. 2629This is a huge boost in convenience for sites with many virtual servers.
1963 Activating LogSQLMassVirtualHosting obviates the need to create every virtual 2630 Activating
1964 server's table and provides more granular security possibilities. 2631\noun on
2632LogSQLMassVirtualHosting
2633\noun default
2634 obviates the need to create every virtual server's table and provides more
2635 granular security possibilities.
1965\layout Quote 2636\layout Quote
1966 2637
1967This is defined only once in the httpd.conf file. 2638This is defined only once in the httpd.conf file.
@@ -1972,7 +2643,19 @@ LogSQLTransferLogTable
1972 2643
1973 2644
1974\series bold 2645\series bold
1975MANDATORY (if not LogSQLMassVirtualHosting) 2646MANDATORY (unless
2647\noun on
2648LogSQLMassVirtualHosting
2649\noun default
2650 is
2651\begin_inset Quotes eld
2652\end_inset
2653
2654on
2655\begin_inset Quotes erd
2656\end_inset
2657
2658)
1976\layout LyX-Code 2659\layout LyX-Code
1977 2660
1978Syntax: LogSQLTransferLogTable table-name 2661Syntax: LogSQLTransferLogTable table-name
@@ -1986,14 +2669,28 @@ Context: virtual host
1986 2669
1987Defines which table is used for logging of Apache's transfers; this is analogous 2670Defines which table is used for logging of Apache's transfers; this is analogous
1988 to Apache's TransferLog directive. 2671 to Apache's TransferLog directive.
1989 table-name must be a valid table within the database defined in LogSQLDatabase. 2672 table-name must be a valid table within the database defined in
2673\noun on
2674LogSQLDatabase
2675\noun default
2676.
1990\layout Quote 2677\layout Quote
1991 2678
1992This directive is not necessary if you declare LogSQLMassVirtualHosting 2679This directive is not necessary if you declare
1993 On, since that directive activates dynamically-named tables. 2680\noun on
1994 If you attempt to use LogSQLTransferLogTable at the same time a warning 2681LogSQLMassVirtualHosting On
1995 will be logged and it will be ignored, since LogSQLMassVirtualHosting takes 2682\noun default
1996 priority. 2683, since that directive activates dynamically-named tables.
2684 If you attempt to use
2685\noun on
2686LogSQLTransferLogTable
2687\noun default
2688 at the same time a warning will be logged and it will be ignored, since
2689
2690\noun on
2691LogSQLMassVirtualHosting
2692\noun default
2693 takes priority.
1997\layout Subsubsection 2694\layout Subsubsection
1998 2695
1999LogSQLRequestIgnore 2696LogSQLRequestIgnore
@@ -2010,17 +2707,29 @@ Context: virtual host
2010\layout Quote 2707\layout Quote
2011 2708
2012Lists a series of strings that, if present in the URI, will cause that request 2709Lists a series of strings that, if present in the URI, will cause that request
2013 NOT to be logged. 2710 to
2014 This directive is useful for cutting down on log clutter when you KNOW 2711\series bold
2015 that you do not want to log requests for certain objects. 2712NOT
2016 (The example above is a genuinely useful one; it will prevent logging of 2713\series default
2017 many common Microsoft-based worm intrusion attempts, as well as those ridiculou 2714 be
2018s requests for the favicon.) 2715\series bold
2716
2717\series default
2718logged.
2719 This directive is useful for cutting down on log clutter when you are certain
2720 that you want to ignore requests for certain objects.
2721 See section
2722\begin_inset LatexCommand \ref{sub:Ignore}
2723
2724\end_inset
2725
2726 for some tips for using this directive.
2019\layout Quote 2727\layout Quote
2020 2728
2021Each string is separated by a space, and NO regular expressions or globbing 2729Each string is separated by a space, and no regular expressions or globbing
2022 are allowed. 2730 are allowed.
2023 Each string is evaluated as a substring of the URI using strstr(). 2731 Each string is evaluated as a substring of the URI using strstr().
2732 The comparison is case sensitive.
2024\layout Subsubsection 2733\layout Subsubsection
2025 2734
2026LogSQLRemhostIgnore 2735LogSQLRemhostIgnore
@@ -2037,18 +2746,33 @@ Context: virtual host
2037\layout Quote 2746\layout Quote
2038 2747
2039Lists a series of strings that, if present in the REMOTE_HOST, will cause 2748Lists a series of strings that, if present in the REMOTE_HOST, will cause
2040 that request NOT to be logged. 2749 that request to
2041 This directive is useful for cutting down on log clutter when you KNOW 2750\series bold
2042 that you do not want to log requests from certain hosts, such as your own 2751NOT
2043 internal network machines. 2752\series default
2753 be logged.
2754 This directive is useful for cutting down on log clutter when you are certain
2755 that you want to ignore requests from certain hosts, such as your own internal
2756 network machines.
2757 See section
2758\begin_inset LatexCommand \ref{sub:Ignore}
2759
2760\end_inset
2761
2762 for some tips for using this directive.
2044\layout Quote 2763\layout Quote
2045 2764
2046Each string is separated by a space, and NO regular expressions or globbing 2765Each string is separated by a space, and no regular expressions or globbing
2047 are allowed. 2766 are allowed.
2048 Each string is simply evaluated as a substring of the REMOTE_HOST using 2767 Each string is evaluated as a substring of the REMOTE_HOST using strstr().
2049 strstr(). 2768 The comparison is case sensitive.
2050\layout Subsubsection 2769\layout Subsubsection
2051 2770
2771
2772\begin_inset LatexCommand \label{sub:Frmat}
2773
2774\end_inset
2775
2052LogSQLTransferLogFormat 2776LogSQLTransferLogFormat
2053\layout LyX-Code 2777\layout LyX-Code
2054 2778
@@ -3345,9 +4069,15 @@ In HTTP, cookies have names to distinguish them from each other.
3345 a name with the CookieName directive. 4069 a name with the CookieName directive.
3346\layout Quote 4070\layout Quote
3347 4071
3348You must include a 'c' character in LogSQLTransferLogFormat for this directive 4072You must include a 'c' character in
3349 to take effect; once you specify 'c', LogSQLWhichCookie tells mod_log_sql 4073\noun on
3350 which cookie to log. 4074LogSQLTransferLogFormat
4075\noun default
4076 for this directive to take effect; once you specify 'c',
4077\noun on
4078LogSQLWhichCookie
4079\noun default
4080 tells mod_log_sql which cookie to log.
3351 This is necessary because many times you will be setting and receiving 4081 This is necessary because many times you will be setting and receiving
3352 more than one cookie from a client; without this directive you'd be unable 4082 more than one cookie from a client; without this directive you'd be unable
3353 to choose which cookie is your mod_usertrack cookie. 4083 to choose which cookie is your mod_usertrack cookie.
@@ -3377,15 +4107,27 @@ Context: virtual host
3377\layout Quote 4107\layout Quote
3378 4108
3379Defines the list of cookies you would like logged. 4109Defines the list of cookies you would like logged.
3380 This works in conjunction with LogSQLCookieLogTable. 4110 This works in conjunction with
4111\noun on
4112LogSQLCookieLogTable
4113\noun default
4114.
3381 This directive does not require any additional characters to be added to 4115 This directive does not require any additional characters to be added to
3382 the LogSQLTransferLogFormat string. 4116 the
4117\noun on
4118LogSQLTransferLogFormat
4119\noun default
4120 string.
3383 The feature is activated simply by including this directive, upon which 4121 The feature is activated simply by including this directive, upon which
3384 you will begin populating the separate cookie table with data. 4122 you will begin populating the separate cookie table with data.
3385\layout Quote 4123\layout Quote
3386 4124
3387Note that you must have already created the table (see create-tables.sql, 4125Note that you must have already created the table (see create-tables.sql,
3388 included in the package), or LogSQLCreateTables must be set to 4126 included in the package), or
4127\noun on
4128LogSQLCreateTables
4129\noun default
4130 must be set to
3389\begin_inset Quotes eld 4131\begin_inset Quotes eld
3390\end_inset 4132\end_inset
3391 4133
@@ -3413,15 +4155,27 @@ Context: virtual host
3413\layout Quote 4155\layout Quote
3414 4156
3415Defines the list of inbound headers you would like logged. 4157Defines the list of inbound headers you would like logged.
3416 This works in conjunction with LogSQLHeadersInLogTable. 4158 This works in conjunction with
4159\noun on
4160 LogSQLHeadersInLogTable
4161\noun default
4162.
3417 This directive does not require any additional characters to be added to 4163 This directive does not require any additional characters to be added to
3418 the LogSQLTransferLogFormat string. 4164 the
4165\noun on
4166LogSQLTransferLogFormat
4167\noun default
4168 string.
3419 The feature is activated simply by including this directive, upon which 4169 The feature is activated simply by including this directive, upon which
3420 you will begin populating the separate inbound-headers table with data. 4170 you will begin populating the separate inbound-headers table with data.
3421\layout Quote 4171\layout Quote
3422 4172
3423Note that you must have already created the table (see create-tables.sql, 4173Note that you must have already created the table (see create-tables.sql,
3424 included in the package), or LogSQLCreateTables must be set to 4174 included in the package), or
4175\noun on
4176LogSQLCreateTables
4177\noun default
4178 must be set to
3425\begin_inset Quotes eld 4179\begin_inset Quotes eld
3426\end_inset 4180\end_inset
3427 4181
@@ -3449,15 +4203,27 @@ Context: virtual host
3449\layout Quote 4203\layout Quote
3450 4204
3451Defines the list of outbound headers you would like logged. 4205Defines the list of outbound headers you would like logged.
3452 This works in conjunction with LogSQLHeadersOutLogTable. 4206 This works in conjunction with
4207\noun on
4208LogSQLHeadersOutLogTable
4209\noun default
4210.
3453 This directive does not require any additional characters to be added to 4211 This directive does not require any additional characters to be added to
3454 the LogSQLTransferLogFormat string. 4212 the
4213\noun on
4214LogSQLTransferLogFormat
4215\noun default
4216 string.
3455 The feature is activated simply by including this directive, upon which 4217 The feature is activated simply by including this directive, upon which
3456 you will begin populating the separate outbound-headers table with data. 4218 you will begin populating the separate outbound-headers table with data.
3457\layout Quote 4219\layout Quote
3458 4220
3459Note that you must have already created the table (see create-tables.sql, 4221Note that you must have already created the table (see create-tables.sql,
3460 included in the package), or LogSQLCreateTables must be set to 4222 included in the package), or
4223\noun on
4224LogSQLCreateTables
4225\noun default
4226 must be set to
3461\begin_inset Quotes eld 4227\begin_inset Quotes eld
3462\end_inset 4228\end_inset
3463 4229
@@ -3485,15 +4251,27 @@ Context: virtual host
3485\layout Quote 4251\layout Quote
3486 4252
3487Defines the list of notes you would like logged. 4253Defines the list of notes you would like logged.
3488 This works in conjunction with LogSQLNotesLogTable. 4254 This works in conjunction with
4255\noun on
4256LogSQLNotesLogTable
4257\noun default
4258.
3489 This directive does not require any additional characters to be added to 4259 This directive does not require any additional characters to be added to
3490 the LogSQLTransferLogFormat string. 4260 the
4261\noun on
4262LogSQLTransferLogFormat
4263\noun default
4264 string.
3491 The feature is activated simply by including this directive, upon which 4265 The feature is activated simply by including this directive, upon which
3492 you will begin populating the separate notes table with data. 4266 you will begin populating the separate notes table with data.
3493\layout Quote 4267\layout Quote
3494 4268
3495Note that you must have already created the table (see create-tables.sql, 4269Note that you must have already created the table (see create-tables.sql,
3496 included in the package), or LogSQLCreateTables must be set to 4270 included in the package), or
4271\noun on
4272LogSQLCreateTables
4273\noun default
4274 must be set to
3497\begin_inset Quotes eld 4275\begin_inset Quotes eld
3498\end_inset 4276\end_inset
3499 4277
@@ -3520,14 +4298,21 @@ Context: virtual host
3520\layout Quote 4298\layout Quote
3521 4299
3522Defines which table is used for logging of cookies. 4300Defines which table is used for logging of cookies.
3523 Working in conjunction with LogSQLWhichCookies, you can log many of each 4301 Working in conjunction with
3524 request's associated cookies to a separate table. 4302\noun on
4303LogSQLWhichCookies
4304\noun default
4305, you can log many of each request's associated cookies to a separate table.
3525 For meaningful data retrieval the cookie table is keyed to the access table 4306 For meaningful data retrieval the cookie table is keyed to the access table
3526 by the unique request ID supplied by the standard Apache module mod_unique_id. 4307 by the unique request ID supplied by the standard Apache module mod_unique_id.
3527\layout Quote 4308\layout Quote
3528 4309
3529Note that you must create the table (see create-tables.sql, included in the 4310Note that you must create the table (see create-tables.sql, included in the
3530 package), or LogSQLCreateTables must be set to 4311 package), or
4312\noun on
4313LogSQLCreateTables
4314\noun default
4315 must be set to
3531\begin_inset Quotes eld 4316\begin_inset Quotes eld
3532\end_inset 4317\end_inset
3533 4318
@@ -3554,15 +4339,22 @@ Context: virtual host
3554\layout Quote 4339\layout Quote
3555 4340
3556Defines which table is used for logging of inbound headers. 4341Defines which table is used for logging of inbound headers.
3557 Working in conjunction with LogSQLWhichHeadersIn, you can log many of each 4342 Working in conjunction with
3558 request's associated headers to a separate table. 4343\noun on
4344LogSQLWhichHeadersIn
4345\noun default
4346, you can log many of each request's associated headers to a separate table.
3559 For meaningful data retrieval the headers table is keyed to the access 4347 For meaningful data retrieval the headers table is keyed to the access
3560 table by the unique request ID supplied by the standard Apache module mod_uniqu 4348 table by the unique request ID supplied by the standard Apache module mod_uniqu
3561e_id. 4349e_id.
3562\layout Quote 4350\layout Quote
3563 4351
3564Note that you must create the table (see create-tables.sql, included in the 4352Note that you must create the table (see create-tables.sql, included in the
3565 package), or LogSQLCreateTables must be set to 4353 package), or
4354\noun on
4355LogSQLCreateTables
4356\noun default
4357 must be set to
3566\begin_inset Quotes eld 4358\begin_inset Quotes eld
3567\end_inset 4359\end_inset
3568 4360
@@ -3589,15 +4381,22 @@ Context: virtual host
3589\layout Quote 4381\layout Quote
3590 4382
3591Defines which table is used for logging of outbound headers. 4383Defines which table is used for logging of outbound headers.
3592 Working in conjunction with LogSQLWhichHeadersOut, you can log many of 4384 Working in conjunction with
3593 each request's associated headers to a separate table. 4385\noun on
4386LogSQLWhichHeadersOut
4387\noun default
4388, you can log many of each request's associated headers to a separate table.
3594 For meaningful data retrieval the headers table is keyed to the access 4389 For meaningful data retrieval the headers table is keyed to the access
3595 table by the unique request ID supplied by the standard Apache module mod_uniqu 4390 table by the unique request ID supplied by the standard Apache module mod_uniqu
3596e_id. 4391e_id.
3597\layout Quote 4392\layout Quote
3598 4393
3599Note that you must create the table (see create-tables.sql, included in the 4394Note that you must create the table (see create-tables.sql, included in the
3600 package), or LogSQLCreateTables must be set to 4395 package), or
4396\noun on
4397LogSQLCreateTables
4398\noun default
4399 must be set to
3601\begin_inset Quotes eld 4400\begin_inset Quotes eld
3602\end_inset 4401\end_inset
3603 4402
@@ -3624,14 +4423,21 @@ Context: virtual host
3624\layout Quote 4423\layout Quote
3625 4424
3626Defines which table is used for logging of notes. 4425Defines which table is used for logging of notes.
3627 Working in conjunction with LogSQLWhichNotes, you can log many of each 4426 Working in conjunction with
3628 request's associated notes to a separate table. 4427\noun on
4428LogSQLWhichNotes
4429\noun default
4430, you can log many of each request's associated notes to a separate table.
3629 For meaningful data retrieval the notes table is keyed to the access table 4431 For meaningful data retrieval the notes table is keyed to the access table
3630 by the unique request ID supplied by the standard Apache module mod_unique_id. 4432 by the unique request ID supplied by the standard Apache module mod_unique_id.
3631\layout Quote 4433\layout Quote
3632 4434
3633Note that you must create the table (see create-tables.sql, included in the 4435Note that you must create the table (see create-tables.sql, included in the
3634 package), or LogSQLCreateTables must be set to 4436 package), or
4437\noun on
4438LogSQLCreateTables
4439\noun default
4440 must be set to
3635\begin_inset Quotes eld 4441\begin_inset Quotes eld
3636\end_inset 4442\end_inset
3637 4443
@@ -3645,6 +4451,11 @@ on
3645FAQ 4451FAQ
3646\layout Subsection 4452\layout Subsection
3647 4453
4454
4455\begin_inset LatexCommand \label{sub:why}
4456
4457\end_inset
4458
3648Why log to an SQL database? 4459Why log to an SQL database?
3649\layout Standard 4460\layout Standard
3650 4461
@@ -4510,8 +5321,11 @@ alter table acc_log_tbl add column cookie varchar(255);
4510\layout Standard 5321\layout Standard
4511 5322
4512Next configure your server to set usertracking cookies as follows, and make 5323Next configure your server to set usertracking cookies as follows, and make
4513 sure you include the new 'c' directive in your LogSQLTransferLogFormat, 5324 sure you include the new 'c' directive in your
4514 which activates cookie logging. 5325\noun on
5326LogSQLTransferLogFormat
5327\noun default
5328, which activates cookie logging.
4515 Here's an example: 5329 Here's an example:
4516\layout LyX-Code 5330\layout LyX-Code
4517 5331
@@ -4543,9 +5357,27 @@ The first three lines configure mod_usertrack to create a COOKIE (RFC 2109)
4543 be sent to the server by the client. 5357 be sent to the server by the client.
4544\layout Standard 5358\layout Standard
4545 5359
4546FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that even 5360Recap: the 'c' character
4547 newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2 (RFC 2965) 5361\emph on
4548 format. 5362activates
5363\emph default
5364 cookie logging, and the
5365\noun on
5366LogSQLWhichCookie
5367\noun default
5368 directive
5369\emph on
5370chooses
5371\emph default
5372which cookie to log.
5373\layout Standard
5374
5375FYI, you are advised NOT to use
5376\noun on
5377CookieStyle Cookie2
5378\noun default
5379 -- it seems that even newer browsers (IE 5.5, etc.) have trouble with the
5380 new COOKIE2 (RFC 2965) format.
4549 Just stick with the standard COOKIE format and you'll be fine. 5381 Just stick with the standard COOKIE format and you'll be fine.
4550\layout Standard 5382\layout Standard
4551 5383
@@ -4659,12 +5491,24 @@ If you are interested in logging only one cookie per request, follow the
4659 5491
4660 above. 5492 above.
4661 That cookie will be logged to a column in the regular access_log table, 5493 That cookie will be logged to a column in the regular access_log table,
4662 and the actual cookie you want to log is specified with LogSQLWhichCookie. 5494 and the actual cookie you want to log is specified with
4663 Don't forget to specify the 'c' character in LogSQLTransferLogFormat. 5495\noun on
5496 LogSQLWhichCookie
5497\noun default
5498.
5499 Don't forget to specify the 'c' character in
5500\noun on
5501LogSQLTransferLogFormat
5502\noun default
5503.
4664\layout Standard 5504\layout Standard
4665 5505
4666If, however, you need to log multiple cookies per request, you must employ 5506If, however, you need to log multiple cookies per request, you must employ
4667 the LogSQLWhichCookies (note the plural) directive. 5507 the
5508\noun on
5509LogSQLWhichCookies
5510\noun default
5511 (note the plural) directive.
4668 The cookies you specify will be logged to a separate table (as discussed 5512 The cookies you specify will be logged to a separate table (as discussed
4669 in section 5513 in section
4670\begin_inset LatexCommand \ref{secMulTable} 5514\begin_inset LatexCommand \ref{secMulTable}
@@ -4675,16 +5519,31 @@ If, however, you need to log multiple cookies per request, you must employ
4675 via the unique ID that is supplied by mod_unique_id. 5519 via the unique ID that is supplied by mod_unique_id.
4676 Without mod_unique_id the information will still be logged but you will 5520 Without mod_unique_id the information will still be logged but you will
4677 be unable to correlate which cookies go with which access-requests. 5521 be unable to correlate which cookies go with which access-requests.
4678 Furthermore, with LogSQLWhichCookies, you do 5522 Furthermore, with
5523\noun on
5524LogSQLWhichCookies
5525\noun default
5526, you do
4679\series bold 5527\series bold
4680not 5528not
4681\series default 5529\series default
4682 need to include the 'c' character in LogSQLTransferLogFormat. 5530 need to include the 'c' character in
5531\noun on
5532LogSQLTransferLogFormat
5533\noun default
5534.
4683\layout Standard 5535\layout Standard
4684 5536
4685LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict because 5537
4686 they operate on entireley different tables, but you're better off choosing 5538\noun on
4687 the one you need. 5539LogSQLWhichCookie
5540\noun default
5541 and
5542\noun on
5543LogSQLWhichCookies
5544\noun default
5545 can coexist without conflict because they operate on entireley different
5546 tables, but you're better off choosing the one you need.
4688\layout Subsection 5547\layout Subsection
4689 5548
4690What are the SSL logging features, and how do I activate them? 5549What are the SSL logging features, and how do I activate them?
@@ -4692,9 +5551,12 @@ What are the SSL logging features, and how do I activate them?
4692 5551
4693If you run an SSL-enabled server you may benefit from logging some SSL details. 5552If you run an SSL-enabled server you may benefit from logging some SSL details.
4694 mod_log_sql now supports this ability. 5553 mod_log_sql now supports this ability.
4695 By adding certain characters to your LogSQLTransferLogFormat string you 5554 By adding certain characters to your
4696 can tell mod_log_sql to log the SSL cipher, the SSL keysize of the connection, 5555\noun on
4697 and the Max-keysize that was available. 5556LogSQLTransferLogFormat
5557\noun default
5558 string you can tell mod_log_sql to log the SSL cipher, the SSL keysize
5559 of the connection, and the Max-keysize that was available.
4698 This would let you tell, for example, which clients were using only export-grad 5560 This would let you tell, for example, which clients were using only export-grad
4699e security to access your secure software area. 5561e security to access your secure software area.
4700\layout Standard 5562\layout Standard
@@ -4953,8 +5815,12 @@ It depends! This actually is not determined by mod_log_sql.
4953\layout Standard 5815\layout Standard
4954 5816
4955You do have control over where mod_log_sql looks for the socket. 5817You do have control over where mod_log_sql looks for the socket.
4956 The LogSQLSocketFile runtime configuration directive overrides the default 5818 The
4957 of "/var/lib/mysql/mysql.sock" to whatever you wish. 5819\noun on
5820LogSQLSocketFile
5821\noun default
5822 runtime configuration directive overrides the default of "/var/lib/mysql/mysql.s
5823ock" to whatever you wish.
4958 (Applies to mod_log_sql 1.16 or later only.) 5824 (Applies to mod_log_sql 1.16 or later only.)
4959\layout Subsection 5825\layout Subsection
4960 5826