Подсчет трафика проходящего через Squid используя MySQL

В статье рассмотрен процесс реализации несложной системы учета трафика для сети коллективного доступа в Интернет, использующей Squid в качестве прокси-сервера

[Осипов Станислав (demofly at mail ru)]

Keywords: squid traffic mysql isp billing linux From: Newsgroups: email Date: Mon, 16 Jan 2004 14:31:37 +0000 (UTC) Subject: Подсчет трафика проходящего через Squid используя MySQL

Как сделать подсчет трафика Squid через MySQL в Linux

Мой механизм был мной создан для организации, в которой я работаю. У нас стоит сервер-шлюз Linux 2.6.0 через который по гигабитным интерфейсам люди выходят в инернет через NAT.

Пару лет назад я мечтал разработать такой механизм для другой организации, и вот сейчас вам представляю простейшую и наглядную схему.

Общая схема состоит из пяти составляющих:

  1. Squid сервер, ведущий логи в native squid формате (родной формат, несовместимый с форматом apache-логов)
  2. MySQL сервер
  3. iptables, которая на шлюзе весь HTTP трафик заворачивает на прокси (Squid)
  4. Два сделанных мной скрипта. Один squid-to-mysql, для перегонки лога в БД, второй - index.php для отображения статистики.
  5. Apache+PHP - нужен для работы php-скрипта, отвечающего за отображение трафика по WWW.
Для тех, у кого Squid используется не как прозрачный прокси, третья составляющая системы (iptables) не обязательна и на остальное, здесь описанное не влияет.

Сделаю сразу западло: отошлю вас на поиск короткой статьи о создании прозрачной прокси, которую я когда-то давно нашел и потерял. Она может вам понадобиться, если вы пожелаете сделать себе прозрачный прокси. Себе такую я сделал исключительно из соображений удобства обслуживания парка компьютеров - не нужно бегать к каждому компу и прописывать настройки прокси.

У меня это работает на: squid 2.5, MySQL 4.0.14, iptables 1.27a, Apache 1.3.27, PHP 4.3.2.

Итак, чтобы приступить к самому созданию всего, что может понадобиться, я полагаю, что у вас уже установлен mysql, squid и все настроено.

Далее я буду полагать, что лог посещений squid лежит в /var/log/squid/access.log

Делаем скрипт squid-to-mysql следующего содержания:

#!/bin/bash

cp /www/logs/squid/access.log /tmp/squidforparse.log
>/www/logs/squid/access.log
awk '{print "INSERT INTO squid (ip,bytes,link,trans,time) \
VALUES(\""$3"\","$5",\""$7"\",\""$9"\",from_unixtime("$1"));"};' \
< /tmp/squidforparse.log | mysql -D traffics -u root --password=my_sql_passwd
rm -f /tmp/squidforparse.log
и размещаем его, например, в папке с нашими скриптами /scripts/

После того, как мы подготовим MySQL-структуры, не забудьте его повесить в расписание crontab с частотой выполнения на ваше усмотрение (оптимально - раз в сутки в 2-3 часа ночи).

Первая строка скрипта забирает лог на обработку в отдельный файл, ибо сразу во второй строке мы опустошаем содержимое лога.

Третья строка - собственно перегон лога в mysql запросы, и немедленная передача сгенерированных запросов самой БД.

Четвертая - собссно, уборка за собой. Как видите, все проще простого.

Прежде чем запускать файл с таким содержимым, нам надо создать структуры для хранения данных о трафике.

Заходите:

mysql -u root -p
введите пароль доступа к БД (в вышеприведенном скрипте я полагал и буду далее полагать, что он my_sql_passwd) далее в mysql консоли вводим такие команды:
mysql>create database traffics;
mysql>use traffics;
mysql>create table squid(ip varchar(16), bytes bigint unsigned, link text, trans varchar(65), time datetime);
все, с БД мы справились. Выходим из mysql.

Теперь надо сделать скрипт для чтения статистики в HTML. Я полагаю, что когда вы набирали в поиске squid+mysql, это значит, что вы стремитесь использовать mysql для упрощения работы с данными, и, следовательно, используете PHP для работы с MySQL в скриптах веб-сервера. Исходя из предположения, что у вас с Apache+PHP все ок (а если нет, ищите тут доки, их тут много), я напишу свой скрипт отображения ежемесячной статистики на PHP.

Пусть у нас корень документов веб-сервера apache в /www/htdocs/. создадим в нем папку reports:

#mkdir /www/htdocs/reports
в папке /www/htdocs/reports создаем файл index.php следующего содержания (если просто скопировать, ничего не пострадает, так как скрипт делает только самое необходимое - соединияется с БД, запрашивает отчет, конструирует HTML, и отдает клиенту):
<?

