$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. [snip] LogSQLTransferLogTable access_log [snip] 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