Поиск (ключи):    ПРОГРАММЫ СТАТЬИ СОВЕТЫ ФОРУМ   WIKI НОВОСТИ (+) MAN'ы ДОКУМЕНТАЦИЯ

<< Предыдущая ИНДЕКС Поиск в статьях src Установить закладку Перейти на закладку Следующая >>

Ключевые слова: squid, traffic, mysql, isp, billing, linux,  (найти похожие документы)

From: Осипов Станислав <demofly at mail ru>
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_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 ($HTTP_GET_VARS["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 ($HTTP_GET_VARS["month"]=="") {
        $res = mysql_query("SELECT LEFT(NOW(),7) as month");
        $rw=mysql_fetch_array($res);
        $cur_month=$rw["month"];
    } else $cur_month=$HTTP_GET_VARS["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>&nbsp;".$rw["ip"]."&nbsp;</td><td align=right>&nbsp;".gethostbyaddr($rw["ip"])."&nbsp;</td><td align=right> ".$rw["trf"]." МБ&nbsp;</td></tr>";
    };
    $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>&nbsp;Итого: </b></td><td align=right>&nbsp;<b>".$extwwwtrf." МБ</b>&nbsp;</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>&nbsp;Скачано локально: </td><td align=right>&nbsp;$locwwwtrf МБ&nbsp;</td></tr>
            <tr><td colspan=2>&nbsp;Скачано из интернета: </td><td align=right>&nbsp;$extwwwtrf МБ&nbsp;</td></tr>
            <tr><td colspan=2>&nbsp;Сэкономлено системой: </td><td align=right>&nbsp;$econtrf МБ&nbsp;</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'>&nbsp;&copy;&nbsp;<a href='mailto:demofly@mail.ru'>Осипов&nbsp;С.С.</a>,&nbsp;2004.&nbsp;</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 

<< Предыдущая ИНДЕКС Поиск в статьях src Установить закладку Перейти на закладку Следующая >>

Обсуждение [ Линейный режим | Показать все | RSS ]   1.1, karl, 04:04, 20/01/2004 [ответить] [смотреть все] +/– Идея чудесная -- только лог лучше парсить по мере поступления Доходим до EOF, ж... весь текст скрыт [показать]     2.7, Осипов Станислав, 18:43, 20/01/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– прощу прощения, я не программирую на C C ваша утилитка для моего случая - раб... весь текст скрыт [показать] [показать ветку]   2.9, yarmol, 00:29, 21/01/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– А я для этих целей крутить на С не стал, и сделал tail -n 1 -f var log squid ac... весь текст скрыт [показать] [показать ветку]   1.2, Vaker, 08:58, 20/01/2004 [ответить] [смотреть все]   +/– Интересно! Пришли утилитку почтой. А то давно пытался под postgres трафик перевести   1.3, AndSerg, 09:02, 20/01/2004 [ответить] [смотреть все]   +/– Только после того как отработает скрипт, мы начнем терять данные. В файле access.log будет тишина. IMXO надо так: #!/bin/bash squid -k rotate sleep "несколько секунд" cp /www/logs/squid/access.log.0 /tmp/squidforparse.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 rm -f /tmp/access.log.0     2.5, Осипов Станислав, 18:26, 20/01/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– почему терять данные идея с rotate достаточно хорошая, но меня не устраивает си... весь текст скрыт [показать] [показать ветку]   2.30, Костя, 23:31, 22/01/2005 [^] [ответить] [смотреть все] [показать ветку]   +/– А зачем нужно cp www logs squid access log 0 tmp squidforparse log Ведь посл... весь текст скрыт [показать] [показать ветку]   1.4, wapr, 10:48, 20/01/2004 [ответить] [смотреть все]   +/– Замечательно. Я не настолько богат, что бы иметь суперкомпьютер, лопатящий базу, содержащую ВЕСЬ ЛОГ. Более 7 дней (60000000 записей. И > 2Гб) - слишком много, что бы любой отчет с помощью cgi построить, ибо занимает > 30 сек. Так что без промежуточных таблиц не обойтись. И товарищи правы - так с логом работать нельзя.     2.6, Осипов Станислав, 18:35, 20/01/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– Согласен, но если у вас в организации такое потребление трафика, то наверно, нес... весь текст скрыт [показать] [показать ветку]   2.8, Осипов Станислав, 21:12, 20/01/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– да, и еще БД на то и БД, чтоб не лопатить ВЕСЬ ЛОГ, а только запрошенную вами... весь текст скрыт [показать] [показать ветку]   1.10, oleg, 15:32, 21/01/2004 [ответить] [смотреть все]   +/– люди, зачем все это? веть есть же squid2mysql (http://evc.fromru.com/squid2mysql/index.html) и custom_log patch from squid-cache.org CVS. зачем изобретать велосипед с квадратными колесами ? :-)     2.11, yarmol, 17:51, 21/01/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– Ну так это все вещи достаточно несложные, что-бы время потраченное на установку ... весь текст скрыт [показать] [показать ветку]   1.12, juDge, 09:29, 22/01/2004 [ответить] [смотреть все]   +/– кто нить получил утилику которую обещал выслать Карл?)   1.13, Skif, 18:20, 05/03/2004 [ответить] [смотреть все]   +/– У меня нечто подобное работает у самого(мене функционально в том плане, что не показывает сколько из кеша было вытянуто, а скока нет) месяцев пять - просто использование сарга запарило, да и надо что-то а-ля риал-тайм для юзверов, но я бы не сказл, что совсем не затрагивает файлов - полностью не читал статью - она мне не столь интересна, раз моя софтина работает - но ты же тоже должен очищать /_чего_то_там_/squid/logs/access.log после того как кинул очередную порцию в топку ;))) базы Кстати, мой совет(впрочем может ты уже сам так же реализовал) - наваять клиенты на сях/делфе, который бы коннектился к базе  раз 20-30 минут и получал данные по статистике пользоваеля, что бы он знал когда пробьет его час и пора готовиться к отключению трафика ;))) Оч полезная штука. ;)))   1.14, jack, 15:21, 01/06/2004 [ответить] [смотреть все]   +/– Самая простая и прекрасная программа для подсчета и ограничения лимита трафика у пользователя squserlim И ничего изобретать не надо. Пишите сами   1.15, raen, 12:37, 06/07/2004 [ответить] [смотреть все]   +/– От себя добавлю, что не помешало бы заменить в insert'е в поле link символы " на \" ;)   1.18, Dmitry, 11:34, 14/07/2004 [ответить] [смотреть все]   +/– А как на счет function 'from_unixtime' not defined? Стоит FreBSD 4.10-STABLE.     2.28, Осипов Станислав, 16:26, 09/12/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– это ж, батенька, функция mysql v4 xx попробуйте написать ее ПРОПИСНЫМИ БУКВАМИ... весь текст скрыт [показать] [показать ветку]   1.19, Butsyk A., 17:23, 20/10/2004 [ответить] [смотреть все]   +/– Написал программку, которая складывает логи сквида в базу PostgreSQL. Буду рад, если кому поможет. http://gkm.sumy.ua/~butsyk/s2pg.tar.bz2     2.22, Butsyk A., 10:16, 18/11/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– прорамма складывает логи сквида в базу MySQL, PostgreSQL, используя родные API э... весь текст скрыт [показать] [показать ветку]     3.23, Butsyk A., 17:02, 24/11/2004 [^] [ответить] [смотреть все]   +/– Проект перемещен http squid2db sourceforge net ... весь текст скрыт [показать]   1.20, Vladimir Dyakov, 15:43, 05/11/2004 [ответить] [смотреть все]   +/– http://linux.alhimia.ru/projects/misc/squid-traffic/   1.21, ilya, 15:15, 10/11/2004 [ответить] [смотреть все]   +/– Мой вопрос немного оффтоп для данной ветки, но тема, безусловно, смежная. Во многих предложенных системах анализа трафика Squid используется basic аутентификация для ведения статистики по пользователям. По сути имена и пароли передаются в открытом виде. В случае, если еще вводятся лимиты для пользователей, то этот становится опасно - передавать в открыто виде. Есть идеи создания безопасной аутентификации на прокси?     2.24, cathul, 17:10, 25/11/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– При запуске скрипта squid-to-mysql выдает следующее, как это победить, подскажит... весь текст скрыт [показать] [показать ветку]   1.25, cathul, 17:32, 25/11/2004 [ответить] [смотреть все]   +/– А сейчас так :( proxy:/srv/www/htdocs/reports # ./squid-to-mysql ERROR at line 1: Unknown command '\%'.     2.26, Megavolt, 09:01, 01/12/2004 [^] [ответить] [смотреть все] [показать ветку]   +/– Я вообще для этой цели патч на сквид писал конечно не очень хороший, но счит... весь текст скрыт [показать] [показать ветку]     3.27, cathul, 17:12, 07/12/2004 [^] [ответить] [смотреть все]   +/– Скинь плиззз cathul ukr net ... весь текст скрыт [показать]   1.29, sergey, 00:12, 16/01/2005 [ответить] [смотреть все]   +/– Привет. Нужна помощь, при попытке доступа к странице статистики выдается: Fatal error: Call to undefined function: mysql_connect() in /var/www/html/reports/index.php on line 4 скрипт автора взят из статьи и не подвергался изменениям, единственное разница в том, что у меня пути разные к корню Апача, то есть  /var/www/html, ну да это мелочь.....     2.31, vlad11, 20:18, 23/01/2005 [^] [ответить] [смотреть все] [показать ветку]   +/– проверь пароль на коннект к базе... весь текст скрыт [показать] [показать ветку]   2.32, Serjio22, 01:35, 28/01/2005 [^] [ответить] [смотреть все] [показать ветку]   +/– Скорее всего у тебя не установлен phpmysql ... весь текст скрыт [показать] [показать ветку]   1.33, Dimmus, 09:03, 12/10/2005 [ответить] [смотреть все]   +/– Скрипт не очень актуален, ибо БД забивается ненужным хламом, допустим один и тот же IP дублируется, меняя разве что размер байтов. Можно ли как-нибудь суммировать эти значения? Иначе с такими темпами за неделю пару Гб будет весить.     2.35, yarmol, 12:10, 12/10/2005 [^] [ответить] [смотреть все] [показать ветку]   +/– Так и есть , и даже не пару гиг, а много больше Но я считаю трафик совсем по ... весь текст скрыт [показать] [показать ветку]   1.34, Dimmus, 10:26, 12/10/2005 [ответить] [смотреть все]   +/– Лично я сделал так -------------------------------------------------- write --... весь текст скрыт [показать]   1.36, Dimmus, 20:39, 16/10/2005 [ответить] [смотреть все]   +/– были и такие идеи, но быстрее было накатать скриптик, чем еще с крондом возиться :)   1.37, dualp2, 14:43, 24/04/2006 [ответить] [смотреть все]   +/– Ссылка дохлая! Ищите тут! http://ftp.citkit.ru/pub/sourceforge/s/sq/ Была взята squid2db-1.1 Демон регулярно падал из за неэкранированных апострофов и неправильной размерности переменной blob. Устранено...Пока стоит...Могу выслать, кому надо...   1.38, Иван, 11:12, 10/05/2006 [ответить] [смотреть все]   +/– а MYSAR уже не рулит?   1.39, rook, 15:00, 27/10/2006 [ответить] [смотреть все]   +/– Я может что-то не догоняю, но как вы считаете траффик фтп,... Я в логах сквида этого не нашел     2.40, yarmol, 11:58, 28/10/2006 [^] [ответить] [смотреть все] [показать ветку]   +/– >Я может что-то не догоняю, но как вы считаете траффик фтп,... Я >в логах сквида этого не нашел Никак :) Точнее, не "никак", а весь трафик, я например, считаю совсем другими способами, а логи сквида занесенные в мускул очень помогают при общении с клиентами. Ну или, если понадобился какой-то анализ http трафика - сколько туда, да сколько сюда...   1.41, Александр, 12:27, 29/12/2006 [ответить] [смотреть все]   +/– А SAMS использовать совместно с контролером домена нельзя ? 8) отличный тарификатор  + ACL + правила для юзеров     2.43, DoBeRmAn8482, 17:05, 10/01/2008 [^] [ответить] [смотреть все] [показать ветку]   +/– Почитал статью, понравилось, притом что от меня как раз попросили такую вещь, только у меня возникла одна загвоздка у меня отдельно действующий сервер Mysql, как ему передать данный из сквидовского access.log'a???   1.44, Dimarik, 06:37, 28/03/2008 [ответить] [смотреть все]   +/– A как из url еще и имя сервера вырвать?   1.45, Eternal, 07:34, 23/05/2008 [ответить] [смотреть все]   +/– MySAR + + подробная статистика за месяц http://kb.etarea.com/2008/05/21/mysar-подробная-статистика-за-месяц/   Ваш комментарий Имя:          E-Mail:       Заголовок: Текст:

АКЦИЯ! ПОДПИШИСЬ на журнал Linux Format до 31 января 2012 года и выиграй СУПЕРПРИЗ! Журнал "Linux Format" (Линукс Формат)- Единственный в России и странах СНГ журнал на русском языке, посвящённый Linux и свободному ПО. Журнал для IT-директоров, IT-менеджеров, программистов, системных администраторов, учителей школ и преподавателей ВУЗов и всех пользователей ПК. В каждом выпуске: Новости индустрии OpenSource, обзоры новинок свободного ПО, обучающие и методические статьи. Каждый, кто оформит подписку, получает бонус- объёмные наклейки на системный блок и подарки: с одним из первых выпусков журнала в 2012 году- диск с архивом номеров за 2005-2011 г.г. и ежемесячно электронную версию журнала в pdf-формате. Подробнее о проведении акции вы можете прочитать на странице сайта.