Mit Apache in MySQL loggen
- Urspruenglicher Autor: Dirk Deimeke
- Urspruengliches Datum: 23.09.2011
Der Apache-Webserver erzeugt im Standard Logdateien, was auch gut ist. Manchmal braucht man für Auswertungen die Daten allerdings in einer Datenbank. Wie beispielsweise bei der Analyse der Downloadzahlen eines Podcasts. Genau fuer diesen Zweck wurde das Modul mod_log_sql geschrieben. Hier geht es jetzt um die Einrichtung und die Abfrage der Daten.
Auf Debian basierten Systemen wird mod_log_sql mittels apt-get install libapache2-mod-log-sql-mysql installiert. Bei uns soll es so sein, dass die Daten in der Datenbank apache landen und dort soll es pro Domain eine Tabelle geben.
CREATE USER 'apache'@'localhost' IDENTIFIED BY '***'; GRANT USAGE ON * . * TO 'apache'@'localhost' IDENTIFIED BY '***' ; CREATE DATABASE IF NOT EXISTS `apache` ; GRANT ALL PRIVILEGES ON `apache` . * TO 'apache'@'localhost';
In /etc/apache2/sites-available erstellen wir dann eine Datei fuer die globale Konfiguration, bei uns 000global:
LogSQLLoginInfo mysql://apache:PASSWORD@localhost/apache
LogSQLCreateTables on
LogSQLDBParam socketfile /var/run/mysqld/mysqld.sock
LogSQLTransferLogFormat IAbPcMfRhluTrmHtUapzqQsSV
Wichtig, diese Konfiguration muss ausserhalb einer VirtualHost-Directive liegen.
Fuer den VirtualHost richten wir jetzt innerhalb der vHost-Konfiguration den Namen der Tabelle mit LogSQLTransferLogTable deimhart_net ein. Da wir nur den Download von mp3- und ogg-Dateien zaehlen wollen, schreiben wir noch LogSQLRequestAccept .mp3 .ogg dazu (wenn man alles in die Datenbank protokollieren moechte, kann das entfallen). Die Tabellen werden automatisch erstellt, siehe LogSQLCreateTables on.
Jetzt aktivieren wir alles noch mit folgenden Befehlen auf der Kommandozeile:
a2ensite 000global # Aktivierung der globalen Konfiguration apache2ctl graceful # Pruefen der Konfiguration und Neustart des Apache
Damit ist das Logging in die Datenbank abgeschlossen, um die Downloads auszuwerten, genügen drei einfache Schritte:
CREATE TABLE deimhart_episoden ( kurzname CHAR(30) NOT NULL, langname CHAR(90) NOT NULL, laenge BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (`kurzname`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Erzeugt eine Tabelle, in die wir alle Dateien, die uns interessieren eintragen.
echo "truncate table deimhart_episoden;" > deimhart_episoden.sql ls -l /srv/www/deimhart.net/uploads/*.{mp3,ogg} | awk '{print $5, $8}' | while read laenge langname do echo "insert into deimhart_episoden values ('$(basename ${langname})','${langname}',${laenge});" done >> deimhart_episoden.sql mysql apache < deimhart_episoden.sql
Liest alle Dateien und deren Laengen aus dem Filesystem und schreibt das in die Datenbank.
SELECT e.kurzname,SUM(n.bytes_sent)/e.laenge FROM deimhart_episoden e, deimhart_net n WHERE e.langname=n.request_file
Obige Abfrage gibt abschliessend die Anzahl der Downloads aus. Berechnet wird sie durch die Summe der übertragenen Bytes pro Episode geteilt durch die Laenge der entsprechenden Episode.