Ведение журнала Apache с помощью MySQL

Задача
Вы не хотите использовать MySQL для ведения журнала доступа к нескольким страницам. Вы хотите записывать обращения ко всем страницам, при этом не хотите явно помещать на каждую страницу механизм протоколирования.

Решение
Сообщите Apache, что для протоколирования доступа ему следует использовать MySQL.

Обсуждение
Применение MySQL для Web не ограничивается формированием и обработкой страниц. Вы можете использовать MySQL для того, чтобы помочь работесамого веб-сервера. Например, большая часть серверов Apache ведут журнал веб-запросов в файле. Но можно вместо этого отправлять записи журнала в программу, из которой они могут быть записаны куда угодно, например в базу данных. Если записи хранятся в базе данных, а не в файле, журнал становится более структурированным, и к нему можно применять методы анализа SQL. Средства анализа журнального файла могут создаваться для обеспечения некоторой гибкости, но зачастую все ограничивается выбором и фильтрацией итогов. Сложно просить у программы вывести информацию, для предоставления которой она не создавалась. Если же записи журнала хранятся в таблице, гибкость увеличивается.


Хотите получить какой-то определенный отчет? Напишите соответствующие предложения SQL. Для вывода отчета в указанном формате выдавайте запросы в API и пользуйтесь возможностями вашего языка программирования по организации вывода.

Разнеся формирование записи журнала и ее хранения в разные процессы, вы обеспечиваете дополнительную гибкость. Появляется возможность, например, отправки журналов с нескольких веб-серверов на один сервер MySQL или отправки разных журналов, созданных одним веб-сервером, разным серверам MySQL.

В этом разделе рассказано о настройке протоколирования веб-запросов Apache в MySQL и рассмотрено несколько полезных итоговых запросов. Настройка протоколирования в базе данных Протоколирование Apache управляется директивами конфигурационного файла httpd.conf. Например, стандартно используются директивы LogFormat и CustomLog:

LogFormat "%h %l %u %t \"%r\" %>s %b" common
CustomLog /usr/local/apache/logs/access_log common

Строка LogFormat определяет формат записей журнала и дает ему мнемоническое имя common. Директива CustomLog указывает на то, что строки в таком формате должны записываться в файл access_log каталога Apache logs.


Для ведения протокола в MySQL вместо файла используйте следующую процедуру:

• Определите, какие значения вы хотите хранить, и создайте таблицу с соответствующими столбцами.

• Напишите программу, которая бы читала строки журнала Apache и записывала их в базу данных.

• Задайте строку LogFormat, указывающую, как записывать строки журнала в формате, необходимом программе, и директиву CustomLog, которая сообщает Apache, что журнал следует записывать в программе, а не в файле.Предположим, что вы хотите записывать дату и время каждого запроса, хост запрашивающего клиента, метод запроса и путь URL, код состояния, количество переданных байтов и пользовательского агента (обычно название броузера или программы-паука). Таблицу, содержащую столбцы для всех этих значений, можно создать так:

CREATE TABLE httpdlog
(
dt DATETIME NOT NULL, # дата запроса
host VARCHAR(255) NOT NULL, # хост клиента
method VARCHAR(4) NOT NULL, # метод запроса (GET, PUT, и т. д.)
url VARCHAR(255) BINARY NOT NULL, # URL
status INT NOT NULL, # статус запроса
size INT, # количество переданных байтов
agent VARCHAR(255) # пользовательский агент
);

Большинство строковых столбцов относятся к типу VARCHAR и не чувствительны к регистру.


Исключением является url, объявленный как двоичная строка, что соответствует серверу, работающему в системе, имена файлов которой чувствительны к регистру. Если вы используете сервер, для которого регистр URL не имеет значения, то можете убрать из определения слово BINARY.

В приведенное выше определение таблицы httpdlog не входит ни один индекс. Необходимо их добавить, так как в противном случае все итоговые запросы будут выполняться все медленнее при увеличении объема таблицы.

Выбор столбцов для индексирования зависит от того, какие запросы вы планируете выбирать для исследования содержимого таблицы. Например, для запросов, анализирующих распределение значений клиентских хостов, индекс для столбца host будет предпочтительнее.

Теперь нужно написать программу обработки строк журнала, сформированного Apache и вставки их в таблицу httpdlog. Сценарий httpdlog.pl устанавливает соединение с сервером MySQL, затем в цикле считывает строки ввода. Он разбирает каждую строку на значения столбцов и вставляет результат в базу данных. Когда Apache завершает работу, канал к программе протоколирования закрывается. Тогда httpdlog.pl видит на входе конец файла и завершает цикл, отключается от MySQL и заканчивает работу.