$color=""; $year = ""; $ipv4=getenv("REMOTE_ADDR");
$res = mysql_char_connect("127.0.0.1", "root", "my_sql_passwd") or die("Fatal PHP 'MySQL CONNECT' error.");
$res = mysql_select_db("traffics") or die("Fatal database query 'USE' error");
if ($_GET["month"]=="list")  {
    $res = mysql_query("SELECT DISTINCT(LEFT(time,7)) AS month,LEFT(time,4) AS year\
     FROM squid ORDER BY year DESC,month DESC;");
    while ($rw=mysql_fetch_array($res)) {
        if ($year<>$rw["year"]) {
            $year=$rw["year"];
            $table.="<H3>$year год:</H3>";
        };
        $table.="<a href=$PHP_SELF?month=".$rw["month"].">".$rw["month"]."</a> ";
    };  $otherlink="<a href=$PHP_SELF?>За текущий месяц</a>";
} else {
    system("./squid-to-mysql");
    if ($_GET["month"]=="") {
        $res = mysql_query("SELECT LEFT(NOW(),7) as month");
        $rw=mysql_fetch_array($res);
        $cur_month=$rw["month"];
    } else $cur_month=$_GET["month"];
    $res = mysql_query("SELECT round(sum(bytes)/10000)/100 as trf,ip FROM squid \
    WHERE (LEFT(time,7)='$cur_month') AND (trans<>'NONE/-') GROUP BY ip ORDER BY LENGTH(ip),ip;");
    while ($rw=mysql_fetch_array($res)) {
        if ($ipv4 == $rw["ip"]) $color=" bgcolor='#FFDDDD'";
            else $color='';
        $table.="
            <tr$color><td> ".$rw["ip"]." </td><td align=right> ".
    };
    $res = mysql_query("SELECT round(sum(bytes)/10000)/100 as trf FROM squid\
     WHERE (LEFT(time,7)='$cur_month') AND (trans<>'NONE/-');");
    $rw=mysql_fetch_array($res);
    $extwwwtrf=$rw["trf"];
    $table.="<tr><td colspan=2><b> Итого: </b></td><td align=right> <b>".$extwwwtrf." МБ</b> </td></tr>";
    $res = mysql_query("SELECT round(sum(bytes)/10000)/100 as trf FROM squid WHERE (LEFT(time,7)='$cur_month');");
    $rw=mysql_fetch_array($res);
    $locwwwtrf=$rw["trf"];
    $econtrf=$locwwwtrf-$extwwwtrf;
    $table.="       <tr><td colspan=3 align=center bgcolor=#DDFFDD>Статистика прокси-кэша за $cur_month:</td></tr>
            <tr><td colspan=2> Скачано локально: </td><td align=right> $locwwwtrf МБ </td></tr>
            <tr><td colspan=2> Скачано из интернета: </td><td align=right> $extwwwtrf МБ </td></tr>
            <tr><td colspan=2> Сэкономлено системой: </td><td align=right> $econtrf МБ </td></tr>";
    $table="<table align=center cellspacing=0 border=1 bordercolor='#777777'>
            <tr><td align=center colspan=3 bgcolor=#DDFFDD><b>Статистика за $cur_month:<b></td></tr>$table
            </table>";
    $otherlink="<a href=$PHP_SELF?month=list>Другой месяц</a>";
};
echo "
    <HTML>
        <HEAD>
            <TITLE>Трафик</TITLE>
        </HEAD>
        <BODY>
            <table align=center cellspacing=0 border=0 cellpadding=0 height='100%' width='100%'>
            <tr height='100%'><td width='100%' valign=top colspan=2>
                $otherlink
                <CENTER>
                    $table
                </CENTER>
                <BR>
            </td></tr>
            <tr><td width='100%'></td>
            <td valign=bottom align=right bgcolor='#FFDDDD'> © 
            <a href='mailto:demofly@mail.ru'>Осипов С.С.</a>, 2004. 
            </td></tr>
            </table>
        </BODY>
    </HTML>";

?>
Предположим, что веб-сервер работает с правами пользователя apache. Делаем
chmod 644 -R /www/htdocs/reports
chown apache.apache -R /www/htdocs/reports
chmod +s /scripts/squid-to-mysql
ln -s /scripts/squid-to-mysql /www/htdocs/reports/squid-to-mysql
chmod 666 /var/log/squid/access.log
Это сделано для полноценной работы скрипта php. Механизм несовершенен с точки зрения безопасности, можете искать обходные пути для правильного (полностью безопасного) рефрешинга логов из native squid в mysql по той причине, что последней командой мы открыли на запись файл логов прокси группе "others".

Далее, если я хочу, чтобы только нужные мне IP-адреса могли иметь доступ к этому скрипту, я делаю в httpd.conf (конфиг веб-сервера Apache, эта настройка необязательна):

<Location /reports/>
    order allow,deny
    allow from 192.168.3.0/24 127.0.0.0/8
</Location>
Где 192.168.3.0/24 - моя локальная сеть. 24 - это маска, она заменяет маску 255.255.255.0

Статья взята с сайта Opennet (www.opennet.ru)

[ опубликовано 31/07/2004 ]

Осипов Станислав (demofly at mail ru) - Подсчет трафика проходящего через Squid используя MySQL   Версия для печати