diff options
Diffstat (limited to 'Documentation')
-rw-r--r-- | Documentation/documentation.lyx | 2969 | ||||
-rw-r--r-- | Documentation/documentation.tex | 1534 |
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 | ||
33 | Installing and Running mod_log_sql | ||
34 | \layout Author | ||
35 | |||
36 | Christopher Powell | ||
37 | \layout Date | ||
38 | |||
39 | 11/13/02 | ||
40 | \layout Standard | ||
41 | \pagebreak_bottom | ||
42 | |||
43 | \begin_inset LatexCommand \tableofcontents{} | ||
44 | |||
45 | \end_inset | ||
46 | |||
47 | |||
48 | \layout Section | ||
49 | |||
50 | Introduction | ||
51 | \layout Subsection | ||
52 | |||
53 | Homepage | ||
54 | \layout LyX-Code | ||
55 | |||
56 | http://www.grubbybaby.com/mod_log_sql/ | ||
57 | \layout Subsection | ||
58 | |||
59 | Summary | ||
60 | \layout Standard | ||
61 | |||
62 | This 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 | |||
70 | Approach | ||
71 | \layout Standard | ||
72 | |||
73 | This 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 | |||
80 | In 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 | |||
87 | Virtual 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 | |||
93 | SQL 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 | |||
98 | A 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 | |||
116 | Supported directives | ||
117 | \layout Standard | ||
118 | |||
119 | Please see the web-based documentation for full explanation of all supported | ||
120 | run-time directives. | ||
121 | \layout Standard | ||
122 | |||
123 | http://www.grubbybaby.com/mod_log_sql/directives.html | ||
124 | \layout Standard | ||
125 | |||
126 | See the FAQ for some handy examples: | ||
127 | \layout Standard | ||
128 | |||
129 | http://www.grubbybaby.com/mod_log_sql/faq.html | ||
130 | \layout Subsection | ||
131 | |||
132 | What gets logged by default? | ||
133 | \layout Standard | ||
134 | |||
135 | All 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 | |||
141 | The online documentation of the run-time directives includes a full explanation | ||
142 | of what you can log, including examples. | ||
143 | \layout Subsection | ||
144 | |||
145 | Minor Notes | ||
146 | \layout Itemize | ||
147 | |||
148 | Note 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 | |||
153 | The '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 | ||
156 | s: 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 | |||
165 | In 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 | |||
169 | select remote_host,request_uri,from_unixtime(time_stamp) from access_log; | ||
170 | \layout Standard | ||
171 | |||
172 | The enclosed perl program | ||
173 | \begin_inset Quotes sld | ||
174 | \end_inset | ||
175 | |||
176 | make_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 | |||
186 | The 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 | ||
188 | gth 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 | |||
193 | Be 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 | |||
199 | Apache 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 | |||
207 | Author / Maintainer | ||
208 | \layout Standard | ||
209 | |||
210 | The 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 | |||
214 | The MySQL routines and directives were added by Zeev Suraski <bourbon@netvision.n | ||
215 | et.il>. | ||
216 | \layout Standard | ||
217 | |||
218 | All 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 | |||
224 | Installation | ||
225 | \layout Subsection | ||
226 | |||
227 | Requirements | ||
228 | \layout Itemize | ||
229 | |||
230 | A 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 | |||
235 | Apache 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 | |||
241 | The 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 | |||
247 | MySQL >= 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 | |||
252 | Again, 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 | |||
257 | Additionally, 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 | |||
262 | Do I want a DSO? | ||
263 | \layout Standard | ||
264 | |||
265 | You 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 | |||
274 | FWIW, 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 | |||
279 | Installation as an Apache DSO (Preferred) | ||
280 | \layout Enumerate | ||
281 | |||
282 | Perform 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 | |||
292 | Edit Makefile for your system. | ||
293 | \newline | ||
294 | |||
295 | \begin_deeper | ||
296 | \layout Standard | ||
297 | |||
298 | NECESSARY: | ||
299 | \layout Itemize | ||
300 | |||
301 | The location where you installed Apache -- usually /usr/local/apache, 'locate | ||
302 | apxs' can help you find it. | ||
303 | \layout Itemize | ||
304 | |||
305 | The location of your MySQL libraries, find using 'locate libmysqlclient' | ||
306 | \layout Itemize | ||
307 | |||
308 | The location of your MySQL header files, find using 'locate mysql.h' | ||
309 | \layout Standard | ||
310 | |||
311 | OPTIONAL 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 | |||
315 | The location of your SSL header files, find using 'locate mod_ssl.h' | ||
316 | \layout Standard | ||
317 | |||
318 | Now that you know these things, edit Makefile and replace the stock values | ||
319 | with your own. | ||
320 | \layout Standard | ||
321 | |||
322 | IMPORTANT: 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 | |||
330 | Instruct 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 | |||
338 | You 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 | |||
347 | gcc -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/ | ||
349 | Apache/include -O2 -Wall -DEAPI -I/usr/include/mysql -I/usr/local/src/apache_1.3.2 | ||
350 | 7-dso/src/modules/ssl -c mod_log_sql.c | ||
351 | \layout LyX-Code | ||
352 | |||
353 | gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -L/usr/li | ||
354 | b -lmysqlclient -lz -lm -lcrypt -ldb | ||
355 | \layout Standard | ||
356 | |||
357 | You should see no errors and have a file called "mod_log_sql.so" in your | ||
358 | directory. | ||
359 | \end_deeper | ||
360 | \layout Enumerate | ||
361 | |||
362 | Instruct apxs to install the DSO. | ||
363 | \begin_deeper | ||
364 | \layout LyX-Code | ||
365 | |||
366 | # make dsoinstall | ||
367 | \layout Standard | ||
368 | |||
369 | You 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 | |||
375 | cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so | ||
376 | \layout LyX-Code | ||
377 | |||
378 | chmod 755 /usr/local/Apache/libexec/mod_log_sql.so | ||
379 | \end_deeper | ||
380 | \layout Enumerate | ||
381 | |||
382 | Module 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 | |||
387 | LoadModule ssl_module libexec/libssl.so | ||
388 | \layout Standard | ||
389 | |||
390 | comes before | ||
391 | \layout LyX-Code | ||
392 | |||
393 | LoadModule sql_log_module libexec/mod_log_sql.so | ||
394 | \layout Standard | ||
395 | |||
396 | If 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 | |||
409 | Now skip below to the "Configuration" section. | ||
410 | \end_deeper | ||
411 | \layout Subsection | ||
412 | |||
413 | Installation as a static module compiled into httpd | ||
414 | \layout Enumerate | ||
415 | |||
416 | Perform all the following steps as root so that you have install privs, | ||
417 | etc. | ||
418 | \layout Enumerate | ||
419 | |||
420 | Unpack 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 | |||
433 | Edit Makefile for your system. | ||
434 | \newline | ||
435 | |||
436 | \begin_deeper | ||
437 | \layout Standard | ||
438 | |||
439 | NECESSARY: | ||
440 | \layout Itemize | ||
441 | |||
442 | The location where you installed Apache -- usually /usr/local/apache, 'locate | ||
443 | apxs' can help you find it. | ||
444 | |||
445 | \layout Itemize | ||
446 | |||
447 | The location of your Apache *sources*, find using 'locate ABOUT_APACHE' | ||
448 | |||
449 | \layout Itemize | ||
450 | |||
451 | The location of your MySQL header files, find using 'locate mysql.h' | ||
452 | \layout Itemize | ||
453 | |||
454 | The location of your MySQL libraries, find using 'locate libmysqlclient' | ||
455 | \layout Standard | ||
456 | |||
457 | OPTIONAL 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 | |||
461 | The location of your mod_ssl header files, find using 'locate mod_ssl.h' | ||
462 | \layout Itemize | ||
463 | |||
464 | The location of your OpenSSL header files, find using 'locate x509.h' | ||
465 | \layout Itemize | ||
466 | |||
467 | The location of your db1 header files, find using 'locate mpool.h' | ||
468 | \layout Standard | ||
469 | |||
470 | Now that you know these things, edit Makefile and replace the stock values | ||
471 | with your own. | ||
472 | \layout Standard | ||
473 | |||
474 | IMPORTANT: 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 | |||
486 | Change 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 | |||
494 | Re-make your httpd binary as follows. | ||
495 | \begin_deeper | ||
496 | \layout Enumerate | ||
497 | |||
498 | Edit Configuration.apaci as follows... | ||
499 | \begin_deeper | ||
500 | \layout Itemize | ||
501 | |||
502 | Append 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 | |||
514 | Find the mod_log_config.o line, and add this line immediately after it: | ||
515 | \layout LyX-Code | ||
516 | |||
517 | AddModule 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 | |||
534 | Test your new apache binary: | ||
535 | \begin_deeper | ||
536 | \layout LyX-Code | ||
537 | |||
538 | # ./httpd -l | ||
539 | \end_deeper | ||
540 | \layout Enumerate | ||
541 | |||
542 | You should see something like: | ||
543 | \begin_deeper | ||
544 | \layout LyX-Code | ||
545 | |||
546 | Compiled-in modules: | ||
547 | \layout LyX-Code | ||
548 | |||
549 | http_core.c | ||
550 | \layout LyX-Code | ||
551 | |||
552 | mod_log_sql.c <-- That's the line you're looking for. | ||
553 | \layout LyX-Code | ||
554 | |||
555 | mod_env.c | ||
556 | \layout LyX-Code | ||
557 | |||
558 | mod_log_config.c | ||
559 | \layout LyX-Code | ||
560 | |||
561 | mod_mime.c | ||
562 | \layout LyX-Code | ||
563 | |||
564 | mod_negotiation.c ... | ||
565 | \layout LyX-Code | ||
566 | |||
567 | etc... | ||
568 | \end_deeper | ||
569 | \layout Enumerate | ||
570 | |||
571 | Install 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 | |||
588 | Configuration | ||
589 | \layout Subsection | ||
590 | |||
591 | Preparing MySQL | ||
592 | \layout Standard | ||
593 | |||
594 | You 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 | |||
599 | This 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 | ||
601 | bybaby.com/mod_log_sql/directives.html | ||
602 | \layout Enumerate | ||
603 | |||
604 | mod_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 | |||
612 | We 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 | |||
621 | If 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 | |||
626 | mysql> source create_tables.sql | ||
627 | \end_deeper | ||
628 | \layout Enumerate | ||
629 | |||
630 | Create 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 | |||
641 | mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com | ||
642 | identified by 'l0gger'; | ||
643 | \end_deeper | ||
644 | \layout Enumerate | ||
645 | |||
646 | You 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 | |||
655 | mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com identified | ||
656 | by 'l0gger'; | ||
657 | \end_deeper | ||
658 | \layout Enumerate | ||
659 | |||
660 | Enable 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 | |||
666 | log=/var/log/mysql-messages | ||
667 | \layout Standard | ||
668 | |||
669 | Then restart MySQL. | ||
670 | \layout LyX-Code | ||
671 | |||
672 | # /etc/rc.d/init.d/mysql restart | ||
673 | \end_deeper | ||
674 | \layout Subsection | ||
675 | |||
676 | Preparing Apache | ||
677 | \layout Enumerate | ||
678 | |||
679 | Tell the module what database to use and the appropriate authentication | ||
680 | information. | ||
681 | \begin_deeper | ||
682 | \layout Description | ||
683 | |||
684 | EXAMPLE: 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 | |||
690 | So, 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 | |||
696 | LogSQLDatabase apachelogs | ||
697 | \layout LyX-Code | ||
698 | |||
699 | LogSQLLoginInfo dbmachine.foo.com loguser l0gger | ||
700 | \layout LyX-Code | ||
701 | |||
702 | LogSQLCreateTables on | ||
703 | \end_deeper | ||
704 | \layout Enumerate | ||
705 | |||
706 | If 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 | |||
711 | LogSQLSocketFile /your/path/to/mysql.sock | ||
712 | \end_deeper | ||
713 | \layout Enumerate | ||
714 | |||
715 | The 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 | |||
721 | access_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 | |||
737 | LogSQLTransferLogTable access_log | ||
738 | \layout LyX-Code | ||
739 | |||
740 | [snip] | ||
741 | \layout LyX-Code | ||
742 | |||
743 | </VirtualHost> | ||
744 | \end_deeper | ||
745 | \layout Enumerate | ||
746 | |||
747 | Restart apache. | ||
748 | \begin_deeper | ||
749 | \layout LyX-Code | ||
750 | |||
751 | # /etc/rc.d/init.d/httpd start | ||
752 | \end_deeper | ||
753 | \layout Subsection | ||
754 | |||
755 | Testing the module | ||
756 | \layout Enumerate | ||
757 | |||
758 | Load 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 | |||
767 | Enter 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 | |||
774 | You 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 | |||
780 | If 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 | |||
786 | Improper privileges set up in the MySQL database | ||
787 | \layout Itemize | ||
788 | |||
789 | You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry | ||
790 | |||
791 | \layout Itemize | ||
792 | |||
793 | You didn't specify the right host | ||
794 | \layout Standard | ||
795 | |||
796 | If 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 | |||
806 | The 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 | |||
814 | You can now activate the advanced features of mod_log_sql. | ||
815 | These are all described in the next section. | ||
816 | \layout Subsection | ||
817 | |||
818 | Configuration directive reference | ||
819 | \layout Subsubsection | ||
820 | |||
821 | LogSQLLoginInfo | ||
822 | \layout LyX-Code | ||
823 | |||
824 | |||
825 | \series bold | ||
826 | MANDATORY | ||
827 | \series default | ||
828 | |||
829 | \layout LyX-Code | ||
830 | |||
831 | Syntax: LogSQLLoginInfo host user password | ||
832 | \layout LyX-Code | ||
833 | |||
834 | Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd | ||
835 | \layout LyX-Code | ||
836 | |||
837 | Context: server config | ||
838 | \layout Quote | ||
839 | |||
840 | Defines 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 | |||
848 | LogSQLDatabase | ||
849 | \layout LyX-Code | ||
850 | |||
851 | |||
852 | \series bold | ||
853 | MANDATORY | ||
854 | \layout LyX-Code | ||
855 | |||
856 | Syntax: LogSQLDatabase database | ||
857 | \layout LyX-Code | ||
858 | |||
859 | Example: LogSQLDatabase loggingdb | ||
860 | \layout LyX-Code | ||
861 | |||
862 | Context: server config | ||
863 | \layout Quote | ||
864 | |||
865 | Defines 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 | |||
870 | LogSQLTransferLogTable | ||
871 | \layout LyX-Code | ||
872 | |||
873 | |||
874 | \series bold | ||
875 | MANDATORY | ||
876 | \layout LyX-Code | ||
877 | |||
878 | Syntax: LogSQLTransferLogTable table-name | ||
879 | \layout LyX-Code | ||
880 | |||
881 | Example: LogSQLTransferLogTable access_log_table | ||
882 | \layout LyX-Code | ||
883 | |||
884 | Context: virtual host | ||
885 | \layout Quote | ||
886 | |||
887 | Defines 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 | |||
892 | This 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 | |||
899 | LogSQLRequestIgnore | ||
900 | \layout LyX-Code | ||
901 | |||
902 | Syntax: LogSQLRequestIgnore req1 req2 req3 ... | ||
903 | reqN | ||
904 | \layout LyX-Code | ||
905 | |||
906 | Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico | ||
907 | \layout LyX-Code | ||
908 | |||
909 | Context: virtual host | ||
910 | \layout Quote | ||
911 | |||
912 | Lists 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 | ||
918 | s requests for the favicon.) | ||
919 | \layout Quote | ||
920 | |||
921 | Each 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 | |||
926 | LogSQLRemhostIgnore | ||
927 | \layout LyX-Code | ||
928 | |||
929 | Syntax: LogSQLRemhostIgnore host1 host2 host3 ... | ||
930 | hostN | ||
931 | \layout LyX-Code | ||
932 | |||
933 | Example: LogSQLRemhostIgnore localnet.com | ||
934 | \layout LyX-Code | ||
935 | |||
936 | Context: virtual host | ||
937 | \layout Quote | ||
938 | |||
939 | Lists 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 | |||
946 | Each 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 | |||
951 | LogSQLRefererIgnore | ||
952 | \layout LyX-Code | ||
953 | |||
954 | Syntax: LogSQLRefererIgnore ref1 ref2 ref3 ... | ||
955 | refN | ||
956 | \layout LyX-Code | ||
957 | |||
958 | Example: LogSQLRefererIgnore google.com | ||
959 | \layout LyX-Code | ||
960 | |||
961 | Context: virtual host | ||
962 | \layout Quote | ||
963 | |||
964 | Lists 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 | |||
970 | Each 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 | |||
975 | LogSQLWhichCookie | ||
976 | \layout LyX-Code | ||
977 | |||
978 | Syntax: LogSQLWhichCookie cookiename | ||
979 | \layout LyX-Code | ||
980 | |||
981 | Example: LogSQLWhichCookie Clicks | ||
982 | \layout LyX-Code | ||
983 | |||
984 | Default: None. | ||
985 | You must set this if you wish to capture cookies. | ||
986 | \layout LyX-Code | ||
987 | |||
988 | Context: virtual host | ||
989 | \layout Quote | ||
990 | |||
991 | In 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 | |||
996 | If you have activated cookie logging in LogSQLTransferLogFormat, then LogSQLWhic | ||
997 | hCookie 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 | |||
1003 | Note: 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 | |||
1009 | LogSQLCreateTables | ||
1010 | \layout LyX-Code | ||
1011 | |||
1012 | Syntax: LogSQLCreateTables flag | ||
1013 | \layout LyX-Code | ||
1014 | |||
1015 | Example: LogSQLCreateTables On | ||
1016 | \layout LyX-Code | ||
1017 | |||
1018 | Default: Off | ||
1019 | \layout LyX-Code | ||
1020 | |||
1021 | Context: server config | ||
1022 | \layout Quote | ||
1023 | |||
1024 | mod_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 | |||
1031 | There 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 | |||
1041 | This is defined only once in the httpd.conf file. | ||
1042 | \layout Subsubsection | ||
1043 | |||
1044 | LogSQLMassVirtualHosting | ||
1045 | \layout LyX-Code | ||
1046 | |||
1047 | Syntax: LogSQLMassVirtualHosting flag | ||
1048 | \layout LyX-Code | ||
1049 | |||
1050 | Example: LogSQLMassVirtualHosting On | ||
1051 | \layout LyX-Code | ||
1052 | |||
1053 | Default: Off | ||
1054 | \layout LyX-Code | ||
1055 | |||
1056 | Context: server config | ||
1057 | \layout Quote | ||
1058 | |||
1059 | If 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 | |||
1065 | the on-the-fly table creation feature is activated automatically | ||
1066 | \layout Itemize | ||
1067 | |||
1068 | the 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 | ||
1070 | ybaby_com) | ||
1071 | \layout Itemize | ||
1072 | |||
1073 | which, 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 | |||
1081 | This 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 | |||
1086 | This is defined only once in the httpd.conf file. | ||
1087 | \layout Subsubsection | ||
1088 | |||
1089 | LogSQLPreserveFile | ||
1090 | \layout LyX-Code | ||
1091 | |||
1092 | Syntax: LogSQLPreserveFile filename | ||
1093 | \layout LyX-Code | ||
1094 | |||
1095 | Example: LogSQLPreserveFile offline-preserve | ||
1096 | \layout LyX-Code | ||
1097 | |||
1098 | Default: mysql-preserve (in /tmp) | ||
1099 | \layout LyX-Code | ||
1100 | |||
1101 | Context: virtual host | ||
1102 | \layout Quote | ||
1103 | |||
1104 | mod_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 | |||
1116 | For security purposes the name you supply will be prepended with "/tmp/" | ||
1117 | to force creation of the file in /tmp. | ||
1118 | \layout Quote | ||
1119 | |||
1120 | If 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 | |||
1129 | The 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 | |||
1135 | The 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 | |||
1143 | LogSQLSocketFile | ||
1144 | \layout LyX-Code | ||
1145 | |||
1146 | Syntax: LogSQLSocketFile filename | ||
1147 | \layout LyX-Code | ||
1148 | |||
1149 | Example: LogSQLSocketFile /tmp/mysql.sock | ||
1150 | \layout LyX-Code | ||
1151 | |||
1152 | Default: /var/lib/mysql/mysql.sock | ||
1153 | \layout LyX-Code | ||
1154 | |||
1155 | Context: server config | ||
1156 | \layout Quote | ||
1157 | |||
1158 | At 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 | |||
1163 | mod_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 | |||
1170 | LogSQLTransferLogFormat | ||
1171 | \layout LyX-Code | ||
1172 | |||
1173 | Syntax: LogSQLTransferLogFormat format-string | ||
1174 | \layout LyX-Code | ||
1175 | |||
1176 | Example: LogSQLTransferLogFormat huSUTv | ||
1177 | \layout LyX-Code | ||
1178 | |||
1179 | Default: AbHhmRSsTUuv | ||
1180 | \layout LyX-Code | ||
1181 | |||
1182 | Context: virtual host | ||
1183 | \layout Quote | ||
1184 | |||
1185 | Each 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 | ||
1210 | Character | ||
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 | ||
1220 | What 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 | ||
1230 | MySQL 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 | ||
1240 | Recommended 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 | ||
1250 | Example | ||
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 | |||
1260 | A | ||
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 | |||
1268 | User 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 | |||
1276 | agent | ||
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 | |||
1284 | varchar(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 | |||
1292 | Mozilla/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 | |||
1302 | b | ||
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 | |||
1310 | Bytes 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 | |||
1318 | bytes_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 | |||
1326 | int 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 | |||
1334 | 32561 | ||
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 | |||
1344 | c | ||
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 | |||
1352 | Text 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 | |||
1360 | cookie | ||
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 | |||
1368 | varchar(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 | |||
1376 | Apache=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 | |||
1386 | f | ||
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 | |||
1401 | request_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 | |||
1409 | varchar(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 | |||
1426 | H | ||
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 | |||
1434 | HTTP 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 | |||
1442 | request_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 | |||
1450 | varchar(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 | |||
1458 | HTTP/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 | |||
1468 | h | ||
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 | |||
1476 | Name 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 | |||
1484 | remote_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 | |||
1492 | varchar(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 | |||
1500 | blah.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 | |||
1510 | I | ||
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 | |||
1518 | Ident 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 | |||
1526 | remote_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 | |||
1534 | varchar(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 | |||
1542 | bobby | ||
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 | |||
1552 | m | ||
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 | |||
1560 | HTTP 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 | |||
1568 | request_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 | |||
1576 | varchar(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 | |||
1584 | GET | ||
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 | |||
1594 | P | ||
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 | |||
1602 | httpd 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 | |||
1610 | child_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 | |||
1618 | smallint 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 | |||
1626 | 3215 | ||
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 | |||
1636 | p | ||
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 | |||
1644 | httpd 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 | |||
1652 | server_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 | |||
1660 | smallint 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 | |||
1668 | 80 | ||
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 | |||
1678 | R | ||
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 | |||
1686 | Referer | ||
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 | |||
1694 | referer | ||
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 | |||
1702 | varchar(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 | |||
1710 | http://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 | |||
1720 | r | ||
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 | |||
1728 | Request 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 | |||
1736 | request_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 | |||
1744 | varchar(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 | |||
1752 | GET /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 | |||
1762 | S | ||
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 | |||
1770 | Time 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 | |||
1778 | time_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 | |||
1786 | int 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 | |||
1794 | 1005598029 | ||
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 | |||
1804 | s | ||
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 | |||
1812 | HTTP 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 | |||
1820 | status | ||
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 | |||
1828 | smallint 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 | |||
1836 | 404 | ||
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 | |||
1846 | T | ||
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 | |||
1854 | Seconds 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 | |||
1862 | request_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 | |||
1870 | smallint 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 | |||
1878 | 2 | ||
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 | |||
1888 | t | ||
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 | |||
1896 | Time 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 | |||
1904 | request_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 | |||
1912 | char(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 | |||
1930 | U | ||
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 | |||
1938 | Request 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 | |||
1946 | request_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 | |||
1954 | varchar(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 | |||
1972 | u | ||
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 | |||
1980 | User 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 | |||
1988 | remote_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 | |||
1996 | varchar(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 | |||
2004 | bobby | ||
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 | |||
2014 | v | ||
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 | |||
2022 | Virtual 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 | |||
2030 | virtual_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 | |||
2038 | varchar(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 | |||
2046 | www.foobar.com | ||
2047 | \end_inset | ||
2048 | </cell> | ||
2049 | </row> | ||
2050 | </lyxtabular> | ||
2051 | |||
2052 | \end_inset | ||
2053 | |||
2054 | |||
2055 | \layout Quote | ||
2056 | |||
2057 | If 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 | ||
2076 | Character | ||
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 | ||
2086 | MySQL 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 | ||
2096 | MySQL 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 | ||
2106 | Example | ||
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 | |||
2116 | z | ||
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 | |||
2124 | ssl_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 | |||
2132 | varchar(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 | |||
2140 | RC4-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 | |||
2150 | q | ||
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 | |||
2158 | ssl_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 | |||
2166 | smallint 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 | |||
2174 | 56 | ||
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 | |||
2184 | Q | ||
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 | |||
2192 | ssl_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 | |||
2200 | smallint 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 | |||
2208 | 128 | ||
2209 | \end_inset | ||
2210 | </cell> | ||
2211 | </row> | ||
2212 | </lyxtabular> | ||
2213 | |||
2214 | \end_inset | ||
2215 | |||
2216 | |||
2217 | \layout Section | ||
2218 | |||
2219 | FAQ | ||
2220 | \layout Subsection | ||
2221 | |||
2222 | Why log to an SQL database? | ||
2223 | \layout Standard | ||
2224 | |||
2225 | To 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 | |||
2232 | Chores 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 | ||
2235 | ebalizer.com/) does not need historic logs after it has processed them, enabling | ||
2236 | you to delete older logs. | ||
2237 | |||
2238 | \layout Itemize | ||
2239 | |||
2240 | People 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 | |||
2247 | People acquainted with the power of SQL SELECT statements will know the | ||
2248 | flexibility of the extraction possibilities at their fingertips. | ||
2249 | \layout Standard | ||
2250 | |||
2251 | For example, do you want to see all your 404's? Do this: | ||
2252 | \layout LyX-Code | ||
2253 | |||
2254 | select 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 | |||
2290 | Or 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 | |||
2294 | select 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 | |||
2341 | Or maybe you want to see who's linking to you? Do this: | ||
2342 | \layout LyX-Code | ||
2343 | |||
2344 | select count(referer) as num,referer from acc_log_tbl where request_uri='/mod_lo | ||
2345 | g_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 | |||
2388 | As 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 | |||
2393 | Why use MySQL? Are there alternatives? | ||
2394 | \layout Standard | ||
2395 | |||
2396 | MySQL 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 | |||
2404 | That 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 | |||
2416 | Is this code production-ready? | ||
2417 | \layout Standard | ||
2418 | |||
2419 | By 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 | |||
2429 | How well does it perform? | ||
2430 | \layout Standard | ||
2431 | |||
2432 | mod_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 | |||
2437 | Overall configuration: | ||
2438 | \layout Itemize | ||
2439 | |||
2440 | Machine A: Apache webserver | ||
2441 | \layout Itemize | ||
2442 | |||
2443 | Machine B: MySQL server | ||
2444 | \layout Itemize | ||
2445 | |||
2446 | Machines A and B connected with 100Mbps Ethernet | ||
2447 | \layout Standard | ||
2448 | |||
2449 | Webserver configuration: | ||
2450 | \layout Itemize | ||
2451 | |||
2452 | Celeron 400 128 MB RAM IDE storage | ||
2453 | \layout Standard | ||
2454 | |||
2455 | Apache configuration: | ||
2456 | \layout LyX-Code | ||
2457 | |||
2458 | Timeout 300 | ||
2459 | \layout LyX-Code | ||
2460 | |||
2461 | KeepAlive On | ||
2462 | \layout LyX-Code | ||
2463 | |||
2464 | MaxKeepAliveRequests 100 | ||
2465 | \layout LyX-Code | ||
2466 | |||
2467 | KeepAliveTimeout 15 | ||
2468 | \layout LyX-Code | ||
2469 | |||
2470 | MinSpareServers 5 | ||
2471 | \layout LyX-Code | ||
2472 | |||
2473 | StartServers 10 | ||
2474 | \layout LyX-Code | ||
2475 | |||
2476 | MaxSpareServers 15 | ||
2477 | \layout LyX-Code | ||
2478 | |||
2479 | MaxClients 256 | ||
2480 | \layout LyX-Code | ||
2481 | |||
2482 | MaxRequestsPerChild 5000 | ||
2483 | \layout LyX-Code | ||
2484 | |||
2485 | LogSQLTransferLogFormat AbHhmRSsTUuvc | ||
2486 | \layout LyX-Code | ||
2487 | |||
2488 | LogSQLWhichCookie Clicks | ||
2489 | \layout LyX-Code | ||
2490 | |||
2491 | CookieTracking on | ||
2492 | \layout LyX-Code | ||
2493 | |||
2494 | CookieName 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 | |||
2508 | Ten 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 | |||
2514 | Average of five runs employing MySQL | ||
2515 | \emph on | ||
2516 | and | ||
2517 | \emph default | ||
2518 | standard text logging: 139.01 requests per second, zero errors. | ||
2519 | \layout Itemize | ||
2520 | |||
2521 | Average of five runs employing | ||
2522 | \emph on | ||
2523 | only | ||
2524 | \emph default | ||
2525 | standard text logging: 139.96 requests per second, zero errors. | ||
2526 | \layout Standard | ||
2527 | |||
2528 | In 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 | |||
2537 | If you run this benchmark yourself, take note of three things: | ||
2538 | \layout Enumerate | ||
2539 | |||
2540 | Use a target URL that is on your own webserver :-). | ||
2541 | |||
2542 | \layout Enumerate | ||
2543 | |||
2544 | Wait 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 | |||
2554 | When done with your runs, clean these many thousands of requests out of | ||
2555 | your database: | ||
2556 | \layout LyX-Code | ||
2557 | |||
2558 | mysql> delete from access_log where agent like 'ApacheBench%'; mysql> optimize | ||
2559 | table access_log; | ||
2560 | \layout Subsection | ||
2561 | |||
2562 | Who's using mod_log_sql? | ||
2563 | \layout Standard | ||
2564 | |||
2565 | Good 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 | |||
2571 | so that you can be mentioned here. | ||
2572 | \layout Subsection | ||
2573 | |||
2574 | How do I extract the data in a format that my analysis tool can understand? | ||
2575 | \layout Standard | ||
2576 | |||
2577 | mod_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 | |||
2587 | For 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 | |||
2596 | 05 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 | |||
2603 | 30 04 * * * root /usr/local/bin/webalizer -c /etc/webalizer.conf; rm -f /var/log/ | ||
2604 | httpd/mysql-grubbybaby | ||
2605 | \layout Standard | ||
2606 | |||
2607 | Or 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 | |||
2612 | webalizer | ||
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 | |||
2630 | rm -f /var/log/httpd/templog | ||
2631 | \layout Standard | ||
2632 | |||
2633 | See? Easy. | ||
2634 | \layout Subsection | ||
2635 | |||
2636 | Why doesn't the module also replace the Apache ErrorLog? | ||
2637 | \layout Standard | ||
2638 | |||
2639 | There 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 | |||
2647 | Error 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 | |||
2656 | How can I log mod_usertrack cookies? | ||
2657 | \layout Standard | ||
2658 | |||
2659 | A 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 | |||
2664 | LogFormat "%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 | |||
2683 | Naturally 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 | |||
2689 | First 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 | |||
2694 | alter table acc_log_tbl add column cookie varchar(255); | ||
2695 | \layout Standard | ||
2696 | |||
2697 | Next 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 | |||
2724 | The 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 | |||
2731 | FYI, 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 | |||
2737 | Perform some hits on your server and run a select: | ||
2738 | \layout LyX-Code | ||
2739 | |||
2740 | mysql> 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 | ||
2756 | 02700823 | | ||
2757 | \layout LyX-Code | ||
2758 | |||
2759 | | /mod_log_sql/usa.gif | ool-18e4.dyn.optonline.net.13005100710 | ||
2760 | 2700823 | | ||
2761 | \layout LyX-Code | ||
2762 | |||
2763 | | /mod_log_sql/style_1.css | ool-18e4.dyn.optonline.net.13005100710 | ||
2764 | 2700823 | | ||
2765 | \layout LyX-Code | ||
2766 | |||
2767 | ...etc... | ||
2768 | \layout Subsection | ||
2769 | |||
2770 | What if I want to log more than one cookie? | ||
2771 | \layout Standard | ||
2772 | |||
2773 | No 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 | |||
2785 | However, if you need to log multiple cookies, you'll employ the LogSQLWhichCooki | ||
2786 | es (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 | |||
2794 | LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict, but | ||
2795 | you're better off choosing the one you need. | ||
2796 | \layout Subsection | ||
2797 | |||
2798 | What are the SSL logging features, and how do I activate them? | ||
2799 | \layout Standard | ||
2800 | |||
2801 | If 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 | ||
2807 | e security to access your secure software area. | ||
2808 | \layout Standard | ||
2809 | |||
2810 | You 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 | |||
2816 | You 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 | |||
2821 | alter table access_log add column ssl_cipher varchar(25); | ||
2822 | \layout LyX-Code | ||
2823 | |||
2824 | alter table access_log add column ssl_keysize smallint unsigned; | ||
2825 | \layout LyX-Code | ||
2826 | |||
2827 | alter table access_log add column ssl_maxkeysize smallint unsigned; | ||
2828 | \layout Standard | ||
2829 | |||
2830 | Finally 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 | |||
2843 | The last three characters (Qqz) in the directive are the SSL ones; see the | ||
2844 | directives documentation for details. | ||
2845 | \layout Standard | ||
2846 | |||
2847 | Perform some hits on your server and run a select: | ||
2848 | \layout LyX-Code | ||
2849 | |||
2850 | mysql> 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 | |||
2881 | Does mod_log_sql connect to MySQL via TCP/IP or a socket? | ||
2882 | \layout Standard | ||
2883 | |||
2884 | It 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 | |||
2896 | You 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 | |||
2902 | Why do I occasionally see a "connection lost, attempting reconnect" message | ||
2903 | in my error-log? | ||
2904 | \layout Standard | ||
2905 | |||
2906 | This 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 | |||
2924 | Reference: | ||
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 | |||
2932 | Does mod_log_sql work with Apache 2.x? | ||
2933 | \layout Standard | ||
2934 | |||
2935 | As 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 | |||
2941 | mod_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 | |||
2955 | I have discovered a bug. | ||
2956 | Who can I contact? | ||
2957 | \layout Standard | ||
2958 | |||
2959 | Please 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} | ||
51 | http://www.grubbybaby.com/mod\_log\_sql/ | ||
52 | \end{lyxcode} | ||
53 | |||
54 | \subsection{Summary} | ||
55 | |||
56 | This Apache module will permit you to log Apache accesses (and a lot | ||
57 | of related information) to a SQL database. Unlike logging to a flat | ||
58 | text file (which is standard in Apache), there are a lot of powerful | ||
59 | advantages to logging to SQL. This module can either replace or happily | ||
60 | coexist with Apache's text file logging facility. | ||
61 | |||
62 | |||
63 | \subsection{Approach} | ||
64 | |||
65 | This project was formerly known as mod\_log\_mysql. It has been renamed | ||
66 | to mod\_log\_sql in order to reflect the project goal of database-inspecificity. | ||
67 | The module currently supports MySQL, and development for other database | ||
68 | backends is underway. | ||
69 | |||
70 | In order to save speed and overhead, links are kept alive in between | ||
71 | queries. This module uses one SQL link per httpd child. Among other | ||
72 | things, this means that this module supports logging into only one | ||
73 | MySQL server, and for now, also, only one SQL database. But that's | ||
74 | a small tradeoff compared to the blinding speed of this module. | ||
75 | |||
76 | Virtual hosts are supported in the same manner they are in the regular | ||
77 | logging modules. You define some basic 'global' directives in the | ||
78 | main server config, then you define more specific 'local' directives | ||
79 | inside each virtualhost stanza. | ||
80 | |||
81 | SQL links are opened by each child process when it is born. Error | ||
82 | reporting is robust throughout and will let you know about database | ||
83 | issues in the standard Apache error-log for the server or virtual | ||
84 | server. | ||
85 | |||
86 | A robust \char`\"{}preserve\char`\"{} capability has now been implemented. | ||
87 | This permits the module to preserve any failed INSERT commands to | ||
88 | a local file on its machine. In any situation that the database is | ||
89 | unavailable -- 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 | ||
91 | its 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`\"{} | ||
93 | on many Linux installations). When your database becomes available | ||
94 | again, mod\_log\_sql seamlessly resumes logging to it. When convenient | ||
95 | for the sysadmin, he/she can easily import the preserve file into | ||
96 | the 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 | |||
104 | Please see the web-based documentation for full explanation of all | ||
105 | supported run-time directives. | ||
106 | |||
107 | http://www.grubbybaby.com/mod\_log\_sql/directives.html | ||
108 | |||
109 | See the FAQ for some handy examples: | ||
110 | |||
111 | http://www.grubbybaby.com/mod\_log\_sql/faq.html | ||
112 | |||
113 | |||
114 | \subsection{What gets logged by default? } | ||
115 | |||
116 | All the data that would be contained in the \char`\"{}Combined Log | ||
117 | Format\char`\"{} is logged by default, plus a little extra. Your best | ||
118 | bet is to begin by accepting this default, then later customize the | ||
119 | log configuration based on your needs. | ||
120 | |||
121 | The online documentation of the run-time directives includes a full | ||
122 | explanation 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 | ||
129 | apply to the 'virtual host config'. This is made clear in the directive | ||
130 | documentation. | ||
131 | \item The 'time\_stamp' field is stored in an UNSIGNED INTEGER column, in | ||
132 | the standard unix \char`\"{}seconds since 1/1/1970 12:00:00\char`\"{} | ||
133 | format. This is superior to storing the access time as a string due | ||
134 | to size requirements: an UNSIGNED INT type fits in 4 bytes, whereas | ||
135 | the Apache date string (e.g. \char`\"{}18/Nov/2001:13:59:52 -0800\char`\"{}) | ||
136 | requires 26 bytes -- significantly larger, and those extra 22 bytes | ||
137 | will add up over the thousands of accesses that a busy server will | ||
138 | experience. Besides, an INT type is far more flexible for comparisons, | ||
139 | etc. | ||
140 | |||
141 | |||
142 | In MySQL 3.21 and above you can easily convert this to a human readable | ||
143 | format using from\_unixtime(), e.g.: | ||
144 | |||
145 | \begin{lyxcode} | ||
146 | select~remote\_host,request\_uri,from\_unixtime(time\_stamp)~from~access\_log; | ||
147 | \end{lyxcode} | ||
148 | The enclosed perl program ''make\_combined\_log.pl'' shows how you | ||
149 | can extract your access records in a format that is completely Combined | ||
150 | Log Format compliant. You can then feed this to your favorite web | ||
151 | log analysis tool. | ||
152 | |||
153 | \item The table's string values can be CHAR or VARCHAR, at a length of your | ||
154 | choice. VARCHAR is superior because it truncates long strings; CHAR | ||
155 | types are fixed-length and will be padded with spaces. Just like the | ||
156 | time\_stamp described above, that kind of space waste will add up | ||
157 | over thousands of records. | ||
158 | \item Be careful not to go overboard setting fields to NOT NULL. If a field | ||
159 | is marked NOT NULL then it must contain data in the INSERT or the | ||
160 | INSERT will fail, sometimes in a really mysterious way to the frustrated | ||
161 | sysad. | ||
162 | \item Apache normally logs numeric fields with a '-' character to mean \char`\"{}not | ||
163 | applicable,\char`\"{} e.g. bytes\_sent on a request with a 304 response | ||
164 | code. Since '-' is an illegal character in an SQL numeric field, such | ||
165 | fields are assigned the value 0 instead of '-' which, of course, makes | ||
166 | perfect sense anyway. | ||
167 | \end{itemize} | ||
168 | |||
169 | \subsection{Author / Maintainer} | ||
170 | |||
171 | The actual logging code was taken from the already existing flat file | ||
172 | text modules, so all that credit goes to the Apache Server group. | ||
173 | |||
174 | The MySQL routines and directives were added by Zeev Suraski <bourbon@netvision.net.il>. | ||
175 | |||
176 | All changes from 1.06+ and the new documentation were added by Chris | ||
177 | Powell <chris@grubbybaby.com>. It seems that the module had fallen | ||
178 | into the \char`\"{}unmaintained\char`\"{} category -- it hadn't been | ||
179 | updated 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 | ||
190 | modern distro. | ||
191 | \item Apache 1.2 or 1.3 installed. (I run 1.3.22 and it works fine). You | ||
192 | should have already successfully compiled Apache and know what you're | ||
193 | doing there. | ||
194 | \item The MySQL development headers. This is called different things on | ||
195 | different distros. For example, Red Hat 6.x called this RPM \char`\"{}MySQL-devel\char`\"{} | ||
196 | whereas Mandrake calls it \char`\"{}libmysql10-devel\char`\"{}. | ||
197 | \item MySQL >= 3.23.15 configured, installed and running on either localhost | ||
198 | or an accessible networked machine. You should already have a basic | ||
199 | understanding of MySQL and how it functions. | ||
200 | \item Again, basic administrative skills with Apache and MySQL. I try to | ||
201 | make things as easy as possible in this file, but its purpose is not | ||
202 | to be an administrative tutorial. | ||
203 | \item Additionally, if you want to be able to log SSL information such as | ||
204 | keysize or cipher, you need OpenSSL and glibc-devel installed. Both | ||
205 | are available as RPMs. | ||
206 | \end{itemize} | ||
207 | |||
208 | \subsection{Do I want a DSO?} | ||
209 | |||
210 | You need to know the answer to this question before you proceed. The | ||
211 | answer is pretty straightforward: what have you done in the past? | ||
212 | If you like all your Apache modules to be dynamic, then you should | ||
213 | keep doing that. If you're more of an old-school type and prefer to | ||
214 | compile the modules right into apache, do that. Both methods work | ||
215 | equally well. | ||
216 | |||
217 | FWIW, the DSO method is more modern and increasing in popularity because | ||
218 | apxs takes care of a lot of dirty little details for you. As you'll | ||
219 | see 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, | ||
226 | etc. 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 | |||
235 | NECESSARY: | ||
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} | ||
243 | OPTIONAL if you have included mod\_ssl in Apache and want to log SSL | ||
244 | data 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} | ||
249 | Now that you know these things, edit Makefile and replace the stock | ||
250 | values with your own. | ||
251 | |||
252 | IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS | ||
253 | by 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 | |||
268 | gcc~-DLINUX=22~-DNO\_DBM\_REWRITEMAP~-DMOD\_SSL=208111~-DUSE\_HSREGEX~-DEAPI~-DUSE\_EXPAT~-I../lib/expat-lite~-fpic~-DSHARED\_CORE~-DSHARED\_MODULE~-I/usr/local/Apache/include~-O2~-Wall~-DEAPI~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-c~mod\_log\_sql.c~ | ||
269 | |||
270 | gcc~-shared~-o~mod\_log\_sql.so~mod\_log\_sql.o~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-L/usr/lib~-lmysqlclient~-lz~-lm~-lcrypt~-ldb | ||
271 | \end{lyxcode} | ||
272 | You should see no errors and have a file called \char`\"{}mod\_log\_sql.so\char`\"{} | ||
273 | in your directory. | ||
274 | |||
275 | \item Instruct apxs to install the DSO. | ||
276 | |||
277 | \begin{lyxcode} | ||
278 | \#~make~dsoinstall | ||
279 | \end{lyxcode} | ||
280 | You should see output similar to the following: | ||
281 | |||
282 | \begin{lyxcode} | ||
283 | /usr/local/Apache/bin/apxs~-i~mod\_log\_sql.so | ||
284 | |||
285 | cp~mod\_log\_sql.so~/usr/local/Apache/libexec/mod\_log\_sql.so | ||
286 | |||
287 | chmod~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 | ||
290 | SSL, you must make sure that | ||
291 | |||
292 | \begin{lyxcode} | ||
293 | LoadModule~ssl\_module~libexec/libssl.so | ||
294 | \end{lyxcode} | ||
295 | comes before | ||
296 | |||
297 | \begin{lyxcode} | ||
298 | LoadModule~sql\_log\_module~libexec/mod\_log\_sql.so | ||
299 | \end{lyxcode} | ||
300 | If 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 | ||
308 | mod\_ssl provides.) | ||
309 | |||
310 | Now 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, | ||
318 | etc. | ||
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 | |||
328 | NECESSARY: | ||
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} | ||
337 | OPTIONAL if you have included mod\_ssl in Apache and want to log SSL | ||
338 | data 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} | ||
345 | Now that you know these things, edit Makefile and replace the stock | ||
346 | values with your own. | ||
347 | |||
348 | IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, | ||
349 | OPNSSLHDRS and DB1HDRS by putting a \# character in front of each | ||
350 | one, 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`\"{} | ||
366 | is 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 | ||
373 | it: | ||
374 | \end{itemize} | ||
375 | \begin{lyxcode} | ||
376 | AddModule~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} | ||
391 | Compiled-in~modules:~ | ||
392 | |||
393 | http\_core.c | ||
394 | |||
395 | mod\_log\_sql.c~<-{}-~That's~the~line~you're~looking~for. | ||
396 | |||
397 | mod\_env.c~ | ||
398 | |||
399 | mod\_log\_config.c~ | ||
400 | |||
401 | mod\_mime.c~ | ||
402 | |||
403 | mod\_negotiation.c~... | ||
404 | |||
405 | etc... | ||
406 | \end{lyxcode} | ||
407 | \item Install your httpd binary. Copy it over your old httpd binary, wherever | ||
408 | it lives. You can and should rename your old httpd first so that you | ||
409 | can easily revert to that working version in case of bugs with the | ||
410 | new 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 | |||
426 | You have to prepare the database to receive data from mod\_log\_sql, | ||
427 | and set up run-time directives in httpd.conf to control how and what | ||
428 | mod\_log\_sql logs. | ||
429 | |||
430 | This section will discuss how to get started with a basic config. | ||
431 | Full 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 | ||
435 | the tables by hand. The advantage of letting the module make the tables | ||
436 | is ease-of-use, but for raw performance you will want to pre-make | ||
437 | the tables in order to save overhead. In this basic setup we'll let | ||
438 | the module create tables for us. | ||
439 | \item We still need to have a logging database created and ready, so run | ||
440 | the 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' | ||
446 | SQL script as follows: | ||
447 | |||
448 | \begin{lyxcode} | ||
449 | mysql>~source~create\_tables.sql | ||
450 | \end{lyxcode} | ||
451 | \item Create a specific MySQL userid that httpd will use to authenticate | ||
452 | and enter data. This userid need not be an actual Unix user. It is | ||
453 | a userid internal to MySQL with specific privileges. In the following | ||
454 | example command, \char`\"{}apachelogs\char`\"{} is the database, \char`\"{}loguser\char`\"{} | ||
455 | is the userid to create, \char`\"{}my.apachemachine.com\char`\"{} | ||
456 | is the name of the Apache machine, and \char`\"{}l0gger\char`\"{} | ||
457 | is the password to assign. Choose values that are different from these | ||
458 | examples. | ||
459 | |||
460 | \begin{lyxcode} | ||
461 | mysql>~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`\"{} | ||
464 | to have \char`\"{}create\char`\"{} capability within the \char`\"{}apachelogs\char`\"{} | ||
465 | databse. You can disable that but the cost is that you cannot use | ||
466 | the module's automatic-table-creation feature. If that's an acceptable | ||
467 | cost, hand-create the tables as described in step 1 and use the following | ||
468 | GRANT statement instead of the one above: | ||
469 | |||
470 | \begin{lyxcode} | ||
471 | mysql>~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 | ||
474 | debugging purposes) if you don't do this already. Edit /etc/my.cnf | ||
475 | and add the following line to your {[}mysqld{]} section: | ||
476 | |||
477 | \begin{lyxcode} | ||
478 | log=/var/log/mysql-messages | ||
479 | \end{lyxcode} | ||
480 | Then 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 | ||
491 | information. | ||
492 | |||
493 | \begin{description} | ||
494 | \item [EXAMPLE:]Use the MySQL database called \char`\"{}apachelogs\char`\"{} | ||
495 | running on \char`\"{}dbmachine.foo.com\char`\"{}. The module uses | ||
496 | username \char`\"{}loguser\char`\"{} and password \char`\"{}l0gger\char`\"{} | ||
497 | to authenticate to the database. The log entries will be INSERTed | ||
498 | into the table called \char`\"{}access\_log\char`\"{}. | ||
499 | \end{description} | ||
500 | So, edit httpd.conf and insert the following lines somewhere AFTER | ||
501 | any LoadModule / AddModule statements. Make sure these statements | ||
502 | are \char`\"{}global,\char`\"{} i.e. not inside any VirtualHost stanza. | ||
503 | |||
504 | \begin{lyxcode} | ||
505 | LogSQLDatabase~apachelogs | ||
506 | |||
507 | LogSQLLoginInfo~dbmachine.foo.com~loguser~l0gger~ | ||
508 | |||
509 | LogSQLCreateTables~on | ||
510 | \end{lyxcode} | ||
511 | \item If your database resides on localhost instead of another host, specify | ||
512 | the MySQL server's socket file as follows: | ||
513 | |||
514 | \begin{lyxcode} | ||
515 | LogSQLSocketFile~/your/path/to/mysql.sock | ||
516 | \end{lyxcode} | ||
517 | \item The actual logging is set up on a virtual-host-by-host basis. So, | ||
518 | skip down to the virtual host you want to set up. Instruct this virtual | ||
519 | host to log entries to the table ''access\_log'' by inserting a | ||
520 | LogSQLTransferLogTable directive. (The LogSQLTransferLogTable directive | ||
521 | is the minimum required to log -- other directives simply tune the | ||
522 | module's behavior.) | ||
523 | |||
524 | \begin{lyxcode} | ||
525 | <VirtualHost~1.2.3.4> | ||
526 | |||
527 | {[}snip{]} | ||
528 | |||
529 | LogSQLTransferLogTable~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 | ||
546 | that 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 | |||
551 | Enter~password: | ||
552 | |||
553 | (Several~lines~of~output~should~follow,~corresponding~to~your~hits~on~the~site.) | ||
554 | \end{lyxcode} | ||
555 | You now have basic functionality. Don't disable your regular Apache | ||
556 | logs until you feel comfortable that the database is behaving as you'd | ||
557 | like and that things are going well. | ||
558 | |||
559 | \item If you do not see any entries in the access\_log, then something is | ||
560 | preventing the inserts from happening. This problem could be caused | ||
561 | by 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 | ||
566 | entry | ||
567 | \item You didn't specify the right host | ||
568 | \end{itemize} | ||
569 | If you have confirmed your LogSQL{*} directives and know them to be | ||
570 | correct, you should examine the httpd server logs for mod\_log\_sql | ||
571 | messages; the module will offer hints as to why it cannot connect, | ||
572 | etc. Also examine the MySQL log that you established in step 3. Ensure | ||
573 | that the INSERTs are not being rejected because of a malformed table | ||
574 | entry or other clerical error. If you see no INSERT attempts in the | ||
575 | log, the module isn't successfully connecting to the database. | ||
576 | |||
577 | The next thing to do is recompile the module with debugging output | ||
578 | activated. change the \char`\"{}\#undef DEBUG\char`\"{} on line 8 | ||
579 | of mod\_log\_sql.c to \char`\"{}\#define DEBUG\char`\"{} and recompile/reinstall. | ||
580 | The module will now output copious notes about what it is doing, and | ||
581 | this will help you (and the maintainer) solve the problem. | ||
582 | |||
583 | \end{enumerate} | ||
584 | You can now activate the advanced features of mod\_log\_sql. These | ||
585 | are 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 | |||
596 | Syntax:~LogSQLLoginInfo~host~user~password | ||
597 | |||
598 | Example:~LogSQLLoginInfo~foobar.baz.com~logwriter~passw0rd~ | ||
599 | |||
600 | Context:~server~config | ||
601 | \end{lyxcode} | ||
602 | \begin{quote} | ||
603 | Defines the general parameters of the MySQL host to which you will | ||
604 | be logging. host is the hostname or IP address of the MySQL machine. | ||
605 | user is the MySQL userid (not a Unix userid!) with INSERT privileges | ||
606 | on the table defined in LogSQLTransferLogTable. password is that user's | ||
607 | password. 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 | |||
615 | Syntax:~LogSQLDatabase~database~ | ||
616 | |||
617 | Example:~LogSQLDatabase~loggingdb~ | ||
618 | |||
619 | Context:~server~config | ||
620 | \end{lyxcode} | ||
621 | \begin{quote} | ||
622 | Defines the database that is used for logging. database must be operating | ||
623 | on the MySQL host defined in LogSQLLoginInfo. This is defined only | ||
624 | once in the httpd.conf file. | ||
625 | \end{quote} | ||
626 | |||
627 | \subsubsection{LogSQLTransferLogTable} | ||
628 | |||
629 | \begin{lyxcode} | ||
630 | \textbf{MANDATORY~} | ||
631 | |||
632 | Syntax:~LogSQLTransferLogTable~table-name~ | ||
633 | |||
634 | Example:~LogSQLTransferLogTable~access\_log\_table~ | ||
635 | |||
636 | Context:~virtual~host | ||
637 | \end{lyxcode} | ||
638 | \begin{quote} | ||
639 | Defines which table is used for logging of Apache's transfers; this | ||
640 | is analogous to Apache's TransferLog directive. table-name must be | ||
641 | a valid table within the database defined in LogSQLDatabase. | ||
642 | |||
643 | This directive is not necessary if you declare LogSQLMassVirtualHosting | ||
644 | On, since that directive activates dynamically-named tables. If you | ||
645 | attempt to use LogSQLTransferLogTable at the same time a warning will | ||
646 | be logged and it will be ignored, since LogSQLMassVirtualHosting takes | ||
647 | priority. | ||
648 | \end{quote} | ||
649 | |||
650 | \subsubsection{LogSQLRequestIgnore} | ||
651 | |||
652 | \begin{lyxcode} | ||
653 | Syntax:~LogSQLRequestIgnore~req1~req2~req3~...~reqN~ | ||
654 | |||
655 | Example:~LogSQLRequestIgnore~root.exe~cmd.exe~default.ida~favicon.ico~ | ||
656 | |||
657 | Context:~virtual~host | ||
658 | \end{lyxcode} | ||
659 | \begin{quote} | ||
660 | Lists a series of strings that, if present in the URI, will cause | ||
661 | that request NOT to be logged. This directive is useful for cutting | ||
662 | down on log clutter when you KNOW that you do not want to log requests | ||
663 | for certain objects. (The example above is a genuinely useful one; | ||
664 | it will prevent logging of many common Microsoft-based worm intrusion | ||
665 | attempts, as well as those ridiculous requests for the favicon.) | ||
666 | |||
667 | Each string is separated by a space, and NO regular expressions or | ||
668 | globbing are allowed. Each string is evaluated as a substring of the | ||
669 | URI using strstr(). | ||
670 | \end{quote} | ||
671 | |||
672 | \subsubsection{LogSQLRemhostIgnore} | ||
673 | |||
674 | \begin{lyxcode} | ||
675 | Syntax:~LogSQLRemhostIgnore~host1~host2~host3~...~hostN~ | ||
676 | |||
677 | Example:~LogSQLRemhostIgnore~localnet.com~ | ||
678 | |||
679 | Context:~virtual~host | ||
680 | \end{lyxcode} | ||
681 | \begin{quote} | ||
682 | Lists a series of strings that, if present in the REMOTE\_HOST, will | ||
683 | cause that request NOT to be logged. This directive is useful for | ||
684 | cutting down on log clutter when you KNOW that you do not want to | ||
685 | log requests from certain hosts, such as your own internal network | ||
686 | machines. | ||
687 | |||
688 | Each string is separated by a space, and NO regular expressions or | ||
689 | globbing are allowed. Each string is evaluated as a substring of the | ||
690 | REMOTE\_HOST using strstr(). | ||
691 | \end{quote} | ||
692 | |||
693 | \subsubsection{LogSQLRefererIgnore} | ||
694 | |||
695 | \begin{lyxcode} | ||
696 | Syntax:~LogSQLRefererIgnore~ref1~ref2~ref3~...~refN~ | ||
697 | |||
698 | Example:~LogSQLRefererIgnore~google.com~ | ||
699 | |||
700 | Context:~virtual~host | ||
701 | \end{lyxcode} | ||
702 | \begin{quote} | ||
703 | Lists a series of strings that, if present in the HTTP\_REFERER, will | ||
704 | cause that request NOT to be logged. This directive is useful for | ||
705 | cutting down on log clutter when you know that you do not want to | ||
706 | log requests from certain referers. | ||
707 | |||
708 | Each string is separated by a space, and NO regular expressions or | ||
709 | globbing are allowed. Each string is evaluated as a substring of the | ||
710 | HTTP\_REFERER using strstr(). | ||
711 | \end{quote} | ||
712 | |||
713 | \subsubsection{LogSQLWhichCookie} | ||
714 | |||
715 | \begin{lyxcode} | ||
716 | Syntax:~LogSQLWhichCookie~cookiename~ | ||
717 | |||
718 | Example:~LogSQLWhichCookie~Clicks | ||
719 | |||
720 | Default:~None.~You~must~set~this~if~you~wish~to~capture~cookies. | ||
721 | |||
722 | Context:~virtual~host | ||
723 | \end{lyxcode} | ||
724 | \begin{quote} | ||
725 | In HTTP, cookies have names to distinguish them from each other. Using | ||
726 | mod\_usertrack, for example, you can give your user-tracking cookies | ||
727 | a name with the CookieName directive. | ||
728 | |||
729 | If you have activated cookie logging in LogSQLTransferLogFormat, then | ||
730 | LogSQLWhichCookie tells mod\_log\_sql which cookie to log. This is | ||
731 | useful because many times you will be setting and receiving more than | ||
732 | one cookie from a client; without this directive you'd be unable to | ||
733 | choose which cookie is your mod\_usertrack cookie. | ||
734 | |||
735 | Note: although this was intended for people who are using mod\_usertrack | ||
736 | to set user-tracking cookies, you aren't restricted in any way. You | ||
737 | can choose which cookie you wish to log to the database, and it doesn't | ||
738 | necessarily have to have anything to do with mod\_usertrack. | ||
739 | \end{quote} | ||
740 | |||
741 | \subsubsection{LogSQLCreateTables} | ||
742 | |||
743 | \begin{lyxcode} | ||
744 | Syntax:~LogSQLCreateTables~flag | ||
745 | |||
746 | Example:~LogSQLCreateTables~On~ | ||
747 | |||
748 | Default:~Off~ | ||
749 | |||
750 | Context:~server~config | ||
751 | \end{lyxcode} | ||
752 | \begin{quote} | ||
753 | mod\_log\_sql now has the ability to create its tables on-the-fly. | ||
754 | The advantage to this is convenience: you don't have to execute any | ||
755 | SQL by hand to prepare the table. This is especially helpful for people | ||
756 | with lots of virtual hosts (who should also see the LogSQLMassVirtualHosting | ||
757 | directive). | ||
758 | |||
759 | There is a slight disadvantage: if you wish to activate this feature, | ||
760 | then the user specified by LogSQLLoginInfo must have CREATE privileges | ||
761 | on the database. In an absolutely paranoid, locked-down situation | ||
762 | you may only want to grant your mod\_log\_sql user INSERT privileges | ||
763 | on the database; in that situation you are unable to take advantage | ||
764 | of LogSQLCreateTables. But most people -- even the very security-conscious | ||
765 | -- will find that granting CREATE on the logging database is reasonable. | ||
766 | |||
767 | This is defined only once in the httpd.conf file. | ||
768 | \end{quote} | ||
769 | |||
770 | \subsubsection{LogSQLMassVirtualHosting} | ||
771 | |||
772 | \begin{lyxcode} | ||
773 | Syntax:~LogSQLMassVirtualHosting~flag~ | ||
774 | |||
775 | Example:~LogSQLMassVirtualHosting~On~ | ||
776 | |||
777 | Default:~Off~ | ||
778 | |||
779 | Context:~server~config | ||
780 | \end{lyxcode} | ||
781 | \begin{quote} | ||
782 | If you administer a site hosting many, many virtual hosts then this | ||
783 | option will appeal to you. If you activate LogSQLMassVirtualHosting | ||
784 | then 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 | ||
788 | name (example: a virtual host www.grubbybaby.com gets logged to table | ||
789 | access\_www\_grubbybaby\_com) | ||
790 | \item which, in turn, means that each virtual host logs to its own segregated | ||
791 | table. Because there is no data shared between virtual servers you | ||
792 | can grant your users access to the tables they need; they will be | ||
793 | unable to view others' data. | ||
794 | \end{itemize} | ||
795 | This is a huge boost in convenience for sites with many virtual servers. | ||
796 | Activating LogSQLMassVirtualHosting obviates the need to create every | ||
797 | virtual server's table and provides more granular security possibilities. | ||
798 | |||
799 | This is defined only once in the httpd.conf file. | ||
800 | \end{quote} | ||
801 | |||
802 | \subsubsection{LogSQLPreserveFile} | ||
803 | |||
804 | \begin{lyxcode} | ||
805 | Syntax:~LogSQLPreserveFile~filename~ | ||
806 | |||
807 | Example:~LogSQLPreserveFile~offline-preserve~ | ||
808 | |||
809 | Default:~mysql-preserve~(in~/tmp)~ | ||
810 | |||
811 | Context:~virtual~host | ||
812 | \end{lyxcode} | ||
813 | \begin{quote} | ||
814 | mod\_log\_sql writes queries to this local preserve file in the event | ||
815 | that it cannot reach the database, and thus ensures that your high-availability | ||
816 | web frontend does not lose logs during a temporary database outage. | ||
817 | This could happen for a number of reasons: the database goes offline, | ||
818 | the network breaks, etc. You will not lose entries since the module | ||
819 | has this backup. The file consists of a series of SQL statements that | ||
820 | can be imported into your database at your convenience; furthermore, | ||
821 | because the SQL queries contain the access timestamps you do not need | ||
822 | to worry about out-of-order data after the import. | ||
823 | |||
824 | For security purposes the name you supply will be prepended with \char`\"{}/tmp/\char`\"{} | ||
825 | to force creation of the file in /tmp. | ||
826 | |||
827 | If you do not define LogSQLPreserveFile then all virtual servers will | ||
828 | log to the same default preserve file (/tmp/mysql-preserve). You can | ||
829 | redefine this on a virtual-host basis in order to segregate your preserve | ||
830 | files if you desire. Note that segregation is not really necessary, | ||
831 | as the SQL statements that are written to the preserve file already | ||
832 | distinguish between different virtual hosts. | ||
833 | |||
834 | The module will log to error-log if/when it notices a database outage, | ||
835 | and upon database return. You will therefore know when the preserve | ||
836 | file is being used, although it is your responsibility to import the | ||
837 | file. | ||
838 | |||
839 | The file does not need to be created in advance. It is safe to remove | ||
840 | or rename the file without interrupting Apache, as the module closes | ||
841 | the filehandle immediately after completing the write. The file is | ||
842 | created 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} | ||
849 | Syntax:~LogSQLSocketFile~filename~ | ||
850 | |||
851 | Example:~LogSQLSocketFile~/tmp/mysql.sock~ | ||
852 | |||
853 | Default:~/var/lib/mysql/mysql.sock~ | ||
854 | |||
855 | Context:~server~config | ||
856 | \end{lyxcode} | ||
857 | \begin{quote} | ||
858 | At Apache runtime you can specify the MySQL socket file to use. Set | ||
859 | this once in your main server config to override the default value. | ||
860 | This value is irrelevant if your database resides on a separate machine. | ||
861 | |||
862 | mod\_log\_sql will automatically employ the socket for db communications | ||
863 | if the database resides on the local host. If the db resides on a | ||
864 | separate host the module will automatically use TCP/IP. This is a | ||
865 | function of the MySQL API and is not user-configurable. | ||
866 | \end{quote} | ||
867 | |||
868 | \subsubsection{LogSQLTransferLogFormat } | ||
869 | |||
870 | \begin{lyxcode} | ||
871 | Syntax:~LogSQLTransferLogFormat~format-string~ | ||
872 | |||
873 | Example:~LogSQLTransferLogFormat~huSUTv~ | ||
874 | |||
875 | Default:~AbHhmRSsTUuv~ | ||
876 | |||
877 | Context:~virtual~host | ||
878 | \end{lyxcode} | ||
879 | \begin{quote} | ||
880 | Each character in the format-string defines an attribute of the request | ||
881 | that you wish to log. The default logs the information required to | ||
882 | create Combined Log Format logs, plus several extras. Here is the | ||
883 | full list of allowable keys, which sometimes resemble their Apache | ||
884 | counterparts, 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 | ||
895 | A& | ||
896 | User agent& | ||
897 | agent& | ||
898 | varchar(255)& | ||
899 | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0)\\ | ||
900 | \hline | ||
901 | b& | ||
902 | Bytes transfered& | ||
903 | bytes\_sent& | ||
904 | int unsigned& | ||
905 | 32561\\ | ||
906 | \hline | ||
907 | c& | ||
908 | Text of cookie& | ||
909 | cookie& | ||
910 | varchar(255)& | ||
911 | Apache=sdyn.fooonline.net.130051007102700823\\ | ||
912 | \hline | ||
913 | f& | ||
914 | & | ||
915 | request\_file& | ||
916 | varchar(255)& | ||
917 | \\ | ||
918 | \hline | ||
919 | H& | ||
920 | HTTP request protocol& | ||
921 | request\_protocol& | ||
922 | varchar(10)& | ||
923 | HTTP/1.1\\ | ||
924 | \hline | ||
925 | h& | ||
926 | Name of remote host& | ||
927 | remote\_host& | ||
928 | varchar(50)& | ||
929 | blah.foobar.com\\ | ||
930 | \hline | ||
931 | I& | ||
932 | Ident user info& | ||
933 | remote\_logname& | ||
934 | varchar(50)& | ||
935 | bobby\\ | ||
936 | \hline | ||
937 | m& | ||
938 | HTTP request method& | ||
939 | request\_method& | ||
940 | varchar(6)& | ||
941 | GET\\ | ||
942 | \hline | ||
943 | P& | ||
944 | httpd child PID& | ||
945 | child\_pid& | ||
946 | smallint unsigned& | ||
947 | 3215\\ | ||
948 | \hline | ||
949 | p& | ||
950 | httpd port& | ||
951 | server\_port& | ||
952 | smallint unsigned& | ||
953 | 80\\ | ||
954 | \hline | ||
955 | R& | ||
956 | Referer& | ||
957 | referer& | ||
958 | varchar(255)& | ||
959 | http://www.biglinkstoyou.com/linkpage.html\\ | ||
960 | \hline | ||
961 | r& | ||
962 | Request in full form& | ||
963 | request\_line& | ||
964 | varchar(255)& | ||
965 | GET /books-cycroad.html HTTP/1.1\\ | ||
966 | \hline | ||
967 | S& | ||
968 | Time of request in UNIX format& | ||
969 | time\_stamp& | ||
970 | int unsigned& | ||
971 | 1005598029\\ | ||
972 | \hline | ||
973 | s& | ||
974 | HTTP status of request& | ||
975 | status& | ||
976 | smallint unsigned& | ||
977 | 404\\ | ||
978 | \hline | ||
979 | T& | ||
980 | Seconds to service request& | ||
981 | request\_duration& | ||
982 | smallint unsigned& | ||
983 | 2\\ | ||
984 | \hline | ||
985 | t& | ||
986 | Time of request in human format& | ||
987 | request\_time& | ||
988 | char(28)& | ||
989 | {[}02/Dec/2001:15:01:26 -0800{]}\\ | ||
990 | \hline | ||
991 | U& | ||
992 | Request in simple form& | ||
993 | request\_uri& | ||
994 | varchar(255)& | ||
995 | /books-cycroad.html\\ | ||
996 | \hline | ||
997 | u& | ||
998 | User info from HTTP authentication& | ||
999 | remote\_user& | ||
1000 | varchar(50)& | ||
1001 | bobby\\ | ||
1002 | \hline | ||
1003 | v& | ||
1004 | Virtual host servicing the request& | ||
1005 | virtual\_host& | ||
1006 | varchar(50)& | ||
1007 | www.foobar.com\\ | ||
1008 | \hline | ||
1009 | \end{tabular} | ||
1010 | |||
1011 | If you have compiled mod\_log\_sql with WANT\_SSL\_LOGGING, you also | ||
1012 | get 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 | ||
1022 | z& | ||
1023 | ssl\_cipher& | ||
1024 | varchar(25)& | ||
1025 | RC4-MD5\\ | ||
1026 | \hline | ||
1027 | q& | ||
1028 | ssl\_keysize& | ||
1029 | smallint unsigned& | ||
1030 | 56\\ | ||
1031 | \hline | ||
1032 | Q& | ||
1033 | ssl\_maxkeysize& | ||
1034 | smallint unsigned& | ||
1035 | 128\\ | ||
1036 | \hline | ||
1037 | \end{tabular} | ||
1038 | \end{quote} | ||
1039 | |||
1040 | \section{FAQ} | ||
1041 | |||
1042 | |||
1043 | \subsection{Why log to an SQL database?} | ||
1044 | |||
1045 | To begin with, let's get it out of the way: logging to a database | ||
1046 | is not a panacea. But while there are complexities with this solution, | ||
1047 | the benefit can be substantial for certain classes of administrator | ||
1048 | or people with advanced requirements: | ||
1049 | |||
1050 | \begin{itemize} | ||
1051 | \item Chores like log rotation go away, as you can DELETE records from the | ||
1052 | SQL database once they are no longer useful. For example, the excellent | ||
1053 | and popular log-analysis tool Webalizer (http://www.webalizer.com/) | ||
1054 | does not need historic logs after it has processed them, enabling | ||
1055 | you to delete older logs. | ||
1056 | \item People with clusters of web servers (for high availability) will benefit | ||
1057 | the most -- all their webservers can log to a single SQL database. | ||
1058 | This obviates the need to collate/interleave the many separate logfiles, | ||
1059 | which can be / highly/ problematic. | ||
1060 | \item People acquainted with the power of SQL SELECT statements will know | ||
1061 | the flexibility of the extraction possibilities at their fingertips. | ||
1062 | \end{itemize} | ||
1063 | For example, do you want to see all your 404's? Do this: | ||
1064 | |||
1065 | \begin{lyxcode} | ||
1066 | select~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} | ||
1084 | Or do you want to see how many bytes you've sent within a certain | ||
1085 | directory or site? Do this: | ||
1086 | |||
1087 | \begin{lyxcode} | ||
1088 | select~request\_uri,sum(bytes\_sent)~as~bytes,count(request\_uri)~as~howmany~from~acc\_log\_tbl~where~request\_uri~like~'\%mod\_log\_sql\%'~group~by~request\_uri~order~by~howmany~desc;~ | ||
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} | ||
1118 | Or maybe you want to see who's linking to you? Do this: | ||
1119 | |||
1120 | \begin{lyxcode} | ||
1121 | select~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} | ||
1143 | As you can see, there are myriad possibilities that can be constructed | ||
1144 | with the wonderful SQL SELECT statement. Logging to an SQL database | ||
1145 | can be really quite useful! | ||
1146 | |||
1147 | |||
1148 | \subsection{Why use MySQL? Are there alternatives?} | ||
1149 | |||
1150 | MySQL is a robust, free, and very powerful production-quality database | ||
1151 | engine. It is well supported and comes with detailed documentation. | ||
1152 | Many 3rd-party software pacakges (e.g. Slashcode, the engine that | ||
1153 | powers Slashdot) run exclusively with MySQL. In other words, you will | ||
1154 | belong to a very robust and well-supported community by choosing MySQL. | ||
1155 | |||
1156 | That being said, there are alternatives. PostgreSQL is probably MySQL's | ||
1157 | leading \char`\"{}competitor\char`\"{} in the free database world. | ||
1158 | There is also an excellent module available for Apache to permit logging | ||
1159 | to a PostgreSQL database, called pgLOGd \url{(http://www.digitalstratum.com/pglogd/)}. | ||
1160 | |||
1161 | |||
1162 | \subsection{Is this code production-ready?} | ||
1163 | |||
1164 | By all accounts it is. It is known to work without a problem on many-thousands-of-hits-per-day | ||
1165 | webservers. Does that mean it is 100\% bug free? Well, no software | ||
1166 | is. But it is well-tested and /believed/ to be fully compatible with | ||
1167 | production environments. (The usual disclaimers apply. This software | ||
1168 | is provided without warranty of any kind.) | ||
1169 | |||
1170 | |||
1171 | \subsection{How well does it perform?} | ||
1172 | |||
1173 | mod\_log\_sql scales to very high loads. Apache 1.3.22 + mod\_log\_sql | ||
1174 | was benchmarked using the \char`\"{}ab\char`\"{} (Apache Bench) program | ||
1175 | that comes with the Apache distribution; here are the results. | ||
1176 | |||
1177 | Overall 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} | ||
1184 | Webserver configuration: | ||
1185 | |||
1186 | \begin{itemize} | ||
1187 | \item Celeron 400 128 MB RAM IDE storage | ||
1188 | \end{itemize} | ||
1189 | Apache configuration: | ||
1190 | |||
1191 | \begin{lyxcode} | ||
1192 | Timeout~300~ | ||
1193 | |||
1194 | KeepAlive~On~ | ||
1195 | |||
1196 | MaxKeepAliveRequests~100~ | ||
1197 | |||
1198 | KeepAliveTimeout~15~ | ||
1199 | |||
1200 | MinSpareServers~5~ | ||
1201 | |||
1202 | StartServers~10~ | ||
1203 | |||
1204 | MaxSpareServers~15~ | ||
1205 | |||
1206 | MaxClients~256~ | ||
1207 | |||
1208 | MaxRequestsPerChild~5000~ | ||
1209 | |||
1210 | LogSQLTransferLogFormat~AbHhmRSsTUuvc~ | ||
1211 | |||
1212 | LogSQLWhichCookie~Clicks~ | ||
1213 | |||
1214 | CookieTracking~on~ | ||
1215 | |||
1216 | CookieName~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`\"{}; | ||
1224 | target = the mod\_log\_sql homepage. ) | ||
1225 | |||
1226 | Ten total ab runs were conducted: five with MySQL logging enabled, | ||
1227 | and five with all MySQL directives commented out of httpd.conf. Then | ||
1228 | each five were averaged. The results: | ||
1229 | |||
1230 | \begin{itemize} | ||
1231 | \item Average of five runs employing MySQL \emph{and} standard text logging: | ||
1232 | 139.01 requests per second, zero errors. | ||
1233 | \item Average of five runs employing \emph{only} standard text logging: | ||
1234 | 139.96 requests per second, zero errors. | ||
1235 | \end{itemize} | ||
1236 | In other words, any rate-limiting effects on this particular hardware | ||
1237 | setup are not caused by MySQL. Note that although this very simple | ||
1238 | webserver setup is hardly cutting-edge -- it is, after all, a fairly | ||
1239 | small machine -- 139 requests per second equal over twelve million | ||
1240 | hits per day\emph{.} | ||
1241 | |||
1242 | If 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 | ||
1247 | several thousand requests your TCP/IP stack will be filled with hundreds | ||
1248 | of 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 | ||
1250 | can expect to see a lot of messages like \char`\"{}ip\_conntrack: | ||
1251 | table full, dropping packet\char`\"{} in your logs. (This has nothing | ||
1252 | to do with mod\_log\_sql, this is simply the nature of the TCP/IP | ||
1253 | stack in the Linux kernel.) | ||
1254 | \item When done with your runs, clean these many thousands of requests out | ||
1255 | of your database: | ||
1256 | \end{enumerate} | ||
1257 | \begin{lyxcode} | ||
1258 | mysql>~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 | |||
1263 | Good question! It would be great to find out! If you are a production-level | ||
1264 | mod\_log\_sql user, please contact the maintainer, Chris Powell \url{chris@grubbybaby.com}so | ||
1265 | that 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 | |||
1270 | mod\_log\_sql would be virtually useless if there weren't a way for | ||
1271 | you to extract the data from your database in a somewhat meaningful | ||
1272 | fashion. To that end there's a Perl script enclosed with the distribution. | ||
1273 | That script (make\_combined\_log.pl) is designed to extract N-many | ||
1274 | days worth of access logs and provide them in a Combined Log Format | ||
1275 | output. You can use this very tool right in /etc/crontab to extract | ||
1276 | logs on a regular basis so that your favorite web analysis tool can | ||
1277 | read them. Or you can examine the Perl code to construct your own | ||
1278 | custom tool. | ||
1279 | |||
1280 | For example, let's say that you want your web statistics updated once | ||
1281 | per day in the wee hours of the morning. A good way to accomplish | ||
1282 | that 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 | |||
1287 | 05~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 | |||
1291 | 30~04~{*}~{*}~{*}~root~/usr/local/bin/webalizer~-c~/etc/webalizer.conf;~rm~-f~/var/log/httpd/mysql-grubbybaby | ||
1292 | \end{lyxcode} | ||
1293 | Or if you have a newer system that puts files in /etc/cron.daily etc., | ||
1294 | create a file called ''webalizer'' in the cron.\_\_\_\_ subdir of | ||
1295 | your choice. Use the following as the contents of your file, and make | ||
1296 | sure 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 | |||
1305 | rm~-f~/var/log/httpd/templog | ||
1306 | \end{lyxcode} | ||
1307 | See? Easy. | ||
1308 | |||
1309 | |||
1310 | \subsection{Why doesn't the module also replace the Apache ErrorLog?} | ||
1311 | |||
1312 | There are circumstances when that would be quite unwise -- for example, | ||
1313 | if Apache could not reach the MySQL server for some reason and needed | ||
1314 | to log that fact. Without a text-based error log you'd never know | ||
1315 | anything was wrong, because Apache would be trying to log a database | ||
1316 | connection error to the database... you get the point. | ||
1317 | |||
1318 | Error logs are usually not very high-traffic and are really best left | ||
1319 | as text files on a web server machine. | ||
1320 | |||
1321 | |||
1322 | \subsection{\label{sec:cookie}How can I log mod\_usertrack cookies?} | ||
1323 | |||
1324 | A number of people like to log mod\_usertrack cookies in their Apache | ||
1325 | TransferLog to aid in understanding their visitors' clickstreams. | ||
1326 | This is accomplished, for example, with a statement as follows: | ||
1327 | |||
1328 | \begin{lyxcode} | ||
1329 | LogFormat~\char`\"{}\%h~\%l~\%u~\%t~\textbackslash{}\char`\"{}\%r\textbackslash{}\char`\"{}~\%s~\%b~\textbackslash{}\char`\"{}\%\{Referer\}i\textbackslash{}\char`\"{}~\textbackslash{}\char`\"{}\%\{User-Agent\}i\textbackslash{}\char`\"{}\char`\"{}~\textbackslash{}\char`\"{}\%\{cookie\}n\textbackslash{}\char`\"{}\char`\"{} | ||
1330 | \end{lyxcode} | ||
1331 | Naturally it would be nice for mod\_log\_sql to permit the admin to | ||
1332 | log the cookie data as well, so as of version 1.10 you can do this. | ||
1333 | You need to have already compiled mod\_usertrack into httpd -- it's | ||
1334 | one of the standard Apache modules. | ||
1335 | |||
1336 | First make sure you have a column called \char`\"{}cookie\char`\"{} | ||
1337 | in the MySQL database to hold the cookies, which can be done as follows | ||
1338 | if you already have a working database: | ||
1339 | |||
1340 | \begin{lyxcode} | ||
1341 | alter~table~acc\_log\_tbl~add~column~cookie~varchar(255); | ||
1342 | \end{lyxcode} | ||
1343 | Next configure your server to set usertracking cookies as follows, | ||
1344 | and make sure you include the new 'c' directive in your LogSQLTransferLogFormat, | ||
1345 | which 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} | ||
1362 | The 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 | ||
1364 | to log cookies named Foobar. You have to choose which cookie to log | ||
1365 | because more than one cookie can/will be sent to the server by the | ||
1366 | client. | ||
1367 | |||
1368 | FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that | ||
1369 | even newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2 | ||
1370 | (RFC 2965) format. Just stick with the standard COOKIE format and | ||
1371 | you'll be fine. | ||
1372 | |||
1373 | Perform some hits on your server and run a select: | ||
1374 | |||
1375 | \begin{lyxcode} | ||
1376 | mysql>~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 | |||
1395 | No problem. As of version 1.17, you have a choice. If you are just | ||
1396 | interested in a single cookie, follow the instructions in section | ||
1397 | \ref{sec:cookie} above. That cookie will be logged to a column in | ||
1398 | the regular access\_log table. | ||
1399 | |||
1400 | However, if you need to log multiple cookies, you'll employ the LogSQLWhichCookies | ||
1401 | (note the plural) directive. The cookies you specify will be logged | ||
1402 | to a separate table, and entries in that table will be linked to the | ||
1403 | regular access\_log entries via the unique ID that is supplied by | ||
1404 | mod\_unique\_id. Without mod\_unique\_id the information will still | ||
1405 | be logged but you will be unable to correlate which cookies go with | ||
1406 | which access-requests. | ||
1407 | |||
1408 | LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict, | ||
1409 | but 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 | |||
1414 | If you run an SSL-enabled server you may benefit from logging some | ||
1415 | SSL details. mod\_log\_sql now supports this ability. By adding certain | ||
1416 | characters to your LogSQLTransferLogFormat string you can tell mod\_log\_sql | ||
1417 | to log the SSL cipher, the SSL keysize of the connection, and the | ||
1418 | Max-keysize that was available. This would let you tell, for example, | ||
1419 | which clients were using only export-grade security to access your | ||
1420 | secure software area. | ||
1421 | |||
1422 | You can compile mod\_log\_sql with SSL logging support if you have | ||
1423 | the right packages installed. If you already have an SSL-enabled Apache | ||
1424 | then you by definition have the correct packages already installed: | ||
1425 | OpenSSL and mod\_ssl. | ||
1426 | |||
1427 | You need to ensure that your database is set up to log the SSL data. | ||
1428 | Issue the following commands to MySQL once you have your basic access\_log | ||
1429 | table built: | ||
1430 | |||
1431 | \begin{lyxcode} | ||
1432 | alter~table~access\_log~add~column~ssl\_cipher~varchar(25); | ||
1433 | |||
1434 | alter~table~access\_log~add~column~ssl\_keysize~smallint~unsigned; | ||
1435 | |||
1436 | alter~table~access\_log~add~column~ssl\_maxkeysize~smallint~unsigned; | ||
1437 | \end{lyxcode} | ||
1438 | Finally configure httpd.conf to activate the SSL fields. Note that | ||
1439 | this 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} | ||
1448 | The last three characters (Qqz) in the directive are the SSL ones; | ||
1449 | see the directives documentation for details. | ||
1450 | |||
1451 | Perform some hits on your server and run a select: | ||
1452 | |||
1453 | \begin{lyxcode} | ||
1454 | mysql>~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 | |||
1473 | It depends! Actually this isn't determined by mod\_log\_sql. mod\_log\_sql | ||
1474 | relies on a connection command that is supplied in the MySQL API, | ||
1475 | and that command is somewhat intelligent. When mod\_log\_sql issues | ||
1476 | the connect command to MySQL, this intelligent connect command uses | ||
1477 | sockets to communicate with MySQL if the specified MySQL database | ||
1478 | is on the same machine (because sockets are more efficient than TCP/IP). | ||
1479 | However, if the specified MySQL db is on a different machine, mod\_log\_sql | ||
1480 | connects using TCP/IP. You don't have any control of which methodology | ||
1481 | is used. | ||
1482 | |||
1483 | You do have control over where mod\_log\_sql looks for the socket. | ||
1484 | The LogSQLSocketFile runtime configuration directive overrides the | ||
1485 | default of \char`\"{}/var/lib/mysql/mysql.sock\char`\"{} to whatever | ||
1486 | you 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 | ||
1490 | reconnect\char`\"{} message in my error-log?} | ||
1491 | |||
1492 | This message may appear every now and then in your Apache error log, | ||
1493 | especially on very lightly loaded servers. This doesn't mean that | ||
1494 | anything is necessarily wrong. Within each httpd child process, mod\_log\_sql | ||
1495 | will open (and keep open) a connection to the MySQL server. MySQL, | ||
1496 | however, will close connections that haven't been used in a while; | ||
1497 | the default timeout is 8 hours. When this occurs, mod\_log\_sql will | ||
1498 | notice and re-open the connection. That event is what is being logged, | ||
1499 | and 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} | ||
1506 | Reference: 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 | |||
1511 | As of this writing, no. The Apache Group significantly altered the | ||
1512 | module API with the release of Apache 2.0. All modules written for | ||
1513 | 1.3, including mod\_log\_sql, will not work with 2.0. | ||
1514 | |||
1515 | mod\_log\_sql will eventually be ported to Apache 2.x, but not immediately. | ||
1516 | It is going to take some time, and there are other features that have | ||
1517 | higher priority. Please sign up for the announcements list (on the | ||
1518 | main website) or monitor the website for updates to learn when the | ||
1519 | port (and other releases) are available. | ||
1520 | |||
1521 | <OPINION>If you're a {*}NIX user, stick with Apache 1.3.x for now. | ||
1522 | Major modules like mod\_ssl and PHP are not even ready for 2.0 yet, | ||
1523 | and the main benefits in 2.0 are for Win32 users anyway. Apache 1.3.x | ||
1524 | is 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 | |||
1529 | Please contact the maintainer \url{chris@grubbybaby.com}! Your comments, | ||
1530 | suggestions, bugfixes, bug catches, and usage testimonials are always | ||
1531 | welcome. As free software, mod\_log\_sql is intended to be a community | ||
1532 | effort -- any code contributions or other ideas will be fully and | ||
1533 | openly credited, of course. | ||
1534 | \end{document} | ||