#! /usr/bin/perl -w
# httpdlog.pl – протоколирование запросов Apache в таблице httpdlog
use strict;
use lib qw(/usr/local/apache/lib/perl);
use Cookbook;
my $dbh = Cookbook::connect ();
my $sth = $dbh->prepare (qq{
INSERT INTO httpdlog (dt,host,method,url,status,size,agent)
VALUES (?,?,?,?,?,?,?)
});while (<>) # цикл чтения ввода
{
chomp;
my ($dt, $host, $method, $url, $status, $size, $agent)
= split (/\t/, $_);
# заменить "-" на NULL в некоторых столбцах
$size = undef if $size eq "-";
$agent = undef if $agent eq "-";
$sth->execute ($dt, $host, $method, $url, $status, $size, $agent);
}
$dbh->disconnect ();
exit (0);

Установите сценарий httpdlog.pl в каталог, где Apache сможет его обнаружить.


В моей системе корневым каталогом Apache является /usr/local/apache, поэтому разумно назначить каталогом установки /usr/local/apache/bin. Путь к данному каталогу нам скоро понадобится – при создании директивы CustomLog, указывающей Apache на необходимость протоколирования в программе.

Сценарий httpdlog.pl считает, что строки ввода содержат значения столбцов httpdlog, разделенные символами табуляции (для простоты разбиения строк ввода), так что Apache должен записывать журнал именно в таком формате. Спецификаторы поля LogFormat для вывода соответствующих значений таковы:

%{%Y-%m-%d %H:%M:%S}
Дата и время запроса в формате даты MySQL DATETIME.
%h
Хост запрашивающего клиента.
%m
Метод запроса (GET, POST и т. д.).
%U
Путь URL.
%>s
Код состояния.
%b
Количество переданных байт.
%{User-Agent}i
Пользовательский агент.

Для определения формата протоколирования с именем mysql, выводящего перечисленные значения, разделенные табуляциями, добавим в файл httpd.conf следующую директиву LogFormat:

LogFormat "%{%Y-%m-%d %H:%M:%S}t\t%h\t%m\t%U\t%>s\t%b\t%{User-Agent}i" mysql

Уже почти все готово.


У нас есть таблица, читающая ее программа и формат mysql для создания записей журнала. Остается лишь сообщить Apache о том, что следует передавать записи в сценарий httpdlog.pl. Однако пока вы не уверены в корректности формата вывода и в том, что программа может соответствующим образом обрабатывать записи журнала, преждевременно сообщать Apache о ведении журнала в программе. Для упрощения тестирования и отладки выведем протокол Apache в формате mysql в файл. Тогда, посмотрев на файл, вы сможете проверить формат вывода и использовать его как ввод для httpdlog.pl, чтобы проконтролировать корректность работы программы. Укажем Apache на необходимость записи строк в формате mysql в файл test_log каталога протоколов при помощи директивы CustomLog:

CustomLog /usr/local/apache/logs/test_log mysql

Перезапускаем Apache, чтобы новые директивы протоколирования вступили в силу. После того как ваш веб-сервер получит несколько запросов, посмотрите на файл test_log. Проверьте, совпадает ли его содержимое с ожидаемым, затем передайте в httpdlog.pl. Если вы находитесь в каталоге Apache logs, и каталоги bin и logs расположены внутри корневого каталога Apache, то команда выглядит так:

% ../bin/httpdlog.pl test_log

После завершения работы httpdlog.pl исследуйте таблицу httpdlog, чтобы проверить, все ли в ней хорошо. Если вас все устраивает, сообщите Apache об отправке записей журнала непосредственно в httpdlog.pl, изменив директиву CustomLog так:

CustomLog "|/usr/local/apache/bin/httpdlog.pl" mysql

Символ | в начале имени файла указывает Apache на то, что httpdlog.pl – это программа, а не файл. Перезапустите Apache, и новые записи будут появляться таблице httpdlog по мере обращения посетителей к вашему сайту.

Ничто из сделанного к этому моменту не меняет протоколирования, осуществлявшегося изначально. Например, если раньше вы вели протокол в файле access_log, ничего не изменилось. То есть Apache будет отправлять записи и в исходный файл журнала, и в MySQL. Если вы этого и хотели, отлично, Apache не интересует, в скольких направлениях ведется протоколирование.

Но в этом случае используется больше дискового пространства. Для отмены протоколирования в файле закомментируйте исходную директиву CustomLog, поместив перед ней символ #, затем перезапустите Apache.

Анализ файла журнала
Теперь Apache ведет протокол в базе данных, но что можно сделать с этойинформацией? Все зависит от того, какие сведения вам интересны. Приведем ряд вопросов, на которые без труда отвечает MySQL:

• Сколько записей содержится в журнале запросов?
SELECT COUNT(*) FROM httpdlog;

• Со скольких различных хостов отправлялись запросы?
SELECT COUNT(DISTINCT host) FROM httpdlog;

