summaryrefslogtreecommitdiffstats
path: root/Documentation
diff options
context:
space:
mode:
Diffstat (limited to 'Documentation')
-rw-r--r--Documentation/README6
-rw-r--r--Documentation/documentation.lyx2272
-rw-r--r--Documentation/documentation.tex1534
3 files changed, 1950 insertions, 1862 deletions
diff --git a/Documentation/README b/Documentation/README
new file mode 100644
index 0000000..df96ba1
--- /dev/null
+++ b/Documentation/README
@@ -0,0 +1,6 @@
1$Id: README,v 1.1 2002/11/14 22:52:56 helios Exp $
2
3The "original" document is the LyX file "documentation.lyx" -- all other
4files here are derived from it. The most visually accurate document
5and the one I'd recommend reading is the PostScript (.ps) file, although
6a whole host of formats are made available here to suit your preference.
diff --git a/Documentation/documentation.lyx b/Documentation/documentation.lyx
index 48c3968..ecee913 100644
--- a/Documentation/documentation.lyx
+++ b/Documentation/documentation.lyx
@@ -5,6 +5,7 @@
5\inputencoding default 5\inputencoding default
6\fontscheme default 6\fontscheme default
7\graphics default 7\graphics default
8\float_placement !hptb
8\paperfontsize 10 9\paperfontsize 10
9\spacing single 10\spacing single
10\papersize letterpaper 11\papersize letterpaper
@@ -12,8 +13,6 @@
12\use_geometry 1 13\use_geometry 1
13\use_amsmath 0 14\use_amsmath 0
14\paperorientation portrait 15\paperorientation portrait
15\paperwidth 8.5in
16\paperheight 11in
17\leftmargin 1in 16\leftmargin 1in
18\topmargin 0.5in 17\topmargin 0.5in
19\rightmargin 1in 18\rightmargin 1in
@@ -22,7 +21,7 @@
22\tocdepth 3 21\tocdepth 3
23\paragraph_separation indent 22\paragraph_separation indent
24\defskip medskip 23\defskip medskip
25\quotes_language swedish 24\quotes_language english
26\quotes_times 2 25\quotes_times 2
27\papercolumns 1 26\papercolumns 1
28\papersides 1 27\papersides 1
@@ -33,10 +32,7 @@
33Installing and Running mod_log_sql 32Installing and Running mod_log_sql
34\layout Author 33\layout Author
35 34
36Christopher Powell 35Christopher Powell, <chris@grubbybaby.com>
37\layout Date
38
3911/13/02
40\layout Standard 36\layout Standard
41\pagebreak_bottom 37\pagebreak_bottom
42 38
@@ -431,8 +427,6 @@ Unpack the archive into a working directory.
431\end_inset 427\end_inset
432 428
433Edit Makefile for your system. 429Edit Makefile for your system.
434\newline
435
436\begin_deeper 430\begin_deeper
437\layout Standard 431\layout Standard
438 432
@@ -588,7 +582,7 @@ Install your httpd binary.
588Configuration 582Configuration
589\layout Subsection 583\layout Subsection
590 584
591Preparing MySQL 585Preparing MySQL for logging
592\layout Standard 586\layout Standard
593 587
594You have to prepare the database to receive data from mod_log_sql, and set 588You have to prepare the database to receive data from mod_log_sql, and set
@@ -673,7 +667,7 @@ Then restart MySQL.
673\end_deeper 667\end_deeper
674\layout Subsection 668\layout Subsection
675 669
676Preparing Apache 670A very basic logging setup in Apache
677\layout Enumerate 671\layout Enumerate
678 672
679Tell the module what database to use and the appropriate authentication 673Tell the module what database to use and the appropriate authentication
@@ -689,8 +683,12 @@ EXAMPLE: Use the MySQL database called "apachelogs" running on "dbmachine.foo.co
689 683
690So, edit httpd.conf and insert the following lines somewhere AFTER any LoadModule 684So, edit httpd.conf and insert the following lines somewhere AFTER any LoadModule
691 / AddModule statements. 685 / AddModule statements.
692 Make sure these statements are "global," i.e. 686
693 not inside any VirtualHost stanza. 687\emph on
688Make sure these statements are "global," i.e.
689 not inside any VirtualHost stanza
690\emph default
691.
694\layout LyX-Code 692\layout LyX-Code
695 693
696LogSQLDatabase apachelogs 694LogSQLDatabase apachelogs
@@ -700,15 +698,20 @@ LogSQLLoginInfo dbmachine.foo.com loguser l0gger
700\layout LyX-Code 698\layout LyX-Code
701 699
702LogSQLCreateTables on 700LogSQLCreateTables on
703\end_deeper 701\layout Standard
704\layout Enumerate
705 702
706If your database resides on localhost instead of another host, specify the 703If your database resides on localhost instead of another host, specify the
707 MySQL server's socket file as follows: 704 MySQL server's socket file as follows:
708\begin_deeper
709\layout LyX-Code 705\layout LyX-Code
710 706
711LogSQLSocketFile /your/path/to/mysql.sock 707LogSQLSocketFile /your/path/to/mysql.sock
708\layout Standard
709
710If your database is listening on a port other than 3306, specify the correct
711 TCP port as follows:
712\layout LyX-Code
713
714LogSQLTCPPort 1234
712\end_deeper 715\end_deeper
713\layout Enumerate 716\layout Enumerate
714 717
@@ -724,20 +727,21 @@ access_log
724 727
725 by inserting a LogSQLTransferLogTable directive. 728 by inserting a LogSQLTransferLogTable directive.
726 (The LogSQLTransferLogTable directive is the minimum required to log -- 729 (The LogSQLTransferLogTable directive is the minimum required to log --
727 other directives simply tune the module's behavior.) 730 other directives that you'll learn about later simply tune the module's
731 behavior.)
728\begin_deeper 732\begin_deeper
729\layout LyX-Code 733\layout LyX-Code
730 734
731<VirtualHost 1.2.3.4> 735<VirtualHost 1.2.3.4>
732\layout LyX-Code 736\layout LyX-Code
733 737
734[snip] 738 [snip]
735\layout LyX-Code 739\layout LyX-Code
736 740
737LogSQLTransferLogTable access_log 741 LogSQLTransferLogTable access_log
738\layout LyX-Code 742\layout LyX-Code
739 743
740[snip] 744 [snip]
741\layout LyX-Code 745\layout LyX-Code
742 746
743</VirtualHost> 747</VirtualHost>
@@ -752,7 +756,7 @@ Restart apache.
752\end_deeper 756\end_deeper
753\layout Subsection 757\layout Subsection
754 758
755Testing the module 759Testing the basic setup
756\layout Enumerate 760\layout Enumerate
757 761
758Load your web site in a browser to trigger some hits, then confirm that 762Load your web site in a browser to trigger some hits, then confirm that
@@ -779,7 +783,7 @@ You now have basic functionality.
779 783
780If you do not see any entries in the access_log, then something is preventing 784If you do not see any entries in the access_log, then something is preventing
781 the inserts from happening. 785 the inserts from happening.
782 This problem could be caused by several things: 786 This problem could be caused by several things:
783\begin_deeper 787\begin_deeper
784\layout Itemize 788\layout Itemize
785 789
@@ -815,34 +819,750 @@ You can now activate the advanced features of mod_log_sql.
815 These are all described in the next section. 819 These are all described in the next section.
816\layout Subsection 820\layout Subsection
817 821
818Configuration directive reference 822How to tune logging with run-time directives
819\layout Subsubsection 823\layout Subsubsection
820 824
821LogSQLLoginInfo 825Instructing the module what to log
826\layout Standard
827
828The LogSQLTransferLogFormat directive tells the module which information
829 to send to the database.
830 This directive resembles -- but is not identical to -- the Apache LogFormat
831 directive.
832 For each virtual host that you want to enable SQL logging, insert a LogSQLTrans
833ferLogFormat directive into the VirtualHost stanza.
834\layout Standard
835
836After LogSQLTransferLogFormat you supply a string of characters that tell
837 the module what to log.
838 In the configuration directive reference (section
839\begin_inset LatexCommand \ref{sec:ConfRef}
840
841\end_inset
842
843) there is a table which clearly defines all the possible things to log.
844 Let's say you only wanted to log only the request time, the remote host,
845 and the request; you'd use:
822\layout LyX-Code 846\layout LyX-Code
823 847
848LogSQLTransferLogFormat HUS
849\layout Standard
824 850
825\series bold 851A more appropriate string to use is
826MANDATORY 852\layout LyX-Code
827\series default 853
854LogSQLTransferLogFormat AbHhmRSsTUuv
855\layout Standard
856
857which logs all the information required to be compatible with the Combined
858 Log Format (CLF).
859\layout Standard
860
861If you don't choose to log everything that is available, that's fine.
862 The table in your database will have unused columns that will simply contain
863 NULL and do not cause issues.
864\layout Standard
865
866Some directives need a little extra configuration:
867\layout Itemize
868
869If you specify 'c' to indicate that you want to log the cookie value, you
870 need to also tell the module which cookie you want with LogSQLWhichCookie
871 -- after all, there could be many cookies associated with a given request.
872 Fail to specify LogSQLWhichCookie, and no cookie information at all will
873 be logged.
828 874
875\layout Itemize
876
877If you specify 'M' to indicate that you want to log the machine ID, you
878 need to also instruct the module this machine's identifier using the LogSQLMach
879ineID directive.
880 Fail to specify LogSQLMachineID, and a simple '-' character will be logged
881 in the machine_id column.
882\layout Subsubsection
883
884Instructing the module what NOT to log
885\layout Standard
886
887Two
888\begin_inset Quotes eld
889\end_inset
890
891ignore
892\begin_inset Quotes srd
893\end_inset
894
895 directives allow you to specify things that the module should not log.
896 This can be very handy for a variety of reasons, such as keeping your database
897 as uncluttered as possible, keeping your statistics free of unneeded numbers,
898 etc.
899\layout Standard
900
901You are able to tell the module to ignore based on LogSQLRemhostIgnore and
902 LogSQLRequestIgnore.
903 A good way to use LogSQLRemhostIgnore is to prevent the module from logging
904 the traffic that your internal hosts generate.
905 LogSQLRequestIgnore is great for preventing things like requests for favicon.ico
906 from cluttering up your database, as well as the various requests that
907 worms make, etc.
908\layout Standard
909
910You can specify a series of strings after each directive.
911 Do not use any type of globbing or regular-expression syntax -- each string
912 is considered a match if it is a substring of the larger request or remote-host.
913 In other words, if you want to match
914\begin_inset Quotes eld
915\end_inset
916
917host1.corp.foo.com
918\begin_inset Quotes srd
919\end_inset
920
921 and
922\begin_inset Quotes eld
923\end_inset
924
925host2.dmz.foo.com
926\begin_inset Quotes srd
927\end_inset
928
929, simply specify:
829\layout LyX-Code 930\layout LyX-Code
830 931
831Syntax: LogSQLLoginInfo host user password 932LogSQLRemhostIgnore foo.com
933\layout Standard
934
935A great way to catch the vast majority of worm-attack requests and prevent
936 them from being logged is to specify:
832\layout LyX-Code 937\layout LyX-Code
833 938
834Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd 939LogSQLRequestIgnore root.exe cmd.exe default.ida
940\layout Subsection
941
942Advanced logging scenarios
943\layout Subsubsection
944
945
946\begin_inset LatexCommand \label{secMulTable}
947
948\end_inset
949
950Logging many-to-one data in separate tables
951\layout Standard
952
953A given HTTP request can have a one-to-many relationship with certain kinds
954 of data.
955 For example, a single HTTP request can have 4 cookies, 3 headers and 5
956 mod_gzip notes associated with it.
957 mod_log_sql is capable of logging these relationships due to the elegance
958 of SQL relational data.
959\layout Standard
960
961You already have a single table containing access requests.
962 One of the columns in that table is 'id' which contains the unique request
963 ID supplied by the standard Apache module mod_unique_id (assuming you compile
964 in that module and employ the LogSQLTransferLogFormat character 'I').
965 Each request gets a unique ID that can be thought of as a primary key within
966 the database.
967\layout Standard
968
969So, let's add several new tables: a notes table, a cookies table, and tables
970 for inbound and outbound headers.
971 We have a certain request, let's say its unique ID is
972\begin_inset Quotes eld
973\end_inset
974
975XYZ123
976\begin_inset Quotes erd
977\end_inset
978
979.
980 Within each separate table will be multiple entries with that request ID:
981 several cookie entries, several header entries, etc.
982 Something like this:
983\layout Standard
984
985\begin_float tab
986\layout Caption
987
988
989\begin_inset LatexCommand \label{tblAcc}
990
991\end_inset
992
993access_log
994\layout Standard
995\align center
996
997\begin_inset Tabular
998<lyxtabular version="2" rows="2" columns="3">
999<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
1000<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
1001<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
1002<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
1003<row topline="true" bottomline="true" newpage="false">
1004<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1005\begin_inset Text
1006
1007\layout Standard
1008
1009id
1010\end_inset
1011</cell>
1012<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1013\begin_inset Text
1014
1015\layout Standard
1016
1017remote_host
1018\end_inset
1019</cell>
1020<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1021\begin_inset Text
1022
1023\layout Standard
1024
1025request_uri
1026\end_inset
1027</cell>
1028</row>
1029<row topline="true" bottomline="true" newpage="false">
1030<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1031\begin_inset Text
1032
1033\layout Standard
1034
1035XYZ123
1036\end_inset
1037</cell>
1038<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1039\begin_inset Text
1040
1041\layout Standard
1042
1043foo.bar.com
1044\end_inset
1045</cell>
1046<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1047\begin_inset Text
1048
1049\layout Standard
1050
1051/index.html
1052\end_inset
1053</cell>
1054</row>
1055</lyxtabular>
1056
1057\end_inset
1058
1059
1060\end_float
1061\begin_float tab
1062\layout Caption
1063
1064
1065\begin_inset LatexCommand \label{tblNotes}
1066
1067\end_inset
1068
1069notes_log
1070\layout Standard
1071\align center
1072
1073\begin_inset Tabular
1074<lyxtabular version="2" rows="3" columns="3">
1075<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
1076<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
1077<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
1078<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
1079<row topline="true" bottomline="true" newpage="false">
1080<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1081\begin_inset Text
1082
1083\layout Standard
1084
1085id
1086\end_inset
1087</cell>
1088<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1089\begin_inset Text
1090
1091\layout Standard
1092
1093item
1094\end_inset
1095</cell>
1096<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1097\begin_inset Text
1098
1099\layout Standard
1100
1101val
1102\end_inset
1103</cell>
1104</row>
1105<row topline="true" bottomline="false" newpage="false">
1106<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1107\begin_inset Text
1108
1109\layout Standard
1110
1111XYZ123
1112\end_inset
1113</cell>
1114<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1115\begin_inset Text
1116
1117\layout Standard
1118
1119mod_gzip_result
1120\end_inset
1121</cell>
1122<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1123\begin_inset Text
1124
1125\layout Standard
1126
1127OK
1128\end_inset
1129</cell>
1130</row>
1131<row topline="true" bottomline="true" newpage="false">
1132<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1133\begin_inset Text
1134
1135\layout Standard
1136
1137XYZ123
1138\end_inset
1139</cell>
1140<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1141\begin_inset Text
1142
1143\layout Standard
1144
1145mod_gzip_compression_ratio
1146\end_inset
1147</cell>
1148<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1149\begin_inset Text
1150
1151\layout Standard
1152
115369
1154\end_inset
1155</cell>
1156</row>
1157</lyxtabular>
1158
1159\end_inset
1160
1161
1162\end_float
1163\layout Standard
1164
1165\begin_float tab
1166\layout Caption
1167
1168
1169\begin_inset LatexCommand \label{tblHdr}
1170
1171\end_inset
1172
1173headers_log
1174\layout Standard
1175\align center
1176
1177\begin_inset Tabular
1178<lyxtabular version="2" rows="5" columns="3">
1179<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
1180<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
1181<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
1182<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
1183<row topline="true" bottomline="true" newpage="false">
1184<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1185\begin_inset Text
1186
1187\layout Standard
1188
1189id
1190\end_inset
1191</cell>
1192<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1193\begin_inset Text
1194
1195\layout Standard
1196
1197item
1198\end_inset
1199</cell>
1200<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1201\begin_inset Text
1202
1203\layout Standard
1204
1205val
1206\end_inset
1207</cell>
1208</row>
1209<row topline="true" bottomline="false" newpage="false">
1210<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1211\begin_inset Text
1212
1213\layout Standard
1214
1215XYZ123
1216\end_inset
1217</cell>
1218<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1219\begin_inset Text
1220
1221\layout Standard
1222
1223Content-Type
1224\end_inset
1225</cell>
1226<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1227\begin_inset Text
1228
1229\layout Standard
1230
1231text/html
1232\end_inset
1233</cell>
1234</row>
1235<row topline="true" bottomline="false" newpage="false">
1236<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1237\begin_inset Text
1238
1239\layout Standard
1240
1241XYZ123
1242\end_inset
1243</cell>
1244<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1245\begin_inset Text
1246
1247\layout Standard
1248
1249Accept-Encoding
1250\end_inset
1251</cell>
1252<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1253\begin_inset Text
1254
1255\layout Standard
1256
1257gzip, deflate
1258\end_inset
1259</cell>
1260</row>
1261<row topline="true" bottomline="false" newpage="false">
1262<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1263\begin_inset Text
1264
1265\layout Standard
1266
1267XYZ123
1268\end_inset
1269</cell>
1270<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1271\begin_inset Text
1272
1273\layout Standard
1274
1275Expires
1276\end_inset
1277</cell>
1278<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1279\begin_inset Text
1280
1281\layout Standard
1282
1283Tue, 28 May 2002 10:00:18 GMT
1284\end_inset
1285</cell>
1286</row>
1287<row topline="true" bottomline="true" newpage="false">
1288<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1289\begin_inset Text
1290
1291\layout Standard
1292
1293XYZ123
1294\end_inset
1295</cell>
1296<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1297\begin_inset Text
1298
1299\layout Standard
1300
1301Cache-Control
1302\end_inset
1303</cell>
1304<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1305\begin_inset Text
1306
1307\layout Standard
1308
1309max-age=86400
1310\end_inset
1311</cell>
1312</row>
1313</lyxtabular>
1314
1315\end_inset
1316
1317
1318\end_float
1319\layout Standard
1320
1321As you can see in tables
1322\begin_inset LatexCommand \ref{tblAcc}
1323
1324\end_inset
1325
1326,
1327\begin_inset LatexCommand \ref{tblNotes}
1328
1329\end_inset
1330
1331 and
1332\begin_inset LatexCommand \ref{tblHdr}
1333
1334\end_inset
1335
1336, you have a many-to-one relationship for request XYZ123: one access has
1337 two associated notes and four associated headers.
1338 You can extract this data easily using the power of SQL's
1339\begin_inset Quotes eld
1340\end_inset
1341
1342select
1343\begin_inset Quotes erd
1344\end_inset
1345
1346 statement and table joins.
1347 To see the notes associated with a particular request:
835\layout LyX-Code 1348\layout LyX-Code
836 1349
837Context: server config 1350select a.remote_host, a.request_uri, n.item, n.val from access_log a, notes_log
838\layout Quote 1351 n where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';
1352\layout LyX-Code
839 1353
840Defines the general parameters of the MySQL host to which you will be logging. 1354\layout Standard
841 host is the hostname or IP address of the MySQL machine. 1355\align center
842 user is the MySQL userid (not a Unix userid!) with INSERT privileges on 1356
843 the table defined in LogSQLTransferLogTable. 1357\begin_inset Tabular
844 password is that user's password. 1358<lyxtabular version="2" rows="3" columns="4">
845 This is defined only once in the httpd.conf file. 1359<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
1360<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
1361<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
1362<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
1363<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
1364<row topline="true" bottomline="true" newpage="false">
1365<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1366\begin_inset Text
1367
1368\layout Standard
1369
1370remote_host
1371\end_inset
1372</cell>
1373<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1374\begin_inset Text
1375
1376\layout Standard
1377
1378request_uri
1379\end_inset
1380</cell>
1381<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1382\begin_inset Text
1383
1384\layout Standard
1385
1386item
1387\end_inset
1388</cell>
1389<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1390\begin_inset Text
1391
1392\layout Standard
1393
1394val
1395\end_inset
1396</cell>
1397</row>
1398<row topline="true" bottomline="false" newpage="false">
1399<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1400\begin_inset Text
1401
1402\layout Standard
1403
1404zerberus.aiacs.net
1405\end_inset
1406</cell>
1407<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1408\begin_inset Text
1409
1410\layout Standard
1411
1412/mod_log_sql/style_1.css
1413\end_inset
1414</cell>
1415<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1416\begin_inset Text
1417
1418\layout Standard
1419
1420mod_gzip_result
1421\end_inset
1422</cell>
1423<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1424\begin_inset Text
1425
1426\layout Standard
1427
1428DECLINED:EXCLUDED
1429\end_inset
1430</cell>
1431</row>
1432<row topline="true" bottomline="true" newpage="false">
1433<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1434\begin_inset Text
1435
1436\layout Standard
1437
1438zerberus.aiacs.net
1439\end_inset
1440</cell>
1441<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1442\begin_inset Text
1443
1444\layout Standard
1445
1446/mod_log_sql/style_1.css
1447\end_inset
1448</cell>
1449<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1450\begin_inset Text
1451
1452\layout Standard
1453
1454mod_gzip_compression_ratio
1455\end_inset
1456</cell>
1457<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
1458\begin_inset Text
1459
1460\layout Standard
1461
14620
1463\end_inset
1464</cell>
1465</row>
1466</lyxtabular>
1467
1468\end_inset
1469
1470
1471\layout LyX-Code
1472
1473\layout LyX-Code
1474
1475\layout Standard
1476
1477In order to use this capability of mod_log_sql, you must do several things:
1478\layout Itemize
1479
1480Compile mod_unique_id into Apache (statically or as a DSO).
1481 mod_log_sql employs the unique request ID that mod_unique_id provides in
1482 order to key between the separate tables.
1483 You can still log the data without mod_unqiue_id, but it will be completely
1484 uncorrelated and you will have no way to discern any meaning.
1485\layout Itemize
1486
1487Create the appropriate tables.
1488 This will be done for you if you permit mod_log_sql to create its own tables,
1489 or if you use the enclosed create_tables.sql script.
1490\layout Itemize
1491
1492Create a SQL index on the
1493\begin_inset Quotes eld
1494\end_inset
1495
1496id
1497\begin_inset Quotes erd
1498\end_inset
1499
1500 column.
1501 Without this index, table joins will be deathly slow.
1502 I recommend you consult the MySQL documentation on the proper way to create
1503 a column index if you are not familiar with this operation.
1504\layout Itemize
1505
1506Within each appropriate VirtualHost stanza, use the LogSQLWhich* and LogSQL*LogT
1507able directives to tell the module what and where to log the data.
1508 In the following example, I have specified a name for the notes table that
1509 is different from the default, whereas I have left the other table names
1510 at their defaults.
1511 I then specified the cookies, headers and notes that interest me.
1512\layout LyX-Code
1513
1514<VirtualHost 216.231.36.128>
1515\layout LyX-Code
1516
1517 (snip)
1518\layout LyX-Code
1519
1520 LogSQLNotesLogTable notestable
1521\layout LyX-Code
1522
1523 LogSQLWhichCookies bluecookie redcookie greencookie
1524\layout LyX-Code
1525
1526 LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio
1527\layout LyX-Code
1528
1529 LogSQLWhichHeadersOut Expires Content-Type Cache-Control
1530\layout LyX-Code
1531
1532 LogSQLWhichHeadersIn UserAgent Accept-Encoding Host
1533\layout LyX-Code
1534
1535 (snip)
1536\layout LyX-Code
1537
1538</VirtualHost>
1539\layout LyX-Code
1540
1541\layout Subsection
1542
1543
1544\begin_inset LatexCommand \label{sec:ConfRef}
1545
1546\end_inset
1547
1548Configuration directive reference
1549\layout Standard
1550
1551It is imperative that you understand which directives are used
1552\emph on
1553only once
1554\emph default
1555in the main server config, and which are used inside VirtualHost stanzas
1556 and therefore multiple times within httpd.conf.
1557 The
1558\begin_inset Quotes eld
1559\end_inset
1560
1561context
1562\begin_inset Quotes srd
1563\end_inset
1564
1565 listed with each entry informs you of this.
846\layout Subsubsection 1566\layout Subsubsection
847 1567
848LogSQLDatabase 1568LogSQLDatabase
@@ -859,7 +1579,7 @@ Syntax: LogSQLDatabase database
859Example: LogSQLDatabase loggingdb 1579Example: LogSQLDatabase loggingdb
860\layout LyX-Code 1580\layout LyX-Code
861 1581
862Context: server config 1582Context: main server config
863\layout Quote 1583\layout Quote
864 1584
865Defines the database that is used for logging. 1585Defines the database that is used for logging.
@@ -867,143 +1587,175 @@ Defines the database that is used for logging.
867 This is defined only once in the httpd.conf file. 1587 This is defined only once in the httpd.conf file.
868\layout Subsubsection 1588\layout Subsubsection
869 1589
870LogSQLTransferLogTable 1590LogSQLLoginInfo
871\layout LyX-Code 1591\layout LyX-Code
872 1592
873 1593
874\series bold 1594\series bold
875MANDATORY 1595MANDATORY
1596\series default
1597
876\layout LyX-Code 1598\layout LyX-Code
877 1599
878Syntax: LogSQLTransferLogTable table-name 1600Syntax: LogSQLLoginInfo host user password
879\layout LyX-Code 1601\layout LyX-Code
880 1602
881Example: LogSQLTransferLogTable access_log_table 1603Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd
882\layout LyX-Code 1604\layout LyX-Code
883 1605
884Context: virtual host 1606Context: main server config
885\layout Quote 1607\layout Quote
886 1608
887Defines which table is used for logging of Apache's transfers; this is analogous 1609Defines the general parameters of the MySQL host to which you will be logging.
888 to Apache's TransferLog directive. 1610 host is the hostname or IP address of the MySQL machine.
889 table-name must be a valid table within the database defined in LogSQLDatabase. 1611 user is the MySQL userid (not a Unix userid!) with INSERT privileges on
890\layout Quote 1612 the table defined in LogSQLTransferLogTable.
891 1613 password is that user's password.
892This directive is not necessary if you declare LogSQLMassVirtualHosting 1614 This is defined only once in the httpd.conf file.
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 1615\layout Subsubsection
898 1616
899LogSQLRequestIgnore 1617LogSQLTCPPort
900\layout LyX-Code 1618\layout LyX-Code
901 1619
902Syntax: LogSQLRequestIgnore req1 req2 req3 ... 1620Syntax: LogSQLTCPPort portnumber
903 reqN
904\layout LyX-Code 1621\layout LyX-Code
905 1622
906Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico 1623Example: LogSQLTCPPort 3309
907\layout LyX-Code 1624\layout LyX-Code
908 1625
909Context: virtual host 1626Default: 3306
910\layout Quote 1627\layout LyX-Code
911 1628
912Lists a series of strings that, if present in the URI, will cause that request 1629Context: main server config
913 NOT to be logged.
914 This directive is useful for cutting down on log clutter when you KNOW
915 that you do not want to log requests for certain objects.
916 (The example above is a genuinely useful one; it will prevent logging of
917 many common Microsoft-based worm intrusion attempts, as well as those ridiculou
918s requests for the favicon.)
919\layout Quote 1630\layout Quote
920 1631
921Each string is separated by a space, and NO regular expressions or globbing 1632Your database may listen on a different port than the default.
922 are allowed. 1633 If so, use this directive to instruct the module which port to use.
923 Each string is evaluated as a substring of the URI using strstr(). 1634 This directive only applies if the database is on a different machine connected
1635 via TCP/IP.
924\layout Subsubsection 1636\layout Subsubsection
925 1637
926LogSQLRemhostIgnore 1638LogSQLSocketFile
927\layout LyX-Code 1639\layout LyX-Code
928 1640
929Syntax: LogSQLRemhostIgnore host1 host2 host3 ... 1641Syntax: LogSQLSocketFile filename
930 hostN
931\layout LyX-Code 1642\layout LyX-Code
932 1643
933Example: LogSQLRemhostIgnore localnet.com 1644Example: LogSQLSocketFile /tmp/mysql.sock
934\layout LyX-Code 1645\layout LyX-Code
935 1646
936Context: virtual host 1647Default: /var/lib/mysql/mysql.sock
1648\layout LyX-Code
1649
1650Context: main server config
937\layout Quote 1651\layout Quote
938 1652
939Lists a series of strings that, if present in the REMOTE_HOST, will cause 1653At Apache runtime you can specify the MySQL socket file to use.
940 that request NOT to be logged. 1654 Set this once in your main server config to override the default value.
941 This directive is useful for cutting down on log clutter when you KNOW 1655 This value is irrelevant if your database resides on a separate machine.
942 that you do not want to log requests from certain hosts, such as your own
943 internal network machines.
944\layout Quote 1656\layout Quote
945 1657
946Each string is separated by a space, and NO regular expressions or globbing 1658mod_log_sql will automatically employ the socket for db communications if
947 are allowed. 1659 the database resides on the local host.
948 Each string is evaluated as a substring of the REMOTE_HOST using strstr(). 1660 If the db resides on a separate host the module will automatically use
1661 TCP/IP.
1662 This is a function of the MySQL API and is not user-configurable.
949\layout Subsubsection 1663\layout Subsubsection
950 1664
951LogSQLRefererIgnore 1665LogSQLPreserveFile
1666\layout LyX-Code
1667
1668Syntax: LogSQLPreserveFile filename
952\layout LyX-Code 1669\layout LyX-Code
953 1670
954Syntax: LogSQLRefererIgnore ref1 ref2 ref3 ... 1671Example: LogSQLPreserveFile offline-preserve
955 refN
956\layout LyX-Code 1672\layout LyX-Code
957 1673
958Example: LogSQLRefererIgnore google.com 1674Default: /tmp/sql-preserve
959\layout LyX-Code 1675\layout LyX-Code
960 1676
961Context: virtual host 1677Context: virtual host
962\layout Quote 1678\layout Quote
963 1679
964Lists a series of strings that, if present in the HTTP_REFERER, will cause 1680mod_log_sql writes queries to this local preserve file in the event that
965 that request NOT to be logged. 1681 it cannot reach the database, and thus ensures that your high-availability
966 This directive is useful for cutting down on log clutter when you know 1682 web frontend does not lose logs during a temporary database outage.
967 that you do not want to log requests from certain referers. 1683 This could happen for a number of reasons: the database goes offline, the
1684 network breaks, etc.
1685 You will not lose entries since the module has this backup.
1686 The file consists of a series of SQL statements that can be imported into
1687 your database at your convenience; furthermore, because the SQL queries
1688 contain the access timestamps you do not need to worry about out-of-order
1689 data after the import.
968\layout Quote 1690\layout Quote
969 1691
970Each string is separated by a space, and NO regular expressions or globbing 1692If you do not define LogSQLPreserveFile then all virtual servers will log
971 are allowed. 1693 to the same default preserve file (/tmp/mysql-preserve).
972 Each string is evaluated as a substring of the HTTP_REFERER using strstr(). 1694 You can redefine this on a virtual-host basis in order to segregate your
1695 preserve files if you desire.
1696 Note that segregation is not really necessary, as the SQL statements that
1697 are written to the preserve file already distinguish between different
1698 virtual hosts if you include the 'v' character in your LogSQLTransferLogFormat
1699 directive.
1700\layout Quote
1701
1702The module will log to error-log if/when it notices a database outage, and
1703 upon database return.
1704 You will therefore know when the preserve file is being used, although
1705 it is your responsibility to import the file.
1706\layout Quote
1707
1708The file does not need to be created in advance.
1709 It is safe to remove or rename the file without interrupting Apache, as
1710 the module closes the filehandle immediately after completing the write.
1711 The file is created with the user & group ID of the running Apache process
1712 (e.g.
1713 'nobody' on many Linux distributions).
973\layout Subsubsection 1714\layout Subsubsection
974 1715
975LogSQLWhichCookie 1716LogSQLForcePreserve
976\layout LyX-Code 1717\layout LyX-Code
977 1718
978Syntax: LogSQLWhichCookie cookiename 1719Syntax: LogSQLForcePreserve Flag
979\layout LyX-Code 1720\layout LyX-Code
980 1721
981Example: LogSQLWhichCookie Clicks 1722Example: LogSQLPreserveFile on
982\layout LyX-Code 1723\layout LyX-Code
983 1724
984Default: None. 1725Default: off
985 You must set this if you wish to capture cookies.
986\layout LyX-Code 1726\layout LyX-Code
987 1727
988Context: virtual host 1728Context: main server config
989\layout Quote 1729\layout Quote
990 1730
991In HTTP, cookies have names to distinguish them from each other. 1731You may need to perform debugging on your database and specifically want
992 Using mod_usertrack, for example, you can give your user-tracking cookies 1732 mod_log_sql to make no attempts to log to it.
993 a name with the CookieName directive. 1733 This directive instructs the module to send all its log entries directly
1734 to the preserve file and to make no database INSERT attempts.
994\layout Quote 1735\layout Quote
995 1736
996If you have activated cookie logging in LogSQLTransferLogFormat, then LogSQLWhic 1737This is presumably a directive for temporary use only; it could be dangerous
997hCookie tells mod_log_sql which cookie to log. 1738 if you set it and forget it, as all your entries will simply pile up in
998 This is useful because many times you will be setting and receiving more 1739 the preserve file.
999 than one cookie from a client; without this directive you'd be unable to 1740\layout Subsubsection
1000 choose which cookie is your mod_usertrack cookie. 1741
1742LogSQLMachineID
1743\layout LyX-Code
1744
1745Syntax: LogSQLMachineID somename
1746\layout LyX-Code
1747
1748Example: LogSQLMachineID web01
1749\layout LyX-Code
1750
1751Context: main server config
1001\layout Quote 1752\layout Quote
1002 1753
1003Note: although this was intended for people who are using mod_usertrack 1754If you have a farm of webservers you may wish to log which particular machine
1004 to set user-tracking cookies, you aren't restricted in any way. 1755 made each entry; this is useful for analyzing your loadbalancing methodology.
1005 You can choose which cookie you wish to log to the database, and it doesn't 1756 LogSQLMachineID permits you to distinguish each machine's entries if you
1006 necessarily have to have anything to do with mod_usertrack. 1757 assign each machine its own LogSQLMachineID: for example, web01, web02,
1758 web03, etc.
1007\layout Subsubsection 1759\layout Subsubsection
1008 1760
1009LogSQLCreateTables 1761LogSQLCreateTables
@@ -1018,10 +1770,10 @@ Example: LogSQLCreateTables On
1018Default: Off 1770Default: Off
1019\layout LyX-Code 1771\layout LyX-Code
1020 1772
1021Context: server config 1773Context: main server config
1022\layout Quote 1774\layout Quote
1023 1775
1024mod_log_sql now has the ability to create its tables on-the-fly. 1776mod_log_sql 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 1777 The advantage to this is convenience: you don't have to execute any SQL
1026 by hand to prepare the table. 1778 by hand to prepare the table.
1027 This is especially helpful for people with lots of virtual hosts (who should 1779 This is especially helpful for people with lots of virtual hosts (who should
@@ -1038,7 +1790,7 @@ There is a slight disadvantage: if you wish to activate this feature, then
1038 CREATE on the logging database is reasonable. 1790 CREATE on the logging database is reasonable.
1039\layout Quote 1791\layout Quote
1040 1792
1041This is defined only once in the httpd.conf file. 1793Defined this only once in the httpd.conf file.
1042\layout Subsubsection 1794\layout Subsubsection
1043 1795
1044LogSQLMassVirtualHosting 1796LogSQLMassVirtualHosting
@@ -1053,7 +1805,7 @@ Example: LogSQLMassVirtualHosting On
1053Default: Off 1805Default: Off
1054\layout LyX-Code 1806\layout LyX-Code
1055 1807
1056Context: server config 1808Context: main server config
1057\layout Quote 1809\layout Quote
1058 1810
1059If you administer a site hosting many, many virtual hosts then this option 1811If you administer a site hosting many, many virtual hosts then this option
@@ -1086,85 +1838,86 @@ This is a huge boost in convenience for sites with many virtual servers.
1086This is defined only once in the httpd.conf file. 1838This is defined only once in the httpd.conf file.
1087\layout Subsubsection 1839\layout Subsubsection
1088 1840
1089LogSQLPreserveFile 1841LogSQLTransferLogTable
1090\layout LyX-Code 1842\layout LyX-Code
1091 1843
1092Syntax: LogSQLPreserveFile filename 1844
1845\series bold
1846MANDATORY (if not LogSQLMassVirtualHosting)
1093\layout LyX-Code 1847\layout LyX-Code
1094 1848
1095Example: LogSQLPreserveFile offline-preserve 1849Syntax: LogSQLTransferLogTable table-name
1096\layout LyX-Code 1850\layout LyX-Code
1097 1851
1098Default: mysql-preserve (in /tmp) 1852Example: LogSQLTransferLogTable access_log_table
1099\layout LyX-Code 1853\layout LyX-Code
1100 1854
1101Context: virtual host 1855Context: virtual host
1102\layout Quote 1856\layout Quote
1103 1857
1104mod_log_sql writes queries to this local preserve file in the event that 1858Defines which table is used for logging of Apache's transfers; this is analogous
1105 it cannot reach the database, and thus ensures that your high-availability 1859 to Apache's TransferLog directive.
1106 web frontend does not lose logs during a temporary database outage. 1860 table-name must be a valid table within the database defined in LogSQLDatabase.
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 1861\layout Quote
1115 1862
1116For security purposes the name you supply will be prepended with "/tmp/" 1863This directive is not necessary if you declare LogSQLMassVirtualHosting
1117 to force creation of the file in /tmp. 1864 On, since that directive activates dynamically-named tables.
1118\layout Quote 1865 If you attempt to use LogSQLTransferLogTable at the same time a warning
1866 will be logged and it will be ignored, since LogSQLMassVirtualHosting takes
1867 priority.
1868\layout Subsubsection
1119 1869
1120If you do not define LogSQLPreserveFile then all virtual servers will log 1870LogSQLRequestIgnore
1121 to the same default preserve file (/tmp/mysql-preserve). 1871\layout LyX-Code
1122 You can redefine this on a virtual-host basis in order to segregate your 1872
1123 preserve files if you desire. 1873Syntax: LogSQLRequestIgnore req1 req2 req3 ...
1124 Note that segregation is not really necessary, as the SQL statements that 1874 reqN
1125 are written to the preserve file already distinguish between different 1875\layout LyX-Code
1126 virtual hosts. 1876
1877Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico
1878\layout LyX-Code
1879
1880Context: virtual host
1127\layout Quote 1881\layout Quote
1128 1882
1129The module will log to error-log if/when it notices a database outage, and 1883Lists a series of strings that, if present in the URI, will cause that request
1130 upon database return. 1884 NOT to be logged.
1131 You will therefore know when the preserve file is being used, although 1885 This directive is useful for cutting down on log clutter when you KNOW
1132 it is your responsibility to import the file. 1886 that you do not want to log requests for certain objects.
1887 (The example above is a genuinely useful one; it will prevent logging of
1888 many common Microsoft-based worm intrusion attempts, as well as those ridiculou
1889s requests for the favicon.)
1133\layout Quote 1890\layout Quote
1134 1891
1135The file does not need to be created in advance. 1892Each string is separated by a space, and NO regular expressions or globbing
1136 It is safe to remove or rename the file without interrupting Apache, as 1893 are allowed.
1137 the module closes the filehandle immediately after completing the write. 1894 Each string is evaluated as a substring of the URI using strstr().
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 1895\layout Subsubsection
1142 1896
1143LogSQLSocketFile 1897LogSQLRemhostIgnore
1144\layout LyX-Code
1145
1146Syntax: LogSQLSocketFile filename
1147\layout LyX-Code 1898\layout LyX-Code
1148 1899
1149Example: LogSQLSocketFile /tmp/mysql.sock 1900Syntax: LogSQLRemhostIgnore host1 host2 host3 ...
1901 hostN
1150\layout LyX-Code 1902\layout LyX-Code
1151 1903
1152Default: /var/lib/mysql/mysql.sock 1904Example: LogSQLRemhostIgnore localnet.com
1153\layout LyX-Code 1905\layout LyX-Code
1154 1906
1155Context: server config 1907Context: virtual host
1156\layout Quote 1908\layout Quote
1157 1909
1158At Apache runtime you can specify the MySQL socket file to use. 1910Lists a series of strings that, if present in the REMOTE_HOST, will cause
1159 Set this once in your main server config to override the default value. 1911 that request NOT to be logged.
1160 This value is irrelevant if your database resides on a separate machine. 1912 This directive is useful for cutting down on log clutter when you KNOW
1913 that you do not want to log requests from certain hosts, such as your own
1914 internal network machines.
1161\layout Quote 1915\layout Quote
1162 1916
1163mod_log_sql will automatically employ the socket for db communications if 1917Each string is separated by a space, and NO regular expressions or globbing
1164 the database resides on the local host. 1918 are allowed.
1165 If the db resides on a separate host the module will automatically use 1919 Each string is simply evaluated as a substring of the REMOTE_HOST using
1166 TCP/IP. 1920 strstr().
1167 This is a function of the MySQL API and is not user-configurable.
1168\layout Subsubsection 1921\layout Subsubsection
1169 1922
1170LogSQLTransferLogFormat 1923LogSQLTransferLogFormat
@@ -1207,7 +1960,7 @@ Each character in the format-string defines an attribute of the request
1207 1960
1208 1961
1209\series bold 1962\series bold
1210Character 1963Char
1211\end_inset 1964\end_inset
1212</cell> 1965</cell>
1213<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 1966<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1237,7 +1990,7 @@ MySQL DB field
1237 1990
1238 1991
1239\series bold 1992\series bold
1240Recommended column type 1993Column type
1241\end_inset 1994\end_inset
1242</cell> 1995</cell>
1243<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 1996<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1289,7 +2042,7 @@ varchar(255)
1289 2042
1290\layout Standard 2043\layout Standard
1291 2044
1292Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0) 2045Mozilla/4.0 (compat; MSIE 6.0; Windows)
1293\end_inset 2046\end_inset
1294</cell> 2047</cell>
1295</row> 2048</row>
@@ -1373,7 +2126,7 @@ varchar(255)
1373 2126
1374\layout Standard 2127\layout Standard
1375 2128
1376Apache=sdyn.fooonline.net.130051007102700823 2129Apache=sdyn.fooonline.net.1300102700823
1377\end_inset 2130\end_inset
1378</cell> 2131</cell>
1379</row> 2132</row>
@@ -1383,7 +2136,7 @@ Apache=sdyn.fooonline.net.130051007102700823
1383 2136
1384\layout Standard 2137\layout Standard
1385 2138
1386f 2139H
1387\end_inset 2140\end_inset
1388</cell> 2141</cell>
1389<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2142<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1391,6 +2144,7 @@ f
1391 2144
1392\layout Standard 2145\layout Standard
1393 2146
2147HTTP request protocol
1394\end_inset 2148\end_inset
1395</cell> 2149</cell>
1396<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2150<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1398,7 +2152,7 @@ f
1398 2152
1399\layout Standard 2153\layout Standard
1400 2154
1401request_file 2155request_protocol
1402\end_inset 2156\end_inset
1403</cell> 2157</cell>
1404<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2158<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1406,7 +2160,7 @@ request_file
1406 2160
1407\layout Standard 2161\layout Standard
1408 2162
1409varchar(255) 2163varchar(10)
1410\end_inset 2164\end_inset
1411</cell> 2165</cell>
1412<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2166<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1414,6 +2168,7 @@ varchar(255)
1414 2168
1415\layout Standard 2169\layout Standard
1416 2170
2171HTTP/1.1
1417\end_inset 2172\end_inset
1418</cell> 2173</cell>
1419</row> 2174</row>
@@ -1423,7 +2178,7 @@ varchar(255)
1423 2178
1424\layout Standard 2179\layout Standard
1425 2180
1426H 2181h
1427\end_inset 2182\end_inset
1428</cell> 2183</cell>
1429<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2184<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1431,7 +2186,7 @@ H
1431 2186
1432\layout Standard 2187\layout Standard
1433 2188
1434HTTP request protocol 2189Name of remote host
1435\end_inset 2190\end_inset
1436</cell> 2191</cell>
1437<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2192<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1439,7 +2194,7 @@ HTTP request protocol
1439 2194
1440\layout Standard 2195\layout Standard
1441 2196
1442request_protocol 2197remote_host
1443\end_inset 2198\end_inset
1444</cell> 2199</cell>
1445<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2200<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1447,7 +2202,7 @@ request_protocol
1447 2202
1448\layout Standard 2203\layout Standard
1449 2204
1450varchar(10) 2205varchar(50)
1451\end_inset 2206\end_inset
1452</cell> 2207</cell>
1453<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2208<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1455,7 +2210,7 @@ varchar(10)
1455 2210
1456\layout Standard 2211\layout Standard
1457 2212
1458HTTP/1.1 2213blah.foobar.com
1459\end_inset 2214\end_inset
1460</cell> 2215</cell>
1461</row> 2216</row>
@@ -1465,15 +2220,15 @@ HTTP/1.1
1465 2220
1466\layout Standard 2221\layout Standard
1467 2222
1468h 2223I
1469\end_inset 2224\end_inset
1470</cell> 2225</cell>
1471<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2226<cell multicolumn="0" alignment="left" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
1472\begin_inset Text 2227\begin_inset Text
1473 2228
1474\layout Standard 2229\layout Standard
1475 2230
1476Name of remote host 2231Request ID (from mod_unique_id)
1477\end_inset 2232\end_inset
1478</cell> 2233</cell>
1479<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2234<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1481,7 +2236,7 @@ Name of remote host
1481 2236
1482\layout Standard 2237\layout Standard
1483 2238
1484remote_host 2239id
1485\end_inset 2240\end_inset
1486</cell> 2241</cell>
1487<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2242<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1489,7 +2244,7 @@ remote_host
1489 2244
1490\layout Standard 2245\layout Standard
1491 2246
1492varchar(50) 2247char(19)
1493\end_inset 2248\end_inset
1494</cell> 2249</cell>
1495<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2250<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1497,7 +2252,7 @@ varchar(50)
1497 2252
1498\layout Standard 2253\layout Standard
1499 2254
1500blah.foobar.com 2255POlFcUBRH30AAALdBG8
1501\end_inset 2256\end_inset
1502</cell> 2257</cell>
1503</row> 2258</row>
@@ -1507,7 +2262,7 @@ blah.foobar.com
1507 2262
1508\layout Standard 2263\layout Standard
1509 2264
1510I 2265l
1511\end_inset 2266\end_inset
1512</cell> 2267</cell>
1513<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2268<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -1707,7 +2462,7 @@ varchar(255)
1707 2462
1708\layout Standard 2463\layout Standard
1709 2464
1710http://www.biglinkstoyou.com/linkpage.html 2465http://www.biglinks4u.com/linkpage.html
1711\end_inset 2466\end_inset
1712</cell> 2467</cell>
1713</row> 2468</row>
@@ -2059,11 +2814,12 @@ If you have compiled mod_log_sql with WANT_SSL_LOGGING, you also get these:
2059 2814
2060 2815
2061\begin_inset Tabular 2816\begin_inset Tabular
2062<lyxtabular version="2" rows="4" columns="4"> 2817<lyxtabular version="2" rows="4" columns="5">
2063<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0"> 2818<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=""> 2819<column alignment="center" valignment="top" leftline="true" rightline="false" width="" special="">
2065<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special=""> 2820<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
2066<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special=""> 2821<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
2822<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
2067<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special=""> 2823<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
2068<row topline="true" bottomline="true" newpage="false"> 2824<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=""> 2825<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -2073,7 +2829,17 @@ If you have compiled mod_log_sql with WANT_SSL_LOGGING, you also get these:
2073 2829
2074 2830
2075\series bold 2831\series bold
2076Character 2832Char
2833\end_inset
2834</cell>
2835<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2836\begin_inset Text
2837
2838\layout Standard
2839
2840
2841\series bold
2842What is this?
2077\end_inset 2843\end_inset
2078</cell> 2844</cell>
2079<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2845<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -2083,7 +2849,7 @@ Character
2083 2849
2084 2850
2085\series bold 2851\series bold
2086MySQL DB field it activates 2852MySQL DB field
2087\end_inset 2853\end_inset
2088</cell> 2854</cell>
2089<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special=""> 2855<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
@@ -2093,7 +2859,7 @@ MySQL DB field it activates
2093 2859
2094 2860
2095\series bold 2861\series bold
2096MySQL DB field it activatesRecommended column type 2862Column Type
2097\end_inset 2863\end_inset
2098</cell> 2864</cell>
2099<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special=""> 2865<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
@@ -2121,6 +2887,14 @@ z
2121 2887
2122\layout Standard 2888\layout Standard
2123 2889
2890SSL cipher used
2891\end_inset
2892</cell>
2893<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2894\begin_inset Text
2895
2896\layout Standard
2897
2124ssl_cipher 2898ssl_cipher
2125\end_inset 2899\end_inset
2126</cell> 2900</cell>
@@ -2155,6 +2929,14 @@ q
2155 2929
2156\layout Standard 2930\layout Standard
2157 2931
2932Keysize of the SSL connection
2933\end_inset
2934</cell>
2935<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2936\begin_inset Text
2937
2938\layout Standard
2939
2158ssl_keysize 2940ssl_keysize
2159\end_inset 2941\end_inset
2160</cell> 2942</cell>
@@ -2189,6 +2971,14 @@ Q
2189 2971
2190\layout Standard 2972\layout Standard
2191 2973
2974Maximum keysize supported
2975\end_inset
2976</cell>
2977<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
2978\begin_inset Text
2979
2980\layout Standard
2981
2192ssl_maxkeysize 2982ssl_maxkeysize
2193\end_inset 2983\end_inset
2194</cell> 2984</cell>
@@ -2214,6 +3004,210 @@ smallint unsigned
2214\end_inset 3004\end_inset
2215 3005
2216 3006
3007\layout Subsubsection
3008
3009LogSQLWhichCookie
3010\layout LyX-Code
3011
3012Syntax: LogSQLWhichCookie cookiename
3013\layout LyX-Code
3014
3015Example: LogSQLWhichCookie Clicks
3016\layout LyX-Code
3017
3018Default: None.
3019 You must set this if you wish to capture cookies.
3020\layout LyX-Code
3021
3022Context: virtual host
3023\layout Quote
3024
3025In HTTP, cookies have names to distinguish them from each other.
3026 Using mod_usertrack, for example, you can give your user-tracking cookies
3027 a name with the CookieName directive.
3028\layout Quote
3029
3030If you have activated cookie logging in LogSQLTransferLogFormat, then LogSQLWhic
3031hCookie tells mod_log_sql which cookie to log.
3032 This is useful because many times you will be setting and receiving more
3033 than one cookie from a client; without this directive you'd be unable to
3034 choose which cookie is your mod_usertrack cookie.
3035\layout Quote
3036
3037Note: although this was intended for people who are using mod_usertrack
3038 to set user-tracking cookies, you aren't restricted in any way.
3039 You can choose which cookie you wish to log to the database, and it doesn't
3040 necessarily have to have anything to do with mod_usertrack.
3041\layout Subsubsection
3042
3043LogSQLWhichCookies
3044\layout LyX-Code
3045
3046Syntax: LogSQLWhichCookies cookie1 cookie2 ...
3047 cookieN
3048\layout LyX-Code
3049
3050Example: LogSQLWhichCookies userlogin foobar foobaz
3051\layout LyX-Code
3052
3053Default: None
3054\layout LyX-Code
3055
3056Context: virtual host
3057\layout Quote
3058
3059Defines the list of cookies you would like logged.
3060 Works in conjunction with LogSQLCookieLogTable.
3061\layout Subsubsection
3062
3063LogSQLWhichHeadersIn
3064\layout LyX-Code
3065
3066Syntax: LogSQLWhichHeadersIn item1 item2 ...
3067 itemN
3068\layout LyX-Code
3069
3070Example: LogSQLWhichHeadersIn UserAgent Accept-Encoding Host
3071\layout LyX-Code
3072
3073Default: None
3074\layout LyX-Code
3075
3076Context: virtual host
3077\layout Quote
3078
3079Defines the list of inbound headers you would like logged.
3080 Works in conjunction with LogSQLHeadersInLogTable.
3081\layout Subsubsection
3082
3083LogSQLWhichHeadersOut
3084\layout LyX-Code
3085
3086Syntax: LogSQLWhichHeadersOut item1 item2 ...
3087 itemN
3088\layout LyX-Code
3089
3090Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control
3091\layout LyX-Code
3092
3093Default: None
3094\layout LyX-Code
3095
3096Context: virtual host
3097\layout Quote
3098
3099Defines the list of outbound headers you would like logged.
3100 Works in conjunction with LogSQLHeadersOutLogTable.
3101\layout Subsubsection
3102
3103LogSQLWhichNotes
3104\layout LyX-Code
3105
3106Syntax: LogSQLWhichNotes item1 item2 ...
3107 itemN
3108\layout LyX-Code
3109
3110Example: LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio
3111\layout LyX-Code
3112
3113Default: None
3114\layout LyX-Code
3115
3116Context: virtual host
3117\layout Quote
3118
3119Defines the list of notes you would like logged.
3120 Works in conjunction with LogSQLNotesLogTable.
3121\layout Subsubsection
3122
3123LogSQLCookieLogTable
3124\layout LyX-Code
3125
3126Syntax: LogSQLCookieLogTable table-name
3127\layout LyX-Code
3128
3129Example: LogSQLCookieLogTable cookie_log
3130\layout LyX-Code
3131
3132Default: cookies
3133\layout LyX-Code
3134
3135Context: virtual host
3136\layout Quote
3137
3138Defines which table is used for logging of cookies.
3139 Working in conjunction with LogSQLWhichCookies, you can log many of each
3140 request's associated cookies to a separate table.
3141 For meaningful data retrieval the cookie table is keyed to the access table
3142 by the unique request ID supplied by the standard Apache module mod_unique_id.
3143\layout Subsubsection
3144
3145LogSQLHeadersInLogTable
3146\layout LyX-Code
3147
3148Syntax: LogSQLHeadersInLogTable table-name
3149\layout LyX-Code
3150
3151Example: LogSQLHeadersInLogTable headers
3152\layout LyX-Code
3153
3154Default: headers_in
3155\layout LyX-Code
3156
3157Context: virtual host
3158\layout Quote
3159
3160Defines which table is used for logging of inbound headers.
3161 Working in conjunction with LogSQLWhichHeadersIn, you can log many of each
3162 request's associated headers to a separate table.
3163 For meaningful data retrieval the headers table is keyed to the access
3164 table by the unique request ID supplied by the standard Apache module mod_uniqu
3165e_id.
3166\layout Subsubsection
3167
3168LogSQLHeadersOutLogTable
3169\layout LyX-Code
3170
3171Syntax: LogSQLHeadersOutLogTable table-name
3172\layout LyX-Code
3173
3174Example: LogSQLHeadersOutLogTable headers
3175\layout LyX-Code
3176
3177Default: headers_out
3178\layout LyX-Code
3179
3180Context: virtual host
3181\layout Quote
3182
3183Defines which table is used for logging of outbound headers.
3184 Working in conjunction with LogSQLWhichHeadersOut, you can log many of
3185 each request's associated headers to a separate table.
3186 For meaningful data retrieval the headers table is keyed to the access
3187 table by the unique request ID supplied by the standard Apache module mod_uniqu
3188e_id.
3189\layout Subsubsection
3190
3191LogSQLNotesLogTable
3192\layout LyX-Code
3193
3194Syntax: LogSQLNotesLogTable table-name
3195\layout LyX-Code
3196
3197Example: LogSQLNotesLogTable notes_log
3198\layout LyX-Code
3199
3200Default: notes
3201\layout LyX-Code
3202
3203Context: virtual host
3204\layout Quote
3205
3206Defines which table is used for logging of notes.
3207 Working in conjunction with LogSQLWhichNotes, you can log many of each
3208 request's associated notes to a separate table.
3209 For meaningful data retrieval the notes table is keyed to the access table
3210 by the unique request ID supplied by the standard Apache module mod_unique_id.
2217\layout Section 3211\layout Section
2218 3212
2219FAQ 3213FAQ
@@ -2231,9 +3225,13 @@ To begin with, let's get it out of the way: logging to a database is not
2231 3225
2232Chores like log rotation go away, as you can DELETE records from the SQL 3226Chores like log rotation go away, as you can DELETE records from the SQL
2233 database once they are no longer useful. 3227 database once they are no longer useful.
2234 For example, the excellent and popular log-analysis tool Webalizer (http://www.w 3228 For example, the excellent and popular log-analysis tool
2235ebalizer.com/) does not need historic logs after it has processed them, enabling 3229\begin_inset LatexCommand \url[Webalizer]{http://www.webalizer.com}
2236 you to delete older logs. 3230
3231\end_inset
3232
3233 does not need historic logs after it has processed them, enabling you to
3234 delete older logs.
2237 3235
2238\layout Itemize 3236\layout Itemize
2239 3237
@@ -2255,36 +3253,234 @@ select remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp)
2255 from acc_log_tbl where status=404 order by time_stamp; 3253 from acc_log_tbl where status=404 order by time_stamp;
2256\layout LyX-Code 3254\layout LyX-Code
2257 3255
2258+-----------------------------------+--------+--------------------------+------- 3256\layout Standard
2259-----+--------------------------- 3257\align center
2260\layout LyX-Code
2261 3258
2262+ remote_host | status | request_uri 3259\begin_inset Tabular
2263 | bytes_sent | from_unixtime(time_stamp)| 3260<lyxtabular version="2" rows="5" columns="5">
2264\layout LyX-Code 3261<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
3262<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
3263<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
3264<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
3265<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
3266<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
3267<row topline="true" bottomline="true" newpage="false">
3268<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3269\begin_inset Text
2265 3270
2266+-----------------------------------+--------+--------------------------+------- 3271\layout Standard
2267-----+---------------------------
2268\layout LyX-Code
2269 3272
2270| marge.mmm.co.uk | 404 | /favicon.ico | 321 3273remote_host
2271 | 2001-11-20 02:30:56 | 3274\end_inset
2272\layout LyX-Code 3275</cell>
3276<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3277\begin_inset Text
2273 3278
2274| 62.180.239.251 | 404 | /favicon.ico | 333 3279\layout Standard
2275 | 2001-11-20 02:45:25 | 3280
2276\layout LyX-Code 3281status
3282\end_inset
3283</cell>
3284<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3285\begin_inset Text
3286
3287\layout Standard
3288
3289request_uri
3290\end_inset
3291</cell>
3292<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3293\begin_inset Text
3294
3295\layout Standard
3296
3297bytes_sent
3298\end_inset
3299</cell>
3300<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3301\begin_inset Text
3302
3303\layout Standard
3304
3305from_unixtime(time_stamp)
3306\end_inset
3307</cell>
3308</row>
3309<row topline="true" bottomline="false" newpage="false">
3310<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3311\begin_inset Text
3312
3313\layout Standard
3314
3315marge.mmm.co.uk
3316\end_inset
3317</cell>
3318<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3319\begin_inset Text
3320
3321\layout Standard
3322
3323404
3324\end_inset
3325</cell>
3326<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3327\begin_inset Text
3328
3329\layout Standard
3330
3331/favicon.ico
3332\end_inset
3333</cell>
3334<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3335\begin_inset Text
3336
3337\layout Standard
3338
3339321
3340\end_inset
3341</cell>
3342<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3343\begin_inset Text
3344
3345\layout Standard
3346
33472001-11-20 02:30:56
3348\end_inset
3349</cell>
3350</row>
3351<row topline="true" bottomline="false" newpage="false">
3352<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3353\begin_inset Text
3354
3355\layout Standard
3356
335762.180.239.251
3358\end_inset
3359</cell>
3360<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3361\begin_inset Text
3362
3363\layout Standard
3364
3365404
3366\end_inset
3367</cell>
3368<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3369\begin_inset Text
3370
3371\layout Standard
3372
3373/favicon.ico
3374\end_inset
3375</cell>
3376<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3377\begin_inset Text
3378
3379\layout Standard
3380
3381333
3382\end_inset
3383</cell>
3384<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3385\begin_inset Text
3386
3387\layout Standard
3388
33892001-11-20 02:45:25
3390\end_inset
3391</cell>
3392</row>
3393<row topline="true" bottomline="false" newpage="false">
3394<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3395\begin_inset Text
3396
3397\layout Standard
3398
3399212.234.12.66
3400\end_inset
3401</cell>
3402<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3403\begin_inset Text
3404
3405\layout Standard
3406
3407404
3408\end_inset
3409</cell>
3410<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3411\begin_inset Text
3412
3413\layout Standard
3414
3415/favicon.ico
3416\end_inset
3417</cell>
3418<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3419\begin_inset Text
3420
3421\layout Standard
3422
3423321
3424\end_inset
3425</cell>
3426<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3427\begin_inset Text
3428
3429\layout Standard
3430
34312001-11-20 03:01:00
3432\end_inset
3433</cell>
3434</row>
3435<row topline="true" bottomline="true" newpage="false">
3436<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3437\begin_inset Text
3438
3439\layout Standard
3440
3441212.210.78.254
3442\end_inset
3443</cell>
3444<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3445\begin_inset Text
3446
3447\layout Standard
3448
3449404
3450\end_inset
3451</cell>
3452<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3453\begin_inset Text
3454
3455\layout Standard
3456
3457/favicon.ico
3458\end_inset
3459</cell>
3460<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3461\begin_inset Text
3462
3463\layout Standard
3464
3465333
3466\end_inset
3467</cell>
3468<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3469\begin_inset Text
3470
3471\layout Standard
3472
34732001-11-20 03:26:05
3474\end_inset
3475</cell>
3476</row>
3477</lyxtabular>
3478
3479\end_inset
2277 3480
2278| 212.234.12.66 | 404 | /favicon.ico | 321
2279 | 2001-11-20 03:01:00 |
2280\layout LyX-Code
2281 3481
2282| 212.210.78.254 | 404 | /favicon.ico | 333
2283 | 2001-11-20 03:26:05 |
2284\layout LyX-Code 3482\layout LyX-Code
2285 3483
2286+-----------------------------------+--------+--------------------------+-------
2287-----+---------------------------+
2288\layout Standard 3484\layout Standard
2289 3485
2290Or do you want to see how many bytes you've sent within a certain directory 3486Or do you want to see how many bytes you've sent within a certain directory
@@ -2296,46 +3492,152 @@ select request_uri,sum(bytes_sent) as bytes,count(request_uri) as howmany
2296 order by howmany desc; 3492 order by howmany desc;
2297\layout LyX-Code 3493\layout LyX-Code
2298 3494
2299+-----------------------------------+---------+---------+ 3495\layout Standard
2300\layout LyX-Code 3496\align center
2301 3497
2302| request_uri | bytes | howmany | 3498\begin_inset Tabular
2303\layout LyX-Code 3499<lyxtabular version="2" rows="5" columns="3">
3500<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
3501<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
3502<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
3503<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
3504<row topline="true" bottomline="true" newpage="false">
3505<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3506\begin_inset Text
2304 3507
2305+-----------------------------------+---------+---------+ 3508\layout Standard
2306\layout LyX-Code
2307 3509
2308| /mod_log_sql/style_1.css | 157396 | 1288 | 3510request_uri
2309\layout LyX-Code 3511\end_inset
3512</cell>
3513<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3514\begin_inset Text
2310 3515
2311| /mod_log_sql/ | 2514337 | 801 | 3516\layout Standard
2312\layout LyX-Code
2313 3517
2314| /mod_log_sql/mod_log_sql.tar.gz | 9769312 | 456 | 3518bytes
2315\layout LyX-Code 3519\end_inset
3520</cell>
3521<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3522\begin_inset Text
2316 3523
2317| /mod_log_sql/faq.html | 5038728 | 436 | 3524\layout Standard
2318\layout LyX-Code
2319 3525
2320| /mod_log_sql/INSTALL | 1196161 | 202 | 3526howmany
2321\layout LyX-Code 3527\end_inset
3528</cell>
3529</row>
3530<row topline="true" bottomline="false" newpage="false">
3531<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3532\begin_inset Text
2322 3533
2323| /mod_log_sql/directives.html | 1096821 | 171 | 3534\layout Standard
2324\layout LyX-Code
2325 3535
2326| /mod_log_sql/CHANGELOG | 424481 | 107 | 3536/mod_log_sql/style_1.css
2327\layout LyX-Code 3537\end_inset
3538</cell>
3539<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3540\begin_inset Text
2328 3541
2329| /mod_log_sql/README | 796072 | 97 | 3542\layout Standard
2330\layout LyX-Code 3543
3544157396
3545\end_inset
3546</cell>
3547<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3548\begin_inset Text
3549
3550\layout Standard
3551
35521288
3553\end_inset
3554</cell>
3555</row>
3556<row topline="true" bottomline="false" newpage="false">
3557<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3558\begin_inset Text
3559
3560\layout Standard
3561
3562/mod_log_sql/
3563\end_inset
3564</cell>
3565<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3566\begin_inset Text
3567
3568\layout Standard
3569
35702514337
3571\end_inset
3572</cell>
3573<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3574\begin_inset Text
3575
3576\layout Standard
3577
3578801
3579\end_inset
3580</cell>
3581</row>
3582<row topline="true" bottomline="false" newpage="false">
3583<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3584\begin_inset Text
3585
3586\layout Standard
3587
3588/mod_log_sql/mod_log_sql.tar.gz
3589\end_inset
3590</cell>
3591<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3592\begin_inset Text
3593
3594\layout Standard
3595
35969769312
3597\end_inset
3598</cell>
3599<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3600\begin_inset Text
3601
3602\layout Standard
3603
3604456
3605\end_inset
3606</cell>
3607</row>
3608<row topline="true" bottomline="true" newpage="false">
3609<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3610\begin_inset Text
3611
3612\layout Standard
3613
3614/mod_log_sql/faq.html
3615\end_inset
3616</cell>
3617<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3618\begin_inset Text
3619
3620\layout Standard
3621
36225038728
3623\end_inset
3624</cell>
3625<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3626\begin_inset Text
3627
3628\layout Standard
3629
3630436
3631\end_inset
3632</cell>
3633</row>
3634</lyxtabular>
3635
3636\end_inset
2331 3637
2332| /mod_log_sql/directives-old.html | 42480 | 6 |
2333\layout LyX-Code
2334 3638
2335| /mod_log_sql/index.html | 9531 | 3 |
2336\layout LyX-Code 3639\layout LyX-Code
2337 3640
2338+-----------------------------------+---------+---------+
2339\layout Standard 3641\layout Standard
2340 3642
2341Or maybe you want to see who's linking to you? Do this: 3643Or maybe you want to see who's linking to you? Do this:
@@ -2345,44 +3647,111 @@ select count(referer) as num,referer from acc_log_tbl where request_uri='/mod_lo
2345g_sql/' group by referer order by num desc; 3647g_sql/' group by referer order by num desc;
2346\layout LyX-Code 3648\layout LyX-Code
2347 3649
2348+-----+-------------------------------------------------------------------------
2349------+
2350\layout LyX-Code 3650\layout LyX-Code
3651\align center
2351 3652
2352| num | referer 3653\begin_inset Tabular
2353 | 3654<lyxtabular version="2" rows="5" columns="2">
2354\layout LyX-Code 3655<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
3656<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
3657<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
3658<row topline="true" bottomline="true" newpage="false">
3659<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3660\begin_inset Text
2355 3661
2356+-----+------------------------------------------------------------------------- 3662\layout Standard
2357------+
2358\layout LyX-Code
2359 3663
2360| 271 | http://freshmeat.net/projects/mod_log_sql/ 3664num
2361 | 3665\end_inset
2362\layout LyX-Code 3666</cell>
3667<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3668\begin_inset Text
2363 3669
2364| 96 | http://modules.apache.org/search?id=339 3670\layout Standard
2365 |
2366\layout LyX-Code
2367 3671
2368| 48 | http://freshmeat.net/ 3672referer
2369 | 3673\end_inset
2370\layout LyX-Code 3674</cell>
3675</row>
3676<row topline="true" bottomline="false" newpage="false">
3677<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3678\begin_inset Text
2371 3679
2372| 8 | http://freshmeat.net 3680\layout Standard
2373 | 3681
2374\layout LyX-Code 3682271
3683\end_inset
3684</cell>
3685<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3686\begin_inset Text
3687
3688\layout Standard
3689
3690http://freshmeat.net/projects/mod_log_sql/
3691\end_inset
3692</cell>
3693</row>
3694<row topline="true" bottomline="false" newpage="false">
3695<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3696\begin_inset Text
3697
3698\layout Standard
3699
370096
3701\end_inset
3702</cell>
3703<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3704\begin_inset Text
3705
3706\layout Standard
3707
3708http://modules.apache.org/search?id=339
3709\end_inset
3710</cell>
3711</row>
3712<row topline="true" bottomline="false" newpage="false">
3713<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3714\begin_inset Text
3715
3716\layout Standard
3717
371848
3719\end_inset
3720</cell>
3721<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3722\begin_inset Text
3723
3724\layout Standard
3725
3726http://freshmeat.net/
3727\end_inset
3728</cell>
3729</row>
3730<row topline="true" bottomline="true" newpage="false">
3731<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
3732\begin_inset Text
3733
3734\layout Standard
3735
37368
3737\end_inset
3738</cell>
3739<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
3740\begin_inset Text
3741
3742\layout Standard
3743
3744http://freshmeat.net
3745\end_inset
3746</cell>
3747</row>
3748</lyxtabular>
3749
3750\end_inset
2375 3751
2376| 7 | http://freshmeat.net/daily/2001/11/30/
2377 |
2378\layout LyX-Code
2379 3752
2380| 6 | http://freshmeat.net/daily/2001/11/20/
2381 |
2382\layout LyX-Code 3753\layout LyX-Code
2383 3754
2384+-----+-------------------------------------------------------------------------
2385------+
2386\layout Standard 3755\layout Standard
2387 3756
2388As you can see, there are myriad possibilities that can be constructed with 3757As you can see, there are myriad possibilities that can be constructed with
@@ -2444,12 +3813,9 @@ Machine B: MySQL server
2444\layout Itemize 3813\layout Itemize
2445 3814
2446Machines A and B connected with 100Mbps Ethernet 3815Machines A and B connected with 100Mbps Ethernet
2447\layout Standard
2448
2449Webserver configuration:
2450\layout Itemize 3816\layout Itemize
2451 3817
2452Celeron 400 128 MB RAM IDE storage 3818Webserver: Celeron 400, 128 MB RAM, IDE storage
2453\layout Standard 3819\layout Standard
2454 3820
2455Apache configuration: 3821Apache configuration:
@@ -2515,23 +3881,31 @@ Average of five runs employing MySQL
2515\emph on 3881\emph on
2516and 3882and
2517\emph default 3883\emph default
2518 standard text logging: 139.01 requests per second, zero errors. 3884 standard text logging:
3885\series bold
3886139.01 requests per second, zero errors
3887\series default
3888.
2519\layout Itemize 3889\layout Itemize
2520 3890
2521Average of five runs employing 3891Average of five runs employing
2522\emph on 3892\emph on
2523only 3893only
2524\emph default 3894\emph default
2525 standard text logging: 139.96 requests per second, zero errors. 3895 standard text logging:
3896\series bold
3897139.96 requests per second, zero errors
3898\series default
3899.
2526\layout Standard 3900\layout Standard
2527 3901
2528In other words, any rate-limiting effects on this particular hardware setup 3902In other words, any rate-limiting effects on this particular hardware setup
2529 are not caused by MySQL. 3903 are not caused by MySQL.
2530 Note that although this very simple webserver setup is hardly cutting-edge 3904 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 3905 -- it is, after all, a fairly small machine -- 139 requests per second
2532 equal over twelve million hits per day 3906 equal over
2533\emph on 3907\emph on
2534. 3908twelve million hits per day.
2535\layout Standard 3909\layout Standard
2536 3910
2537If you run this benchmark yourself, take note of three things: 3911If you run this benchmark yourself, take note of three things:
@@ -2586,7 +3960,7 @@ mod_log_sql would be virtually useless if there weren't a way for you to
2586 3960
2587For example, let's say that you want your web statistics updated once per 3961For example, let's say that you want your web statistics updated once per
2588 day in the wee hours of the morning. 3962 day in the wee hours of the morning.
2589 A good way to accomplish that would be the following entries in /etc/crontab: 3963 A good way to accomplish that could be the following entries in /etc/crontab:
2590\layout LyX-Code 3964\layout LyX-Code
2591 3965
2592# Generate the temporary apache logs from the MySQL database (for webalizer) 3966# Generate the temporary apache logs from the MySQL database (for webalizer)
@@ -2606,14 +3980,14 @@ httpd/mysql-grubbybaby
2606 3980
2607Or if you have a newer system that puts files in /etc/cron.daily etc., create 3981Or if you have a newer system that puts files in /etc/cron.daily etc., create
2608 a file called 3982 a file called
2609\begin_inset Quotes sld 3983\begin_inset Quotes eld
2610\end_inset 3984\end_inset
2611 3985
2612webalizer 3986webalizer
2613\begin_inset Quotes srd 3987\begin_inset Quotes srd
2614\end_inset 3988\end_inset
2615 3989
2616 in the cron.____ subdir of your choice. 3990 in the cron.daily subdirectory.
2617 Use the following as the contents of your file, and make sure to chmod 3991 Use the following as the contents of your file, and make sure to chmod
2618 755 it when done. 3992 755 it when done.
2619\layout LyX-Code 3993\layout LyX-Code
@@ -2740,59 +4114,136 @@ Perform some hits on your server and run a select:
2740mysql> select request_uri,cookie from access_log where cookie is not null; 4114mysql> select request_uri,cookie from access_log where cookie is not null;
2741\layout LyX-Code 4115\layout LyX-Code
2742 4116
2743+--------------------------------------------+----------------------------------
2744------------------+
2745\layout LyX-Code 4117\layout LyX-Code
4118\align center
2746 4119
2747| request_uri | 4120\begin_inset Tabular
2748 cookie | 4121<lyxtabular version="2" rows="4" columns="2">
2749\layout LyX-Code 4122<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
4123<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
4124<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
4125<row topline="true" bottomline="true" newpage="false">
4126<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4127\begin_inset Text
2750 4128
2751+--------------------------------------------+---------------------------------- 4129\layout Standard
2752------------------+
2753\layout LyX-Code
2754 4130
2755| /mod_log_sql/ | ool-18e4.dyn.optonline.net.1300510071 4131request_uri
275602700823 | 4132\end_inset
2757\layout LyX-Code 4133</cell>
4134<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
4135\begin_inset Text
4136
4137\layout Standard
4138
4139cookie
4140\end_inset
4141</cell>
4142</row>
4143<row topline="true" bottomline="false" newpage="false">
4144<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4145\begin_inset Text
4146
4147\layout Standard
4148
4149/mod_log_sql/
4150\end_inset
4151</cell>
4152<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
4153\begin_inset Text
4154
4155\layout Standard
4156
4157ool-18e4.dyn.optonline.net.130051007102700823
4158\end_inset
4159</cell>
4160</row>
4161<row topline="true" bottomline="false" newpage="false">
4162<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4163\begin_inset Text
4164
4165\layout Standard
4166
4167/mod_log_sql/usa.gif
4168\end_inset
4169</cell>
4170<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
4171\begin_inset Text
4172
4173\layout Standard
4174
4175ool-18e4.dyn.optonline.net.130051007102700823
4176\end_inset
4177</cell>
4178</row>
4179<row topline="true" bottomline="true" newpage="false">
4180<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4181\begin_inset Text
4182
4183\layout Standard
4184
4185/mod_log_sql/style_1.css
4186\end_inset
4187</cell>
4188<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
4189\begin_inset Text
4190
4191\layout Standard
4192
4193ool-18e4.dyn.optonline.net.130051007102700823
4194\end_inset
4195</cell>
4196</row>
4197</lyxtabular>
4198
4199\end_inset
2758 4200
2759| /mod_log_sql/usa.gif | ool-18e4.dyn.optonline.net.13005100710
27602700823 |
2761\layout LyX-Code
2762 4201
2763| /mod_log_sql/style_1.css | ool-18e4.dyn.optonline.net.13005100710
27642700823 |
2765\layout LyX-Code 4202\layout LyX-Code
2766 4203
2767...etc...
2768\layout Subsection 4204\layout Subsection
2769 4205
2770What if I want to log more than one cookie? 4206What if I want to log more than one cookie? What is the difference between
4207 LogSQLWhichCookie and LogSQLWhichCookies?
2771\layout Standard 4208\layout Standard
2772 4209
2773No problem. 4210As of version 1.17, you have a choice in how you want cookie logging handled.
2774 As of version 1.17, you have a choice. 4211\layout Standard
2775 If you are just interested in a single cookie, follow the instructions 4212
2776 in section 4213If you are interested in logging only one cookie per request, follow the
4214 instructions in section
2777\begin_inset LatexCommand \ref{sec:cookie} 4215\begin_inset LatexCommand \ref{sec:cookie}
2778 4216
2779\end_inset 4217\end_inset
2780 4218
2781 above. 4219 above.
2782 That cookie will be logged to a column in the regular access_log table. 4220 That cookie will be logged to a column in the regular access_log table,
4221 and the actual cookie you want to log is specified with LogSQLWhichCookie.
4222 Don't forget to specify the 'c' character in LogSQLTransferLogFormat.
2783\layout Standard 4223\layout Standard
2784 4224
2785However, if you need to log multiple cookies, you'll employ the LogSQLWhichCooki 4225If, however, you need to log multiple cookies per request, you must employ
2786es (note the plural) directive. 4226 the LogSQLWhichCookies (note the plural) directive.
2787 The cookies you specify will be logged to a separate table, and entries 4227 The cookies you specify will be logged to a separate table (as discussed
2788 in that table will be linked to the regular access_log entries via the 4228 in section
2789 unique ID that is supplied by mod_unique_id. 4229\begin_inset LatexCommand \ref{secMulTable}
4230
4231\end_inset
4232
4233), and entries in that table will be linked to the regular access_log entries
4234 via the unique ID that is supplied by mod_unique_id.
2790 Without mod_unique_id the information will still be logged but you will 4235 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. 4236 be unable to correlate which cookies go with which access-requests.
4237 Furthermore, with LogSQLWhichCookies, you do
4238\series bold
4239not
4240\series default
4241 need to include the 'c' character in LogSQLTransferLogFormat.
2792\layout Standard 4242\layout Standard
2793 4243
2794LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict, but 4244LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict because
2795 you're better off choosing the one you need. 4245 they operate on entireley different tables, but you're better off choosing
4246 the one you need.
2796\layout Subsection 4247\layout Subsection
2797 4248
2798What are the SSL logging features, and how do I activate them? 4249What are the SSL logging features, and how do I activate them?
@@ -2814,8 +4265,8 @@ You can compile mod_log_sql with SSL logging support if you have the right
2814\layout Standard 4265\layout Standard
2815 4266
2816You need to ensure that your database is set up to log the SSL data. 4267You 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 4268 Issue the following commands to MySQL if your access table does not already
2818 table built: 4269 have them:
2819\layout LyX-Code 4270\layout LyX-Code
2820 4271
2821alter table access_log add column ssl_cipher varchar(25); 4272alter table access_log add column ssl_cipher varchar(25);
@@ -2834,7 +4285,11 @@ Finally configure httpd.conf to activate the SSL fields.
2834<VirtualHost 1.2.3.4:443> 4285<VirtualHost 1.2.3.4:443>
2835\layout LyX-Code 4286\layout LyX-Code
2836 4287
2837 LogSQLTransferLogFormat AbHhmRSsTUuvcQqz 4288 LogSQLTransferLogFormat AbHhmRSsTUuvc
4289\series bold
4290Qqz
4291\series default
4292
2838\layout LyX-Code 4293\layout LyX-Code
2839 4294
2840</VirtualHost> 4295</VirtualHost>
@@ -2851,37 +4306,198 @@ mysql> select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize
2851 from access_log where ssl_cipher is not null; 4306 from access_log where ssl_cipher is not null;
2852\layout LyX-Code 4307\layout LyX-Code
2853 4308
2854+--------------------------+----------------------------------------------------
2855+------------+-------------+----------------+
2856\layout LyX-Code 4309\layout LyX-Code
4310\align center
2857 4311
2858| remote_host | request_uri 4312\begin_inset Tabular
2859 | ssl_cipher | ssl_keysize | ssl_maxkeysize | 4313<lyxtabular version="2" rows="4" columns="5">
2860\layout LyX-Code 4314<features rotate="false" islongtable="false" endhead="0" endfirsthead="0" endfoot="0" endlastfoot="0">
4315<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
4316<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
4317<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
4318<column alignment="left" valignment="top" leftline="true" rightline="false" width="" special="">
4319<column alignment="left" valignment="top" leftline="true" rightline="true" width="" special="">
4320<row topline="true" bottomline="true" newpage="false">
4321<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4322\begin_inset Text
2861 4323
2862+--------------------------+---------------------------------------------------- 4324\layout Standard
2863+------------+-------------+----------------+
2864\layout LyX-Code
2865 4325
2866| 216.190.52.4 | /dir/somefile.html 4326remote_host
2867 | RC4-MD5 | 128 | 128 | 4327\end_inset
2868\layout LyX-Code 4328</cell>
4329<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4330\begin_inset Text
4331
4332\layout Standard
4333
4334request_uri
4335\end_inset
4336</cell>
4337<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4338\begin_inset Text
4339
4340\layout Standard
4341
4342ssl_cipher
4343\end_inset
4344</cell>
4345<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4346\begin_inset Text
4347
4348\layout Standard
4349
4350ssl_keysize
4351\end_inset
4352</cell>
4353<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
4354\begin_inset Text
4355
4356\layout Standard
4357
4358ssl_maxkeysize
4359\end_inset
4360</cell>
4361</row>
4362<row topline="true" bottomline="false" newpage="false">
4363<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4364\begin_inset Text
4365
4366\layout Standard
4367
4368216.190.52.4
4369\end_inset
4370</cell>
4371<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4372\begin_inset Text
4373
4374\layout Standard
4375
4376/dir/somefile.html
4377\end_inset
4378</cell>
4379<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4380\begin_inset Text
4381
4382\layout Standard
4383
4384RC4-MD5
4385\end_inset
4386</cell>
4387<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4388\begin_inset Text
4389
4390\layout Standard
4391
4392128
4393\end_inset
4394</cell>
4395<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
4396\begin_inset Text
4397
4398\layout Standard
4399
4400128
4401\end_inset
4402</cell>
4403</row>
4404<row topline="true" bottomline="false" newpage="false">
4405<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4406\begin_inset Text
4407
4408\layout Standard
4409
4410216.190.52.4
4411\end_inset
4412</cell>
4413<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4414\begin_inset Text
4415
4416\layout Standard
4417
4418/dir/somefile.gif
4419\end_inset
4420</cell>
4421<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4422\begin_inset Text
4423
4424\layout Standard
4425
4426RC4-MD5
4427\end_inset
4428</cell>
4429<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4430\begin_inset Text
4431
4432\layout Standard
4433
4434128
4435\end_inset
4436</cell>
4437<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
4438\begin_inset Text
4439
4440\layout Standard
4441
4442128
4443\end_inset
4444</cell>
4445</row>
4446<row topline="true" bottomline="true" newpage="false">
4447<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4448\begin_inset Text
4449
4450\layout Standard
4451
4452216.190.52.4
4453\end_inset
4454</cell>
4455<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4456\begin_inset Text
4457
4458\layout Standard
4459
4460/dir/somefile.jpg
4461\end_inset
4462</cell>
4463<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4464\begin_inset Text
4465
4466\layout Standard
4467
4468RC4-MD5
4469\end_inset
4470</cell>
4471<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="false" rotate="false" usebox="none" width="" special="">
4472\begin_inset Text
4473
4474\layout Standard
4475
4476128
4477\end_inset
4478</cell>
4479<cell multicolumn="0" alignment="center" valignment="top" topline="true" bottomline="false" leftline="true" rightline="true" rotate="false" usebox="none" width="" special="">
4480\begin_inset Text
4481
4482\layout Standard
4483
4484128
4485\end_inset
4486</cell>
4487</row>
4488</lyxtabular>
4489
4490\end_inset
2869 4491
2870| 216.190.52.4 | /dir/somefile.gif
2871 | RC4-MD5 | 128 | 128 |
2872\layout LyX-Code
2873 4492
2874| 216.190.52.4 | /dir/somefile.jpg
2875 | RC4-MD5 | 128 | 128 |
2876\layout LyX-Code 4493\layout LyX-Code
2877 4494
2878...etc...
2879\layout Subsection 4495\layout Subsection
2880 4496
2881Does mod_log_sql connect to MySQL via TCP/IP or a socket? 4497Does mod_log_sql connect to MySQL via TCP/IP or a socket?
2882\layout Standard 4498\layout Standard
2883 4499
2884It depends! Actually this isn't determined by mod_log_sql. 4500It depends! This actually is not determined by mod_log_sql.
2885 mod_log_sql relies on a connection command that is supplied in the MySQL 4501 mod_log_sql relies on a connection command that is supplied in the MySQL
2886 API, and that command is somewhat intelligent. 4502 API, and that command is somewhat intelligent.
2887 When mod_log_sql issues the connect command to MySQL, this intelligent 4503 When mod_log_sql issues the connect command to MySQL, this intelligent
diff --git a/Documentation/documentation.tex b/Documentation/documentation.tex
deleted file mode 100644
index 0eb6359..0000000
--- a/Documentation/documentation.tex
+++ /dev/null
@@ -1,1534 +0,0 @@
1%% LyX 1.1 created this file. For more info, see http://www.lyx.org/.
2%% Do not edit unless you really know what you are doing.
3\documentclass[10pt,english]{article}
4\usepackage[T1]{fontenc}
5\usepackage{geometry}
6\geometry{verbose,letterpaper,tmargin=0.5in,bmargin=1in,lmargin=1in,rmargin=1in}
7\usepackage{babel}
8\IfFileExists{url.sty}{\usepackage{url}}
9 {\newcommand{\url}{\texttt}}
10
11\makeatletter
12
13%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% LyX specific LaTeX commands.
14\providecommand{\LyX}{L\kern-.1667em\lower.25em\hbox{Y}\kern-.125emX\@}
15
16%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Textclass specific LaTeX commands.
17 \newenvironment{lyxcode}
18 {\begin{list}{}{
19 \setlength{\rightmargin}{\leftmargin}
20 \raggedright
21 \setlength{\itemsep}{0pt}
22 \setlength{\parsep}{0pt}
23 \normalfont\ttfamily}%
24 \item[]}
25 {\end{list}}
26
27\makeatother
28\begin{document}
29
30\vfill{}
31\title{Installing and Running mod\_log\_sql}
32\vfill{}
33
34
35\author{Christopher Powell }
36
37
38\date{11/13/02}
39
40\maketitle
41\tableofcontents{}
42\newpage
43
44
45\section{Introduction}
46
47
48\subsection{Homepage }
49
50\begin{lyxcode}
51http://www.grubbybaby.com/mod\_log\_sql/
52\end{lyxcode}
53
54\subsection{Summary}
55
56This Apache module will permit you to log Apache accesses (and a lot
57of related information) to a SQL database. Unlike logging to a flat
58text file (which is standard in Apache), there are a lot of powerful
59advantages to logging to SQL. This module can either replace or happily
60coexist with Apache's text file logging facility.
61
62
63\subsection{Approach}
64
65This project was formerly known as mod\_log\_mysql. It has been renamed
66to mod\_log\_sql in order to reflect the project goal of database-inspecificity.
67The module currently supports MySQL, and development for other database
68backends is underway.
69
70In order to save speed and overhead, links are kept alive in between
71queries. This module uses one SQL link per httpd child. Among other
72things, this means that this module supports logging into only one
73MySQL server, and for now, also, only one SQL database. But that's
74a small tradeoff compared to the blinding speed of this module.
75
76Virtual hosts are supported in the same manner they are in the regular
77logging modules. You define some basic 'global' directives in the
78main server config, then you define more specific 'local' directives
79inside each virtualhost stanza.
80
81SQL links are opened by each child process when it is born. Error
82reporting is robust throughout and will let you know about database
83issues in the standard Apache error-log for the server or virtual
84server.
85
86A robust \char`\"{}preserve\char`\"{} capability has now been implemented.
87This permits the module to preserve any failed INSERT commands to
88a local file on its machine. In any situation that the database is
89unavailable -- e.g. the network fails, you reboot the db host, etc.
90-- mod\_log\_sql will note this in the error log and begin appending
91its log entries to the preserve file (which is created with the user
92\& group ID of the running Apache process, e.g. \char`\"{}nobody\char`\"{}
93on many Linux installations). When your database becomes available
94again, mod\_log\_sql seamlessly resumes logging to it. When convenient
95for the sysadmin, he/she can easily import the preserve file into
96the database because it is simply a series of SQL insert statements:
97
98\begin{lyxcode}
99\#~mysql~-uadminuser~-p~mydbname~<~/tmp/mysql-preserve
100\end{lyxcode}
101
102\subsection{Supported directives}
103
104Please see the web-based documentation for full explanation of all
105supported run-time directives.
106
107http://www.grubbybaby.com/mod\_log\_sql/directives.html
108
109See the FAQ for some handy examples:
110
111http://www.grubbybaby.com/mod\_log\_sql/faq.html
112
113
114\subsection{What gets logged by default? }
115
116All the data that would be contained in the \char`\"{}Combined Log
117Format\char`\"{} is logged by default, plus a little extra. Your best
118bet is to begin by accepting this default, then later customize the
119log configuration based on your needs.
120
121The online documentation of the run-time directives includes a full
122explanation of what you can log, including examples.
123
124
125\subsection{Minor Notes}
126
127\begin{itemize}
128\item Note which directives go in the 'main server config' and which directives
129apply to the 'virtual host config'. This is made clear in the directive
130documentation.
131\item The 'time\_stamp' field is stored in an UNSIGNED INTEGER column, in
132the standard unix \char`\"{}seconds since 1/1/1970 12:00:00\char`\"{}
133format. This is superior to storing the access time as a string due
134to size requirements: an UNSIGNED INT type fits in 4 bytes, whereas
135the Apache date string (e.g. \char`\"{}18/Nov/2001:13:59:52 -0800\char`\"{})
136requires 26 bytes -- significantly larger, and those extra 22 bytes
137will add up over the thousands of accesses that a busy server will
138experience. Besides, an INT type is far more flexible for comparisons,
139etc.
140
141
142In MySQL 3.21 and above you can easily convert this to a human readable
143format using from\_unixtime(), e.g.:
144
145\begin{lyxcode}
146select~remote\_host,request\_uri,from\_unixtime(time\_stamp)~from~access\_log;
147\end{lyxcode}
148The enclosed perl program ''make\_combined\_log.pl'' shows how you
149can extract your access records in a format that is completely Combined
150Log Format compliant. You can then feed this to your favorite web
151log analysis tool.
152
153\item The table's string values can be CHAR or VARCHAR, at a length of your
154choice. VARCHAR is superior because it truncates long strings; CHAR
155types are fixed-length and will be padded with spaces. Just like the
156time\_stamp described above, that kind of space waste will add up
157over thousands of records.
158\item Be careful not to go overboard setting fields to NOT NULL. If a field
159is marked NOT NULL then it must contain data in the INSERT or the
160INSERT will fail, sometimes in a really mysterious way to the frustrated
161sysad.
162\item Apache normally logs numeric fields with a '-' character to mean \char`\"{}not
163applicable,\char`\"{} e.g. bytes\_sent on a request with a 304 response
164code. Since '-' is an illegal character in an SQL numeric field, such
165fields are assigned the value 0 instead of '-' which, of course, makes
166perfect sense anyway.
167\end{itemize}
168
169\subsection{Author / Maintainer}
170
171The actual logging code was taken from the already existing flat file
172text modules, so all that credit goes to the Apache Server group.
173
174The MySQL routines and directives were added by Zeev Suraski <bourbon@netvision.net.il>.
175
176All changes from 1.06+ and the new documentation were added by Chris
177Powell <chris@grubbybaby.com>. It seems that the module had fallen
178into the \char`\"{}unmaintained\char`\"{} category -- it hadn't been
179updated since 1998 -- so Chris adopted it as the new maintainer.
180
181
182\section{Installation}
183
184
185\subsection{Requirements}
186
187\begin{itemize}
188\item A compatible system. I have run mod\_log\_sql on Red Hat based systems
189(Red Hat, Mandrake). These instructions should easily adapt to any
190modern distro.
191\item Apache 1.2 or 1.3 installed. (I run 1.3.22 and it works fine). You
192should have already successfully compiled Apache and know what you're
193doing there.
194\item The MySQL development headers. This is called different things on
195different distros. For example, Red Hat 6.x called this RPM \char`\"{}MySQL-devel\char`\"{}
196whereas Mandrake calls it \char`\"{}libmysql10-devel\char`\"{}.
197\item MySQL >= 3.23.15 configured, installed and running on either localhost
198or an accessible networked machine. You should already have a basic
199understanding of MySQL and how it functions.
200\item Again, basic administrative skills with Apache and MySQL. I try to
201make things as easy as possible in this file, but its purpose is not
202to be an administrative tutorial.
203\item Additionally, if you want to be able to log SSL information such as
204keysize or cipher, you need OpenSSL and glibc-devel installed. Both
205are available as RPMs.
206\end{itemize}
207
208\subsection{Do I want a DSO?}
209
210You need to know the answer to this question before you proceed. The
211answer is pretty straightforward: what have you done in the past?
212If you like all your Apache modules to be dynamic, then you should
213keep doing that. If you're more of an old-school type and prefer to
214compile the modules right into apache, do that. Both methods work
215equally well.
216
217FWIW, the DSO method is more modern and increasing in popularity because
218apxs takes care of a lot of dirty little details for you. As you'll
219see below, the static-module method is a little more complex.
220
221
222\subsection{Installation as an Apache DSO (Preferred) }
223
224\begin{enumerate}
225\item Perform all the following steps as root so that you have install privs,
226etc. Unpack the archive into a working directory.
227
228\begin{lyxcode}
229\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql
230\end{lyxcode}
231\item Edit Makefile for your system.\\
232
233
234
235NECESSARY:
236
237\begin{itemize}
238\item The location where you installed Apache -- usually /usr/local/apache,
239'locate apxs' can help you find it.
240\item The location of your MySQL libraries, find using 'locate libmysqlclient'
241\item The location of your MySQL header files, find using 'locate mysql.h'
242\end{itemize}
243OPTIONAL if you have included mod\_ssl in Apache and want to log SSL
244data such as keysize and cipher type:
245
246\begin{itemize}
247\item The location of your SSL header files, find using 'locate mod\_ssl.h'
248\end{itemize}
249Now that you know these things, edit Makefile and replace the stock
250values with your own.
251
252IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS
253by putting a \# character in front of it:
254
255\begin{lyxcode}
256\#MODSSLHDRS=/usr/include/...
257\end{lyxcode}
258\item Instruct apxs to compile the module as a DSO.
259
260\begin{lyxcode}
261\#~make~dso
262\end{lyxcode}
263\item You should see output similar to the following:
264
265\begin{lyxcode}
266/usr/local/Apache/bin/apxs~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-c~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-L/usr/lib~-lmysqlclient~-lz~mod\_log\_sql.c~
267
268gcc~-DLINUX=22~-DNO\_DBM\_REWRITEMAP~-DMOD\_SSL=208111~-DUSE\_HSREGEX~-DEAPI~-DUSE\_EXPAT~-I../lib/expat-lite~-fpic~-DSHARED\_CORE~-DSHARED\_MODULE~-I/usr/local/Apache/include~-O2~-Wall~-DEAPI~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-c~mod\_log\_sql.c~
269
270gcc~-shared~-o~mod\_log\_sql.so~mod\_log\_sql.o~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-L/usr/lib~-lmysqlclient~-lz~-lm~-lcrypt~-ldb
271\end{lyxcode}
272You should see no errors and have a file called \char`\"{}mod\_log\_sql.so\char`\"{}
273in your directory.
274
275\item Instruct apxs to install the DSO.
276
277\begin{lyxcode}
278\#~make~dsoinstall
279\end{lyxcode}
280You should see output similar to the following:
281
282\begin{lyxcode}
283/usr/local/Apache/bin/apxs~-i~mod\_log\_sql.so
284
285cp~mod\_log\_sql.so~/usr/local/Apache/libexec/mod\_log\_sql.so
286
287chmod~755~/usr/local/Apache/libexec/mod\_log\_sql.so
288\end{lyxcode}
289\item Module ordering within httpd.conf is important. If you are logging
290SSL, you must make sure that
291
292\begin{lyxcode}
293LoadModule~ssl\_module~libexec/libssl.so
294\end{lyxcode}
295comes before
296
297\begin{lyxcode}
298LoadModule~sql\_log\_module~libexec/mod\_log\_sql.so
299\end{lyxcode}
300If you don't, you will get this error when you start Apache:
301
302\begin{lyxcode}
303/usr/local/apache/libexec/mod\_log\_mysql.so:~undefined~symbol:~ssl\_var\_lookup
304
305/usr/local/apache/bin/apachectl~startssl:~httpd~could~not~be~started
306\end{lyxcode}
307(Because mod\_log\_sql doesn't yet have the required symbols that
308mod\_ssl provides.)
309
310Now skip below to the \char`\"{}Configuration\char`\"{} section.
311
312\end{enumerate}
313
314\subsection{Installation as a static module compiled into httpd}
315
316\begin{enumerate}
317\item Perform all the following steps as root so that you have install privs,
318etc.
319\item Unpack the archive into a working directory.
320
321\begin{lyxcode}
322\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql
323\end{lyxcode}
324\item \label{step:editMF}Edit Makefile for your system.\\
325
326
327
328NECESSARY:
329
330\begin{itemize}
331\item The location where you installed Apache -- usually /usr/local/apache,
332'locate apxs' can help you find it.
333\item The location of your Apache {*}sources{*}, find using 'locate ABOUT\_APACHE'
334\item The location of your MySQL header files, find using 'locate mysql.h'
335\item The location of your MySQL libraries, find using 'locate libmysqlclient'
336\end{itemize}
337OPTIONAL if you have included mod\_ssl in Apache and want to log SSL
338data such as keysize and cipher type:
339
340\begin{itemize}
341\item The location of your mod\_ssl header files, find using 'locate mod\_ssl.h'
342\item The location of your OpenSSL header files, find using 'locate x509.h'
343\item The location of your db1 header files, find using 'locate mpool.h'
344\end{itemize}
345Now that you know these things, edit Makefile and replace the stock
346values with your own.
347
348IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS,
349OPNSSLHDRS and DB1HDRS by putting a \# character in front of each
350one, e.g. \#OPNSSLHDRS=/usr/include/...
351
352\item \# make static
353\item \# make statinstall
354\item Change to your Apache source dir.
355
356\begin{lyxcode}
357\#~cd~/usr/local/src/apache-1.3.22/src
358\end{lyxcode}
359\item Re-make your httpd binary as follows.
360
361\begin{enumerate}
362\item Edit Configuration.apaci as follows...
363
364\begin{itemize}
365\item Append the following string to the EXTRA\_LIBS= line. (\char`\"{}/usr/lib/mysql\char`\"{}
366is from step \ref{step:editMF}, where your MySQL libraries live):
367\end{itemize}
368\begin{lyxcode}
369-L/usr/lib/mysql~-lmysqlclient~-lm~-lz
370\end{lyxcode}
371\begin{itemize}
372\item Find the mod\_log\_config.o line, and add this line immediately after
373it:
374\end{itemize}
375\begin{lyxcode}
376AddModule~modules/sql/mod\_log\_sql.o
377\end{lyxcode}
378\item \# cp Configuration.apaci Configuration
379\item \# ./Configure
380\item \# make
381\item \# strip httpd
382\end{enumerate}
383\item Test your new apache binary:
384
385\begin{lyxcode}
386\#~./httpd~-l
387\end{lyxcode}
388\item You should see something like:
389
390\begin{lyxcode}
391Compiled-in~modules:~
392
393http\_core.c
394
395mod\_log\_sql.c~<-{}-~That's~the~line~you're~looking~for.
396
397mod\_env.c~
398
399mod\_log\_config.c~
400
401mod\_mime.c~
402
403mod\_negotiation.c~...
404
405etc...
406\end{lyxcode}
407\item Install your httpd binary. Copy it over your old httpd binary, wherever
408it lives. You can and should rename your old httpd first so that you
409can easily revert to that working version in case of bugs with the
410new version.
411
412\begin{lyxcode}
413\#~/etc/rc.d/init.d/httpd~stop~
414
415\#~mv~/usr/local/Apache/bin/httpd~\textasciitilde{}/httpd-save~
416
417\#~cp~-f~./httpd~/usr/local/Apache/bin/
418\end{lyxcode}
419\end{enumerate}
420
421\section{Configuration}
422
423
424\subsection{Preparing MySQL}
425
426You have to prepare the database to receive data from mod\_log\_sql,
427and set up run-time directives in httpd.conf to control how and what
428mod\_log\_sql logs.
429
430This section will discuss how to get started with a basic config.
431Full documentation of the run-time directives is available here: http://www.grubbybaby.com/mod\_log\_sql/directives.html
432
433\begin{enumerate}
434\item mod\_log\_sql can make its own tables on-the-fly, or you can pre-make
435the tables by hand. The advantage of letting the module make the tables
436is ease-of-use, but for raw performance you will want to pre-make
437the tables in order to save overhead. In this basic setup we'll let
438the module create tables for us.
439\item We still need to have a logging database created and ready, so run
440the MySQL command line client and create a database:
441
442\begin{lyxcode}
443\#~mysql~-uadmin~-pmypassword~mysql>~create~database~apachelogs;
444\end{lyxcode}
445\item If you want to hand-create the tables, run the enclosed 'create-tables'
446SQL script as follows:
447
448\begin{lyxcode}
449mysql>~source~create\_tables.sql
450\end{lyxcode}
451\item Create a specific MySQL userid that httpd will use to authenticate
452and enter data. This userid need not be an actual Unix user. It is
453a userid internal to MySQL with specific privileges. In the following
454example command, \char`\"{}apachelogs\char`\"{} is the database, \char`\"{}loguser\char`\"{}
455is the userid to create, \char`\"{}my.apachemachine.com\char`\"{}
456is the name of the Apache machine, and \char`\"{}l0gger\char`\"{}
457is the password to assign. Choose values that are different from these
458examples.
459
460\begin{lyxcode}
461mysql>~grant~insert,create~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger';
462\end{lyxcode}
463\item You may be especially security-paranoid and not want \char`\"{}loguser\char`\"{}
464to have \char`\"{}create\char`\"{} capability within the \char`\"{}apachelogs\char`\"{}
465databse. You can disable that but the cost is that you cannot use
466the module's automatic-table-creation feature. If that's an acceptable
467cost, hand-create the tables as described in step 1 and use the following
468GRANT statement instead of the one above:
469
470\begin{lyxcode}
471mysql>~grant~insert~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger';
472\end{lyxcode}
473\item Enable full logging of your MySQL daemon (at least temporarily for
474debugging purposes) if you don't do this already. Edit /etc/my.cnf
475and add the following line to your {[}mysqld{]} section:
476
477\begin{lyxcode}
478log=/var/log/mysql-messages
479\end{lyxcode}
480Then restart MySQL.
481
482\begin{lyxcode}
483\#~/etc/rc.d/init.d/mysql~restart
484\end{lyxcode}
485\end{enumerate}
486
487\subsection{Preparing Apache}
488
489\begin{enumerate}
490\item Tell the module what database to use and the appropriate authentication
491information.
492
493\begin{description}
494\item [EXAMPLE:]Use the MySQL database called \char`\"{}apachelogs\char`\"{}
495running on \char`\"{}dbmachine.foo.com\char`\"{}. The module uses
496username \char`\"{}loguser\char`\"{} and password \char`\"{}l0gger\char`\"{}
497to authenticate to the database. The log entries will be INSERTed
498into the table called \char`\"{}access\_log\char`\"{}.
499\end{description}
500So, edit httpd.conf and insert the following lines somewhere AFTER
501any LoadModule / AddModule statements. Make sure these statements
502are \char`\"{}global,\char`\"{} i.e. not inside any VirtualHost stanza.
503
504\begin{lyxcode}
505LogSQLDatabase~apachelogs
506
507LogSQLLoginInfo~dbmachine.foo.com~loguser~l0gger~
508
509LogSQLCreateTables~on
510\end{lyxcode}
511\item If your database resides on localhost instead of another host, specify
512the MySQL server's socket file as follows:
513
514\begin{lyxcode}
515LogSQLSocketFile~/your/path/to/mysql.sock
516\end{lyxcode}
517\item The actual logging is set up on a virtual-host-by-host basis. So,
518skip down to the virtual host you want to set up. Instruct this virtual
519host to log entries to the table ''access\_log'' by inserting a
520LogSQLTransferLogTable directive. (The LogSQLTransferLogTable directive
521is the minimum required to log -- other directives simply tune the
522module's behavior.)
523
524\begin{lyxcode}
525<VirtualHost~1.2.3.4>
526
527{[}snip{]}
528
529LogSQLTransferLogTable~access\_log
530
531{[}snip{]}
532
533</VirtualHost>
534\end{lyxcode}
535\item Restart apache.
536
537\begin{lyxcode}
538\#~/etc/rc.d/init.d/httpd~start
539\end{lyxcode}
540\end{enumerate}
541
542\subsection{Testing the module}
543
544\begin{enumerate}
545\item Load your web site in a browser to trigger some hits, then confirm
546that the entries are being successfully logged:
547
548\begin{lyxcode}
549\#~mysql~-hmysql.host.com~-umysqladmin~-p~-e~\char`\"{}select~{*}~from~access\_log\char`\"{}~apachelogs~
550
551Enter~password:
552
553(Several~lines~of~output~should~follow,~corresponding~to~your~hits~on~the~site.)
554\end{lyxcode}
555You now have basic functionality. Don't disable your regular Apache
556logs until you feel comfortable that the database is behaving as you'd
557like and that things are going well.
558
559\item If you do not see any entries in the access\_log, then something is
560preventing the inserts from happening. This problem could be caused
561by several things:
562
563\begin{itemize}
564\item Improper privileges set up in the MySQL database
565\item You aren't hitting a VirtualHost that has a LogSQLTransferLogTable
566entry
567\item You didn't specify the right host
568\end{itemize}
569If you have confirmed your LogSQL{*} directives and know them to be
570correct, you should examine the httpd server logs for mod\_log\_sql
571messages; the module will offer hints as to why it cannot connect,
572etc. Also examine the MySQL log that you established in step 3. Ensure
573that the INSERTs are not being rejected because of a malformed table
574entry or other clerical error. If you see no INSERT attempts in the
575log, the module isn't successfully connecting to the database.
576
577The next thing to do is recompile the module with debugging output
578activated. change the \char`\"{}\#undef DEBUG\char`\"{} on line 8
579of mod\_log\_sql.c to \char`\"{}\#define DEBUG\char`\"{} and recompile/reinstall.
580The module will now output copious notes about what it is doing, and
581this will help you (and the maintainer) solve the problem.
582
583\end{enumerate}
584You can now activate the advanced features of mod\_log\_sql. These
585are all described in the next section.
586
587
588\subsection{Configuration directive reference}
589
590
591\subsubsection{LogSQLLoginInfo }
592
593\begin{lyxcode}
594\textbf{MANDATORY}~
595
596Syntax:~LogSQLLoginInfo~host~user~password
597
598Example:~LogSQLLoginInfo~foobar.baz.com~logwriter~passw0rd~
599
600Context:~server~config
601\end{lyxcode}
602\begin{quote}
603Defines the general parameters of the MySQL host to which you will
604be logging. host is the hostname or IP address of the MySQL machine.
605user is the MySQL userid (not a Unix userid!) with INSERT privileges
606on the table defined in LogSQLTransferLogTable. password is that user's
607password. This is defined only once in the httpd.conf file.
608\end{quote}
609
610\subsubsection{LogSQLDatabase }
611
612\begin{lyxcode}
613\textbf{MANDATORY}
614
615Syntax:~LogSQLDatabase~database~
616
617Example:~LogSQLDatabase~loggingdb~
618
619Context:~server~config
620\end{lyxcode}
621\begin{quote}
622Defines the database that is used for logging. database must be operating
623on the MySQL host defined in LogSQLLoginInfo. This is defined only
624once in the httpd.conf file.
625\end{quote}
626
627\subsubsection{LogSQLTransferLogTable}
628
629\begin{lyxcode}
630\textbf{MANDATORY~}
631
632Syntax:~LogSQLTransferLogTable~table-name~
633
634Example:~LogSQLTransferLogTable~access\_log\_table~
635
636Context:~virtual~host
637\end{lyxcode}
638\begin{quote}
639Defines which table is used for logging of Apache's transfers; this
640is analogous to Apache's TransferLog directive. table-name must be
641a valid table within the database defined in LogSQLDatabase.
642
643This directive is not necessary if you declare LogSQLMassVirtualHosting
644On, since that directive activates dynamically-named tables. If you
645attempt to use LogSQLTransferLogTable at the same time a warning will
646be logged and it will be ignored, since LogSQLMassVirtualHosting takes
647priority.
648\end{quote}
649
650\subsubsection{LogSQLRequestIgnore}
651
652\begin{lyxcode}
653Syntax:~LogSQLRequestIgnore~req1~req2~req3~...~reqN~
654
655Example:~LogSQLRequestIgnore~root.exe~cmd.exe~default.ida~favicon.ico~
656
657Context:~virtual~host
658\end{lyxcode}
659\begin{quote}
660Lists a series of strings that, if present in the URI, will cause
661that request NOT to be logged. This directive is useful for cutting
662down on log clutter when you KNOW that you do not want to log requests
663for certain objects. (The example above is a genuinely useful one;
664it will prevent logging of many common Microsoft-based worm intrusion
665attempts, as well as those ridiculous requests for the favicon.)
666
667Each string is separated by a space, and NO regular expressions or
668globbing are allowed. Each string is evaluated as a substring of the
669URI using strstr().
670\end{quote}
671
672\subsubsection{LogSQLRemhostIgnore}
673
674\begin{lyxcode}
675Syntax:~LogSQLRemhostIgnore~host1~host2~host3~...~hostN~
676
677Example:~LogSQLRemhostIgnore~localnet.com~
678
679Context:~virtual~host
680\end{lyxcode}
681\begin{quote}
682Lists a series of strings that, if present in the REMOTE\_HOST, will
683cause that request NOT to be logged. This directive is useful for
684cutting down on log clutter when you KNOW that you do not want to
685log requests from certain hosts, such as your own internal network
686machines.
687
688Each string is separated by a space, and NO regular expressions or
689globbing are allowed. Each string is evaluated as a substring of the
690REMOTE\_HOST using strstr().
691\end{quote}
692
693\subsubsection{LogSQLRefererIgnore}
694
695\begin{lyxcode}
696Syntax:~LogSQLRefererIgnore~ref1~ref2~ref3~...~refN~
697
698Example:~LogSQLRefererIgnore~google.com~
699
700Context:~virtual~host
701\end{lyxcode}
702\begin{quote}
703Lists a series of strings that, if present in the HTTP\_REFERER, will
704cause that request NOT to be logged. This directive is useful for
705cutting down on log clutter when you know that you do not want to
706log requests from certain referers.
707
708Each string is separated by a space, and NO regular expressions or
709globbing are allowed. Each string is evaluated as a substring of the
710HTTP\_REFERER using strstr().
711\end{quote}
712
713\subsubsection{LogSQLWhichCookie}
714
715\begin{lyxcode}
716Syntax:~LogSQLWhichCookie~cookiename~
717
718Example:~LogSQLWhichCookie~Clicks
719
720Default:~None.~You~must~set~this~if~you~wish~to~capture~cookies.
721
722Context:~virtual~host
723\end{lyxcode}
724\begin{quote}
725In HTTP, cookies have names to distinguish them from each other. Using
726mod\_usertrack, for example, you can give your user-tracking cookies
727a name with the CookieName directive.
728
729If you have activated cookie logging in LogSQLTransferLogFormat, then
730LogSQLWhichCookie tells mod\_log\_sql which cookie to log. This is
731useful because many times you will be setting and receiving more than
732one cookie from a client; without this directive you'd be unable to
733choose which cookie is your mod\_usertrack cookie.
734
735Note: although this was intended for people who are using mod\_usertrack
736to set user-tracking cookies, you aren't restricted in any way. You
737can choose which cookie you wish to log to the database, and it doesn't
738necessarily have to have anything to do with mod\_usertrack.
739\end{quote}
740
741\subsubsection{LogSQLCreateTables}
742
743\begin{lyxcode}
744Syntax:~LogSQLCreateTables~flag
745
746Example:~LogSQLCreateTables~On~
747
748Default:~Off~
749
750Context:~server~config
751\end{lyxcode}
752\begin{quote}
753mod\_log\_sql now has the ability to create its tables on-the-fly.
754The advantage to this is convenience: you don't have to execute any
755SQL by hand to prepare the table. This is especially helpful for people
756with lots of virtual hosts (who should also see the LogSQLMassVirtualHosting
757directive).
758
759There is a slight disadvantage: if you wish to activate this feature,
760then the user specified by LogSQLLoginInfo must have CREATE privileges
761on the database. In an absolutely paranoid, locked-down situation
762you may only want to grant your mod\_log\_sql user INSERT privileges
763on the database; in that situation you are unable to take advantage
764of LogSQLCreateTables. But most people -- even the very security-conscious
765-- will find that granting CREATE on the logging database is reasonable.
766
767This is defined only once in the httpd.conf file.
768\end{quote}
769
770\subsubsection{LogSQLMassVirtualHosting}
771
772\begin{lyxcode}
773Syntax:~LogSQLMassVirtualHosting~flag~
774
775Example:~LogSQLMassVirtualHosting~On~
776
777Default:~Off~
778
779Context:~server~config
780\end{lyxcode}
781\begin{quote}
782If you administer a site hosting many, many virtual hosts then this
783option will appeal to you. If you activate LogSQLMassVirtualHosting
784then several things happen:
785\begin{itemize}
786\item the on-the-fly table creation feature is activated automatically
787\item the transfer log table name is dynamically set from the virtual host's
788name (example: a virtual host www.grubbybaby.com gets logged to table
789access\_www\_grubbybaby\_com)
790\item which, in turn, means that each virtual host logs to its own segregated
791table. Because there is no data shared between virtual servers you
792can grant your users access to the tables they need; they will be
793unable to view others' data.
794\end{itemize}
795This is a huge boost in convenience for sites with many virtual servers.
796Activating LogSQLMassVirtualHosting obviates the need to create every
797virtual server's table and provides more granular security possibilities.
798
799This is defined only once in the httpd.conf file.
800\end{quote}
801
802\subsubsection{LogSQLPreserveFile}
803
804\begin{lyxcode}
805Syntax:~LogSQLPreserveFile~filename~
806
807Example:~LogSQLPreserveFile~offline-preserve~
808
809Default:~mysql-preserve~(in~/tmp)~
810
811Context:~virtual~host
812\end{lyxcode}
813\begin{quote}
814mod\_log\_sql writes queries to this local preserve file in the event
815that it cannot reach the database, and thus ensures that your high-availability
816web frontend does not lose logs during a temporary database outage.
817This could happen for a number of reasons: the database goes offline,
818the network breaks, etc. You will not lose entries since the module
819has this backup. The file consists of a series of SQL statements that
820can be imported into your database at your convenience; furthermore,
821because the SQL queries contain the access timestamps you do not need
822to worry about out-of-order data after the import.
823
824For security purposes the name you supply will be prepended with \char`\"{}/tmp/\char`\"{}
825to force creation of the file in /tmp.
826
827If you do not define LogSQLPreserveFile then all virtual servers will
828log to the same default preserve file (/tmp/mysql-preserve). You can
829redefine this on a virtual-host basis in order to segregate your preserve
830files if you desire. Note that segregation is not really necessary,
831as the SQL statements that are written to the preserve file already
832distinguish between different virtual hosts.
833
834The module will log to error-log if/when it notices a database outage,
835and upon database return. You will therefore know when the preserve
836file is being used, although it is your responsibility to import the
837file.
838
839The file does not need to be created in advance. It is safe to remove
840or rename the file without interrupting Apache, as the module closes
841the filehandle immediately after completing the write. The file is
842created with the user \& group ID of the running Apache process (e.g.
843'nobody' on many Linux distributions).
844\end{quote}
845
846\subsubsection{LogSQLSocketFile }
847
848\begin{lyxcode}
849Syntax:~LogSQLSocketFile~filename~
850
851Example:~LogSQLSocketFile~/tmp/mysql.sock~
852
853Default:~/var/lib/mysql/mysql.sock~
854
855Context:~server~config
856\end{lyxcode}
857\begin{quote}
858At Apache runtime you can specify the MySQL socket file to use. Set
859this once in your main server config to override the default value.
860This value is irrelevant if your database resides on a separate machine.
861
862mod\_log\_sql will automatically employ the socket for db communications
863if the database resides on the local host. If the db resides on a
864separate host the module will automatically use TCP/IP. This is a
865function of the MySQL API and is not user-configurable.
866\end{quote}
867
868\subsubsection{LogSQLTransferLogFormat }
869
870\begin{lyxcode}
871Syntax:~LogSQLTransferLogFormat~format-string~
872
873Example:~LogSQLTransferLogFormat~huSUTv~
874
875Default:~AbHhmRSsTUuv~
876
877Context:~virtual~host
878\end{lyxcode}
879\begin{quote}
880Each character in the format-string defines an attribute of the request
881that you wish to log. The default logs the information required to
882create Combined Log Format logs, plus several extras. Here is the
883full list of allowable keys, which sometimes resemble their Apache
884counterparts, but do not always:
885
886\begin{tabular}{|c|l|l|l|l|}
887\hline
888\textbf{Character}&
889\textbf{What is this?}&
890\textbf{MySQL DB field}&
891\textbf{Recommended column type}&
892\textbf{Example}\\
893\hline
894\hline
895A&
896User agent&
897agent&
898varchar(255)&
899Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0)\\
900\hline
901b&
902Bytes transfered&
903bytes\_sent&
904int unsigned&
90532561\\
906\hline
907c&
908Text of cookie&
909cookie&
910varchar(255)&
911Apache=sdyn.fooonline.net.130051007102700823\\
912\hline
913f&
914&
915request\_file&
916varchar(255)&
917\\
918\hline
919H&
920HTTP request protocol&
921request\_protocol&
922varchar(10)&
923HTTP/1.1\\
924\hline
925h&
926Name of remote host&
927remote\_host&
928varchar(50)&
929blah.foobar.com\\
930\hline
931I&
932Ident user info&
933remote\_logname&
934varchar(50)&
935bobby\\
936\hline
937m&
938HTTP request method&
939request\_method&
940varchar(6)&
941GET\\
942\hline
943P&
944httpd child PID&
945child\_pid&
946smallint unsigned&
9473215\\
948\hline
949p&
950httpd port&
951server\_port&
952smallint unsigned&
95380\\
954\hline
955R&
956Referer&
957referer&
958varchar(255)&
959http://www.biglinkstoyou.com/linkpage.html\\
960\hline
961r&
962Request in full form&
963request\_line&
964varchar(255)&
965GET /books-cycroad.html HTTP/1.1\\
966\hline
967S&
968Time of request in UNIX format&
969time\_stamp&
970int unsigned&
9711005598029\\
972\hline
973s&
974HTTP status of request&
975status&
976smallint unsigned&
977404\\
978\hline
979T&
980Seconds to service request&
981request\_duration&
982smallint unsigned&
9832\\
984\hline
985t&
986Time of request in human format&
987request\_time&
988char(28)&
989{[}02/Dec/2001:15:01:26 -0800{]}\\
990\hline
991U&
992Request in simple form&
993request\_uri&
994varchar(255)&
995/books-cycroad.html\\
996\hline
997u&
998User info from HTTP authentication&
999remote\_user&
1000varchar(50)&
1001bobby\\
1002\hline
1003v&
1004Virtual host servicing the request&
1005virtual\_host&
1006varchar(50)&
1007www.foobar.com\\
1008\hline
1009\end{tabular}
1010
1011If you have compiled mod\_log\_sql with WANT\_SSL\_LOGGING, you also
1012get these:
1013
1014\begin{tabular}{|c|l|l|l|}
1015\hline
1016\textbf{Character}&
1017\textbf{MySQL DB field it activates}&
1018\textbf{MySQL DB field it activatesRecommended column type}&
1019\textbf{Example}\\
1020\hline
1021\hline
1022z&
1023ssl\_cipher&
1024varchar(25)&
1025RC4-MD5\\
1026\hline
1027q&
1028ssl\_keysize&
1029smallint unsigned&
103056\\
1031\hline
1032Q&
1033ssl\_maxkeysize&
1034smallint unsigned&
1035128\\
1036\hline
1037\end{tabular}
1038\end{quote}
1039
1040\section{FAQ}
1041
1042
1043\subsection{Why log to an SQL database?}
1044
1045To begin with, let's get it out of the way: logging to a database
1046is not a panacea. But while there are complexities with this solution,
1047the benefit can be substantial for certain classes of administrator
1048or people with advanced requirements:
1049
1050\begin{itemize}
1051\item Chores like log rotation go away, as you can DELETE records from the
1052SQL database once they are no longer useful. For example, the excellent
1053and popular log-analysis tool Webalizer (http://www.webalizer.com/)
1054does not need historic logs after it has processed them, enabling
1055you to delete older logs.
1056\item People with clusters of web servers (for high availability) will benefit
1057the most -- all their webservers can log to a single SQL database.
1058This obviates the need to collate/interleave the many separate logfiles,
1059which can be / highly/ problematic.
1060\item People acquainted with the power of SQL SELECT statements will know
1061the flexibility of the extraction possibilities at their fingertips.
1062\end{itemize}
1063For example, do you want to see all your 404's? Do this:
1064
1065\begin{lyxcode}
1066select~remote\_host,status,request\_uri,bytes\_sent,from\_unixtime(time\_stamp)~from~acc\_log\_tbl~where~status=404~order~by~time\_stamp;
1067
1068+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-
1069
1070+~~~~~~~~~~~~~~~~~~~~~~~remote\_host~|~status~|~request\_uri~~~~~~~~~~~~~~|~bytes\_sent~|~from\_unixtime(time\_stamp)|
1071
1072+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-
1073
1074|~marge.mmm.co.uk~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~02:30:56~~~~~~|
1075
1076|~62.180.239.251~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~02:45:25~~~~~~|
1077
1078|~212.234.12.66~~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~03:01:00~~~~~~|~
1079
1080|~212.210.78.254~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~03:26:05~~~~~~|~
1081
1082+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1083\end{lyxcode}
1084Or do you want to see how many bytes you've sent within a certain
1085directory or site? Do this:
1086
1087\begin{lyxcode}
1088select~request\_uri,sum(bytes\_sent)~as~bytes,count(request\_uri)~as~howmany~from~acc\_log\_tbl~where~request\_uri~like~'\%mod\_log\_sql\%'~group~by~request\_uri~order~by~howmany~desc;~
1089
1090+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~
1091
1092|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~|~bytes~~~|~howmany~|~
1093
1094+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~
1095
1096|~/mod\_log\_sql/style\_1.css~~~~~~~~~~|~157396~~|~~~~1288~|~
1097
1098|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~|~2514337~|~~~~~801~|~
1099
1100|~/mod\_log\_sql/mod\_log\_sql.tar.gz~~~|~9769312~|~~~~~456~|~
1101
1102|~/mod\_log\_sql/faq.html~~~~~~~~~~~~~|~5038728~|~~~~~436~|~
1103
1104|~/mod\_log\_sql/INSTALL~~~~~~~~~~~~~~|~1196161~|~~~~~202~|~
1105
1106|~/mod\_log\_sql/directives.html~~~~~~|~1096821~|~~~~~171~|~
1107
1108|~/mod\_log\_sql/CHANGELOG~~~~~~~~~~~~|~424481~~|~~~~~107~|~
1109
1110|~/mod\_log\_sql/README~~~~~~~~~~~~~~~|~796072~~|~~~~~~97~|~
1111
1112|~/mod\_log\_sql/directives-old.html~~|~42480~~~|~~~~~~~6~|
1113
1114|~/mod\_log\_sql/index.html~~~~~~~~~~~|~9531~~~~|~~~~~~~3~|~
1115
1116+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+
1117\end{lyxcode}
1118Or maybe you want to see who's linking to you? Do this:
1119
1120\begin{lyxcode}
1121select~count(referer)~as~num,referer~from~acc\_log\_tbl~where~request\_uri='/mod\_log\_sql/'~group~by~referer~order~by~num~desc;
1122
1123+-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1124
1125|~num~|~referer~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1126
1127+-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1128
1129|~271~|~http://freshmeat.net/projects/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1130
1131|~96~~|~http://modules.apache.org/search?id=339~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1132
1133|~48~~|~http://freshmeat.net/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1134
1135|~8~~~|~http://freshmeat.net~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
1136
1137|~7~~~|~http://freshmeat.net/daily/2001/11/30/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
1138
1139|~6~~~|~http://freshmeat.net/daily/2001/11/20/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
1140
1141+-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1142\end{lyxcode}
1143As you can see, there are myriad possibilities that can be constructed
1144with the wonderful SQL SELECT statement. Logging to an SQL database
1145can be really quite useful!
1146
1147
1148\subsection{Why use MySQL? Are there alternatives?}
1149
1150MySQL is a robust, free, and very powerful production-quality database
1151engine. It is well supported and comes with detailed documentation.
1152Many 3rd-party software pacakges (e.g. Slashcode, the engine that
1153powers Slashdot) run exclusively with MySQL. In other words, you will
1154belong to a very robust and well-supported community by choosing MySQL.
1155
1156That being said, there are alternatives. PostgreSQL is probably MySQL's
1157leading \char`\"{}competitor\char`\"{} in the free database world.
1158There is also an excellent module available for Apache to permit logging
1159to a PostgreSQL database, called pgLOGd \url{(http://www.digitalstratum.com/pglogd/)}.
1160
1161
1162\subsection{Is this code production-ready?}
1163
1164By all accounts it is. It is known to work without a problem on many-thousands-of-hits-per-day
1165webservers. Does that mean it is 100\% bug free? Well, no software
1166is. But it is well-tested and /believed/ to be fully compatible with
1167production environments. (The usual disclaimers apply. This software
1168is provided without warranty of any kind.)
1169
1170
1171\subsection{How well does it perform?}
1172
1173mod\_log\_sql scales to very high loads. Apache 1.3.22 + mod\_log\_sql
1174was benchmarked using the \char`\"{}ab\char`\"{} (Apache Bench) program
1175that comes with the Apache distribution; here are the results.
1176
1177Overall configuration:
1178
1179\begin{itemize}
1180\item Machine A: Apache webserver
1181\item Machine B: MySQL server
1182\item Machines A and B connected with 100Mbps Ethernet
1183\end{itemize}
1184Webserver configuration:
1185
1186\begin{itemize}
1187\item Celeron 400 128 MB RAM IDE storage
1188\end{itemize}
1189Apache configuration:
1190
1191\begin{lyxcode}
1192Timeout~300~
1193
1194KeepAlive~On~
1195
1196MaxKeepAliveRequests~100~
1197
1198KeepAliveTimeout~15~
1199
1200MinSpareServers~5~
1201
1202StartServers~10~
1203
1204MaxSpareServers~15~
1205
1206MaxClients~256~
1207
1208MaxRequestsPerChild~5000~
1209
1210LogSQLTransferLogFormat~AbHhmRSsTUuvc~
1211
1212LogSQLWhichCookie~Clicks~
1213
1214CookieTracking~on~
1215
1216CookieName~Clicks
1217\end{lyxcode}
1218\char`\"{}ab\char`\"{} commandline:
1219
1220\begin{lyxcode}
1221./ab~-c~10~-t~20~-v~2~-C~Clicks=ab\_run~http://www.hostname.com/target~
1222\end{lyxcode}
1223( 10 concurrent requests; 20 second test; setting a cookie \char`\"{}Clicks=ab\_run\char`\"{};
1224target = the mod\_log\_sql homepage. )
1225
1226Ten total ab runs were conducted: five with MySQL logging enabled,
1227and five with all MySQL directives commented out of httpd.conf. Then
1228each five were averaged. The results:
1229
1230\begin{itemize}
1231\item Average of five runs employing MySQL \emph{and} standard text logging:
1232139.01 requests per second, zero errors.
1233\item Average of five runs employing \emph{only} standard text logging:
1234139.96 requests per second, zero errors.
1235\end{itemize}
1236In other words, any rate-limiting effects on this particular hardware
1237setup are not caused by MySQL. Note that although this very simple
1238webserver setup is hardly cutting-edge -- it is, after all, a fairly
1239small machine -- 139 requests per second equal over twelve million
1240hits per day\emph{.}
1241
1242If you run this benchmark yourself, take note of three things:
1243
1244\begin{enumerate}
1245\item Use a target URL that is on your own webserver :-).
1246\item Wait until all your connections are closed out between runs; after
1247several thousand requests your TCP/IP stack will be filled with hundreds
1248of connections in TIME\_WAIT that need to close. Do a \char`\"{}netstat
1249-t|wc -l\char`\"{} on the webserver to see. If you don't wait, you
1250can expect to see a lot of messages like \char`\"{}ip\_conntrack:
1251table full, dropping packet\char`\"{} in your logs. (This has nothing
1252to do with mod\_log\_sql, this is simply the nature of the TCP/IP
1253stack in the Linux kernel.)
1254\item When done with your runs, clean these many thousands of requests out
1255of your database:
1256\end{enumerate}
1257\begin{lyxcode}
1258mysql>~delete~from~access\_log~where~agent~like~'ApacheBench\%';~mysql>~optimize~table~access\_log;~
1259\end{lyxcode}
1260
1261\subsection{Who's using mod\_log\_sql?}
1262
1263Good question! It would be great to find out! If you are a production-level
1264mod\_log\_sql user, please contact the maintainer, Chris Powell \url{chris@grubbybaby.com}so
1265that you can be mentioned here.
1266
1267
1268\subsection{How do I extract the data in a format that my analysis tool can understand?}
1269
1270mod\_log\_sql would be virtually useless if there weren't a way for
1271you to extract the data from your database in a somewhat meaningful
1272fashion. To that end there's a Perl script enclosed with the distribution.
1273That script (make\_combined\_log.pl) is designed to extract N-many
1274days worth of access logs and provide them in a Combined Log Format
1275output. You can use this very tool right in /etc/crontab to extract
1276logs on a regular basis so that your favorite web analysis tool can
1277read them. Or you can examine the Perl code to construct your own
1278custom tool.
1279
1280For example, let's say that you want your web statistics updated once
1281per day in the wee hours of the morning. A good way to accomplish
1282that would be the following entries in /etc/crontab:
1283
1284\begin{lyxcode}
1285\#~Generate~the~temporary~apache~logs~from~the~MySQL~database~(for~webalizer)~
1286
128705~04~{*}~{*}~{*}~root~/usr/local/sbin/make\_combined\_log.pl~1~www.grubbybaby.com~>~/var/log/httpd/mysql-grubbybaby
1288
1289\#~Run~webalizer~on~httpd~log~
1290
129130~04~{*}~{*}~{*}~root~/usr/local/bin/webalizer~-c~/etc/webalizer.conf;~rm~-f~/var/log/httpd/mysql-grubbybaby
1292\end{lyxcode}
1293Or if you have a newer system that puts files in /etc/cron.daily etc.,
1294create a file called ''webalizer'' in the cron.\_\_\_\_ subdir of
1295your choice. Use the following as the contents of your file, and make
1296sure to chmod 755 it when done.
1297
1298\begin{lyxcode}
1299\#!/bin/sh
1300
1301/usr/local/sbin/make\_combined\_log.pl~1~www.yourdomain.com~>~/var/log/httpd/templog
1302
1303/usr/local/bin/webalizer~-q~-c~/etc/webalizer.conf~
1304
1305rm~-f~/var/log/httpd/templog
1306\end{lyxcode}
1307See? Easy.
1308
1309
1310\subsection{Why doesn't the module also replace the Apache ErrorLog?}
1311
1312There are circumstances when that would be quite unwise -- for example,
1313if Apache could not reach the MySQL server for some reason and needed
1314to log that fact. Without a text-based error log you'd never know
1315anything was wrong, because Apache would be trying to log a database
1316connection error to the database... you get the point.
1317
1318Error logs are usually not very high-traffic and are really best left
1319as text files on a web server machine.
1320
1321
1322\subsection{\label{sec:cookie}How can I log mod\_usertrack cookies?}
1323
1324A number of people like to log mod\_usertrack cookies in their Apache
1325TransferLog to aid in understanding their visitors' clickstreams.
1326This is accomplished, for example, with a statement as follows:
1327
1328\begin{lyxcode}
1329LogFormat~\char`\"{}\%h~\%l~\%u~\%t~\textbackslash{}\char`\"{}\%r\textbackslash{}\char`\"{}~\%s~\%b~\textbackslash{}\char`\"{}\%\{Referer\}i\textbackslash{}\char`\"{}~\textbackslash{}\char`\"{}\%\{User-Agent\}i\textbackslash{}\char`\"{}\char`\"{}~\textbackslash{}\char`\"{}\%\{cookie\}n\textbackslash{}\char`\"{}\char`\"{}
1330\end{lyxcode}
1331Naturally it would be nice for mod\_log\_sql to permit the admin to
1332log the cookie data as well, so as of version 1.10 you can do this.
1333You need to have already compiled mod\_usertrack into httpd -- it's
1334one of the standard Apache modules.
1335
1336First make sure you have a column called \char`\"{}cookie\char`\"{}
1337in the MySQL database to hold the cookies, which can be done as follows
1338if you already have a working database:
1339
1340\begin{lyxcode}
1341alter~table~acc\_log\_tbl~add~column~cookie~varchar(255);
1342\end{lyxcode}
1343Next configure your server to set usertracking cookies as follows,
1344and make sure you include the new 'c' directive in your LogSQLTransferLogFormat,
1345which activates cookie logging. Here's an example:
1346
1347\begin{lyxcode}
1348<VirtualHost~1.2.3.4>~
1349
1350~CookieTracking~on~
1351
1352~CookieStyle~Cookie~
1353
1354~CookieName~Foobar~
1355
1356~LogSQLTransferLogFormat~huSUsbTvRAc~
1357
1358~LogSQLWhichCookie~Foobar~
1359
1360</VirtualHost>
1361\end{lyxcode}
1362The first three lines configure mod\_usertrack to create a COOKIE
1363(RFC 2109) format cookie called Foobar. The last two lines tell mod\_log\_sql
1364to log cookies named Foobar. You have to choose which cookie to log
1365because more than one cookie can/will be sent to the server by the
1366client.
1367
1368FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that
1369even newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2
1370(RFC 2965) format. Just stick with the standard COOKIE format and
1371you'll be fine.
1372
1373Perform some hits on your server and run a select:
1374
1375\begin{lyxcode}
1376mysql>~select~request\_uri,cookie~from~access\_log~where~cookie~is~not~null;
1377
1378+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
1379
1380|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~cookie~|~
1381
1382+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~
1383
1384|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~
1385
1386|~/mod\_log\_sql/usa.gif~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~
1387
1388|~/mod\_log\_sql/style\_1.css~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~
1389
1390...etc...
1391\end{lyxcode}
1392
1393\subsection{What if I want to log more than one cookie?}
1394
1395No problem. As of version 1.17, you have a choice. If you are just
1396interested in a single cookie, follow the instructions in section
1397\ref{sec:cookie} above. That cookie will be logged to a column in
1398the regular access\_log table.
1399
1400However, if you need to log multiple cookies, you'll employ the LogSQLWhichCookies
1401(note the plural) directive. The cookies you specify will be logged
1402to a separate table, and entries in that table will be linked to the
1403regular access\_log entries via the unique ID that is supplied by
1404mod\_unique\_id. Without mod\_unique\_id the information will still
1405be logged but you will be unable to correlate which cookies go with
1406which access-requests.
1407
1408LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict,
1409but you're better off choosing the one you need.
1410
1411
1412\subsection{What are the SSL logging features, and how do I activate them?}
1413
1414If you run an SSL-enabled server you may benefit from logging some
1415SSL details. mod\_log\_sql now supports this ability. By adding certain
1416characters to your LogSQLTransferLogFormat string you can tell mod\_log\_sql
1417to log the SSL cipher, the SSL keysize of the connection, and the
1418Max-keysize that was available. This would let you tell, for example,
1419which clients were using only export-grade security to access your
1420secure software area.
1421
1422You can compile mod\_log\_sql with SSL logging support if you have
1423the right packages installed. If you already have an SSL-enabled Apache
1424then you by definition have the correct packages already installed:
1425OpenSSL and mod\_ssl.
1426
1427You need to ensure that your database is set up to log the SSL data.
1428Issue the following commands to MySQL once you have your basic access\_log
1429table built:
1430
1431\begin{lyxcode}
1432alter~table~access\_log~add~column~ssl\_cipher~varchar(25);
1433
1434alter~table~access\_log~add~column~ssl\_keysize~smallint~unsigned;
1435
1436alter~table~access\_log~add~column~ssl\_maxkeysize~smallint~unsigned;
1437\end{lyxcode}
1438Finally configure httpd.conf to activate the SSL fields. Note that
1439this is only meaningful in a VirtualHost that is set up for SSL.
1440
1441\begin{lyxcode}
1442<VirtualHost~1.2.3.4:443>~
1443
1444~LogSQLTransferLogFormat~AbHhmRSsTUuvcQqz~
1445
1446</VirtualHost>
1447\end{lyxcode}
1448The last three characters (Qqz) in the directive are the SSL ones;
1449see the directives documentation for details.
1450
1451Perform some hits on your server and run a select:
1452
1453\begin{lyxcode}
1454mysql>~select~remote\_host,request\_uri,ssl\_cipher,ssl\_keysize,ssl\_maxkeysize~from~access\_log~where~ssl\_cipher~is~not~null;
1455
1456+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~
1457
1458|~remote\_host~~~~~~~~~~~~~~|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ssl\_cipher~|~ssl\_keysize~|~ssl\_maxkeysize~|
1459
1460+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~
1461
1462|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.html~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~
1463
1464|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.gif~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~
1465
1466|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.jpg~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~
1467
1468...etc...
1469\end{lyxcode}
1470
1471\subsection{Does mod\_log\_sql connect to MySQL via TCP/IP or a socket?}
1472
1473It depends! Actually this isn't determined by mod\_log\_sql. mod\_log\_sql
1474relies on a connection command that is supplied in the MySQL API,
1475and that command is somewhat intelligent. When mod\_log\_sql issues
1476the connect command to MySQL, this intelligent connect command uses
1477sockets to communicate with MySQL if the specified MySQL database
1478is on the same machine (because sockets are more efficient than TCP/IP).
1479However, if the specified MySQL db is on a different machine, mod\_log\_sql
1480connects using TCP/IP. You don't have any control of which methodology
1481is used.
1482
1483You do have control over where mod\_log\_sql looks for the socket.
1484The LogSQLSocketFile runtime configuration directive overrides the
1485default of \char`\"{}/var/lib/mysql/mysql.sock\char`\"{} to whatever
1486you wish. (Applies to mod\_log\_sql 1.16 or later only.)
1487
1488
1489\subsection{Why do I occasionally see a \char`\"{}connection lost, attempting
1490reconnect\char`\"{} message in my error-log?}
1491
1492This message may appear every now and then in your Apache error log,
1493especially on very lightly loaded servers. This doesn't mean that
1494anything is necessarily wrong. Within each httpd child process, mod\_log\_sql
1495will open (and keep open) a connection to the MySQL server. MySQL,
1496however, will close connections that haven't been used in a while;
1497the default timeout is 8 hours. When this occurs, mod\_log\_sql will
1498notice and re-open the connection. That event is what is being logged,
1499and looks like this:
1500
1501\begin{lyxcode}
1502{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~connection~lost,~attempting~reconnect
1503
1504{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~reconnect~successful
1505\end{lyxcode}
1506Reference: MySQL documentation \url{http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away}
1507
1508
1509\subsection{Does mod\_log\_sql work with Apache 2.x?}
1510
1511As of this writing, no. The Apache Group significantly altered the
1512module API with the release of Apache 2.0. All modules written for
15131.3, including mod\_log\_sql, will not work with 2.0.
1514
1515mod\_log\_sql will eventually be ported to Apache 2.x, but not immediately.
1516It is going to take some time, and there are other features that have
1517higher priority. Please sign up for the announcements list (on the
1518main website) or monitor the website for updates to learn when the
1519port (and other releases) are available.
1520
1521<OPINION>If you're a {*}NIX user, stick with Apache 1.3.x for now.
1522Major modules like mod\_ssl and PHP are not even ready for 2.0 yet,
1523and the main benefits in 2.0 are for Win32 users anyway. Apache 1.3.x
1524is rock-stable and performs equally well on {*}NIX as 2.0.</OPINION>
1525
1526
1527\subsection{I have discovered a bug. Who can I contact?}
1528
1529Please contact the maintainer \url{chris@grubbybaby.com}! Your comments,
1530suggestions, bugfixes, bug catches, and usage testimonials are always
1531welcome. As free software, mod\_log\_sql is intended to be a community
1532effort -- any code contributions or other ideas will be fully and
1533openly credited, of course.
1534\end{document}
generated by cgit (git 2.34.1) at 2024-11-22 10:32:34 +0000