Mod log sql is an awesome way of getting away from those old log files and is really handy for both web development and system administration. It’s been a while since I’ve posted a blog and this is something I’ve never done before so here we go. If you are ever doing any kind of parsing of your apache access log and run a relatively high traffic website (the one I’m doing this for, mp3crib.com, averages over 5,000 hits a day and some days gets over 10,000) you will begin eating up huge amounts of CPU and memory (if storing the information in an array). Well lately it’s gotten so bad that my PHP script fails due to memory exhaustion. I can’t have that. I heard somewhere that databases are better than flat files, go figure. If I knew a lower level language then I would of course write my parser in that…but I don’t. So luckily libapache2-mod-log-sql exists.
It’s in the debian unstable repository. To install it you will need to edit your /etc/apt/sources.list file switching from the stable branch (currently etch) to the unstable branch (sid). These two lines should now look like this:
deb http://ftp.debian.org/debian/ sid main
deb-src http://ftp.debian.org/debian/ sid main
Now perform the following commands: aptitude update and aptitude install libapache2-mod-log-sql libapache2-mod-log-sql-mysql libapache2-mod-log-sql-dbi to install the package. Now change your /etc/apt/sources.list back to what it was or your server’s life could turn into hell. I used the documentation located here http://www.outoforder.cc/projects/apache/mod_log_sql/docs/ to install. The documentation says you will have a create_tables.sql script you can run inside of sql, but its a lie. I instead had to download the package as if I were going to compile from source and yoink out that lovely script. For you, I have included the table creation syntax below.
First though log into mysql as root, create a database create table apachelogs; use the database use apachelogs and run the following queries (of course if you have phpMyAdmin installed you can use that as well).
Now would be a good point in time to setup a user for this so grant insert, create, select, update on apachelogs.* to loguser@my.apachemachine.com identified by ‘password’; will do that for you.
Now in your httpd.conf file add the following lines:
LogSQLLoginInfo mysql://apachelog:password@localhost/apachelogs
LogSQLDBParam socketfile /var/run/mysqld/mysqld.sock
LogSQLCreateTables On
LogSQLMassVirtualHosting Off
LogSQLMachineID localhost
Now in your /etc/apache2/sites-enabled/000-default file:
LogSQLTransferLogTable apachelog
LogSQLScoreDomain yourdomain.com
Restart your apache server and you should have a table in the apachelog db called apachelog. If you hit your web server then you should see a log in there. You probably want to add a new column to the database called log_id as a bigint(20) set it as an auto_increment primary key so you have something to index on. This will make querying the database much fastera s this table will get huge this is very important. Let me know if you have any problems with these steps.