• Сколько разных страниц запросили клиенты?
SELECT COUNT(DISTINCT url) FROM httpdlog;

• Какие страницы входят в десятку наиболее популярных?
SELECT url, COUNT(*) AS count FROM httpdlog
GROUP BY url ORDER BY count DESC LIMIT 10;

• Сколько запросов было получено для этих бесполезных файлов favicon.ico, которые любят проверять некоторые броузеры?
SELECT COUNT(*) FROM httpdlog WHERE url LIKE '%/favicon.ico%';

• Какой диапазон дат охватывает журнал?
SELECT MIN(dt), MAX(dt) FROM httpdlog;

• Сколько запросов было получено в каждый из дней?
SELECT FROM_DAYS(TO_DAYS(dt)) AS day, COUNT(*) FROM httpdlog GROUP BY day;

Ответ на этот вопросы требует выделения составляющей времени дня из значений dt для группировки запросов по указанной дате. Запрос использует функции TO_DAYS() и FROM_DAYS() для преобразования значений DATETIME в DATE. Однако если вы собираетесь создать множество запросов, использующих только составляющую даты значений dt, будет эффективнее создать таблицу httpdlog с отдельными столбцами DATE и TIME, изменить директиву LogFormat для вывода даты и времени в виде отдельных значений и соответствующим образом изменить httpdlog.pl. Тогда можно будет работать непосредственно с датами запросов, не отбрасывая время. Для еще большего повышения производительности можно индексировать столбец дат.

• Как запросы распределяются по времени суток?
SELECT HOUR(dt) AS hour, COUNT(*) FROM httpdlog GROUP BY hour;

• Каково среднее количество запросов, получаемых за день?
SELECT COUNT(*)/(TO_DAYS(MAX(dt)) - TO_DAYS(MIN(dt)) + 1) FROM httpdlog;

Числитель – общее количество запросов в таблице. Знаменатель – количество дней, для которых есть записи.

• Какой самый длинный URL сохранен в таблице?
SELECT MAX(LENGTH(url)) FROM httpdlog;

Если столбец url определен как VARCHAR(255), а запрос выводит значение 255, то вероятно, некоторые значения URL оказались слишком длинными для столбца и были усечены с конца. Во избежание подобных ситуаций можно преобразовать столбец в BLOB или TEXT (в зависимости от того, интересует ли вас чувствительность значений к регистру). Например, если выхотите хранить чувствительные к регистру значения длиной до 65 535 символов, измените столбец url так:

ALTER TABLE httpdlog MODIFY url BLOB NOT NULL;

• Каково общее количество отправленных байт и среднее количество байт для запроса?
SELECT
COUNT(size) AS requests,
SUM(size) AS bytes,
AVG(size) AS 'bytes/request'
FROM httpdlog;

Запрос использует COUNT(size), а не COUNT(*) для подсчета только запросов с значением size не-NULL. (Если клиент запрашивает страницу дважды, сервер может ответить на второй запрос отправкой заголовка, сообщающего о том, что страница не изменилась с момента последнего посещения.

В такой ситуации запись журнала для запроса будет содержать значение NULL в столбце size.)

• Каким был трафик для каждой из разновидностей файлов (по расширению, например .html, .jpg или .php)?

SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(url,'?',1),'.',-1) AS extension,
COUNT(size) AS requests,
SUM(size) AS bytes,
AVG(size) AS 'bytes/request'
FROM httpdlog
WHERE url LIKE '%.%'
GROUP BY extension;

Инструкция WHERE выбирает только значения url, включающие точку, чтобы исключить из рассмотрения путевые имена без расширения имени файла. Для извлечения расширения из списка столбцов вывода внутренний вызов SUBSTRING_INDEX() удаляет строку параметров, находящуюся в конце URL (если она присутствует), и оставляет все остальное. (Тем самым значение типа /cgi-bin/script.pl?id=43 превращается в /cgi-bin/script.pl. Если у значения не было параметрической составляющей, SUBSTRING_INDEX() возвращает всю строку.) Внешний вызов SUBSTRING_INDEX() удаляет все вплоть до самой правой точки результата (включая ее), оставляя только собственно расширение.

Еще о протоколировании
Я выбрал простой способ связывания Apache с MySQL – создание небольшого сценария, взаимодействующего с MySQL и сообщающего Apache о необходимости ведения журнала не в файле, а в сценарии. Этот способ хорошо работает, если все запросы регистрируются в одном файле, но, естественно, подходит не для любой конфигурации Apache. Например, если в файле httpd.conf определены виртуальные серверы, вы можете указать для каждого из нихотдельные директивы CustomLog. Для протоколирования их всех в MySQL можно изменить каждую директиву, указав на запись в httpdlog.pl, но тогда для каждого виртуального сервера будет работать свой процесс протоколирования. Возникает два вопроса:

