CS & IT Solutions

Squid2MySQL for FreeBSD 5.2 (08.07.2004 r)

What Squid2MySQL? This authorization, and accounting traffic users
Internet. There is a proxy-server Squid (http://www.squid-cache.org/)
and the database MySQL, (http://dev.mysql.com/)
Squid caching of information involved, and MySQL stores information about
user (name, password, URL and downloaded, etc.).
Binder link – it Squid2MySQL (http://evc.fromru.com/squid2mysql/download.html) 1.0.0
by Eugene V. Chernyshev for Linux.

For those who use Linux as the OS for servers – it will be better
Visit poster’s Squid2MySQL and use information from
Copyright (http://evc.fromru.com/squid2mysql/download.html) site.
I just ported squid2mysql under FreeBSD, and corrected a few errors
them were reported to the author for correspondence e-mail.

This article describes the stages and features of the installation of the corrected
version squid2mysql under FreeBSD.
Let’s go …

1) must be installed MySQL, Squid and Perl. Check
efficiency of these programs: go to MySQL, make a simple
request to table mysql; check Perl command perl-v; do not forget
do for Squid base command squid-z. Check the DNS team
nslookup host, if there will be problems with DNS, you will not run Squid.

2) Download the archive squid2mysql (http://www.uvsw.narod.ru/project/squid2mysql_v.tar.gz)
under FreeBSD (~ 600 Kb) and unpack

# Tar-zxvf squid2mysql_v.tar.gz

3) Go to the newly created directory squid2mysql_v. Go to the subdirectory
for_perl and install the modules for the Perl DBI-1.42.tar.gz and
Msql-Mysql-modules-1.2219.tar.gz or download the newer.

# Tar-zxvf DBI-1.42.tar.gz
# Cd DBI-1.42
# Perl Makefile.PL
# Make
# Make test
# Make install

analogous set module Msql-Mysql-modules-1.2219.tar.gz

4) Read the file squid2mysql_v/install_info/INSTALL. Copy
perl-script squid2mysql and sh-script sqauth in the directory with squid.

Example,

# Cp squid2mysql / usr/local/squid/sbin/squid2mysql
# Cp sqauth / usr / local / squid / sbin / sqauth

Change copying the path to squid, if you are in another directory.
Perl-script squid2mysql – parses logfiles from Squid and record in the database MySQL.
Sh-script sqauth – authorization script checks the name and password
compares the downloaded volume limit. If everything is OK – the script
returns OK and Squid lets Internet, or ERR. This script is written
I (the original script, more and allows you to limit by day and
weeks), so that his work claim is made to me
e-mail. (mailto: vasilisc@rambler.ru? Subject = squid2mysql)

5) Create a command mkfifo pipe-channel.

# Mkfifo / var / log / squid / access.log

I recommend to leave this way it is.
The author used a nice move. Instead of a text file will be located
pipe-channel. Nichegonepodozrevayuschy Squid will write about the user logs in
one end of the pipe, and perl-script squid2mysql read from the other end of the
pipes, disassemble and entered into the database MySQL. If MySQL would not
available, the information is temporarily being stored in a file
/ var / log / squid / backup.log

6) Copy mysql.sh and squid.sh from directory
squid2mysql_v/install_info / directory / usr / local / etc / rc.d /
Make these scripts are run for the startup of MySQL and Squid after
restart FreeBSD.

# Chmod + x mysql.sh
# Chmod + x squid.sh
Look at the contents squid.sh with comments

#! / bin / sh
case “$ 1″ in # parse parameter
start)
/ usr/local/squid/sbin/squid2mysql </ var / log / squid / access.log &
# We read the logs Squid via pipe-channel
/ usr / local / squid / sbin / squid # run Squid
echo “Squid starting” # inform
,;
stop)
/ usr / local / squid / sbin / squid-k shutdown # stop Squid
echo “Squid stoping” # inform
,;
*)
/ usr / local / squid / sbin / squid-k reconfigure # restartuem Squid
echo “Squid restarting” # inform
,;
esac
exit 0

