path: root/INSTALL

$Id: INSTALL,v 1.10 2002/11/14 03:51:34 helios Exp $


Requirements
============

* 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.

* 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.

* 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".

* 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.

* 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.

* 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.


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.


Installation as an Apache DSO (Preferred)
=========================================

For folks interested in using this module as an Apache DSO:

0) Perform all the following steps as root so that you have install
   privs, etc.

1) Unpack the archive into a working directory.

   # tar zxf mod_log_sql.tar.gz -C /usr/local/src
   # cd /usr/local/src/mod_log_sql

2) Edit Makefile for your system.

   NECESSARY:
   - The location where you installed Apache -- usually /usr/local/apache,
     'locate apxs' can help you find it.
   - The location of your MySQL libraries, find using 'locate libmysqlclient'
   - The location of your MySQL header files, find using 'locate mysql.h'

   OPTIONAL if you have included mod_ssl in Apache and want to log SSL data
   such as keysize and cipher type:
   - The location of your SSL header files, find using 'locate mod_ssl.h'

   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, e.g. #MODSSLHDRS=/usr/include/...

3) Instruct apxs to compile the module as a DSO.

   # make dso

   You should see output similar to the following:

   /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

   You should see no errors and have a file called "mod_log_sql.so" in your
   directory.

4) Instruct apxs to install the DSO.

   # make dsoinstall

   You should see output similar to the following:

   /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

5) Module ordering within httpd.conf is important.  If you are logging
   SSL, you must make sure that

       LoadModule ssl_module libexec/libssl.so

   comes before

       LoadModule sql_log_module libexec/mod_log_sql.so

   If you don't, you will get this error when you start Apache:
   /usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup
   /usr/local/apache/bin/apachectl startssl: httpd could not be started

   (Because mod_log_sql doesn't yet have the required symbols that mod_ssl
   provides.)

6) Now skip below to the "Configuration" section.


Installation as a static module compiled into httpd
===================================================

0) Perform all the following steps as root so that you have install
   privs, etc.

1) Unpack the archive into a working directory.

   # tar zxf mod_log_sql.tar.gz -C /usr/local/src
   # cd /usr/local/src/mod_log_sql

2) Edit Makefile for your system.

   NECESSARY:
   - The location where you installed Apache -- usually /usr/local/apache,
     'locate apxs' can help you find it.
   - The location of your Apache *sources*, find using 'locate ABOUT_APACHE'
   - The location of your MySQL header files, find using 'locate mysql.h'
   - The location of your MySQL libraries, find using 'locate libmysqlclient'

   OPTIONAL if you have included mod_ssl in Apache and want to log SSL data
   such as keysize and cipher type:
   - The location of your mod_ssl header files, find using 'locate mod_ssl.h'
   - The location of your OpenSSL header files, find using 'locate x509.h'
   - The location of your db1 header files, find using 'locate mpool.h'

   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/...

3) # make static

4) # make statinstall

5) Change to your Apache source dir.

   # cd /usr/local/src/apache-1.3.22/src

6) Re-make your httpd binary as follows.

   6a) Edit Configuration.apaci as follows...

       * Append the following string to the EXTRA_LIBS= line. ("/usr/lib/mysql"
	     is from step 2, where your MySQL libraries live):

	   -L/usr/lib/mysql -lmysqlclient -lm -lz

       * Find the mod_log_config.o line, and add this line immediately after it:

	   AddModule modules/sql/mod_log_sql.o

   6b) # cp Configuration.apaci Configuration

   6c) # ./Configure

   6d) # make

   6e) # strip httpd

7) Test your new apache binary:

   # ./httpd -l

   You should see something like:

	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...

8) 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.

   # /etc/rc.d/init.d/httpd stop
   # mv /usr/local/Apache/bin/httpd ~/httpd-save
   # cp -f ./httpd /usr/local/Apache/bin/


Configuration
=============

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

1)  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.

	We still need to have a logging database created and ready, so
	run the MySQL command line client and create a database:

	# mysql -uadmin -pmypassword
    mysql> create database apachelogs;

    If you want to hand-create the tables, run the enclosed 'create-tables'
	SQL script as follows:

    mysql> source create_tables.sql

2)  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.

	mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';

	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:

    mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';

3) 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:

      log=/var/log/mysql-messages

    Then restart MySQL.

4) Tell the module what database to use and the appropriate authentication
   information.

     OUR 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".

   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.

   LogSQLDatabase apachelogs
   LogSQLLoginInfo dbmachine.foo.com loguser l0gger
   LogSQLCreateTables on

   If your database resides on localhost instead of another host, specify
   the MySQL server's socket file as follows:

   LogSQLSocketFile /your/path/to/mysql.sock

5) 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.  The LogSQLTransferLogTable
   directive is the minimum required to log -- other directives simply
   tune the module's behavior.

   <VirtualHost 1.2.3.4>
        [snip]

        LogSQLTransferLogTable access_log

        [snip]
   </VirtualHost>

6)  Restart apache.

    # /etc/rc.d/init.d/httpd start

7)  Load your web site in a browser to trigger some hits, then confirm that
    the entries are being successfully logged:

    # mysql -hmysql.host.com -umysqladmin -p -e "select * from access_log" apachelogs
    Enter password:

    +---------------------------------------------------+-------------+-------------+------------------+------------------+------------+--------+------------+------------------------------------+
    | remote_host                                       | remote_user | request_uri | request_duration | virtual_host     | time_stamp | status | bytes_sent | referer                            |
    +---------------------------------------------------+-------------+-------------+------------------+------------------+------------+--------+------------+------------------------------------+
	[snipped lines]
	.
	.
	.
    +---------------------------------------------------+-------------+-------------+------------------+------------------+------------+--------+------------+------------------------------------+

    You 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.

8) 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:
   - Improper privileges set up in the MySQL database
   - You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry
   - You didn't specify the right host

   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 "#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.

9) You can now activate the advanced features of mod_log_sql.  These are all
   described in the online directive documentation:
   http://www.grubbybaby.com/mod_log_sql/directives.html