From da2aa667865606ade6a47d060ab12c851231933c Mon Sep 17 00:00:00 2001 From: Christopher Powell Date: Thu, 14 Nov 2002 03:51:37 +0000 Subject: Monster changes en route to 1.17. Esp. with new documentation. --- Documentation/documentation.lyx | 2969 +++++++++++++++++++++++++++++++++++++++ Documentation/documentation.tex | 1534 ++++++++++++++++++++ 2 files changed, 4503 insertions(+) create mode 100644 Documentation/documentation.lyx create mode 100644 Documentation/documentation.tex (limited to 'Documentation') diff --git a/Documentation/documentation.lyx b/Documentation/documentation.lyx new file mode 100644 index 0000000..48c3968 --- /dev/null +++ b/Documentation/documentation.lyx @@ -0,0 +1,2969 @@ +#LyX 1.1 created this file. For more info see http://www.lyx.org/ +\lyxformat 218 +\textclass article +\language english +\inputencoding default +\fontscheme default +\graphics default +\paperfontsize 10 +\spacing single +\papersize letterpaper +\paperpackage a4 +\use_geometry 1 +\use_amsmath 0 +\paperorientation portrait +\paperwidth 8.5in +\paperheight 11in +\leftmargin 1in +\topmargin 0.5in +\rightmargin 1in +\bottommargin 1in +\secnumdepth 3 +\tocdepth 3 +\paragraph_separation indent +\defskip medskip +\quotes_language swedish +\quotes_times 2 +\papercolumns 1 +\papersides 1 +\paperpagestyle default + +\layout Title +\added_space_top vfill \added_space_bottom vfill +Installing and Running mod_log_sql +\layout Author + +Christopher Powell +\layout Date + +11/13/02 +\layout Standard +\pagebreak_bottom + +\begin_inset LatexCommand \tableofcontents{} + +\end_inset + + +\layout Section + +Introduction +\layout Subsection + +Homepage +\layout LyX-Code + +http://www.grubbybaby.com/mod_log_sql/ +\layout Subsection + +Summary +\layout Standard + +This Apache module will permit you to log Apache accesses (and a lot of + related information) to a SQL database. + Unlike logging to a flat text file (which is standard in Apache), there + are a lot of powerful advantages to logging to SQL. + This module can either replace or happily coexist with Apache's text file + logging facility. +\layout Subsection + +Approach +\layout Standard + +This project was formerly known as mod_log_mysql. + It has been renamed to mod_log_sql in order to reflect the project goal + of database-inspecificity. + The module currently supports MySQL, and development for other database + backends is underway. +\layout Standard + +In order to save speed and overhead, links are kept alive in between queries. + This module uses one SQL link per httpd child. + Among other things, this means that this module supports logging into only + one MySQL server, and for now, also, only one SQL database. + But that's a small tradeoff compared to the blinding speed of this module. +\layout Standard + +Virtual hosts are supported in the same manner they are in the regular logging + modules. + You define some basic 'global' directives in the main server config, then + you define more specific 'local' directives inside each virtualhost stanza. +\layout Standard + +SQL links are opened by each child process when it is born. + Error reporting is robust throughout and will let you know about database + issues in the standard Apache error-log for the server or virtual server. +\layout Standard + +A robust "preserve" capability has now been implemented. + This permits the module to preserve any failed INSERT commands to a local + file on its machine. + In any situation that the database is unavailable -- e.g. + the network fails, you reboot the db host, etc. + -- mod_log_sql will note this in the error log and begin appending its + log entries to the preserve file (which is created with the user & group + ID of the running Apache process, e.g. + "nobody" on many Linux installations). + When your database becomes available again, mod_log_sql seamlessly resumes + logging to it. + When convenient for the sysadmin, he/she can easily import the preserve + file into the database because it is simply a series of SQL insert statements: +\layout LyX-Code + +# mysql -uadminuser -p mydbname < /tmp/mysql-preserve +\layout Subsection + +Supported directives +\layout Standard + +Please see the web-based documentation for full explanation of all supported + run-time directives. +\layout Standard + +http://www.grubbybaby.com/mod_log_sql/directives.html +\layout Standard + +See the FAQ for some handy examples: +\layout Standard + +http://www.grubbybaby.com/mod_log_sql/faq.html +\layout Subsection + +What gets logged by default? +\layout Standard + +All the data that would be contained in the "Combined Log Format" is logged + by default, plus a little extra. + Your best bet is to begin by accepting this default, then later customize + the log configuration based on your needs. +\layout Standard + +The online documentation of the run-time directives includes a full explanation + of what you can log, including examples. +\layout Subsection + +Minor Notes +\layout Itemize + +Note which directives go in the 'main server config' and which directives + apply to the 'virtual host config'. + This is made clear in the directive documentation. +\layout Itemize + +The 'time_stamp' field is stored in an UNSIGNED INTEGER column, in the standard + unix "seconds since 1/1/1970 12:00:00" format. + This is superior to storing the access time as a string due to size requirement +s: an UNSIGNED INT type fits in 4 bytes, whereas the Apache date string + (e.g. + "18/Nov/2001:13:59:52 -0800") requires 26 bytes -- significantly larger, + and those extra 22 bytes will add up over the thousands of accesses that + a busy server will experience. + Besides, an INT type is far more flexible for comparisons, etc. +\begin_deeper +\layout Standard + +In MySQL 3.21 and above you can easily convert this to a human readable format + using from_unixtime(), e.g.: +\layout LyX-Code + +select remote_host,request_uri,from_unixtime(time_stamp) from access_log; +\layout Standard + +The enclosed perl program +\begin_inset Quotes sld +\end_inset + +make_combined_log.pl +\begin_inset Quotes srd +\end_inset + + shows how you can extract your access records in a format that is completely + Combined Log Format compliant. + You can then feed this to your favorite web log analysis tool. +\end_deeper +\layout Itemize + +The table's string values can be CHAR or VARCHAR, at a length of your choice. + VARCHAR is superior because it truncates long strings; CHAR types are fixed-len +gth and will be padded with spaces. + Just like the time_stamp described above, that kind of space waste will + add up over thousands of records. +\layout Itemize + +Be careful not to go overboard setting fields to NOT NULL. + If a field is marked NOT NULL then it must contain data in the INSERT or + the INSERT will fail, sometimes in a really mysterious way to the frustrated + sysad. +\layout Itemize + +Apache normally logs numeric fields with a '-' character to mean "not applicable +," e.g. + bytes_sent on a request with a 304 response code. + Since '-' is an illegal character in an SQL numeric field, such fields + are assigned the value 0 instead of '-' which, of course, makes perfect + sense anyway. +\layout Subsection + +Author / Maintainer +\layout Standard + +The actual logging code was taken from the already existing flat file text + modules, so all that credit goes to the Apache Server group. +\layout Standard + +The MySQL routines and directives were added by Zeev Suraski . +\layout Standard + +All changes from 1.06+ and the new documentation were added by Chris Powell + . + It seems that the module had fallen into the "unmaintained" category -- + it hadn't been updated since 1998 -- so Chris adopted it as the new maintainer. +\layout Section + +Installation +\layout Subsection + +Requirements +\layout Itemize + +A compatible system. + I have run mod_log_sql on Red Hat based systems (Red Hat, Mandrake). + These instructions should easily adapt to any modern distro. +\layout Itemize + +Apache 1.2 or 1.3 installed. + (I run 1.3.22 and it works fine). + You should have already successfully compiled Apache and know what you're + doing there. +\layout Itemize + +The MySQL development headers. + This is called different things on different distros. + For example, Red Hat 6.x called this RPM "MySQL-devel" whereas Mandrake + calls it "libmysql10-devel". +\layout Itemize + +MySQL >= 3.23.15 configured, installed and running on either localhost or + an accessible networked machine. + You should already have a basic understanding of MySQL and how it functions. +\layout Itemize + +Again, basic administrative skills with Apache and MySQL. + I try to make things as easy as possible in this file, but its purpose + is not to be an administrative tutorial. +\layout Itemize + +Additionally, if you want to be able to log SSL information such as keysize + or cipher, you need OpenSSL and glibc-devel installed. + Both are available as RPMs. +\layout Subsection + +Do I want a DSO? +\layout Standard + +You need to know the answer to this question before you proceed. + The answer is pretty straightforward: what have you done in the past? If + you like all your Apache modules to be dynamic, then you should keep doing + that. + If you're more of an old-school type and prefer to compile the modules + right into apache, do that. + Both methods work equally well. +\layout Standard + +FWIW, the DSO method is more modern and increasing in popularity because + apxs takes care of a lot of dirty little details for you. + As you'll see below, the static-module method is a little more complex. +\layout Subsection + +Installation as an Apache DSO (Preferred) +\layout Enumerate + +Perform all the following steps as root so that you have install privs, + etc. + Unpack the archive into a working directory. +\begin_deeper +\layout LyX-Code + +# tar zxf mod_log_sql.tar.gz -C /usr/local/src # cd /usr/local/src/mod_log_sql +\end_deeper +\layout Enumerate + +Edit Makefile for your system. +\newline + +\begin_deeper +\layout Standard + +NECESSARY: +\layout Itemize + +The location where you installed Apache -- usually /usr/local/apache, 'locate + apxs' can help you find it. +\layout Itemize + +The location of your MySQL libraries, find using 'locate libmysqlclient' +\layout Itemize + +The location of your MySQL header files, find using 'locate mysql.h' +\layout Standard + +OPTIONAL if you have included mod_ssl in Apache and want to log SSL data + such as keysize and cipher type: +\layout Itemize + +The location of your SSL header files, find using 'locate mod_ssl.h' +\layout Standard + +Now that you know these things, edit Makefile and replace the stock values + with your own. +\layout Standard + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting + a # character in front of it: +\layout LyX-Code + +#MODSSLHDRS=/usr/include/... +\end_deeper +\layout Enumerate + +Instruct apxs to compile the module as a DSO. +\begin_deeper +\layout LyX-Code + +# make dso +\end_deeper +\layout Enumerate + +You should see output similar to the following: +\begin_deeper +\layout LyX-Code + +/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 +\layout LyX-Code + +gcc -DLINUX=22 -DNO_DBM_REWRITEMAP -DMOD_SSL=208111 -DUSE_HSREGEX -DEAPI + -DUSE_EXPAT -I../lib/expat-lite -fpic -DSHARED_CORE -DSHARED_MODULE -I/usr/local/ +Apache/include -O2 -Wall -DEAPI -I/usr/include/mysql -I/usr/local/src/apache_1.3.2 +7-dso/src/modules/ssl -c mod_log_sql.c +\layout LyX-Code + +gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -L/usr/li +b -lmysqlclient -lz -lm -lcrypt -ldb +\layout Standard + +You should see no errors and have a file called "mod_log_sql.so" in your + directory. +\end_deeper +\layout Enumerate + +Instruct apxs to install the DSO. +\begin_deeper +\layout LyX-Code + +# make dsoinstall +\layout Standard + +You should see output similar to the following: +\layout LyX-Code + +/usr/local/Apache/bin/apxs -i mod_log_sql.so +\layout LyX-Code + +cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so +\layout LyX-Code + +chmod 755 /usr/local/Apache/libexec/mod_log_sql.so +\end_deeper +\layout Enumerate + +Module ordering within httpd.conf is important. + If you are logging SSL, you must make sure that +\begin_deeper +\layout LyX-Code + +LoadModule ssl_module libexec/libssl.so +\layout Standard + +comes before +\layout LyX-Code + +LoadModule sql_log_module libexec/mod_log_sql.so +\layout Standard + +If you don't, you will get this error when you start Apache: +\layout LyX-Code + +/usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup +\layout LyX-Code + +/usr/local/apache/bin/apachectl startssl: httpd could not be started +\layout Standard + +(Because mod_log_sql doesn't yet have the required symbols that mod_ssl + provides.) +\layout Standard + +Now skip below to the "Configuration" section. +\end_deeper +\layout Subsection + +Installation as a static module compiled into httpd +\layout Enumerate + +Perform all the following steps as root so that you have install privs, + etc. +\layout Enumerate + +Unpack the archive into a working directory. +\begin_deeper +\layout LyX-Code + +# tar zxf mod_log_sql.tar.gz -C /usr/local/src # cd /usr/local/src/mod_log_sql +\end_deeper +\layout Enumerate + + +\begin_inset LatexCommand \label{step:editMF} + +\end_inset + +Edit Makefile for your system. +\newline + +\begin_deeper +\layout Standard + +NECESSARY: +\layout Itemize + +The location where you installed Apache -- usually /usr/local/apache, 'locate + apxs' can help you find it. + +\layout Itemize + +The location of your Apache *sources*, find using 'locate ABOUT_APACHE' + +\layout Itemize + +The location of your MySQL header files, find using 'locate mysql.h' +\layout Itemize + +The location of your MySQL libraries, find using 'locate libmysqlclient' +\layout Standard + +OPTIONAL if you have included mod_ssl in Apache and want to log SSL data + such as keysize and cipher type: +\layout Itemize + +The location of your mod_ssl header files, find using 'locate mod_ssl.h' +\layout Itemize + +The location of your OpenSSL header files, find using 'locate x509.h' +\layout Itemize + +The location of your db1 header files, find using 'locate mpool.h' +\layout Standard + +Now that you know these things, edit Makefile and replace the stock values + with your own. +\layout Standard + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, OPNSSLHDRS + and DB1HDRS by putting a # character in front of each one, e.g. + #OPNSSLHDRS=/usr/include/... +\end_deeper +\layout Enumerate + +# make static +\layout Enumerate + +# make statinstall +\layout Enumerate + +Change to your Apache source dir. +\begin_deeper +\layout LyX-Code + +# cd /usr/local/src/apache-1.3.22/src +\end_deeper +\layout Enumerate + +Re-make your httpd binary as follows. +\begin_deeper +\layout Enumerate + +Edit Configuration.apaci as follows... +\begin_deeper +\layout Itemize + +Append the following string to the EXTRA_LIBS= line. + ("/usr/lib/mysql" is from step +\begin_inset LatexCommand \ref{step:editMF} + +\end_inset + +, where your MySQL libraries live): +\layout LyX-Code + +-L/usr/lib/mysql -lmysqlclient -lm -lz +\layout Itemize + +Find the mod_log_config.o line, and add this line immediately after it: +\layout LyX-Code + +AddModule modules/sql/mod_log_sql.o +\end_deeper +\layout Enumerate + +# cp Configuration.apaci Configuration +\layout Enumerate + +# ./Configure +\layout Enumerate + +# make +\layout Enumerate + +# strip httpd +\end_deeper +\layout Enumerate + +Test your new apache binary: +\begin_deeper +\layout LyX-Code + +# ./httpd -l +\end_deeper +\layout Enumerate + +You should see something like: +\begin_deeper +\layout LyX-Code + +Compiled-in modules: +\layout LyX-Code + +http_core.c +\layout LyX-Code + +mod_log_sql.c <-- That's the line you're looking for. +\layout LyX-Code + +mod_env.c +\layout LyX-Code + +mod_log_config.c +\layout LyX-Code + +mod_mime.c +\layout LyX-Code + +mod_negotiation.c ... +\layout LyX-Code + +etc... +\end_deeper +\layout Enumerate + +Install your httpd binary. + Copy it over your old httpd binary, wherever it lives. + You can and should rename your old httpd first so that you can easily revert + to that working version in case of bugs with the new version. +\begin_deeper +\layout LyX-Code + +# /etc/rc.d/init.d/httpd stop +\layout LyX-Code + +# mv /usr/local/Apache/bin/httpd ~/httpd-save +\layout LyX-Code + +# cp -f ./httpd /usr/local/Apache/bin/ +\end_deeper +\layout Section + +Configuration +\layout Subsection + +Preparing MySQL +\layout Standard + +You have to prepare the database to receive data from mod_log_sql, and set + up run-time directives in httpd.conf to control how and what mod_log_sql + logs. +\layout Standard + +This section will discuss how to get started with a basic config. + Full documentation of the run-time directives is available here: http://www.grub +bybaby.com/mod_log_sql/directives.html +\layout Enumerate + +mod_log_sql can make its own tables on-the-fly, or you can pre-make the + tables by hand. + The advantage of letting the module make the tables is ease-of-use, but + for raw performance you will want to pre-make the tables in order to save + overhead. + In this basic setup we'll let the module create tables for us. +\layout Enumerate + +We still need to have a logging database created and ready, so run the MySQL + command line client and create a database: +\begin_deeper +\layout LyX-Code + +# mysql -uadmin -pmypassword mysql> create database apachelogs; +\end_deeper +\layout Enumerate + +If you want to hand-create the tables, run the enclosed 'create-tables' + SQL script as follows: +\begin_deeper +\layout LyX-Code + +mysql> source create_tables.sql +\end_deeper +\layout Enumerate + +Create a specific MySQL userid that httpd will use to authenticate and enter + data. + This userid need not be an actual Unix user. + It is a userid internal to MySQL with specific privileges. + In the following example command, "apachelogs" is the database, "loguser" + is the userid to create, "my.apachemachine.com" is the name of the Apache + machine, and "l0gger" is the password to assign. + Choose values that are different from these examples. +\begin_deeper +\layout LyX-Code + +mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com + identified by 'l0gger'; +\end_deeper +\layout Enumerate + +You may be especially security-paranoid and not want "loguser" to have "create" + capability within the "apachelogs" databse. + You can disable that but the cost is that you cannot use the module's automatic +-table-creation feature. + If that's an acceptable cost, hand-create the tables as described in step + 1 and use the following GRANT statement instead of the one above: +\begin_deeper +\layout LyX-Code + +mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com identified + by 'l0gger'; +\end_deeper +\layout Enumerate + +Enable full logging of your MySQL daemon (at least temporarily for debugging + purposes) if you don't do this already. + Edit /etc/my.cnf and add the following line to your [mysqld] section: +\begin_deeper +\layout LyX-Code + +log=/var/log/mysql-messages +\layout Standard + +Then restart MySQL. +\layout LyX-Code + +# /etc/rc.d/init.d/mysql restart +\end_deeper +\layout Subsection + +Preparing Apache +\layout Enumerate + +Tell the module what database to use and the appropriate authentication + information. +\begin_deeper +\layout Description + +EXAMPLE: Use the MySQL database called "apachelogs" running on "dbmachine.foo.com". + The module uses username "loguser" and password "l0gger" to authenticate + to the database. + The log entries will be INSERTed into the table called "access_log". +\layout Standard + +So, edit httpd.conf and insert the following lines somewhere AFTER any LoadModule + / AddModule statements. + Make sure these statements are "global," i.e. + not inside any VirtualHost stanza. +\layout LyX-Code + +LogSQLDatabase apachelogs +\layout LyX-Code + +LogSQLLoginInfo dbmachine.foo.com loguser l0gger +\layout LyX-Code + +LogSQLCreateTables on +\end_deeper +\layout Enumerate + +If your database resides on localhost instead of another host, specify the + MySQL server's socket file as follows: +\begin_deeper +\layout LyX-Code + +LogSQLSocketFile /your/path/to/mysql.sock +\end_deeper +\layout Enumerate + +The actual logging is set up on a virtual-host-by-host basis. + So, skip down to the virtual host you want to set up. + Instruct this virtual host to log entries to the table +\begin_inset Quotes sld +\end_inset + +access_log +\begin_inset Quotes srd +\end_inset + + by inserting a LogSQLTransferLogTable directive. + (The LogSQLTransferLogTable directive is the minimum required to log -- + other directives simply tune the module's behavior.) +\begin_deeper +\layout LyX-Code + + +\layout LyX-Code + +[snip] +\layout LyX-Code + +LogSQLTransferLogTable access_log +\layout LyX-Code + +[snip] +\layout LyX-Code + + +\end_deeper +\layout Enumerate + +Restart apache. +\begin_deeper +\layout LyX-Code + +# /etc/rc.d/init.d/httpd start +\end_deeper +\layout Subsection + +Testing the module +\layout Enumerate + +Load your web site in a browser to trigger some hits, then confirm that + the entries are being successfully logged: +\begin_deeper +\layout LyX-Code + +# mysql -hmysql.host.com -umysqladmin -p -e "select * from access_log" apachelogs + +\layout LyX-Code + +Enter password: +\layout LyX-Code + +(Several lines of output should follow, corresponding to your hits on the + site.) +\layout Standard + +You now have basic functionality. + Don't disable your regular Apache logs until you feel comfortable that + the database is behaving as you'd like and that things are going well. +\end_deeper +\layout Enumerate + +If you do not see any entries in the access_log, then something is preventing + the inserts from happening. + This problem could be caused by several things: +\begin_deeper +\layout Itemize + +Improper privileges set up in the MySQL database +\layout Itemize + +You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry + +\layout Itemize + +You didn't specify the right host +\layout Standard + +If you have confirmed your LogSQL* directives and know them to be correct, + you should examine the httpd server logs for mod_log_sql messages; the + module will offer hints as to why it cannot connect, etc. + Also examine the MySQL log that you established in step 3. + Ensure that the INSERTs are not being rejected because of a malformed table + entry or other clerical error. + If you see no INSERT attempts in the log, the module isn't successfully + connecting to the database. +\layout Standard + +The next thing to do is recompile the module with debugging output activated. + change the "#undef DEBUG" on line 8 of mod_log_sql.c to "#define DEBUG" + and recompile/reinstall. + The module will now output copious notes about what it is doing, and this + will help you (and the maintainer) solve the problem. +\end_deeper +\layout Standard + +You can now activate the advanced features of mod_log_sql. + These are all described in the next section. +\layout Subsection + +Configuration directive reference +\layout Subsubsection + +LogSQLLoginInfo +\layout LyX-Code + + +\series bold +MANDATORY +\series default + +\layout LyX-Code + +Syntax: LogSQLLoginInfo host user password +\layout LyX-Code + +Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd +\layout LyX-Code + +Context: server config +\layout Quote + +Defines the general parameters of the MySQL host to which you will be logging. + host is the hostname or IP address of the MySQL machine. + user is the MySQL userid (not a Unix userid!) with INSERT privileges on + the table defined in LogSQLTransferLogTable. + password is that user's password. + This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLDatabase +\layout LyX-Code + + +\series bold +MANDATORY +\layout LyX-Code + +Syntax: LogSQLDatabase database +\layout LyX-Code + +Example: LogSQLDatabase loggingdb +\layout LyX-Code + +Context: server config +\layout Quote + +Defines the database that is used for logging. + database must be operating on the MySQL host defined in LogSQLLoginInfo. + This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLTransferLogTable +\layout LyX-Code + + +\series bold +MANDATORY +\layout LyX-Code + +Syntax: LogSQLTransferLogTable table-name +\layout LyX-Code + +Example: LogSQLTransferLogTable access_log_table +\layout LyX-Code + +Context: virtual host +\layout Quote + +Defines which table is used for logging of Apache's transfers; this is analogous + to Apache's TransferLog directive. + table-name must be a valid table within the database defined in LogSQLDatabase. +\layout Quote + +This directive is not necessary if you declare LogSQLMassVirtualHosting + On, since that directive activates dynamically-named tables. + If you attempt to use LogSQLTransferLogTable at the same time a warning + will be logged and it will be ignored, since LogSQLMassVirtualHosting takes + priority. +\layout Subsubsection + +LogSQLRequestIgnore +\layout LyX-Code + +Syntax: LogSQLRequestIgnore req1 req2 req3 ... + reqN +\layout LyX-Code + +Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico +\layout LyX-Code + +Context: virtual host +\layout Quote + +Lists a series of strings that, if present in the URI, will cause that request + NOT to be logged. + This directive is useful for cutting down on log clutter when you KNOW + that you do not want to log requests for certain objects. + (The example above is a genuinely useful one; it will prevent logging of + many common Microsoft-based worm intrusion attempts, as well as those ridiculou +s requests for the favicon.) +\layout Quote + +Each string is separated by a space, and NO regular expressions or globbing + are allowed. + Each string is evaluated as a substring of the URI using strstr(). +\layout Subsubsection + +LogSQLRemhostIgnore +\layout LyX-Code + +Syntax: LogSQLRemhostIgnore host1 host2 host3 ... + hostN +\layout LyX-Code + +Example: LogSQLRemhostIgnore localnet.com +\layout LyX-Code + +Context: virtual host +\layout Quote + +Lists a series of strings that, if present in the REMOTE_HOST, will cause + that request NOT to be logged. + This directive is useful for cutting down on log clutter when you KNOW + that you do not want to log requests from certain hosts, such as your own + internal network machines. +\layout Quote + +Each string is separated by a space, and NO regular expressions or globbing + are allowed. + Each string is evaluated as a substring of the REMOTE_HOST using strstr(). +\layout Subsubsection + +LogSQLRefererIgnore +\layout LyX-Code + +Syntax: LogSQLRefererIgnore ref1 ref2 ref3 ... + refN +\layout LyX-Code + +Example: LogSQLRefererIgnore google.com +\layout LyX-Code + +Context: virtual host +\layout Quote + +Lists a series of strings that, if present in the HTTP_REFERER, will cause + that request NOT to be logged. + This directive is useful for cutting down on log clutter when you know + that you do not want to log requests from certain referers. +\layout Quote + +Each string is separated by a space, and NO regular expressions or globbing + are allowed. + Each string is evaluated as a substring of the HTTP_REFERER using strstr(). +\layout Subsubsection + +LogSQLWhichCookie +\layout LyX-Code + +Syntax: LogSQLWhichCookie cookiename +\layout LyX-Code + +Example: LogSQLWhichCookie Clicks +\layout LyX-Code + +Default: None. + You must set this if you wish to capture cookies. +\layout LyX-Code + +Context: virtual host +\layout Quote + +In HTTP, cookies have names to distinguish them from each other. + Using mod_usertrack, for example, you can give your user-tracking cookies + a name with the CookieName directive. +\layout Quote + +If you have activated cookie logging in LogSQLTransferLogFormat, then LogSQLWhic +hCookie tells mod_log_sql which cookie to log. + This is useful because many times you will be setting and receiving more + than one cookie from a client; without this directive you'd be unable to + choose which cookie is your mod_usertrack cookie. +\layout Quote + +Note: although this was intended for people who are using mod_usertrack + to set user-tracking cookies, you aren't restricted in any way. + You can choose which cookie you wish to log to the database, and it doesn't + necessarily have to have anything to do with mod_usertrack. +\layout Subsubsection + +LogSQLCreateTables +\layout LyX-Code + +Syntax: LogSQLCreateTables flag +\layout LyX-Code + +Example: LogSQLCreateTables On +\layout LyX-Code + +Default: Off +\layout LyX-Code + +Context: server config +\layout Quote + +mod_log_sql now has the ability to create its tables on-the-fly. + The advantage to this is convenience: you don't have to execute any SQL + by hand to prepare the table. + This is especially helpful for people with lots of virtual hosts (who should + also see the LogSQLMassVirtualHosting directive). +\layout Quote + +There is a slight disadvantage: if you wish to activate this feature, then + the user specified by LogSQLLoginInfo must have CREATE privileges on the + database. + In an absolutely paranoid, locked-down situation you may only want to grant + your mod_log_sql user INSERT privileges on the database; in that situation + you are unable to take advantage of LogSQLCreateTables. + But most people -- even the very security-conscious -- will find that granting + CREATE on the logging database is reasonable. +\layout Quote + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLMassVirtualHosting +\layout LyX-Code + +Syntax: LogSQLMassVirtualHosting flag +\layout LyX-Code + +Example: LogSQLMassVirtualHosting On +\layout LyX-Code + +Default: Off +\layout LyX-Code + +Context: server config +\layout Quote + +If you administer a site hosting many, many virtual hosts then this option + will appeal to you. + If you activate LogSQLMassVirtualHosting then several things happen: +\begin_deeper +\layout Itemize + +the on-the-fly table creation feature is activated automatically +\layout Itemize + +the transfer log table name is dynamically set from the virtual host's name + (example: a virtual host www.grubbybaby.com gets logged to table access_www_grubb +ybaby_com) +\layout Itemize + +which, in turn, means that each virtual host logs to its own segregated + table. + Because there is no data shared between virtual servers you can grant your + users access to the tables they need; they will be unable to view others' + data. +\end_deeper +\layout Quote + +This is a huge boost in convenience for sites with many virtual servers. + Activating LogSQLMassVirtualHosting obviates the need to create every virtual + server's table and provides more granular security possibilities. +\layout Quote + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLPreserveFile +\layout LyX-Code + +Syntax: LogSQLPreserveFile filename +\layout LyX-Code + +Example: LogSQLPreserveFile offline-preserve +\layout LyX-Code + +Default: mysql-preserve (in /tmp) +\layout LyX-Code + +Context: virtual host +\layout Quote + +mod_log_sql writes queries to this local preserve file in the event that + it cannot reach the database, and thus ensures that your high-availability + web frontend does not lose logs during a temporary database outage. + This could happen for a number of reasons: the database goes offline, the + network breaks, etc. + You will not lose entries since the module has this backup. + The file consists of a series of SQL statements that can be imported into + your database at your convenience; furthermore, because the SQL queries + contain the access timestamps you do not need to worry about out-of-order + data after the import. +\layout Quote + +For security purposes the name you supply will be prepended with "/tmp/" + to force creation of the file in /tmp. +\layout Quote + +If you do not define LogSQLPreserveFile then all virtual servers will log + to the same default preserve file (/tmp/mysql-preserve). + You can redefine this on a virtual-host basis in order to segregate your + preserve files if you desire. + Note that segregation is not really necessary, as the SQL statements that + are written to the preserve file already distinguish between different + virtual hosts. +\layout Quote + +The module will log to error-log if/when it notices a database outage, and + upon database return. + You will therefore know when the preserve file is being used, although + it is your responsibility to import the file. +\layout Quote + +The file does not need to be created in advance. + It is safe to remove or rename the file without interrupting Apache, as + the module closes the filehandle immediately after completing the write. + The file is created with the user & group ID of the running Apache process + (e.g. + 'nobody' on many Linux distributions). +\layout Subsubsection + +LogSQLSocketFile +\layout LyX-Code + +Syntax: LogSQLSocketFile filename +\layout LyX-Code + +Example: LogSQLSocketFile /tmp/mysql.sock +\layout LyX-Code + +Default: /var/lib/mysql/mysql.sock +\layout LyX-Code + +Context: server config +\layout Quote + +At Apache runtime you can specify the MySQL socket file to use. + Set this once in your main server config to override the default value. + This value is irrelevant if your database resides on a separate machine. +\layout Quote + +mod_log_sql will automatically employ the socket for db communications if + the database resides on the local host. + If the db resides on a separate host the module will automatically use + TCP/IP. + This is a function of the MySQL API and is not user-configurable. +\layout Subsubsection + +LogSQLTransferLogFormat +\layout LyX-Code + +Syntax: LogSQLTransferLogFormat format-string +\layout LyX-Code + +Example: LogSQLTransferLogFormat huSUTv +\layout LyX-Code + +Default: AbHhmRSsTUuv +\layout LyX-Code + +Context: virtual host +\layout Quote + +Each character in the format-string defines an attribute of the request + that you wish to log. + The default logs the information required to create Combined Log Format + logs, plus several extras. + Here is the full list of allowable keys, which sometimes resemble their + Apache counterparts, but do not always: +\layout Quote + + +\begin_inset Tabular + + + + + + + + + +\begin_inset Text + +\layout Standard + + +\series bold +Character +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +What is this? +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +MySQL DB field +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +Recommended column type +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +Example +\end_inset + + + + +\begin_inset Text + +\layout Standard + +A +\end_inset + + +\begin_inset Text + +\layout Standard + +User agent +\end_inset + + +\begin_inset Text + +\layout Standard + +agent +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0) +\end_inset + + + + +\begin_inset Text + +\layout Standard + +b +\end_inset + + +\begin_inset Text + +\layout Standard + +Bytes transfered +\end_inset + + +\begin_inset Text + +\layout Standard + +bytes_sent +\end_inset + + +\begin_inset Text + +\layout Standard + +int unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +32561 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +c +\end_inset + + +\begin_inset Text + +\layout Standard + +Text of cookie +\end_inset + + +\begin_inset Text + +\layout Standard + +cookie +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +Apache=sdyn.fooonline.net.130051007102700823 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +f +\end_inset + + +\begin_inset Text + +\layout Standard + +\end_inset + + +\begin_inset Text + +\layout Standard + +request_file +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +\end_inset + + + + +\begin_inset Text + +\layout Standard + +H +\end_inset + + +\begin_inset Text + +\layout Standard + +HTTP request protocol +\end_inset + + +\begin_inset Text + +\layout Standard + +request_protocol +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(10) +\end_inset + + +\begin_inset Text + +\layout Standard + +HTTP/1.1 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +h +\end_inset + + +\begin_inset Text + +\layout Standard + +Name of remote host +\end_inset + + +\begin_inset Text + +\layout Standard + +remote_host +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + +blah.foobar.com +\end_inset + + + + +\begin_inset Text + +\layout Standard + +I +\end_inset + + +\begin_inset Text + +\layout Standard + +Ident user info +\end_inset + + +\begin_inset Text + +\layout Standard + +remote_logname +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + +bobby +\end_inset + + + + +\begin_inset Text + +\layout Standard + +m +\end_inset + + +\begin_inset Text + +\layout Standard + +HTTP request method +\end_inset + + +\begin_inset Text + +\layout Standard + +request_method +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(6) +\end_inset + + +\begin_inset Text + +\layout Standard + +GET +\end_inset + + + + +\begin_inset Text + +\layout Standard + +P +\end_inset + + +\begin_inset Text + +\layout Standard + +httpd child PID +\end_inset + + +\begin_inset Text + +\layout Standard + +child_pid +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +3215 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +p +\end_inset + + +\begin_inset Text + +\layout Standard + +httpd port +\end_inset + + +\begin_inset Text + +\layout Standard + +server_port +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +80 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +R +\end_inset + + +\begin_inset Text + +\layout Standard + +Referer +\end_inset + + +\begin_inset Text + +\layout Standard + +referer +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +http://www.biglinkstoyou.com/linkpage.html +\end_inset + + + + +\begin_inset Text + +\layout Standard + +r +\end_inset + + +\begin_inset Text + +\layout Standard + +Request in full form +\end_inset + + +\begin_inset Text + +\layout Standard + +request_line +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +GET /books-cycroad.html HTTP/1.1 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +S +\end_inset + + +\begin_inset Text + +\layout Standard + +Time of request in UNIX format +\end_inset + + +\begin_inset Text + +\layout Standard + +time_stamp +\end_inset + + +\begin_inset Text + +\layout Standard + +int unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +1005598029 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +s +\end_inset + + +\begin_inset Text + +\layout Standard + +HTTP status of request +\end_inset + + +\begin_inset Text + +\layout Standard + +status +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +404 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +T +\end_inset + + +\begin_inset Text + +\layout Standard + +Seconds to service request +\end_inset + + +\begin_inset Text + +\layout Standard + +request_duration +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +2 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +t +\end_inset + + +\begin_inset Text + +\layout Standard + +Time of request in human format +\end_inset + + +\begin_inset Text + +\layout Standard + +request_time +\end_inset + + +\begin_inset Text + +\layout Standard + +char(28) +\end_inset + + +\begin_inset Text + +\layout Standard + +[02/Dec/2001:15:01:26 -0800] +\end_inset + + + + +\begin_inset Text + +\layout Standard + +U +\end_inset + + +\begin_inset Text + +\layout Standard + +Request in simple form +\end_inset + + +\begin_inset Text + +\layout Standard + +request_uri +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +/books-cycroad.html +\end_inset + + + + +\begin_inset Text + +\layout Standard + +u +\end_inset + + +\begin_inset Text + +\layout Standard + +User info from HTTP authentication +\end_inset + + +\begin_inset Text + +\layout Standard + +remote_user +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + +bobby +\end_inset + + + + +\begin_inset Text + +\layout Standard + +v +\end_inset + + +\begin_inset Text + +\layout Standard + +Virtual host servicing the request +\end_inset + + +\begin_inset Text + +\layout Standard + +virtual_host +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + +www.foobar.com +\end_inset + + + + +\end_inset + + +\layout Quote + +If you have compiled mod_log_sql with WANT_SSL_LOGGING, you also get these: +\layout Quote + + +\begin_inset Tabular + + + + + + + + +\begin_inset Text + +\layout Standard + + +\series bold +Character +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +MySQL DB field it activates +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +MySQL DB field it activatesRecommended column type +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +Example +\end_inset + + + + +\begin_inset Text + +\layout Standard + +z +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_cipher +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(25) +\end_inset + + +\begin_inset Text + +\layout Standard + +RC4-MD5 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +q +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_keysize +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +56 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +Q +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_maxkeysize +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +128 +\end_inset + + + + +\end_inset + + +\layout Section + +FAQ +\layout Subsection + +Why log to an SQL database? +\layout Standard + +To begin with, let's get it out of the way: logging to a database is not + a panacea. + But while there are complexities with this solution, the benefit can be + substantial for certain classes of administrator or people with advanced + requirements: +\layout Itemize + +Chores like log rotation go away, as you can DELETE records from the SQL + database once they are no longer useful. + For example, the excellent and popular log-analysis tool Webalizer (http://www.w +ebalizer.com/) does not need historic logs after it has processed them, enabling + you to delete older logs. + +\layout Itemize + +People with clusters of web servers (for high availability) will benefit + the most -- all their webservers can log to a single SQL database. + This obviates the need to collate/interleave the many separate logfiles, + which can be / highly/ problematic. + +\layout Itemize + +People acquainted with the power of SQL SELECT statements will know the + flexibility of the extraction possibilities at their fingertips. +\layout Standard + +For example, do you want to see all your 404's? Do this: +\layout LyX-Code + +select remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp) + from acc_log_tbl where status=404 order by time_stamp; +\layout LyX-Code + ++-----------------------------------+--------+--------------------------+------- +-----+--------------------------- +\layout LyX-Code + ++ remote_host | status | request_uri + | bytes_sent | from_unixtime(time_stamp)| +\layout LyX-Code + ++-----------------------------------+--------+--------------------------+------- +-----+--------------------------- +\layout LyX-Code + +| marge.mmm.co.uk | 404 | /favicon.ico | 321 + | 2001-11-20 02:30:56 | +\layout LyX-Code + +| 62.180.239.251 | 404 | /favicon.ico | 333 + | 2001-11-20 02:45:25 | +\layout LyX-Code + +| 212.234.12.66 | 404 | /favicon.ico | 321 + | 2001-11-20 03:01:00 | +\layout LyX-Code + +| 212.210.78.254 | 404 | /favicon.ico | 333 + | 2001-11-20 03:26:05 | +\layout LyX-Code + ++-----------------------------------+--------+--------------------------+------- +-----+---------------------------+ +\layout Standard + +Or do you want to see how many bytes you've sent within a certain directory + or site? Do this: +\layout LyX-Code + +select request_uri,sum(bytes_sent) as bytes,count(request_uri) as howmany + from acc_log_tbl where request_uri like '%mod_log_sql%' group by request_uri + order by howmany desc; +\layout LyX-Code + ++-----------------------------------+---------+---------+ +\layout LyX-Code + +| request_uri | bytes | howmany | +\layout LyX-Code + ++-----------------------------------+---------+---------+ +\layout LyX-Code + +| /mod_log_sql/style_1.css | 157396 | 1288 | +\layout LyX-Code + +| /mod_log_sql/ | 2514337 | 801 | +\layout LyX-Code + +| /mod_log_sql/mod_log_sql.tar.gz | 9769312 | 456 | +\layout LyX-Code + +| /mod_log_sql/faq.html | 5038728 | 436 | +\layout LyX-Code + +| /mod_log_sql/INSTALL | 1196161 | 202 | +\layout LyX-Code + +| /mod_log_sql/directives.html | 1096821 | 171 | +\layout LyX-Code + +| /mod_log_sql/CHANGELOG | 424481 | 107 | +\layout LyX-Code + +| /mod_log_sql/README | 796072 | 97 | +\layout LyX-Code + +| /mod_log_sql/directives-old.html | 42480 | 6 | +\layout LyX-Code + +| /mod_log_sql/index.html | 9531 | 3 | +\layout LyX-Code + ++-----------------------------------+---------+---------+ +\layout Standard + +Or maybe you want to see who's linking to you? Do this: +\layout LyX-Code + +select count(referer) as num,referer from acc_log_tbl where request_uri='/mod_lo +g_sql/' group by referer order by num desc; +\layout LyX-Code + ++-----+------------------------------------------------------------------------- +------+ +\layout LyX-Code + +| num | referer + | +\layout LyX-Code + ++-----+------------------------------------------------------------------------- +------+ +\layout LyX-Code + +| 271 | http://freshmeat.net/projects/mod_log_sql/ + | +\layout LyX-Code + +| 96 | http://modules.apache.org/search?id=339 + | +\layout LyX-Code + +| 48 | http://freshmeat.net/ + | +\layout LyX-Code + +| 8 | http://freshmeat.net + | +\layout LyX-Code + +| 7 | http://freshmeat.net/daily/2001/11/30/ + | +\layout LyX-Code + +| 6 | http://freshmeat.net/daily/2001/11/20/ + | +\layout LyX-Code + ++-----+------------------------------------------------------------------------- +------+ +\layout Standard + +As you can see, there are myriad possibilities that can be constructed with + the wonderful SQL SELECT statement. + Logging to an SQL database can be really quite useful! +\layout Subsection + +Why use MySQL? Are there alternatives? +\layout Standard + +MySQL is a robust, free, and very powerful production-quality database engine. + It is well supported and comes with detailed documentation. + Many 3rd-party software pacakges (e.g. + Slashcode, the engine that powers Slashdot) run exclusively with MySQL. + In other words, you will belong to a very robust and well-supported community + by choosing MySQL. +\layout Standard + +That being said, there are alternatives. + PostgreSQL is probably MySQL's leading "competitor" in the free database + world. + There is also an excellent module available for Apache to permit logging + to a PostgreSQL database, called +\begin_inset LatexCommand \url[pgLOGd]{(http://www.digitalstratum.com/pglogd/)} + +\end_inset + +. +\layout Subsection + +Is this code production-ready? +\layout Standard + +By all accounts it is. + It is known to work without a problem on many-thousands-of-hits-per-day + webservers. + Does that mean it is 100% bug free? Well, no software is. + But it is well-tested and /believed/ to be fully compatible with production + environments. + (The usual disclaimers apply. + This software is provided without warranty of any kind.) +\layout Subsection + +How well does it perform? +\layout Standard + +mod_log_sql scales to very high loads. + Apache 1.3.22 + mod_log_sql was benchmarked using the "ab" (Apache Bench) + program that comes with the Apache distribution; here are the results. +\layout Standard + +Overall configuration: +\layout Itemize + +Machine A: Apache webserver +\layout Itemize + +Machine B: MySQL server +\layout Itemize + +Machines A and B connected with 100Mbps Ethernet +\layout Standard + +Webserver configuration: +\layout Itemize + +Celeron 400 128 MB RAM IDE storage +\layout Standard + +Apache configuration: +\layout LyX-Code + +Timeout 300 +\layout LyX-Code + +KeepAlive On +\layout LyX-Code + +MaxKeepAliveRequests 100 +\layout LyX-Code + +KeepAliveTimeout 15 +\layout LyX-Code + +MinSpareServers 5 +\layout LyX-Code + +StartServers 10 +\layout LyX-Code + +MaxSpareServers 15 +\layout LyX-Code + +MaxClients 256 +\layout LyX-Code + +MaxRequestsPerChild 5000 +\layout LyX-Code + +LogSQLTransferLogFormat AbHhmRSsTUuvc +\layout LyX-Code + +LogSQLWhichCookie Clicks +\layout LyX-Code + +CookieTracking on +\layout LyX-Code + +CookieName Clicks +\layout Standard + +"ab" commandline: +\layout LyX-Code + +./ab -c 10 -t 20 -v 2 -C Clicks=ab_run http://www.hostname.com/target +\layout Standard + +( 10 concurrent requests; 20 second test; setting a cookie "Clicks=ab_run"; + target = the mod_log_sql homepage. + ) +\layout Standard + +Ten total ab runs were conducted: five with MySQL logging enabled, and five + with all MySQL directives commented out of httpd.conf. + Then each five were averaged. + The results: +\layout Itemize + +Average of five runs employing MySQL +\emph on +and +\emph default + standard text logging: 139.01 requests per second, zero errors. +\layout Itemize + +Average of five runs employing +\emph on +only +\emph default + standard text logging: 139.96 requests per second, zero errors. +\layout Standard + +In other words, any rate-limiting effects on this particular hardware setup + are not caused by MySQL. + Note that although this very simple webserver setup is hardly cutting-edge + -- it is, after all, a fairly small machine -- 139 requests per second + equal over twelve million hits per day +\emph on +. +\layout Standard + +If you run this benchmark yourself, take note of three things: +\layout Enumerate + +Use a target URL that is on your own webserver :-). + +\layout Enumerate + +Wait until all your connections are closed out between runs; after several + thousand requests your TCP/IP stack will be filled with hundreds of connections + in TIME_WAIT that need to close. + Do a "netstat -t|wc -l" on the webserver to see. + If you don't wait, you can expect to see a lot of messages like "ip_conntrack: + table full, dropping packet" in your logs. + (This has nothing to do with mod_log_sql, this is simply the nature of + the TCP/IP stack in the Linux kernel.) +\layout Enumerate + +When done with your runs, clean these many thousands of requests out of + your database: +\layout LyX-Code + +mysql> delete from access_log where agent like 'ApacheBench%'; mysql> optimize + table access_log; +\layout Subsection + +Who's using mod_log_sql? +\layout Standard + +Good question! It would be great to find out! If you are a production-level + mod_log_sql user, please contact +\begin_inset LatexCommand \url[the maintainer, Chris Powell]{chris@grubbybaby.com} + +\end_inset + +so that you can be mentioned here. +\layout Subsection + +How do I extract the data in a format that my analysis tool can understand? +\layout Standard + +mod_log_sql would be virtually useless if there weren't a way for you to + extract the data from your database in a somewhat meaningful fashion. + To that end there's a Perl script enclosed with the distribution. + That script (make_combined_log.pl) is designed to extract N-many days worth + of access logs and provide them in a Combined Log Format output. + You can use this very tool right in /etc/crontab to extract logs on a regular + basis so that your favorite web analysis tool can read them. + Or you can examine the Perl code to construct your own custom tool. +\layout Standard + +For example, let's say that you want your web statistics updated once per + day in the wee hours of the morning. + A good way to accomplish that would be the following entries in /etc/crontab: +\layout LyX-Code + +# Generate the temporary apache logs from the MySQL database (for webalizer) + +\layout LyX-Code + +05 04 * * * root /usr/local/sbin/make_combined_log.pl 1 www.grubbybaby.com + > /var/log/httpd/mysql-grubbybaby +\layout LyX-Code + +# Run webalizer on httpd log +\layout LyX-Code + +30 04 * * * root /usr/local/bin/webalizer -c /etc/webalizer.conf; rm -f /var/log/ +httpd/mysql-grubbybaby +\layout Standard + +Or if you have a newer system that puts files in /etc/cron.daily etc., create + a file called +\begin_inset Quotes sld +\end_inset + +webalizer +\begin_inset Quotes srd +\end_inset + + in the cron.____ subdir of your choice. + Use the following as the contents of your file, and make sure to chmod + 755 it when done. +\layout LyX-Code + +#!/bin/sh +\layout LyX-Code + +/usr/local/sbin/make_combined_log.pl 1 www.yourdomain.com > /var/log/httpd/templog +\layout LyX-Code + +/usr/local/bin/webalizer -q -c /etc/webalizer.conf +\layout LyX-Code + +rm -f /var/log/httpd/templog +\layout Standard + +See? Easy. +\layout Subsection + +Why doesn't the module also replace the Apache ErrorLog? +\layout Standard + +There are circumstances when that would be quite unwise -- for example, + if Apache could not reach the MySQL server for some reason and needed to + log that fact. + Without a text-based error log you'd never know anything was wrong, because + Apache would be trying to log a database connection error to the database... + you get the point. +\layout Standard + +Error logs are usually not very high-traffic and are really best left as + text files on a web server machine. +\layout Subsection + + +\begin_inset LatexCommand \label{sec:cookie} + +\end_inset + +How can I log mod_usertrack cookies? +\layout Standard + +A number of people like to log mod_usertrack cookies in their Apache TransferLog + to aid in understanding their visitors' clickstreams. + This is accomplished, for example, with a statement as follows: +\layout LyX-Code + +LogFormat "%h %l %u %t +\backslash +"%r +\backslash +" %s %b +\backslash +"%{Referer}i +\backslash +" +\backslash +"%{User-Agent}i +\backslash +"" +\backslash +"%{cookie}n +\backslash +"" +\layout Standard + +Naturally it would be nice for mod_log_sql to permit the admin to log the + cookie data as well, so as of version 1.10 you can do this. + You need to have already compiled mod_usertrack into httpd -- it's one + of the standard Apache modules. +\layout Standard + +First make sure you have a column called "cookie" in the MySQL database + to hold the cookies, which can be done as follows if you already have a + working database: +\layout LyX-Code + +alter table acc_log_tbl add column cookie varchar(255); +\layout Standard + +Next configure your server to set usertracking cookies as follows, and make + sure you include the new 'c' directive in your LogSQLTransferLogFormat, + which activates cookie logging. + Here's an example: +\layout LyX-Code + + +\layout LyX-Code + + CookieTracking on +\layout LyX-Code + + CookieStyle Cookie +\layout LyX-Code + + CookieName Foobar +\layout LyX-Code + + LogSQLTransferLogFormat huSUsbTvRAc +\layout LyX-Code + + LogSQLWhichCookie Foobar +\layout LyX-Code + + +\layout Standard + +The first three lines configure mod_usertrack to create a COOKIE (RFC 2109) + format cookie called Foobar. + The last two lines tell mod_log_sql to log cookies named Foobar. + You have to choose which cookie to log because more than one cookie can/will + be sent to the server by the client. +\layout Standard + +FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that even + newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2 (RFC 2965) + format. + Just stick with the standard COOKIE format and you'll be fine. +\layout Standard + +Perform some hits on your server and run a select: +\layout LyX-Code + +mysql> select request_uri,cookie from access_log where cookie is not null; +\layout LyX-Code + ++--------------------------------------------+---------------------------------- +------------------+ +\layout LyX-Code + +| request_uri | + cookie | +\layout LyX-Code + ++--------------------------------------------+---------------------------------- +------------------+ +\layout LyX-Code + +| /mod_log_sql/ | ool-18e4.dyn.optonline.net.1300510071 +02700823 | +\layout LyX-Code + +| /mod_log_sql/usa.gif | ool-18e4.dyn.optonline.net.13005100710 +2700823 | +\layout LyX-Code + +| /mod_log_sql/style_1.css | ool-18e4.dyn.optonline.net.13005100710 +2700823 | +\layout LyX-Code + +...etc... +\layout Subsection + +What if I want to log more than one cookie? +\layout Standard + +No problem. + As of version 1.17, you have a choice. + If you are just interested in a single cookie, follow the instructions + in section +\begin_inset LatexCommand \ref{sec:cookie} + +\end_inset + + above. + That cookie will be logged to a column in the regular access_log table. +\layout Standard + +However, if you need to log multiple cookies, you'll employ the LogSQLWhichCooki +es (note the plural) directive. + The cookies you specify will be logged to a separate table, and entries + in that table will be linked to the regular access_log entries via the + unique ID that is supplied by mod_unique_id. + Without mod_unique_id the information will still be logged but you will + be unable to correlate which cookies go with which access-requests. +\layout Standard + +LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict, but + you're better off choosing the one you need. +\layout Subsection + +What are the SSL logging features, and how do I activate them? +\layout Standard + +If you run an SSL-enabled server you may benefit from logging some SSL details. + mod_log_sql now supports this ability. + By adding certain characters to your LogSQLTransferLogFormat string you + can tell mod_log_sql to log the SSL cipher, the SSL keysize of the connection, + and the Max-keysize that was available. + This would let you tell, for example, which clients were using only export-grad +e security to access your secure software area. +\layout Standard + +You can compile mod_log_sql with SSL logging support if you have the right + packages installed. + If you already have an SSL-enabled Apache then you by definition have the + correct packages already installed: OpenSSL and mod_ssl. +\layout Standard + +You need to ensure that your database is set up to log the SSL data. + Issue the following commands to MySQL once you have your basic access_log + table built: +\layout LyX-Code + +alter table access_log add column ssl_cipher varchar(25); +\layout LyX-Code + +alter table access_log add column ssl_keysize smallint unsigned; +\layout LyX-Code + +alter table access_log add column ssl_maxkeysize smallint unsigned; +\layout Standard + +Finally configure httpd.conf to activate the SSL fields. + Note that this is only meaningful in a VirtualHost that is set up for SSL. +\layout LyX-Code + + +\layout LyX-Code + + LogSQLTransferLogFormat AbHhmRSsTUuvcQqz +\layout LyX-Code + + +\layout Standard + +The last three characters (Qqz) in the directive are the SSL ones; see the + directives documentation for details. +\layout Standard + +Perform some hits on your server and run a select: +\layout LyX-Code + +mysql> select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize + from access_log where ssl_cipher is not null; +\layout LyX-Code + ++--------------------------+---------------------------------------------------- ++------------+-------------+----------------+ +\layout LyX-Code + +| remote_host | request_uri + | ssl_cipher | ssl_keysize | ssl_maxkeysize | +\layout LyX-Code + ++--------------------------+---------------------------------------------------- ++------------+-------------+----------------+ +\layout LyX-Code + +| 216.190.52.4 | /dir/somefile.html + | RC4-MD5 | 128 | 128 | +\layout LyX-Code + +| 216.190.52.4 | /dir/somefile.gif + | RC4-MD5 | 128 | 128 | +\layout LyX-Code + +| 216.190.52.4 | /dir/somefile.jpg + | RC4-MD5 | 128 | 128 | +\layout LyX-Code + +...etc... +\layout Subsection + +Does mod_log_sql connect to MySQL via TCP/IP or a socket? +\layout Standard + +It depends! Actually this isn't determined by mod_log_sql. + mod_log_sql relies on a connection command that is supplied in the MySQL + API, and that command is somewhat intelligent. + When mod_log_sql issues the connect command to MySQL, this intelligent + connect command uses sockets to communicate with MySQL if the specified + MySQL database is on the same machine (because sockets are more efficient + than TCP/IP). + However, if the specified MySQL db is on a different machine, mod_log_sql + connects using TCP/IP. + You don't have any control of which methodology is used. +\layout Standard + +You do have control over where mod_log_sql looks for the socket. + The LogSQLSocketFile runtime configuration directive overrides the default + of "/var/lib/mysql/mysql.sock" to whatever you wish. + (Applies to mod_log_sql 1.16 or later only.) +\layout Subsection + +Why do I occasionally see a "connection lost, attempting reconnect" message + in my error-log? +\layout Standard + +This message may appear every now and then in your Apache error log, especially + on very lightly loaded servers. + This doesn't mean that anything is necessarily wrong. + Within each httpd child process, mod_log_sql will open (and keep open) + a connection to the MySQL server. + MySQL, however, will close connections that haven't been used in a while; + the default timeout is 8 hours. + When this occurs, mod_log_sql will notice and re-open the connection. + That event is what is being logged, and looks like this: +\layout LyX-Code + +[Thu Dec 13 05:42:18 2001] [error] mod_log_sql: connection lost, attempting + reconnect +\layout LyX-Code + +[Thu Dec 13 05:42:18 2001] [error] mod_log_sql: reconnect successful +\layout Standard + +Reference: +\begin_inset LatexCommand \url[MySQL documentation]{http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away} + +\end_inset + + +\layout Subsection + +Does mod_log_sql work with Apache 2.x? +\layout Standard + +As of this writing, no. + The Apache Group significantly altered the module API with the release + of Apache 2.0. + All modules written for 1.3, including mod_log_sql, will not work with 2.0. +\layout Standard + +mod_log_sql will eventually be ported to Apache 2.x, but not immediately. + It is going to take some time, and there are other features that have higher + priority. + Please sign up for the announcements list (on the main website) or monitor + the website for updates to learn when the port (and other releases) are + available. +\layout Standard + +If you're a *NIX user, stick with Apache 1.3.x for now. + Major modules like mod_ssl and PHP are not even ready for 2.0 yet, and the + main benefits in 2.0 are for Win32 users anyway. + Apache 1.3.x is rock-stable and performs equally well on *NIX as 2.0. +\layout Subsection + +I have discovered a bug. + Who can I contact? +\layout Standard + +Please contact +\begin_inset LatexCommand \url[the maintainer]{chris@grubbybaby.com} + +\end_inset + +! Your comments, suggestions, bugfixes, bug catches, and usage testimonials + are always welcome. + As free software, mod_log_sql is intended to be a community effort -- any + code contributions or other ideas will be fully and openly credited, of + course. +\the_end diff --git a/Documentation/documentation.tex b/Documentation/documentation.tex new file mode 100644 index 0000000..0eb6359 --- /dev/null +++ b/Documentation/documentation.tex @@ -0,0 +1,1534 @@ +%% LyX 1.1 created this file. For more info, see http://www.lyx.org/. +%% Do not edit unless you really know what you are doing. +\documentclass[10pt,english]{article} +\usepackage[T1]{fontenc} +\usepackage{geometry} +\geometry{verbose,letterpaper,tmargin=0.5in,bmargin=1in,lmargin=1in,rmargin=1in} +\usepackage{babel} +\IfFileExists{url.sty}{\usepackage{url}} + {\newcommand{\url}{\texttt}} + +\makeatletter + +%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% LyX specific LaTeX commands. +\providecommand{\LyX}{L\kern-.1667em\lower.25em\hbox{Y}\kern-.125emX\@} + +%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Textclass specific LaTeX commands. + \newenvironment{lyxcode} + {\begin{list}{}{ + \setlength{\rightmargin}{\leftmargin} + \raggedright + \setlength{\itemsep}{0pt} + \setlength{\parsep}{0pt} + \normalfont\ttfamily}% + \item[]} + {\end{list}} + +\makeatother +\begin{document} + +\vfill{} +\title{Installing and Running mod\_log\_sql} +\vfill{} + + +\author{Christopher Powell } + + +\date{11/13/02} + +\maketitle +\tableofcontents{} +\newpage + + +\section{Introduction} + + +\subsection{Homepage } + +\begin{lyxcode} +http://www.grubbybaby.com/mod\_log\_sql/ +\end{lyxcode} + +\subsection{Summary} + +This Apache module will permit you to log Apache accesses (and a lot +of related information) to a SQL database. Unlike logging to a flat +text file (which is standard in Apache), there are a lot of powerful +advantages to logging to SQL. This module can either replace or happily +coexist with Apache's text file logging facility. + + +\subsection{Approach} + +This project was formerly known as mod\_log\_mysql. It has been renamed +to mod\_log\_sql in order to reflect the project goal of database-inspecificity. +The module currently supports MySQL, and development for other database +backends is underway. + +In order to save speed and overhead, links are kept alive in between +queries. This module uses one SQL link per httpd child. Among other +things, this means that this module supports logging into only one +MySQL server, and for now, also, only one SQL database. But that's +a small tradeoff compared to the blinding speed of this module. + +Virtual hosts are supported in the same manner they are in the regular +logging modules. You define some basic 'global' directives in the +main server config, then you define more specific 'local' directives +inside each virtualhost stanza. + +SQL links are opened by each child process when it is born. Error +reporting is robust throughout and will let you know about database +issues in the standard Apache error-log for the server or virtual +server. + +A robust \char`\"{}preserve\char`\"{} capability has now been implemented. +This permits the module to preserve any failed INSERT commands to +a local file on its machine. In any situation that the database is +unavailable -- e.g. the network fails, you reboot the db host, etc. +-- mod\_log\_sql will note this in the error log and begin appending +its log entries to the preserve file (which is created with the user +\& group ID of the running Apache process, e.g. \char`\"{}nobody\char`\"{} +on many Linux installations). When your database becomes available +again, mod\_log\_sql seamlessly resumes logging to it. When convenient +for the sysadmin, he/she can easily import the preserve file into +the database because it is simply a series of SQL insert statements: + +\begin{lyxcode} +\#~mysql~-uadminuser~-p~mydbname~<~/tmp/mysql-preserve +\end{lyxcode} + +\subsection{Supported directives} + +Please see the web-based documentation for full explanation of all +supported run-time directives. + +http://www.grubbybaby.com/mod\_log\_sql/directives.html + +See the FAQ for some handy examples: + +http://www.grubbybaby.com/mod\_log\_sql/faq.html + + +\subsection{What gets logged by default? } + +All the data that would be contained in the \char`\"{}Combined Log +Format\char`\"{} is logged by default, plus a little extra. Your best +bet is to begin by accepting this default, then later customize the +log configuration based on your needs. + +The online documentation of the run-time directives includes a full +explanation of what you can log, including examples. + + +\subsection{Minor Notes} + +\begin{itemize} +\item Note which directives go in the 'main server config' and which directives +apply to the 'virtual host config'. This is made clear in the directive +documentation. +\item The 'time\_stamp' field is stored in an UNSIGNED INTEGER column, in +the standard unix \char`\"{}seconds since 1/1/1970 12:00:00\char`\"{} +format. This is superior to storing the access time as a string due +to size requirements: an UNSIGNED INT type fits in 4 bytes, whereas +the Apache date string (e.g. \char`\"{}18/Nov/2001:13:59:52 -0800\char`\"{}) +requires 26 bytes -- significantly larger, and those extra 22 bytes +will add up over the thousands of accesses that a busy server will +experience. Besides, an INT type is far more flexible for comparisons, +etc. + + +In MySQL 3.21 and above you can easily convert this to a human readable +format using from\_unixtime(), e.g.: + +\begin{lyxcode} +select~remote\_host,request\_uri,from\_unixtime(time\_stamp)~from~access\_log; +\end{lyxcode} +The enclosed perl program ''make\_combined\_log.pl'' shows how you +can extract your access records in a format that is completely Combined +Log Format compliant. You can then feed this to your favorite web +log analysis tool. + +\item The table's string values can be CHAR or VARCHAR, at a length of your +choice. VARCHAR is superior because it truncates long strings; CHAR +types are fixed-length and will be padded with spaces. Just like the +time\_stamp described above, that kind of space waste will add up +over thousands of records. +\item Be careful not to go overboard setting fields to NOT NULL. If a field +is marked NOT NULL then it must contain data in the INSERT or the +INSERT will fail, sometimes in a really mysterious way to the frustrated +sysad. +\item Apache normally logs numeric fields with a '-' character to mean \char`\"{}not +applicable,\char`\"{} e.g. bytes\_sent on a request with a 304 response +code. Since '-' is an illegal character in an SQL numeric field, such +fields are assigned the value 0 instead of '-' which, of course, makes +perfect sense anyway. +\end{itemize} + +\subsection{Author / Maintainer} + +The actual logging code was taken from the already existing flat file +text modules, so all that credit goes to the Apache Server group. + +The MySQL routines and directives were added by Zeev Suraski . + +All changes from 1.06+ and the new documentation were added by Chris +Powell . It seems that the module had fallen +into the \char`\"{}unmaintained\char`\"{} category -- it hadn't been +updated since 1998 -- so Chris adopted it as the new maintainer. + + +\section{Installation} + + +\subsection{Requirements} + +\begin{itemize} +\item A compatible system. I have run mod\_log\_sql on Red Hat based systems +(Red Hat, Mandrake). These instructions should easily adapt to any +modern distro. +\item Apache 1.2 or 1.3 installed. (I run 1.3.22 and it works fine). You +should have already successfully compiled Apache and know what you're +doing there. +\item The MySQL development headers. This is called different things on +different distros. For example, Red Hat 6.x called this RPM \char`\"{}MySQL-devel\char`\"{} +whereas Mandrake calls it \char`\"{}libmysql10-devel\char`\"{}. +\item MySQL >= 3.23.15 configured, installed and running on either localhost +or an accessible networked machine. You should already have a basic +understanding of MySQL and how it functions. +\item Again, basic administrative skills with Apache and MySQL. I try to +make things as easy as possible in this file, but its purpose is not +to be an administrative tutorial. +\item Additionally, if you want to be able to log SSL information such as +keysize or cipher, you need OpenSSL and glibc-devel installed. Both +are available as RPMs. +\end{itemize} + +\subsection{Do I want a DSO?} + +You need to know the answer to this question before you proceed. The +answer is pretty straightforward: what have you done in the past? +If you like all your Apache modules to be dynamic, then you should +keep doing that. If you're more of an old-school type and prefer to +compile the modules right into apache, do that. Both methods work +equally well. + +FWIW, the DSO method is more modern and increasing in popularity because +apxs takes care of a lot of dirty little details for you. As you'll +see below, the static-module method is a little more complex. + + +\subsection{Installation as an Apache DSO (Preferred) } + +\begin{enumerate} +\item Perform all the following steps as root so that you have install privs, +etc. Unpack the archive into a working directory. + +\begin{lyxcode} +\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql +\end{lyxcode} +\item Edit Makefile for your system.\\ + + + +NECESSARY: + +\begin{itemize} +\item The location where you installed Apache -- usually /usr/local/apache, +'locate apxs' can help you find it. +\item The location of your MySQL libraries, find using 'locate libmysqlclient' +\item The location of your MySQL header files, find using 'locate mysql.h' +\end{itemize} +OPTIONAL if you have included mod\_ssl in Apache and want to log SSL +data such as keysize and cipher type: + +\begin{itemize} +\item The location of your SSL header files, find using 'locate mod\_ssl.h' +\end{itemize} +Now that you know these things, edit Makefile and replace the stock +values with your own. + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS +by putting a \# character in front of it: + +\begin{lyxcode} +\#MODSSLHDRS=/usr/include/... +\end{lyxcode} +\item Instruct apxs to compile the module as a DSO. + +\begin{lyxcode} +\#~make~dso +\end{lyxcode} +\item You should see output similar to the following: + +\begin{lyxcode} +/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~ + +gcc~-DLINUX=22~-DNO\_DBM\_REWRITEMAP~-DMOD\_SSL=208111~-DUSE\_HSREGEX~-DEAPI~-DUSE\_EXPAT~-I../lib/expat-lite~-fpic~-DSHARED\_CORE~-DSHARED\_MODULE~-I/usr/local/Apache/include~-O2~-Wall~-DEAPI~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-c~mod\_log\_sql.c~ + +gcc~-shared~-o~mod\_log\_sql.so~mod\_log\_sql.o~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-L/usr/lib~-lmysqlclient~-lz~-lm~-lcrypt~-ldb +\end{lyxcode} +You should see no errors and have a file called \char`\"{}mod\_log\_sql.so\char`\"{} +in your directory. + +\item Instruct apxs to install the DSO. + +\begin{lyxcode} +\#~make~dsoinstall +\end{lyxcode} +You should see output similar to the following: + +\begin{lyxcode} +/usr/local/Apache/bin/apxs~-i~mod\_log\_sql.so + +cp~mod\_log\_sql.so~/usr/local/Apache/libexec/mod\_log\_sql.so + +chmod~755~/usr/local/Apache/libexec/mod\_log\_sql.so +\end{lyxcode} +\item Module ordering within httpd.conf is important. If you are logging +SSL, you must make sure that + +\begin{lyxcode} +LoadModule~ssl\_module~libexec/libssl.so +\end{lyxcode} +comes before + +\begin{lyxcode} +LoadModule~sql\_log\_module~libexec/mod\_log\_sql.so +\end{lyxcode} +If you don't, you will get this error when you start Apache: + +\begin{lyxcode} +/usr/local/apache/libexec/mod\_log\_mysql.so:~undefined~symbol:~ssl\_var\_lookup + +/usr/local/apache/bin/apachectl~startssl:~httpd~could~not~be~started +\end{lyxcode} +(Because mod\_log\_sql doesn't yet have the required symbols that +mod\_ssl provides.) + +Now skip below to the \char`\"{}Configuration\char`\"{} section. + +\end{enumerate} + +\subsection{Installation as a static module compiled into httpd} + +\begin{enumerate} +\item Perform all the following steps as root so that you have install privs, +etc. +\item Unpack the archive into a working directory. + +\begin{lyxcode} +\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql +\end{lyxcode} +\item \label{step:editMF}Edit Makefile for your system.\\ + + + +NECESSARY: + +\begin{itemize} +\item The location where you installed Apache -- usually /usr/local/apache, +'locate apxs' can help you find it. +\item The location of your Apache {*}sources{*}, find using 'locate ABOUT\_APACHE' +\item The location of your MySQL header files, find using 'locate mysql.h' +\item The location of your MySQL libraries, find using 'locate libmysqlclient' +\end{itemize} +OPTIONAL if you have included mod\_ssl in Apache and want to log SSL +data such as keysize and cipher type: + +\begin{itemize} +\item The location of your mod\_ssl header files, find using 'locate mod\_ssl.h' +\item The location of your OpenSSL header files, find using 'locate x509.h' +\item The location of your db1 header files, find using 'locate mpool.h' +\end{itemize} +Now that you know these things, edit Makefile and replace the stock +values with your own. + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, +OPNSSLHDRS and DB1HDRS by putting a \# character in front of each +one, e.g. \#OPNSSLHDRS=/usr/include/... + +\item \# make static +\item \# make statinstall +\item Change to your Apache source dir. + +\begin{lyxcode} +\#~cd~/usr/local/src/apache-1.3.22/src +\end{lyxcode} +\item Re-make your httpd binary as follows. + +\begin{enumerate} +\item Edit Configuration.apaci as follows... + +\begin{itemize} +\item Append the following string to the EXTRA\_LIBS= line. (\char`\"{}/usr/lib/mysql\char`\"{} +is from step \ref{step:editMF}, where your MySQL libraries live): +\end{itemize} +\begin{lyxcode} +-L/usr/lib/mysql~-lmysqlclient~-lm~-lz +\end{lyxcode} +\begin{itemize} +\item Find the mod\_log\_config.o line, and add this line immediately after +it: +\end{itemize} +\begin{lyxcode} +AddModule~modules/sql/mod\_log\_sql.o +\end{lyxcode} +\item \# cp Configuration.apaci Configuration +\item \# ./Configure +\item \# make +\item \# strip httpd +\end{enumerate} +\item Test your new apache binary: + +\begin{lyxcode} +\#~./httpd~-l +\end{lyxcode} +\item You should see something like: + +\begin{lyxcode} +Compiled-in~modules:~ + +http\_core.c + +mod\_log\_sql.c~<-{}-~That's~the~line~you're~looking~for. + +mod\_env.c~ + +mod\_log\_config.c~ + +mod\_mime.c~ + +mod\_negotiation.c~... + +etc... +\end{lyxcode} +\item Install your httpd binary. Copy it over your old httpd binary, wherever +it lives. You can and should rename your old httpd first so that you +can easily revert to that working version in case of bugs with the +new version. + +\begin{lyxcode} +\#~/etc/rc.d/init.d/httpd~stop~ + +\#~mv~/usr/local/Apache/bin/httpd~\textasciitilde{}/httpd-save~ + +\#~cp~-f~./httpd~/usr/local/Apache/bin/ +\end{lyxcode} +\end{enumerate} + +\section{Configuration} + + +\subsection{Preparing MySQL} + +You have to prepare the database to receive data from mod\_log\_sql, +and set up run-time directives in httpd.conf to control how and what +mod\_log\_sql logs. + +This section will discuss how to get started with a basic config. +Full documentation of the run-time directives is available here: http://www.grubbybaby.com/mod\_log\_sql/directives.html + +\begin{enumerate} +\item mod\_log\_sql can make its own tables on-the-fly, or you can pre-make +the tables by hand. The advantage of letting the module make the tables +is ease-of-use, but for raw performance you will want to pre-make +the tables in order to save overhead. In this basic setup we'll let +the module create tables for us. +\item We still need to have a logging database created and ready, so run +the MySQL command line client and create a database: + +\begin{lyxcode} +\#~mysql~-uadmin~-pmypassword~mysql>~create~database~apachelogs; +\end{lyxcode} +\item If you want to hand-create the tables, run the enclosed 'create-tables' +SQL script as follows: + +\begin{lyxcode} +mysql>~source~create\_tables.sql +\end{lyxcode} +\item Create a specific MySQL userid that httpd will use to authenticate +and enter data. This userid need not be an actual Unix user. It is +a userid internal to MySQL with specific privileges. In the following +example command, \char`\"{}apachelogs\char`\"{} is the database, \char`\"{}loguser\char`\"{} +is the userid to create, \char`\"{}my.apachemachine.com\char`\"{} +is the name of the Apache machine, and \char`\"{}l0gger\char`\"{} +is the password to assign. Choose values that are different from these +examples. + +\begin{lyxcode} +mysql>~grant~insert,create~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger'; +\end{lyxcode} +\item You may be especially security-paranoid and not want \char`\"{}loguser\char`\"{} +to have \char`\"{}create\char`\"{} capability within the \char`\"{}apachelogs\char`\"{} +databse. You can disable that but the cost is that you cannot use +the module's automatic-table-creation feature. If that's an acceptable +cost, hand-create the tables as described in step 1 and use the following +GRANT statement instead of the one above: + +\begin{lyxcode} +mysql>~grant~insert~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger'; +\end{lyxcode} +\item Enable full logging of your MySQL daemon (at least temporarily for +debugging purposes) if you don't do this already. Edit /etc/my.cnf +and add the following line to your {[}mysqld{]} section: + +\begin{lyxcode} +log=/var/log/mysql-messages +\end{lyxcode} +Then restart MySQL. + +\begin{lyxcode} +\#~/etc/rc.d/init.d/mysql~restart +\end{lyxcode} +\end{enumerate} + +\subsection{Preparing Apache} + +\begin{enumerate} +\item Tell the module what database to use and the appropriate authentication +information. + +\begin{description} +\item [EXAMPLE:]Use the MySQL database called \char`\"{}apachelogs\char`\"{} +running on \char`\"{}dbmachine.foo.com\char`\"{}. The module uses +username \char`\"{}loguser\char`\"{} and password \char`\"{}l0gger\char`\"{} +to authenticate to the database. The log entries will be INSERTed +into the table called \char`\"{}access\_log\char`\"{}. +\end{description} +So, edit httpd.conf and insert the following lines somewhere AFTER +any LoadModule / AddModule statements. Make sure these statements +are \char`\"{}global,\char`\"{} i.e. not inside any VirtualHost stanza. + +\begin{lyxcode} +LogSQLDatabase~apachelogs + +LogSQLLoginInfo~dbmachine.foo.com~loguser~l0gger~ + +LogSQLCreateTables~on +\end{lyxcode} +\item If your database resides on localhost instead of another host, specify +the MySQL server's socket file as follows: + +\begin{lyxcode} +LogSQLSocketFile~/your/path/to/mysql.sock +\end{lyxcode} +\item The actual logging is set up on a virtual-host-by-host basis. So, +skip down to the virtual host you want to set up. Instruct this virtual +host to log entries to the table ''access\_log'' by inserting a +LogSQLTransferLogTable directive. (The LogSQLTransferLogTable directive +is the minimum required to log -- other directives simply tune the +module's behavior.) + +\begin{lyxcode} + + +{[}snip{]} + +LogSQLTransferLogTable~access\_log + +{[}snip{]} + + +\end{lyxcode} +\item Restart apache. + +\begin{lyxcode} +\#~/etc/rc.d/init.d/httpd~start +\end{lyxcode} +\end{enumerate} + +\subsection{Testing the module} + +\begin{enumerate} +\item Load your web site in a browser to trigger some hits, then confirm +that the entries are being successfully logged: + +\begin{lyxcode} +\#~mysql~-hmysql.host.com~-umysqladmin~-p~-e~\char`\"{}select~{*}~from~access\_log\char`\"{}~apachelogs~ + +Enter~password: + +(Several~lines~of~output~should~follow,~corresponding~to~your~hits~on~the~site.) +\end{lyxcode} +You now have basic functionality. Don't disable your regular Apache +logs until you feel comfortable that the database is behaving as you'd +like and that things are going well. + +\item If you do not see any entries in the access\_log, then something is +preventing the inserts from happening. This problem could be caused +by several things: + +\begin{itemize} +\item Improper privileges set up in the MySQL database +\item You aren't hitting a VirtualHost that has a LogSQLTransferLogTable +entry +\item You didn't specify the right host +\end{itemize} +If you have confirmed your LogSQL{*} directives and know them to be +correct, you should examine the httpd server logs for mod\_log\_sql +messages; the module will offer hints as to why it cannot connect, +etc. Also examine the MySQL log that you established in step 3. Ensure +that the INSERTs are not being rejected because of a malformed table +entry or other clerical error. If you see no INSERT attempts in the +log, the module isn't successfully connecting to the database. + +The next thing to do is recompile the module with debugging output +activated. change the \char`\"{}\#undef DEBUG\char`\"{} on line 8 +of mod\_log\_sql.c to \char`\"{}\#define DEBUG\char`\"{} and recompile/reinstall. +The module will now output copious notes about what it is doing, and +this will help you (and the maintainer) solve the problem. + +\end{enumerate} +You can now activate the advanced features of mod\_log\_sql. These +are all described in the next section. + + +\subsection{Configuration directive reference} + + +\subsubsection{LogSQLLoginInfo } + +\begin{lyxcode} +\textbf{MANDATORY}~ + +Syntax:~LogSQLLoginInfo~host~user~password + +Example:~LogSQLLoginInfo~foobar.baz.com~logwriter~passw0rd~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +Defines the general parameters of the MySQL host to which you will +be logging. host is the hostname or IP address of the MySQL machine. +user is the MySQL userid (not a Unix userid!) with INSERT privileges +on the table defined in LogSQLTransferLogTable. password is that user's +password. This is defined only once in the httpd.conf file. +\end{quote} + +\subsubsection{LogSQLDatabase } + +\begin{lyxcode} +\textbf{MANDATORY} + +Syntax:~LogSQLDatabase~database~ + +Example:~LogSQLDatabase~loggingdb~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +Defines the database that is used for logging. database must be operating +on the MySQL host defined in LogSQLLoginInfo. This is defined only +once in the httpd.conf file. +\end{quote} + +\subsubsection{LogSQLTransferLogTable} + +\begin{lyxcode} +\textbf{MANDATORY~} + +Syntax:~LogSQLTransferLogTable~table-name~ + +Example:~LogSQLTransferLogTable~access\_log\_table~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Defines which table is used for logging of Apache's transfers; this +is analogous to Apache's TransferLog directive. table-name must be +a valid table within the database defined in LogSQLDatabase. + +This directive is not necessary if you declare LogSQLMassVirtualHosting +On, since that directive activates dynamically-named tables. If you +attempt to use LogSQLTransferLogTable at the same time a warning will +be logged and it will be ignored, since LogSQLMassVirtualHosting takes +priority. +\end{quote} + +\subsubsection{LogSQLRequestIgnore} + +\begin{lyxcode} +Syntax:~LogSQLRequestIgnore~req1~req2~req3~...~reqN~ + +Example:~LogSQLRequestIgnore~root.exe~cmd.exe~default.ida~favicon.ico~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Lists a series of strings that, if present in the URI, will cause +that request NOT to be logged. This directive is useful for cutting +down on log clutter when you KNOW that you do not want to log requests +for certain objects. (The example above is a genuinely useful one; +it will prevent logging of many common Microsoft-based worm intrusion +attempts, as well as those ridiculous requests for the favicon.) + +Each string is separated by a space, and NO regular expressions or +globbing are allowed. Each string is evaluated as a substring of the +URI using strstr(). +\end{quote} + +\subsubsection{LogSQLRemhostIgnore} + +\begin{lyxcode} +Syntax:~LogSQLRemhostIgnore~host1~host2~host3~...~hostN~ + +Example:~LogSQLRemhostIgnore~localnet.com~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Lists a series of strings that, if present in the REMOTE\_HOST, will +cause that request NOT to be logged. This directive is useful for +cutting down on log clutter when you KNOW that you do not want to +log requests from certain hosts, such as your own internal network +machines. + +Each string is separated by a space, and NO regular expressions or +globbing are allowed. Each string is evaluated as a substring of the +REMOTE\_HOST using strstr(). +\end{quote} + +\subsubsection{LogSQLRefererIgnore} + +\begin{lyxcode} +Syntax:~LogSQLRefererIgnore~ref1~ref2~ref3~...~refN~ + +Example:~LogSQLRefererIgnore~google.com~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Lists a series of strings that, if present in the HTTP\_REFERER, will +cause that request NOT to be logged. This directive is useful for +cutting down on log clutter when you know that you do not want to +log requests from certain referers. + +Each string is separated by a space, and NO regular expressions or +globbing are allowed. Each string is evaluated as a substring of the +HTTP\_REFERER using strstr(). +\end{quote} + +\subsubsection{LogSQLWhichCookie} + +\begin{lyxcode} +Syntax:~LogSQLWhichCookie~cookiename~ + +Example:~LogSQLWhichCookie~Clicks + +Default:~None.~You~must~set~this~if~you~wish~to~capture~cookies. + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +In HTTP, cookies have names to distinguish them from each other. Using +mod\_usertrack, for example, you can give your user-tracking cookies +a name with the CookieName directive. + +If you have activated cookie logging in LogSQLTransferLogFormat, then +LogSQLWhichCookie tells mod\_log\_sql which cookie to log. This is +useful because many times you will be setting and receiving more than +one cookie from a client; without this directive you'd be unable to +choose which cookie is your mod\_usertrack cookie. + +Note: although this was intended for people who are using mod\_usertrack +to set user-tracking cookies, you aren't restricted in any way. You +can choose which cookie you wish to log to the database, and it doesn't +necessarily have to have anything to do with mod\_usertrack. +\end{quote} + +\subsubsection{LogSQLCreateTables} + +\begin{lyxcode} +Syntax:~LogSQLCreateTables~flag + +Example:~LogSQLCreateTables~On~ + +Default:~Off~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +mod\_log\_sql now has the ability to create its tables on-the-fly. +The advantage to this is convenience: you don't have to execute any +SQL by hand to prepare the table. This is especially helpful for people +with lots of virtual hosts (who should also see the LogSQLMassVirtualHosting +directive). + +There is a slight disadvantage: if you wish to activate this feature, +then the user specified by LogSQLLoginInfo must have CREATE privileges +on the database. In an absolutely paranoid, locked-down situation +you may only want to grant your mod\_log\_sql user INSERT privileges +on the database; in that situation you are unable to take advantage +of LogSQLCreateTables. But most people -- even the very security-conscious +-- will find that granting CREATE on the logging database is reasonable. + +This is defined only once in the httpd.conf file. +\end{quote} + +\subsubsection{LogSQLMassVirtualHosting} + +\begin{lyxcode} +Syntax:~LogSQLMassVirtualHosting~flag~ + +Example:~LogSQLMassVirtualHosting~On~ + +Default:~Off~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +If you administer a site hosting many, many virtual hosts then this +option will appeal to you. If you activate LogSQLMassVirtualHosting +then several things happen: +\begin{itemize} +\item the on-the-fly table creation feature is activated automatically +\item the transfer log table name is dynamically set from the virtual host's +name (example: a virtual host www.grubbybaby.com gets logged to table +access\_www\_grubbybaby\_com) +\item which, in turn, means that each virtual host logs to its own segregated +table. Because there is no data shared between virtual servers you +can grant your users access to the tables they need; they will be +unable to view others' data. +\end{itemize} +This is a huge boost in convenience for sites with many virtual servers. +Activating LogSQLMassVirtualHosting obviates the need to create every +virtual server's table and provides more granular security possibilities. + +This is defined only once in the httpd.conf file. +\end{quote} + +\subsubsection{LogSQLPreserveFile} + +\begin{lyxcode} +Syntax:~LogSQLPreserveFile~filename~ + +Example:~LogSQLPreserveFile~offline-preserve~ + +Default:~mysql-preserve~(in~/tmp)~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +mod\_log\_sql writes queries to this local preserve file in the event +that it cannot reach the database, and thus ensures that your high-availability +web frontend does not lose logs during a temporary database outage. +This could happen for a number of reasons: the database goes offline, +the network breaks, etc. You will not lose entries since the module +has this backup. The file consists of a series of SQL statements that +can be imported into your database at your convenience; furthermore, +because the SQL queries contain the access timestamps you do not need +to worry about out-of-order data after the import. + +For security purposes the name you supply will be prepended with \char`\"{}/tmp/\char`\"{} +to force creation of the file in /tmp. + +If you do not define LogSQLPreserveFile then all virtual servers will +log to the same default preserve file (/tmp/mysql-preserve). You can +redefine this on a virtual-host basis in order to segregate your preserve +files if you desire. Note that segregation is not really necessary, +as the SQL statements that are written to the preserve file already +distinguish between different virtual hosts. + +The module will log to error-log if/when it notices a database outage, +and upon database return. You will therefore know when the preserve +file is being used, although it is your responsibility to import the +file. + +The file does not need to be created in advance. It is safe to remove +or rename the file without interrupting Apache, as the module closes +the filehandle immediately after completing the write. The file is +created with the user \& group ID of the running Apache process (e.g. +'nobody' on many Linux distributions). +\end{quote} + +\subsubsection{LogSQLSocketFile } + +\begin{lyxcode} +Syntax:~LogSQLSocketFile~filename~ + +Example:~LogSQLSocketFile~/tmp/mysql.sock~ + +Default:~/var/lib/mysql/mysql.sock~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +At Apache runtime you can specify the MySQL socket file to use. Set +this once in your main server config to override the default value. +This value is irrelevant if your database resides on a separate machine. + +mod\_log\_sql will automatically employ the socket for db communications +if the database resides on the local host. If the db resides on a +separate host the module will automatically use TCP/IP. This is a +function of the MySQL API and is not user-configurable. +\end{quote} + +\subsubsection{LogSQLTransferLogFormat } + +\begin{lyxcode} +Syntax:~LogSQLTransferLogFormat~format-string~ + +Example:~LogSQLTransferLogFormat~huSUTv~ + +Default:~AbHhmRSsTUuv~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Each character in the format-string defines an attribute of the request +that you wish to log. The default logs the information required to +create Combined Log Format logs, plus several extras. Here is the +full list of allowable keys, which sometimes resemble their Apache +counterparts, but do not always: + +\begin{tabular}{|c|l|l|l|l|} +\hline +\textbf{Character}& +\textbf{What is this?}& +\textbf{MySQL DB field}& +\textbf{Recommended column type}& +\textbf{Example}\\ +\hline +\hline +A& +User agent& +agent& +varchar(255)& +Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0)\\ +\hline +b& +Bytes transfered& +bytes\_sent& +int unsigned& +32561\\ +\hline +c& +Text of cookie& +cookie& +varchar(255)& +Apache=sdyn.fooonline.net.130051007102700823\\ +\hline +f& +& +request\_file& +varchar(255)& +\\ +\hline +H& +HTTP request protocol& +request\_protocol& +varchar(10)& +HTTP/1.1\\ +\hline +h& +Name of remote host& +remote\_host& +varchar(50)& +blah.foobar.com\\ +\hline +I& +Ident user info& +remote\_logname& +varchar(50)& +bobby\\ +\hline +m& +HTTP request method& +request\_method& +varchar(6)& +GET\\ +\hline +P& +httpd child PID& +child\_pid& +smallint unsigned& +3215\\ +\hline +p& +httpd port& +server\_port& +smallint unsigned& +80\\ +\hline +R& +Referer& +referer& +varchar(255)& +http://www.biglinkstoyou.com/linkpage.html\\ +\hline +r& +Request in full form& +request\_line& +varchar(255)& +GET /books-cycroad.html HTTP/1.1\\ +\hline +S& +Time of request in UNIX format& +time\_stamp& +int unsigned& +1005598029\\ +\hline +s& +HTTP status of request& +status& +smallint unsigned& +404\\ +\hline +T& +Seconds to service request& +request\_duration& +smallint unsigned& +2\\ +\hline +t& +Time of request in human format& +request\_time& +char(28)& +{[}02/Dec/2001:15:01:26 -0800{]}\\ +\hline +U& +Request in simple form& +request\_uri& +varchar(255)& +/books-cycroad.html\\ +\hline +u& +User info from HTTP authentication& +remote\_user& +varchar(50)& +bobby\\ +\hline +v& +Virtual host servicing the request& +virtual\_host& +varchar(50)& +www.foobar.com\\ +\hline +\end{tabular} + +If you have compiled mod\_log\_sql with WANT\_SSL\_LOGGING, you also +get these: + +\begin{tabular}{|c|l|l|l|} +\hline +\textbf{Character}& +\textbf{MySQL DB field it activates}& +\textbf{MySQL DB field it activatesRecommended column type}& +\textbf{Example}\\ +\hline +\hline +z& +ssl\_cipher& +varchar(25)& +RC4-MD5\\ +\hline +q& +ssl\_keysize& +smallint unsigned& +56\\ +\hline +Q& +ssl\_maxkeysize& +smallint unsigned& +128\\ +\hline +\end{tabular} +\end{quote} + +\section{FAQ} + + +\subsection{Why log to an SQL database?} + +To begin with, let's get it out of the way: logging to a database +is not a panacea. But while there are complexities with this solution, +the benefit can be substantial for certain classes of administrator +or people with advanced requirements: + +\begin{itemize} +\item Chores like log rotation go away, as you can DELETE records from the +SQL database once they are no longer useful. For example, the excellent +and popular log-analysis tool Webalizer (http://www.webalizer.com/) +does not need historic logs after it has processed them, enabling +you to delete older logs. +\item People with clusters of web servers (for high availability) will benefit +the most -- all their webservers can log to a single SQL database. +This obviates the need to collate/interleave the many separate logfiles, +which can be / highly/ problematic. +\item People acquainted with the power of SQL SELECT statements will know +the flexibility of the extraction possibilities at their fingertips. +\end{itemize} +For example, do you want to see all your 404's? Do this: + +\begin{lyxcode} +select~remote\_host,status,request\_uri,bytes\_sent,from\_unixtime(time\_stamp)~from~acc\_log\_tbl~where~status=404~order~by~time\_stamp; + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- + ++~~~~~~~~~~~~~~~~~~~~~~~remote\_host~|~status~|~request\_uri~~~~~~~~~~~~~~|~bytes\_sent~|~from\_unixtime(time\_stamp)| + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- + +|~marge.mmm.co.uk~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~02:30:56~~~~~~| + +|~62.180.239.251~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~02:45:25~~~~~~| + +|~212.234.12.66~~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~03:01:00~~~~~~|~ + +|~212.210.78.254~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~03:26:05~~~~~~|~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ +\end{lyxcode} +Or do you want to see how many bytes you've sent within a certain +directory or site? Do this: + +\begin{lyxcode} +select~request\_uri,sum(bytes\_sent)~as~bytes,count(request\_uri)~as~howmany~from~acc\_log\_tbl~where~request\_uri~like~'\%mod\_log\_sql\%'~group~by~request\_uri~order~by~howmany~desc;~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~|~bytes~~~|~howmany~|~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~/mod\_log\_sql/style\_1.css~~~~~~~~~~|~157396~~|~~~~1288~|~ + +|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~|~2514337~|~~~~~801~|~ + +|~/mod\_log\_sql/mod\_log\_sql.tar.gz~~~|~9769312~|~~~~~456~|~ + +|~/mod\_log\_sql/faq.html~~~~~~~~~~~~~|~5038728~|~~~~~436~|~ + +|~/mod\_log\_sql/INSTALL~~~~~~~~~~~~~~|~1196161~|~~~~~202~|~ + +|~/mod\_log\_sql/directives.html~~~~~~|~1096821~|~~~~~171~|~ + +|~/mod\_log\_sql/CHANGELOG~~~~~~~~~~~~|~424481~~|~~~~~107~|~ + +|~/mod\_log\_sql/README~~~~~~~~~~~~~~~|~796072~~|~~~~~~97~|~ + +|~/mod\_log\_sql/directives-old.html~~|~42480~~~|~~~~~~~6~| + +|~/mod\_log\_sql/index.html~~~~~~~~~~~|~9531~~~~|~~~~~~~3~|~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+ +\end{lyxcode} +Or maybe you want to see who's linking to you? Do this: + +\begin{lyxcode} +select~count(referer)~as~num,referer~from~acc\_log\_tbl~where~request\_uri='/mod\_log\_sql/'~group~by~referer~order~by~num~desc; + ++-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ + +|~num~|~referer~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + ++-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ + +|~271~|~http://freshmeat.net/projects/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + +|~96~~|~http://modules.apache.org/search?id=339~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + +|~48~~|~http://freshmeat.net/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + +|~8~~~|~http://freshmeat.net~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| + +|~7~~~|~http://freshmeat.net/daily/2001/11/30/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + +|~6~~~|~http://freshmeat.net/daily/2001/11/20/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| + ++-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ +\end{lyxcode} +As you can see, there are myriad possibilities that can be constructed +with the wonderful SQL SELECT statement. Logging to an SQL database +can be really quite useful! + + +\subsection{Why use MySQL? Are there alternatives?} + +MySQL is a robust, free, and very powerful production-quality database +engine. It is well supported and comes with detailed documentation. +Many 3rd-party software pacakges (e.g. Slashcode, the engine that +powers Slashdot) run exclusively with MySQL. In other words, you will +belong to a very robust and well-supported community by choosing MySQL. + +That being said, there are alternatives. PostgreSQL is probably MySQL's +leading \char`\"{}competitor\char`\"{} in the free database world. +There is also an excellent module available for Apache to permit logging +to a PostgreSQL database, called pgLOGd \url{(http://www.digitalstratum.com/pglogd/)}. + + +\subsection{Is this code production-ready?} + +By all accounts it is. It is known to work without a problem on many-thousands-of-hits-per-day +webservers. Does that mean it is 100\% bug free? Well, no software +is. But it is well-tested and /believed/ to be fully compatible with +production environments. (The usual disclaimers apply. This software +is provided without warranty of any kind.) + + +\subsection{How well does it perform?} + +mod\_log\_sql scales to very high loads. Apache 1.3.22 + mod\_log\_sql +was benchmarked using the \char`\"{}ab\char`\"{} (Apache Bench) program +that comes with the Apache distribution; here are the results. + +Overall configuration: + +\begin{itemize} +\item Machine A: Apache webserver +\item Machine B: MySQL server +\item Machines A and B connected with 100Mbps Ethernet +\end{itemize} +Webserver configuration: + +\begin{itemize} +\item Celeron 400 128 MB RAM IDE storage +\end{itemize} +Apache configuration: + +\begin{lyxcode} +Timeout~300~ + +KeepAlive~On~ + +MaxKeepAliveRequests~100~ + +KeepAliveTimeout~15~ + +MinSpareServers~5~ + +StartServers~10~ + +MaxSpareServers~15~ + +MaxClients~256~ + +MaxRequestsPerChild~5000~ + +LogSQLTransferLogFormat~AbHhmRSsTUuvc~ + +LogSQLWhichCookie~Clicks~ + +CookieTracking~on~ + +CookieName~Clicks +\end{lyxcode} +\char`\"{}ab\char`\"{} commandline: + +\begin{lyxcode} +./ab~-c~10~-t~20~-v~2~-C~Clicks=ab\_run~http://www.hostname.com/target~ +\end{lyxcode} +( 10 concurrent requests; 20 second test; setting a cookie \char`\"{}Clicks=ab\_run\char`\"{}; +target = the mod\_log\_sql homepage. ) + +Ten total ab runs were conducted: five with MySQL logging enabled, +and five with all MySQL directives commented out of httpd.conf. Then +each five were averaged. The results: + +\begin{itemize} +\item Average of five runs employing MySQL \emph{and} standard text logging: +139.01 requests per second, zero errors. +\item Average of five runs employing \emph{only} standard text logging: +139.96 requests per second, zero errors. +\end{itemize} +In other words, any rate-limiting effects on this particular hardware +setup are not caused by MySQL. Note that although this very simple +webserver setup is hardly cutting-edge -- it is, after all, a fairly +small machine -- 139 requests per second equal over twelve million +hits per day\emph{.} + +If you run this benchmark yourself, take note of three things: + +\begin{enumerate} +\item Use a target URL that is on your own webserver :-). +\item Wait until all your connections are closed out between runs; after +several thousand requests your TCP/IP stack will be filled with hundreds +of connections in TIME\_WAIT that need to close. Do a \char`\"{}netstat +-t|wc -l\char`\"{} on the webserver to see. If you don't wait, you +can expect to see a lot of messages like \char`\"{}ip\_conntrack: +table full, dropping packet\char`\"{} in your logs. (This has nothing +to do with mod\_log\_sql, this is simply the nature of the TCP/IP +stack in the Linux kernel.) +\item When done with your runs, clean these many thousands of requests out +of your database: +\end{enumerate} +\begin{lyxcode} +mysql>~delete~from~access\_log~where~agent~like~'ApacheBench\%';~mysql>~optimize~table~access\_log;~ +\end{lyxcode} + +\subsection{Who's using mod\_log\_sql?} + +Good question! It would be great to find out! If you are a production-level +mod\_log\_sql user, please contact the maintainer, Chris Powell \url{chris@grubbybaby.com}so +that you can be mentioned here. + + +\subsection{How do I extract the data in a format that my analysis tool can understand?} + +mod\_log\_sql would be virtually useless if there weren't a way for +you to extract the data from your database in a somewhat meaningful +fashion. To that end there's a Perl script enclosed with the distribution. +That script (make\_combined\_log.pl) is designed to extract N-many +days worth of access logs and provide them in a Combined Log Format +output. You can use this very tool right in /etc/crontab to extract +logs on a regular basis so that your favorite web analysis tool can +read them. Or you can examine the Perl code to construct your own +custom tool. + +For example, let's say that you want your web statistics updated once +per day in the wee hours of the morning. A good way to accomplish +that would be the following entries in /etc/crontab: + +\begin{lyxcode} +\#~Generate~the~temporary~apache~logs~from~the~MySQL~database~(for~webalizer)~ + +05~04~{*}~{*}~{*}~root~/usr/local/sbin/make\_combined\_log.pl~1~www.grubbybaby.com~>~/var/log/httpd/mysql-grubbybaby + +\#~Run~webalizer~on~httpd~log~ + +30~04~{*}~{*}~{*}~root~/usr/local/bin/webalizer~-c~/etc/webalizer.conf;~rm~-f~/var/log/httpd/mysql-grubbybaby +\end{lyxcode} +Or if you have a newer system that puts files in /etc/cron.daily etc., +create a file called ''webalizer'' in the cron.\_\_\_\_ subdir of +your choice. Use the following as the contents of your file, and make +sure to chmod 755 it when done. + +\begin{lyxcode} +\#!/bin/sh + +/usr/local/sbin/make\_combined\_log.pl~1~www.yourdomain.com~>~/var/log/httpd/templog + +/usr/local/bin/webalizer~-q~-c~/etc/webalizer.conf~ + +rm~-f~/var/log/httpd/templog +\end{lyxcode} +See? Easy. + + +\subsection{Why doesn't the module also replace the Apache ErrorLog?} + +There are circumstances when that would be quite unwise -- for example, +if Apache could not reach the MySQL server for some reason and needed +to log that fact. Without a text-based error log you'd never know +anything was wrong, because Apache would be trying to log a database +connection error to the database... you get the point. + +Error logs are usually not very high-traffic and are really best left +as text files on a web server machine. + + +\subsection{\label{sec:cookie}How can I log mod\_usertrack cookies?} + +A number of people like to log mod\_usertrack cookies in their Apache +TransferLog to aid in understanding their visitors' clickstreams. +This is accomplished, for example, with a statement as follows: + +\begin{lyxcode} +LogFormat~\char`\"{}\%h~\%l~\%u~\%t~\textbackslash{}\char`\"{}\%r\textbackslash{}\char`\"{}~\%s~\%b~\textbackslash{}\char`\"{}\%\{Referer\}i\textbackslash{}\char`\"{}~\textbackslash{}\char`\"{}\%\{User-Agent\}i\textbackslash{}\char`\"{}\char`\"{}~\textbackslash{}\char`\"{}\%\{cookie\}n\textbackslash{}\char`\"{}\char`\"{} +\end{lyxcode} +Naturally it would be nice for mod\_log\_sql to permit the admin to +log the cookie data as well, so as of version 1.10 you can do this. +You need to have already compiled mod\_usertrack into httpd -- it's +one of the standard Apache modules. + +First make sure you have a column called \char`\"{}cookie\char`\"{} +in the MySQL database to hold the cookies, which can be done as follows +if you already have a working database: + +\begin{lyxcode} +alter~table~acc\_log\_tbl~add~column~cookie~varchar(255); +\end{lyxcode} +Next configure your server to set usertracking cookies as follows, +and make sure you include the new 'c' directive in your LogSQLTransferLogFormat, +which activates cookie logging. Here's an example: + +\begin{lyxcode} +~ + +~CookieTracking~on~ + +~CookieStyle~Cookie~ + +~CookieName~Foobar~ + +~LogSQLTransferLogFormat~huSUsbTvRAc~ + +~LogSQLWhichCookie~Foobar~ + + +\end{lyxcode} +The first three lines configure mod\_usertrack to create a COOKIE +(RFC 2109) format cookie called Foobar. The last two lines tell mod\_log\_sql +to log cookies named Foobar. You have to choose which cookie to log +because more than one cookie can/will be sent to the server by the +client. + +FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that +even newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2 +(RFC 2965) format. Just stick with the standard COOKIE format and +you'll be fine. + +Perform some hits on your server and run a select: + +\begin{lyxcode} +mysql>~select~request\_uri,cookie~from~access\_log~where~cookie~is~not~null; + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ + +|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~cookie~|~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~ + +|~/mod\_log\_sql/usa.gif~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~ + +|~/mod\_log\_sql/style\_1.css~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~ + +...etc... +\end{lyxcode} + +\subsection{What if I want to log more than one cookie?} + +No problem. As of version 1.17, you have a choice. If you are just +interested in a single cookie, follow the instructions in section +\ref{sec:cookie} above. That cookie will be logged to a column in +the regular access\_log table. + +However, if you need to log multiple cookies, you'll employ the LogSQLWhichCookies +(note the plural) directive. The cookies you specify will be logged +to a separate table, and entries in that table will be linked to the +regular access\_log entries via the unique ID that is supplied by +mod\_unique\_id. Without mod\_unique\_id the information will still +be logged but you will be unable to correlate which cookies go with +which access-requests. + +LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict, +but you're better off choosing the one you need. + + +\subsection{What are the SSL logging features, and how do I activate them?} + +If you run an SSL-enabled server you may benefit from logging some +SSL details. mod\_log\_sql now supports this ability. By adding certain +characters to your LogSQLTransferLogFormat string you can tell mod\_log\_sql +to log the SSL cipher, the SSL keysize of the connection, and the +Max-keysize that was available. This would let you tell, for example, +which clients were using only export-grade security to access your +secure software area. + +You can compile mod\_log\_sql with SSL logging support if you have +the right packages installed. If you already have an SSL-enabled Apache +then you by definition have the correct packages already installed: +OpenSSL and mod\_ssl. + +You need to ensure that your database is set up to log the SSL data. +Issue the following commands to MySQL once you have your basic access\_log +table built: + +\begin{lyxcode} +alter~table~access\_log~add~column~ssl\_cipher~varchar(25); + +alter~table~access\_log~add~column~ssl\_keysize~smallint~unsigned; + +alter~table~access\_log~add~column~ssl\_maxkeysize~smallint~unsigned; +\end{lyxcode} +Finally configure httpd.conf to activate the SSL fields. Note that +this is only meaningful in a VirtualHost that is set up for SSL. + +\begin{lyxcode} +~ + +~LogSQLTransferLogFormat~AbHhmRSsTUuvcQqz~ + + +\end{lyxcode} +The last three characters (Qqz) in the directive are the SSL ones; +see the directives documentation for details. + +Perform some hits on your server and run a select: + +\begin{lyxcode} +mysql>~select~remote\_host,request\_uri,ssl\_cipher,ssl\_keysize,ssl\_maxkeysize~from~access\_log~where~ssl\_cipher~is~not~null; + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~remote\_host~~~~~~~~~~~~~~|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ssl\_cipher~|~ssl\_keysize~|~ssl\_maxkeysize~| + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.html~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~ + +|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.gif~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~ + +|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.jpg~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~ + +...etc... +\end{lyxcode} + +\subsection{Does mod\_log\_sql connect to MySQL via TCP/IP or a socket?} + +It depends! Actually this isn't determined by mod\_log\_sql. mod\_log\_sql +relies on a connection command that is supplied in the MySQL API, +and that command is somewhat intelligent. When mod\_log\_sql issues +the connect command to MySQL, this intelligent connect command uses +sockets to communicate with MySQL if the specified MySQL database +is on the same machine (because sockets are more efficient than TCP/IP). +However, if the specified MySQL db is on a different machine, mod\_log\_sql +connects using TCP/IP. You don't have any control of which methodology +is used. + +You do have control over where mod\_log\_sql looks for the socket. +The LogSQLSocketFile runtime configuration directive overrides the +default of \char`\"{}/var/lib/mysql/mysql.sock\char`\"{} to whatever +you wish. (Applies to mod\_log\_sql 1.16 or later only.) + + +\subsection{Why do I occasionally see a \char`\"{}connection lost, attempting +reconnect\char`\"{} message in my error-log?} + +This message may appear every now and then in your Apache error log, +especially on very lightly loaded servers. This doesn't mean that +anything is necessarily wrong. Within each httpd child process, mod\_log\_sql +will open (and keep open) a connection to the MySQL server. MySQL, +however, will close connections that haven't been used in a while; +the default timeout is 8 hours. When this occurs, mod\_log\_sql will +notice and re-open the connection. That event is what is being logged, +and looks like this: + +\begin{lyxcode} +{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~connection~lost,~attempting~reconnect + +{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~reconnect~successful +\end{lyxcode} +Reference: MySQL documentation \url{http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away} + + +\subsection{Does mod\_log\_sql work with Apache 2.x?} + +As of this writing, no. The Apache Group significantly altered the +module API with the release of Apache 2.0. All modules written for +1.3, including mod\_log\_sql, will not work with 2.0. + +mod\_log\_sql will eventually be ported to Apache 2.x, but not immediately. +It is going to take some time, and there are other features that have +higher priority. Please sign up for the announcements list (on the +main website) or monitor the website for updates to learn when the +port (and other releases) are available. + +If you're a {*}NIX user, stick with Apache 1.3.x for now. +Major modules like mod\_ssl and PHP are not even ready for 2.0 yet, +and the main benefits in 2.0 are for Win32 users anyway. Apache 1.3.x +is rock-stable and performs equally well on {*}NIX as 2.0. + + +\subsection{I have discovered a bug. Who can I contact?} + +Please contact the maintainer \url{chris@grubbybaby.com}! Your comments, +suggestions, bugfixes, bug catches, and usage testimonials are always +welcome. As free software, mod\_log\_sql is intended to be a community +effort -- any code contributions or other ideas will be fully and +openly credited, of course. +\end{document} -- cgit