7) Catalog site_php – a web-based interface for managing users.
For their work to install Apache and PHP, to prescribe in php.ini
globalregister = on. I fine control by using SQL queries
directly to the database or using graphics programs,
so I refused to use them. With these scripts you
can enable users to change their password and watch
his (and only his) statistics.

Edit include.php on your own. If you have
questions about the work of web-interface for squid2mysql, then
contact author (http://evc.fromru.com/squid2mysql/download.html) site.

Copy
squid2mysql_v/var_arch/sqlogarch in / var / arch / squid / sqlogarch
squid2mysql_v/var_arch/sqlogupload in / var / arch / squid / sqlogupload
These scripts are used from the php-script web-interface for
managing backup and restore logs.

9) In order to work with Squid squid2mysql need in squid.conf
prescribe the following parameters:

# Authorization parameters
auth_param basic program / usr / local / squid / sbin / sqauth # path to the script
# Which returns OK or ERR
auth_param basic children 5
auth_param basic realm Squid proxy-caching web server
auth_param basic credentialsttl 2 hours

# All driven under the proxy
acl all src 0.0.0.0/0.0.0.0
acl users proxy_auth REQUIRED
http_access allow users
http_access deny all

# Tells Squid not to pump to check if anyone?
authenticate_ttl 60 seconds

10) Run / usr / local / mysql / bin / mysql. If you are strong enough to
MySQL, then podredaktiruyte file squid2mysql.sql

For example, this line may be connected to the base under the name
vasilisc and password 123456 c any host on the network.

GRANT ALL PRIVILEGES ON *.* TO vasilisc @ “%” IDENTIFIED BY `123456`;

This line gives the default 30 Mb per month limit

maxmonthly bigint (15) NOT NULL default `31457280`

Apply the scheme, which will create the necessary tables.

mysql> \. / path_to/squid2mysql_v/install_info/squid2mysql.sql

May receive error messages, but that’s OK, because the scheme
squid2mysql.sql contains SQL command to remove the table (DROP TABLE) and
create a new (CREATE TABLE) with the same name. But for the first time such
table does not, so you get error messages.

11) I recommend to do in squid.conf Next:
cache_effective_user nobody
cache_effective_group nogroup

That is, Squid will work from the user nobody and group nogroup.
Do not forget to make the directory owned by nobody Squid.
# Chown-R nobody: nogroup / usr / local / squid /

Provide access to user nobody in catalogs:
/ var / log / squid /
/ var / arch / squid /

12) Add a test user in the database or by
php-script from the directory site_php, either directly from the console
MySQL.

mysql> \ u squidlog
mysql> INSERT INTO auth
VALUES ( `vasilisc`, PASSWORD ( `123456`), `N`, 0,0,31457280);
mysql> INSERT INTO usernames VALUES ( `vasilisc`, `Alekseenko VN`, `simple
user `,` OVIT `,` 11-22-33 `,` s@s.ru `,` 2004-08-01 `);
Or fix the file insert_user.sql and call it.
mysql> \. / path_to / insert_user.sql

Check the performance! If not work go through all the stages are
time carefully. Read logs Squid, MySQL, FreeBSD. Browse
contents of files, especially include.php, sqauth, perl-script
squid2mysql, squid.conf. Check out the official website of products
read the FAQ.

My comments:

1) I changed all the scripts in the user name to connect to MySQL.
A user with a password squidroot sqroot become root (it is not FreeBSD
root, and root MySQL – are two different things) c blank password. This is
that port under FreeBSD was difficult enough, add bugs
in the original scripts.

2) sqauth – I wrote the authorization sh-script. His
performance on my conscience. He has been simplified to determine
monthly limit, although the original script is checked weekly and
daily limit.

3) perl-script squid2mysql – was I changed, because there was a bug with
doubling of the first URL. You visited the site the first time in this day and the size
downloaded html doubled, then the problem disappears until you next day.

That’s all, I hope all of you to install and operate for the benefit!

source here