• Как сопоставить записи журнала с соответствующим виртуальным сервером? Можно создать отдельную таблицу для каждого сервера и изменить сценарий httpdlog.pl так, чтобы он принимал аргумент, определяющий, какую из таблиц следует использовать. Другой способ заключается в добавление столбца virt_host в таблицу httpdlog и изменении httpdlog.pl так, чтобы он принимал аргумент имени хоста, указывающий имя сервера для записи в столбец virt_host.

• Действительно ли нужно столько работающих процессов httpdlog.pl? Если у вас есть несколько виртуальных серверов, то можно подумать об использовании модуля протоколирования, устанавливаемого непосредственно в Apache. Некоторые из них разрешают мультиплексное протоколирование для нескольких виртуальных хостов в рамках одного соединения с сервером базы данных, что снижает объем потребляемых ресурсов.

Ведение журнала не в файле, а в базе данных позволяет использовать для его анализа всю мощь MySQL, но при этом не избавляет от необходимости думать об организации пространства для хранения. Веб-серверы могут использоваться весьма активно, а записи журнала занимают место вне зависимости от того, в файле или в базе данных они хранятся. Одним из способов экономии пространства является удаление устаревших записей. Например, чтобы удалить из журнала записи более чем годичной давности, периодически выполняйте такой запрос:

DELETE FROM httpdlog WHERE dt < DATE_SUB(NOW(),INTERVAL 1 YEAR);

Еще одна возможность заключается в архивировании старых записей в сжимаемых таблицах. (Для этого необходимо работать с таблицами MyISAM, которые можно сжимать при помощи функции myisampack.) Например, при переходе от сентября 2001 года к октябрю того же года вы знаете, что сентябрьских записей Apache больше не сгенерирует, так что их можно перенести в другую таблицу, которая останется неизменной. Создайте таблицу httpdlog_2001_09 с такой же структурой, как у httpdlog (включая индексы).

Затем перенесите сентябрьские записи журнала из httpdlog в httpdlog_2001_09, используя следующие запросы:

INSERT INTO httpdlog_2001_09
SELECT * FROM httpdlog
WHERE dt >= '2001-09-01' AND dt < '2001-10-01';
DELETE FROM httpdlog
WHERE dt >= '2001-09-01' AND dt < '2001-10-01';

Наконец, запустите myisampack для httpdlog_2001_09 с тем, чтобы сжать ее и сделать доступной только для чтения.

Недостатком этого подхода можно назвать распределение записей журнала по множеству таблиц. Если вы хотите рассматривать таблицы как единуюсущность, чтобы можно было выполнять запрос ко всему множеству записей журнала, создайте таблицу MERGE, которая будет включать их все. Предположим, что есть набор таблиц, содержащий текущую таблицу и таблицы для промежутка времени с сентября 2001 по апрель 2002 года. Предложение, создающее таблицу MERGE, будет выглядеть так:

CREATE TABLE httpdlog_all
(
dt DATETIME NOT NULL, # дата запроса
host VARCHAR(255) NOT NULL, # хост клиента
method VARCHAR(4) NOT NULL, # метод запроса(GET, PUT и т. д.)
url VARCHAR(255) BINARY NOT NULL, # URL
status INT NOT NULL, # статус запроса
size INT, # количество переданных байтов
agent VARCHAR(255) # пользовательский агент
)
TYPE = MERGE
UNION = (httpdlog, httpdlog_2001_09, httpdlog_2001_10, httpdlog_2001_11,
httpdlog_2001_12, httpdlog_2002_01, httpdlog_2002_02, httpdlog_2002_03,
httpdlog_2002_04);

Инструкция UNION должна указывать имена всех таблиц, включаемых в таблицу MERGE. Обратите внимание на то, что вам придется удалять и заново создавать определение httpdlog_all при каждом формировании новой статической таблицы журнала для очередного месяца. (Кроме того, если вы добавляете индекс, нужно добавить его во все отдельные таблицы, а затем пересоздать таблицу MERGE, включив и в нее определение индекса.)

Отчеты, получаемые по таблице httpdlog_all, будут принимать в расчет все записи журнала. Для составления ежемесячных отчетов просто обратитесь к соответствующей отдельной таблице.

Что касается объема дискового пространства, расходуемого на протоколирование веб-активности, помните о том, что если для сервера MySQL включено протоколирование запросов, то каждый запрос будет записан в таблицу httpdlog, а также в журнал запросов. То есть может случиться так, что дисковое пространство будет исчезать быстрее, чем вы думали, поэтому разумно применять для сервера MySQL какой-либо способ сокращения объема журнала (установку строка действия записи или ротацию журналов).

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Выполнение поиска и получение результатов
Журнал доступа к веб-странице
Загрузка в форму записи базы данных
Использование ввода через Web для формирования запросов
Обработка загружаемых файлов