The OpenNET Project / Index page

[ новости/++ | форум | wiki | теги ]

Подсчет трафика проходящего через Squid используя MySQL (squid traffic mysql isp billing linux)


<< Предыдущая ИНДЕКС Поиск в статьях 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:      
    Заголовок:
    Текст:





      Закладки на сайте
      Проследить за страницей
    Created 1996-2017 by Maxim Chirkov  
    ДобавитьРекламаВебмастеруГИД  
    Hosting by Ihor