Up: Configuration --- Next: A very basic logging setup in Apache

Preparing MySQL for logging

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 configuration. Full documentation of all available run-time directives is available in section Configuration Directive Reference .

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 some overhead. In this basic setup we'll just let the module create tables for us.

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

   # mysql -uadmin -p
   Enter password:
   mysql> create database apachelogs;

3. If you want to hand-create the tables, run the enclosed 'create-tables' SQL script as follows ("create_tables.sql" needs to be in your current working directory).

   mysql> use apachelogs
   Database changed
   mysql> source create_tables.sql

4. 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';

5. You may be especially security-paranoid and want "loguser" to not have "create" capability within the "apachelogs" database. You can disable that privilege, but the cost is that you will not be able to use the module's on-the-fly table creation feature. If that cost is acceptable, hand-create the tables as described in step and use the following GRANT statement instead of the one above:

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

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

   # /etc/rc.d/init.d/mysql restart