LogToMysql - piped logging from Apache to MySQL

Home

Readme

Install

Troubleshooting

Bugs & TODO

Download

Install

1. Make sure you have Apache installed, and can edit its configuration file, usually it is something like

   /etc/httpd/httpd.conf
   or
   /usr/local/apache/conf/httpd.conf

2. Make sure you have MySQL 4 installed and can edit its configuration file, usually it is something like

   /etc/my.cnf

   You need to have the development libraries for MySQL available. If you installed it from source this is no problem, but if you installed from RPMs or other packages, you should check that the development packages are installed. Note that LogToMysql will only work with MySQL version 4.0 and above.

3. Untar/unzip the LogToMysql software into wherever you like to compile software.

4. Edit the LogToMysql Makefile to correctly point to the headers and libraries for MySQL on your system. Typical locations to look in are

   Includes:
   /usr/local/mysql/include/mysql
   /usr/local/include/mysql
   /usr/include/mysql

   Libraries:
   /usr/local/mysql/lib/mysql
   /usr/local/lib/mysql
   /usr/lib/mysql

5. Type
   make

   This should compile everything in a couple of seconds. If you are having problems with the MySQL libraries not being found it can help to set up the directories

   /usr/local/include/mysql
   /usr/local/lib/mysql

   and copy or link the required files from wherever your distribution has put them.

6. Two executable files are produced

   logtomysql
   logerrortomysql

   for access logs and error logs respectively. Copy these to

   /usr/local/bin

   and make sure that root (or whichever user Apache starts as) can execute them.

7. Set up the MySQL database for logging. Assuming that you are connected to the MySQL server with permissions to create a database and user for logging, type the following to set up your database (change passwords and IP numbers for your set-up):

   CREATE DATABASE weblog;

   For logging on the local machine

   GRANT ALL PRIVILEGES ON weblog.* TO weblogger@localhost IDENTIFIED BY 'secret';

   or if logging to a remote machine from 192.168.0.2

   GRANT ALL PRIVILEGES ON weblog.* TO weblogger@192.168.0.2 IDENTIFIED BY 'secret';

   CREATE TABLE log (hostname text,datetime varchar(100),
   url text,refer text,agent varchar(150),
   ident varchar(150),resp_code int unsigned,xfer_size int unsigned);

   CREATE TABLE errorlog (datetime timestamp(14) NOT NULL, message varchar(255));

   If you need to use an existing user or database, just create the tables, and modify the options in section (8) below to match.

8. LogToMysql reads passwords and other configuration information from the MySQL configuration file. Details of the entries are:

   • Socket is the local unix socket for connections to MySQL running on the same server as Apache. Set to NULL if logging to a remote machine.
   • Database is the name of the database in which your log files will be stored. It needs to agree with whatever you set up in section 7.
   • Host is 'localhost' or remote host IP on which MySQL is running.
   • Port should be 0 for logging to the localhost using a socket, otherwise, 3306 for a standard MySQL installation on a remote machine.
   • User is the MySQL user name through which logging will take place. It needs to agree with whatever you set up in section 7.
   • Password is the MySQL user password. It needs to agree with whatever you set up in section 7.
   • Logging is the level of information generated by the LogToMysql programs and send to their log files. It has nothing to do with the Apache logging level. Set to zero for minimal information, 1 for important messages, and 2 to show you all the queries sent to MySQL. Level 1 is usually best.
   • Logfile is the place that LogToMysql programs will use to store their log information. Different files are needed for logtomysql and logerrortomysql.
   • Sysuser and sysgroup are the username and group under which logtomysql and logerrortomysql will run once they have started. Just like Apache itself, it is best to avoid running these logging programs as root.

   Examples of the configuration information are below, which you will need to edit for your system. Examples are given for logging on the same machine as the Apache installation, and for logging to a remote machine.

   Example 1:
   If MySQL is running on the same machine as the Apache server, edit and copy the lines below into /etc/my.cnf (or whichever file is used on your system). Set the 'socket' to match the value in the [mysqld] section of my.cnf.

   [logtomysql]
   socket = /tmp/mysql.sock
   database = weblog
   host = localhost
   port = 0
   user = weblogger
   password = secret
   logging = 1
   logfile = /tmp/logtomysql.log
   sysuser = apache
   sysgroup = apache

   [logerrortomysql]
   socket = /tmp/mysql.sock
   database = weblog
   host = localhost
   port = 0
   user = weblogger
   password = secret
   sysuser = apache
   sysgroup = apache
   logfile = /tmp/logerrortomysql.log
   logging = 1

   Example 2:
   If MySQL is on a remote machine, edit and copy the lines below into /etc/my.cnf ON THE MACHINE ON WHICH APACHE IS RUNNING. Create the file if it doesn't exist. Change 'host' to the machine on which MySQL is running.

   [logtomysql]
   socket = NULL
   database = weblog
   host = 192.168.0.3
   port = 3306
   user = weblogger
   password = secret
   logging = 1
   logfile = /tmp/logtomysql.log
   sysuser = apache
   sysgroup = apache

   [logerrortomysql]
   socket = NULL
   database = weblog
   host = 192.168.0.3
   port = 3306
   user = weblogger
   password = secret
   sysuser = apache
   sysgroup = apache
   logfile = /tmp/logerrortomysql.log
   logging = 1

   When logging both access and errors both sections of options are needed, even though there might be duplication between them. If you only want to log access just include the [logtomysql] and not the [logerrortomysql], and visa versa if you just want to log errors.

9. Configure Apache to use the piped logs.

   Access Logs
   Anywhere that you would normally use piped logs, use lines like the ones below to use LogToMysql. This works in both server wide and virtual server domains.

   LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
   CustomLog "|/usr/local/bin/logtomysql" combined

   making sure that the logtomysql binary is in the place you enter here.

   Error Logs
   Using logerrortomysql in the server wide ErrorLog directive is dangerous, and likely to cause you trouble. This is because the server may attempt to log information during start-up before the piped logging process is fully up and running. Virtual hosts for SSL are particularly bad at causing trouble this way.

   To prevent problems use a file based ErrorLog directive for the server wide configuration, and then put a logerrortomysql based ErrorLog directive inside each virtual host. Example:

   #Virtual host for SSL
   <VirtualHost *:443>
   #Rest of virtual host config goes here
   LogLevel warn
   ErrorLog "|/usr/local/bin/logerrortomysql"
   </VirtualHost>
   #Virtual host for port 80
   </VirtualHost *:80>
   #Rest of virtual host config goes here
   LogLevel warn
   ErrorLog "|/usr/local/bin/logerrortomysql"
   </VirtualHost>
   #Server wide error log
   ErrorLog /usr/local/apache/logs/error_log

   Again, make sure that the logerrortomysql binary is in the place you enter in the httpd.conf file.

10. Start or re-start Apache

    Once you have set all this up, restart Apache and logging will begin to the database. If it doesn't, see troubleshooting.