Re: SQL db failover
Rohaizam Abu Bakar wrote: >>> How can we possible do to ensure only when sql1 down.. then the >>> accounting will be sent to sql2..?? >> >> You might try a different approach: >> - store accounting in "detail" files (man rlm_detail) >> - run radsqlrelay to send accounting in the database (get it from >> a CVS snapshot) > > Thanks.. Will try it out... > > Do I need to make any addition/changes in Makefile to compile > radsqlrelay...? If yes... What changes is needed.. Indeed, radsqlrelay is something new and it is not build with the server in the current CVS. You could apply the following patch on the CVS version to build radsqlrelay, too. Index: src/main/Makefile.in === RCS file: /source/radiusd/src/main/Makefile.in,v retrieving revision 1.36 diff -u -r1.36 Makefile.in --- src/main/Makefile.in3 Dec 2004 18:24:31 - 1.36 +++ src/main/Makefile.in26 Jan 2005 10:50:22 - @@ -20,7 +20,7 @@ MODULE_OBJS= VFLAGS = -DRADIUSD_MAJOR_VERSION=$(RADIUSD_MAJOR_VERSION) VFLAGS += -DRADIUSD_MINOR_VERSION=$(RADIUSD_MINOR_VERSION) -BINARIES = radiusd radwho radzap radclient radrelay +BINARIES = radiusd radwho radzap radclient radrelay radsqlrelay LT_BIN_FLAGS = # @@ -137,6 +137,12 @@ radrelay.o: radrelay.c $(INCLUDES) $(LIBTOOL) --mode=compile $(CC) $(CFLAGS) -c radrelay.c +radsqlrelay: radsqlrelay.o mainconfig.o util.o nas.o client.o log.o conffile.o files.o xlat.o valuepair.o ../lib/libradius.la + $(LIBTOOL) --mode=link $(CC) -export-dynamic -dlopen self $(CFLAGS) $(LDFLAGS) -o radsqlrelay radsqlrelay.o mainconfig.o util.o nas.o client.o log.o conffile.o files.o xlat.o valuepair.o $(LIBLTDL) $(LCRYPT) $(LIBS) + +radsqlrelay.o: radsqlrelay.c $(INCLUDES) + $(LIBTOOL) --mode=compile $(CC) $(CFLAGS) -c radsqlrelay.c + radwho.o: radwho.c $(INCLUDES) $(LIBTOOL) --mode=compile $(CC) $(CFLAGS) -c radwho.c @@ -159,5 +165,6 @@ $(INSTALL) -m 755radlast$(R)$(bindir) $(LIBTOOL) --mode=install $(INSTALL) -m 755radclient$(EXEEXT) $(R)$(bindir) $(LIBTOOL) --mode=install $(INSTALL) -m 755radrelay$(EXEEXT) $(R)$(bindir) + $(LIBTOOL) --mode=install $(INSTALL) -m 755radsqlrelay$(EXEEXT) $(R)$(bindir) $(INSTALL) -m 755radtest$(R)$(bindir) $(INSTALL) -m 755checkrad.pl$(R)$(sbindir)/checkrad -- Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
Thanks.. Will try it out... Do I need to make any addition/changes in Makefile to compile radsqlrelay...? If yes... What changes is needed.. thanks.. --haizam - Original Message - From: "Nicolas Baradakis" <[EMAIL PROTECTED]> To: Sent: Monday, January 24, 2005 19:16 Subject: Re: SQL db failover Rohaizam Abu Bakar wrote: How can we possible do to ensure only when sql1 down.. then the accounting will be sent to sql2..?? You might try a different approach: - store accounting in "detail" files (man rlm_detail) - run radsqlrelay to send accounting in the database (get it from a CVS snapshot) Even if the SQL server is down for a day, radsqlrelay will buffer the accounting packets and send them later. The advantages: - all accounting go in a single database (it's easier to check simultaneous login) - even under high load radsqlrelay still sends accounting requests according to the SQL server's capabilities - you won't have a lot of outstanding requests on the RADIUS sever when the SQL server is slow -- Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
Rohaizam Abu Bakar wrote: > How can we possible do to ensure only when sql1 down.. then the accounting > will be sent to sql2..?? You might try a different approach: - store accounting in "detail" files (man rlm_detail) - run radsqlrelay to send accounting in the database (get it from a CVS snapshot) Even if the SQL server is down for a day, radsqlrelay will buffer the accounting packets and send them later. The advantages: - all accounting go in a single database (it's easier to check simultaneous login) - even under high load radsqlrelay still sends accounting requests according to the SQL server's capabilities - you won't have a lot of outstanding requests on the RADIUS sever when the SQL server is slow -- Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
I've changed all except "fail" to return... and seems OK..
But the fail over (sql2) mysql still receive accounting although sql1 works
fine.. This has problem when checking single login since possible of start
& stop record at different server... Although accounting that spill over
to sql2 is not much.. but it still effect the whole process...
How can we possible do to ensure only when sql1 down.. then the accounting
will be sent to sql2..??
I'm afraid that my unlimited customer (sinultaneous=1) get denied although
there is no other session active due to start & stop record being sent to
different mysql
thanks..
--haizam
- Original Message -
From: "Rohaizam Abu Bakar" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, January 18, 2005 11:30
Subject: Re: SQL db failover
One more thing related to SQL accounting...
Everytime I received error "Stop packet with zero session length"...
Accounting will be stored in both sql1 & sq2.. please refer debug log..
should I change noop= to something else instead of below...???
--haizam
group {
sql1 {
fail=1
notfound=return
noop=2
ok=return
updated=3
reject=return
userlock=4
invalid=5
handled=6
}
sql2 {
< same as above>
}
}
##
radius_xlat: 'UPDATE radacct SET AcctStopTime = '2005-01-18 10:39:34',
AcctSessionTime = '', AcctInputOctets =
'', AcctOutputOctets = '', AcctTerminateCause = '', AcctStopDelay = '0',
ConnectInfo_stop = '' WHERE AcctSessi
onId = '442225381' AND UserName = '' AND NASIPAddress = '161.142.17.2''
rlm_sql (sql1): Reserving sql socket id: 4
radius_xlat: 'rlm_sql: Stop packet with zero session length. (user '',
nas '161.142.17.2')'
rlm_sql: Stop packet with zero session length. (user '', nas
'161.142.17.2')
rlm_sql (sql1): Released sql socket id: 4
radius_xlat: 'INSERT into radacct (AcctSessionId, AcctUniqueId, UserName,
Realm, NASIPAddress, NASPortId, NASP
ortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,
ConnectInfo_start, ConnectInfo_stop, Acct
InputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
AcctTerminateCause, ServiceType, FramedProtoc
ol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('442225381',
'18e9d9976b13739f', '', '', '161.142.17
.2', '10202', 'Sync', DATE_SUB('2005-01-18 10:39:34', INTERVAL (0 + 0)
SECOND), '2005-01-18 10:39:34', '', '',
'', '', '', '', '9915600', '0320529716', '', '', '', '', '0', '0')'
rlm_sql (sql1): Released sql socket id: 4
modcall[accounting]: module "sql1" returns noop for request 132
radius_xlat: ''
radius_xlat: 'UPDATE radacct SET AcctStopTime = '2005-01-18 10:39:34',
AcctSessionTime = '', AcctInputOctets =
'', AcctOutputOctets = '', AcctTerminateCause = '', AcctStopDelay = '0',
ConnectInfo_stop = '' WHERE AcctSessi
onId = '442225381' AND UserName = '' AND NASIPAddress = '161.142.17.2''
rlm_sql (sql2): Reserving sql socket id: 4
radius_xlat: 'rlm_sql: Stop packet with zero session length. (user '',
nas '161.142.17.2')'
rlm_sql: Stop packet with zero session length. (user '', nas
'161.142.17.2')
rlm_sql (sql2): Released sql socket id: 4
radius_xlat: 'INSERT into radacct (AcctSessionId, AcctUniqueId, UserName,
Realm, NASIPAddress, NASPortId, NASP
ortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,
ConnectInfo_start, ConnectInfo_stop, Acct
InputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
AcctTerminateCause, ServiceType, FramedProtoc
ol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('442225381',
'18e9d9976b13739f', '', '', '161.142.17
.2', '10202', 'Sync', DATE_SUB('2005-01-18 10:39:34', INTERVAL (0 + 0)
SECOND), '2005-01-18 10:39:34', '', '',
'', '', '', '', '9915600', '0320529716', '', '', '', '', '0', '0')'
rlm_sql (sql2): Released sql socket id: 4
modcall[accounting]: module
Re: SQL db failover
One more thing related to SQL accounting...
Everytime I received error "Stop packet with zero session length"...
Accounting will be stored in both sql1 & sq2.. please refer debug log..
should I change noop= to something else instead of below...???
--haizam
group {
sql1 {
fail=1
notfound=return
noop=2
ok=return
updated=3
reject=return
userlock=4
invalid=5
handled=6
}
sql2 {
< same as above>
}
}
##
radius_xlat: 'UPDATE radacct SET AcctStopTime = '2005-01-18 10:39:34',
AcctSessionTime = '', AcctInputOctets =
'', AcctOutputOctets = '', AcctTerminateCause = '', AcctStopDelay = '0',
ConnectInfo_stop = '' WHERE AcctSessi
onId = '442225381' AND UserName = '' AND NASIPAddress = '161.142.17.2''
rlm_sql (sql1): Reserving sql socket id: 4
radius_xlat: 'rlm_sql: Stop packet with zero session length. (user '', nas
'161.142.17.2')'
rlm_sql: Stop packet with zero session length. (user '', nas
'161.142.17.2')
rlm_sql (sql1): Released sql socket id: 4
radius_xlat: 'INSERT into radacct (AcctSessionId, AcctUniqueId, UserName,
Realm, NASIPAddress, NASPortId, NASP
ortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,
ConnectInfo_start, ConnectInfo_stop, Acct
InputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
AcctTerminateCause, ServiceType, FramedProtoc
ol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('442225381',
'18e9d9976b13739f', '', '', '161.142.17
.2', '10202', 'Sync', DATE_SUB('2005-01-18 10:39:34', INTERVAL (0 + 0)
SECOND), '2005-01-18 10:39:34', '', '',
'', '', '', '', '9915600', '0320529716', '', '', '', '', '0', '0')'
rlm_sql (sql1): Released sql socket id: 4
modcall[accounting]: module "sql1" returns noop for request 132
radius_xlat: ''
radius_xlat: 'UPDATE radacct SET AcctStopTime = '2005-01-18 10:39:34',
AcctSessionTime = '', AcctInputOctets =
'', AcctOutputOctets = '', AcctTerminateCause = '', AcctStopDelay = '0',
ConnectInfo_stop = '' WHERE AcctSessi
onId = '442225381' AND UserName = '' AND NASIPAddress = '161.142.17.2''
rlm_sql (sql2): Reserving sql socket id: 4
radius_xlat: 'rlm_sql: Stop packet with zero session length. (user '', nas
'161.142.17.2')'
rlm_sql: Stop packet with zero session length. (user '', nas
'161.142.17.2')
rlm_sql (sql2): Released sql socket id: 4
radius_xlat: 'INSERT into radacct (AcctSessionId, AcctUniqueId, UserName,
Realm, NASIPAddress, NASPortId, NASP
ortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,
ConnectInfo_start, ConnectInfo_stop, Acct
InputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
AcctTerminateCause, ServiceType, FramedProtoc
ol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('442225381',
'18e9d9976b13739f', '', '', '161.142.17
.2', '10202', 'Sync', DATE_SUB('2005-01-18 10:39:34', INTERVAL (0 + 0)
SECOND), '2005-01-18 10:39:34', '', '',
'', '', '', '', '9915600', '0320529716', '', '', '', '', '0', '0')'
rlm_sql (sql2): Released sql socket id: 4
modcall[accounting]: module "sql2" returns noop for request 132
modcall: group group returns noop for request 132
modcall: group accounting returns ok for request 132
Sending Accounting-Response of id 101 to 161.142.17.2:1027
Finished request 132
Going to the next request
###
- Original Message -
From: "Alan DeKok" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, January 18, 2005 00:40
Subject: Re: SQL db failover
"Rohaizam Abu Bakar" <[EMAIL PROTECTED]> wrote:
But before that.. I tried to use the simplified one using "redundant" as
below:-
redundant {
sql1
sql2
}
But seems everytime accounting record being sent.. I will store in both
of
the mysql server... not the first one that return OK.. why???
It's a known bug. See bugs.freeradius.org
Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
"Rohaizam Abu Bakar" <[EMAIL PROTECTED]> wrote:
> But before that.. I tried to use the simplified one using "redundant" as
> below:-
>
> redundant {
> sql1
> sql2
> }
>
> But seems everytime accounting record being sent.. I will store in both of
> the mysql server... not the first one that return OK.. why???
It's a known bug. See bugs.freeradius.org
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
Just wanna share... I've also configured as below setting (using group) for
mysql failover.. and its working as well!!...
But before that.. I tried to use the simplified one using "redundant" as
below:-
redundant {
sql1
sql2
}
But seems everytime accounting record being sent.. I will store in both of
the mysql server... not the first one that return OK.. why???
Also before that.. I try to do as suggested in doc... to put "handled" after
sql2.. and resulting accounting record being resent a few times from
client.. (testing using NTRadping).. Can somebody help me on the "handled"
portion ??
redundant {
sql1
sql2
handled
}
always handled{
rcode = handled
}
--haizam
- Original Message -
From: "Michel van Dop" <[EMAIL PROTECTED]>
To:
Sent: Sunday, January 16, 2005 08:04
Subject: Re: SQL db failover
It works!! Yes
Thanks Alan !!
I replace on every refrence to "sql" this:
group {
sql1 {
fail = 1
notfound = return
noop = 2
ok = return
updated = 3
reject = return
userlock = 4
invalid = 5
handled = 6
}
sql2 {
fail = 1
notfound = return
noop = 2
ok = return
updated = 3
reject = return
userlock = 4
invalid = 5
handled = 6
}
}
- Original Message -
From: "Michel van Dop" <[EMAIL PROTECTED]>
To:
Sent: Saturday, January 15, 2005 8:11 PM
Subject: Re: SQL db failover
Okay good, i replace any refrence to "sql" module and fix it.
But how do i replace this? In "group" or "sql1,sql2" or ?
What's the name of sql1 and sql2 ?
Thank you,
Michel
- Original Message -
From: "Alan DeKok" <[EMAIL PROTECTED]>
To:
Sent: Saturday, January 15, 2005 5:58 PM
Subject: Re: SQL db failover
"Michel van Dop" <[EMAIL PROTECTED]> wrote:
Okay i understand that ( i hope so):
Now i do this in radiusd.conf:
Yup, that should work.
I get this error cat /var/log/radius/radius.log
Sat Jan 15 13:35:19 2005 : Error: ERROR: Cannot find a configuration
entry
for module "sql".
Some part of radiusd.conf has a reference to an "sql" module. Find
that, fix it, and it should work.
Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
"Michel van Dop" <[EMAIL PROTECTED]> wrote: > Okay good, i replace any refrence to "sql" module and fix it. > But how do i replace this? In "group" or "sql1,sql2" or ? You probably want the group. > What's the name of sql1 and sql2 ? sql1 and sql2. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
It works!! Yes
Thanks Alan !!
I replace on every refrence to "sql" this:
group {
sql1 {
fail = 1
notfound = return
noop = 2
ok = return
updated = 3
reject = return
userlock = 4
invalid = 5
handled = 6
}
sql2 {
fail = 1
notfound = return
noop = 2
ok = return
updated = 3
reject = return
userlock = 4
invalid = 5
handled = 6
}
}
- Original Message -
From: "Michel van Dop" <[EMAIL PROTECTED]>
To:
Sent: Saturday, January 15, 2005 8:11 PM
Subject: Re: SQL db failover
Okay good, i replace any refrence to "sql" module and fix it.
But how do i replace this? In "group" or "sql1,sql2" or ?
What's the name of sql1 and sql2 ?
Thank you,
Michel
- Original Message -
From: "Alan DeKok" <[EMAIL PROTECTED]>
To:
Sent: Saturday, January 15, 2005 5:58 PM
Subject: Re: SQL db failover
"Michel van Dop" <[EMAIL PROTECTED]> wrote:
Okay i understand that ( i hope so):
Now i do this in radiusd.conf:
Yup, that should work.
I get this error cat /var/log/radius/radius.log
Sat Jan 15 13:35:19 2005 : Error: ERROR: Cannot find a configuration
entry
for module "sql".
Some part of radiusd.conf has a reference to an "sql" module. Find
that, fix it, and it should work.
Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
Okay good, i replace any refrence to "sql" module and fix it. But how do i replace this? In "group" or "sql1,sql2" or ? What's the name of sql1 and sql2 ? Thank you, Michel - Original Message - From: "Alan DeKok" <[EMAIL PROTECTED]> To: Sent: Saturday, January 15, 2005 5:58 PM Subject: Re: SQL db failover "Michel van Dop" <[EMAIL PROTECTED]> wrote: Okay i understand that ( i hope so): Now i do this in radiusd.conf: Yup, that should work. I get this error cat /var/log/radius/radius.log Sat Jan 15 13:35:19 2005 : Error: ERROR: Cannot find a configuration entry for module "sql". Some part of radiusd.conf has a reference to an "sql" module. Find that, fix it, and it should work. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
"Michel van Dop" <[EMAIL PROTECTED]> wrote: > Okay i understand that ( i hope so): > Now i do this in radiusd.conf: Yup, that should work. > I get this error cat /var/log/radius/radius.log > Sat Jan 15 13:35:19 2005 : Error: ERROR: Cannot find a configuration entry > for module "sql". Some part of radiusd.conf has a reference to an "sql" module. Find that, fix it, and it should work. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
Hi,
I have to run TWO MySQL servers and have FreeRadius fall over between them
modules {
# same place where this $INCLUDE ${confdir}/sql.conf
$INCLUDE ${confdir}/sql1.conf
$INCLUDE ${confdir}/sql2.conf
On 'authorize' section of radiusd.conf i change the 'sql' entry to a
'group' one:
group {
sql1 {
fail = 1
notfound = return
noop = 2
ok = return
updated = 3
reject = return
userlock = 4
invalid = 5
handled = 6
}
sql2 {
fail = 1
notfound = return
noop = 2
ok = return
updated = 3
reject = return
userlock = 4
invalid = 5
handled = 6
}
}
radiusd -X
...
WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
rlm_sql (sql1): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and
linked
rlm_sql (sql1): Attempting to connect to
[EMAIL PROTECTED]:/westwireless
rlm_sql (sql1): starting 0
rlm_sql (sql1): Attempting to connect rlm_sql_mysql #0
rlm_sql_mysql: Starting connect to MySQL server for #0
rlm_sql (sql1): Connected new DB handle, #0
rlm_sql (sql1): starting 1
rlm_sql (sql1): Attempting to connect rlm_sql_mysql #1
rlm_sql_mysql: Starting connect to MySQL server for #1
rlm_sql (sql1): Connected new DB handle, #1
rlm_sql (sql1): starting 2
rlm_sql (sql1): Attempting to connect rlm_sql_mysql #2
rlm_sql_mysql: Starting connect to MySQL server for #2
rlm_sql (sql1): Connected new DB handle, #2
rlm_sql (sql1): starting 3
rlm_sql (sql1): Attempting to connect rlm_sql_mysql #3
rlm_sql_mysql: Starting connect to MySQL server for #3
rlm_sql (sql1): Connected new DB handle, #3
rlm_sql (sql1): starting 4
rlm_sql (sql1): Attempting to connect rlm_sql_mysql #4
rlm_sql_mysql: Starting connect to MySQL server for #4
rlm_sql (sql1): Connected new DB handle, #4
Module: Instantiated sql (sql1)
sql: driver = "rlm_sql_mysql"
sql: server = "192.168.170.6"
sql: port = ""
sql: login = "root"
sql: password = "password"
sql: radius_db = "westwireless"
sql: acct_table = "radacct"
sql: acct_table2 = "radacct"
sql: authcheck_table = "users"
sql: authreply_table = "radreply"
sql: groupcheck_table = "radgroupcheck"
sql: groupreply_table = "radgroupreply"
sql: usergroup_table = "users"
sql: nas_table = "nas"
sql: dict_table = "dictionary"
sql: sqltrace = no
sql: sqltracefile = "/var/log/radius/sqltrace.sql"
sql: deletestalesessions = yes
sql: num_sql_socks = 5
sql: sql_user_name = "%{User-Name}"
sql: default_user_profile = ""
sql: query_on_not_found = no
sql: authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM
users WHERE Username = '%{SQL-User-Name}' ORDER BY id"
sql: authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM
radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id"
sql: authorize_group_check_query = "SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,users WHERE users.Username = '%{SQL-User-Name}' AND
users.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id"
sql: authorize_group_reply_query = "SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,users WHERE users.Username = '%{SQL-User-Name}' AND
users.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id"
sql: accounting_onoff_query = "UPDATE radacct SET AcctStopTime='%S',
AcctSessionTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime),
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay =
%{Acct-Delay-Time} WHERE AcctSessionTime=0 AND AcctStopTime=0 AND
NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'"
sql: accounting_update_query = "UPDATE radacct SET FramedIPAddress =
'%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND
UserName = '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND
AcctStopTime = 0"
sql: accounting_start_query = "INSERT into radacct (RadAcctId,
AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId,
NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,
ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets,
CalledStationId, CallingStationId, AcctTerminateCause, ServiceType,
FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('',
'%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}',
'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', '%S',
'0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}',
'%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"
sql: accounting_start_query_alt = "UPDATE radacct SET AcctStartTime = '%S',
AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}'
WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}'
AND NASIPAddress = '%{NAS-IP-Address}' A
Re: SQL db failover
Okay i understand that ( i hope so):
Now i do this in radiusd.conf:
modules {
# same place where this $INCLUDE ${confdir}/sql.conf
$INCLUDE ${confdir}/sql1.conf
$INCLUDE ${confdir}/sql2.conf
# insert this i reed this in the doc confiruable_failover
always handled {
rcode = handled
}
# i copy sql.conf to sql1.conf and sql2.conf and edit this files
# and edit the begin in sql1.conf to sql sql1{ and in the sql2.conf i edit
sql sql2{
accounting {
# i place this in accounting and remove sql
redundant {
sql1 # try module sql1
sql2 # if that's down, try module sql2
handled # otherwise drop the request as
}
I get this error cat /var/log/radius/radius.log
Sat Jan 15 13:35:19 2005 : Error: ERROR: Cannot find a configuration entry
for module "sql".
- Original Message -
From: "Alan DeKok" <[EMAIL PROTECTED]>
To:
Sent: Saturday, January 15, 2005 1:26 AM
Subject: Re: SQL db failover
"Michel van Dop" <[EMAIL PROTECTED]> wrote:
So i leave the default config in radiusd.conf include the file sql.conf
and put this in my radiusd.conf:
$INCLUDE ${confdir}/sql.conf
modules {
Once again, the include for sql.conf MUST BE INSIDE of the "modules"
section. It's there in the default config. Why do you expect it to
work when you move it outside?
sql sql1 {
driver = "rlm_sql_mysql
server="myfirstserver.example"
# Insert the rest of config of sql.conf in here
Why? Just copy "sql.conf" to "sql1.conf" and "sql2.conf". Edit
THOSE files to start off with "sql sql1 {" and "sql sql2 {" instead of
just "sql {". Then, $INCLUDE both of the files in radiusd.conf, in
the SAME PLACE that the normal sql.conf file is included.
Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
"Michel van Dop" <[EMAIL PROTECTED]> wrote:
> So i leave the default config in radiusd.conf include the file sql.conf
> and put this in my radiusd.conf:
>
> $INCLUDE ${confdir}/sql.conf
>
> modules {
Once again, the include for sql.conf MUST BE INSIDE of the "modules"
section. It's there in the default config. Why do you expect it to
work when you move it outside?
> sql sql1 {
> driver = "rlm_sql_mysql
> server="myfirstserver.example"
> # Insert the rest of config of sql.conf in here
Why? Just copy "sql.conf" to "sql1.conf" and "sql2.conf". Edit
THOSE files to start off with "sql sql1 {" and "sql sql2 {" instead of
just "sql {". Then, $INCLUDE both of the files in radiusd.conf, in
the SAME PLACE that the normal sql.conf file is included.
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
Thank you Alan,
Sorry but this is new for me. Freeradius is now running but on one db.
Now the last step freeradius SQL db failover!
So i leave the default config in radiusd.conf include the file sql.conf
and put this in my radiusd.conf:
$INCLUDE ${confdir}/sql.conf
modules {
sql sql1 {
driver = "rlm_sql_mysql
server="myfirstserver.example"
# Insert the rest of config of sql.conf in here
# include this? -> STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY
id"
# etc.
}
sql sql2 {
driver = "rlm_sql_mysql
server="mysecondserver.example"
# Insert the rest of config of sql2.conf in here
# include this? -> STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY
id"
# etc.
}
always handled {
rcode = handled
}
}
accounting {
redundant {
sql1 # try module sql1
sql2 # if that's down, try module sql2
handled # otherwise drop the request as
# it's been "handled" by the "always"
# module (see doc/rlm_always)
}
}
Thats it? Thank you to support me.
Michel
- Original Message -
From: "Alan DeKok" <[EMAIL PROTECTED]>
To:
Sent: Friday, January 14, 2005 3:56 PM
Subject: Re: SQL db failover
Michel van Dop <[EMAIL PROTECTED]> wrote:
I use freeradius-0.9.3-1.1 and freeradius-mysql-0.9.3-1.1 I now this is
old
but this is the last stap!
I would suggest upgrading. See http://www.freeradius.org/security.html
I have this in the radiusd.conf
$INCLUDE ${confdir}/sql.conf
$INCLUDE ${confdir}/sql2.conf
The default configuration includes sql.conf from *inside* of the
"modules" section. Why have you taken them out?
modules {
sql sql {
}
sql sql2 {
}
And you've just defined two SQL modules with ZERO configuration.
Let me guess: you put those two entries there because after you
removed "$INCLUDE ...sql.conf" from the "modules" section, the server
complained about "no such module sql". Now that you've added empty
SQL configuration, the server doesn't give that errror, but gives
another one.
The solution is to follow the default configuration of the server.
Don't move things around if you don't know why they're in a particular
location. And if the server complains after you've moved things
around anyways, odds are that you did the wrong thing.
Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
sql.conf
Description: Binary data
Re: SQL db failover
Michel van Dop <[EMAIL PROTECTED]> wrote:
> I use freeradius-0.9.3-1.1 and freeradius-mysql-0.9.3-1.1 I now this is old
> but this is the last stap!
I would suggest upgrading. See http://www.freeradius.org/security.html
> I have this in the radiusd.conf
>
> $INCLUDE ${confdir}/sql.conf
> $INCLUDE ${confdir}/sql2.conf
The default configuration includes sql.conf from *inside* of the
"modules" section. Why have you taken them out?
> modules {
> sql sql {
> }
> sql sql2 {
> }
And you've just defined two SQL modules with ZERO configuration.
Let me guess: you put those two entries there because after you
removed "$INCLUDE ...sql.conf" from the "modules" section, the server
complained about "no such module sql". Now that you've added empty
SQL configuration, the server doesn't give that errror, but gives
another one.
The solution is to follow the default configuration of the server.
Don't move things around if you don't know why they're in a particular
location. And if the server complains after you've moved things
around anyways, odds are that you did the wrong thing.
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
SQL db failover
Hi,
The mirroring between the two mysql db (3.23.58-4) work with the replication
mechanism of mysql and I have two sql.conf to
describe the way to connect to each db and right config on radiusd.conf
I use freeradius-0.9.3-1.1 and freeradius-mysql-0.9.3-1.1 I now this is old
but this is the last stap!
I have this in the radiusd.conf
$INCLUDE ${confdir}/sql.conf
$INCLUDE ${confdir}/sql2.conf
modules {
sql sql {
}
sql sql2 {
}
always handled {
rcode = handled
}
}
Fri Jan 14 15:23:35 2005 : Error: rlm_sql (sql): "mysql" is NOT an SQL driver!
Fri Jan 14 15:23:35 2005 : Error: radiusd.conf[480]: sql: Module instantiation
failed.
I read the configurable_failover doc. But i don't understanding.
I'm guessing I'm doing something wrong. Any help would be great!
Thanks Michel
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
On Thu, 30 Sep 2004, Edgars wrote:
> ok, thanks for the input.
> So, what can someone suggest what configuration should i add only for
> such a case: if sql1 is down then go and search for the user to sql2?
Your question has already been answered. Just replace accounting with authorize.
authorize{
[...]
redundant{
sql1
sql2
}
}
>
> Edgars
>
> Alan DeKok wrote:
>
> >"Cris Boisvert" <[EMAIL PROTECTED]> wrote:
> >
> >
> >> $INCLUDE ${confdir}/sql.conf
> >> $INCLUDE ${confdir}/sql2.conf
> >>
> >> modules {
> >>sql sql {
> >> }
> >>sql sql2 {
> >> }
> >>
> >>
> >
> > The $INCLUDE ${confdir}/sql.conf should be INSIDE of the modules{}
> >section, which means you don't need to have TWO definitions for it.
> >
> > i.e. delete the empty "sql sql {}" and "sql sql2 {}" blocks from
> >modules{}. They're wrong, and you don't need them.
> >
> > Alan DeKok.
> >
> >
> >-
> >List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
> >
> >
> >
> >
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
--
Kostas Kalevras Network Operations Center
[EMAIL PROTECTED] National Technical University of Athens, Greece
Work Phone: +30 210 7721861
'Go back to the shadow' Gandalf
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
ok, thanks for the input.
So, what can someone suggest what configuration should i add only for
such a case: if sql1 is down then go and search for the user to sql2?
Edgars
Alan DeKok wrote:
"Cris Boisvert" <[EMAIL PROTECTED]> wrote:
$INCLUDE ${confdir}/sql.conf
$INCLUDE ${confdir}/sql2.conf
modules {
sql sql {
}
sql sql2 {
}
The $INCLUDE ${confdir}/sql.conf should be INSIDE of the modules{}
section, which means you don't need to have TWO definitions for it.
i.e. delete the empty "sql sql {}" and "sql sql2 {}" blocks from
modules{}. They're wrong, and you don't need them.
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
It looks like its working... YEEEA!!!
Thanx Very much...
The
> always handled {
> rcode = handled
> }
> }
I found in the example in the /doc/configure-failover
Thanx again..
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Kostas
Kalevras
Sent: Wednesday, September 29, 2004 12:33 PM
To: [EMAIL PROTECTED]
Subject: RE: SQL db failover
On Wed, 29 Sep 2004, Cris Boisvert wrote:
> I have this in my radiusd.conf now. I get this error when I try to start
> radius
>
>
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #0
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #1
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #2
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #3
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #4
> Wed Sep 29 12:00:27 2004 : Error: radiusd.conf[14] Failed to link to
module
> 'rlm_sql2': rlm_sql2.so: cannot open shared object file: No such file or
> directory
>
>
>
> In my sql2.conf it looks like this.
> sql2 {
This should be sql sql2
>
> driver = "rlm_sql_mysql"
>
> server = "192.168.1.100"
> login = "user"
> password = "password"
>
> radius_db = "radius"
>
> acct_table1 = "radacct"
> acct_table2 = "radacct"
>
>
>
>
> $INCLUDE ${confdir}/sql.conf
> $INCLUDE ${confdir}/sql2.conf
>
> modules {
> sql sql {
> }
> sql sql2 {
> }
Where did you see this? Delete it.
> always handled {
> rcode = handled
> }
> }
>
>
> authorize {
> ...
> redundant {
> sql
> sql2
> }
> ...
> }
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
>
>
>
> -
> List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
>
--
Kostas Kalevras Network Operations Center
[EMAIL PROTECTED] National Technical University of Athens, Greece
Work Phone: +30 210 7721861
'Go back to the shadow' Gandalf
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
"Cris Boisvert" <[EMAIL PROTECTED]> wrote:
> $INCLUDE ${confdir}/sql.conf
> $INCLUDE ${confdir}/sql2.conf
>
> modules {
> sql sql {
> }
> sql sql2 {
> }
The $INCLUDE ${confdir}/sql.conf should be INSIDE of the modules{}
section, which means you don't need to have TWO definitions for it.
i.e. delete the empty "sql sql {}" and "sql sql2 {}" blocks from
modules{}. They're wrong, and you don't need them.
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
On Wed, 29 Sep 2004, Cris Boisvert wrote:
> I have this in my radiusd.conf now. I get this error when I try to start
> radius
>
>
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #0
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #1
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #2
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #3
> Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
> server for #4
> Wed Sep 29 12:00:27 2004 : Error: radiusd.conf[14] Failed to link to module
> 'rlm_sql2': rlm_sql2.so: cannot open shared object file: No such file or
> directory
>
>
>
> In my sql2.conf it looks like this.
> sql2 {
This should be sql sql2
>
> driver = "rlm_sql_mysql"
>
> server = "192.168.1.100"
> login = "user"
> password = "password"
>
> radius_db = "radius"
>
> acct_table1 = "radacct"
> acct_table2 = "radacct"
>
>
>
>
> $INCLUDE ${confdir}/sql.conf
> $INCLUDE ${confdir}/sql2.conf
>
> modules {
> sql sql {
> }
> sql sql2 {
> }
Where did you see this? Delete it.
> always handled {
> rcode = handled
> }
> }
>
>
> authorize {
> ...
> redundant {
> sql
> sql2
> }
> ...
> }
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
>
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
--
Kostas Kalevras Network Operations Center
[EMAIL PROTECTED] National Technical University of Athens, Greece
Work Phone: +30 210 7721861
'Go back to the shadow' Gandalf
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
I have this in my radiusd.conf now. I get this error when I try to start
radius
Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #0
Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #1
Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #2
Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #3
Wed Sep 29 12:00:27 2004 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #4
Wed Sep 29 12:00:27 2004 : Error: radiusd.conf[14] Failed to link to module
'rlm_sql2': rlm_sql2.so: cannot open shared object file: No such file or
directory
In my sql2.conf it looks like this.
sql2 {
driver = "rlm_sql_mysql"
server = "192.168.1.100"
login = "user"
password = "password"
radius_db = "radius"
acct_table1 = "radacct"
acct_table2 = "radacct"
$INCLUDE ${confdir}/sql.conf
$INCLUDE ${confdir}/sql2.conf
modules {
sql sql {
}
sql sql2 {
}
always handled {
rcode = handled
}
}
authorize {
...
redundant {
sql
sql2
}
...
}
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
On Wed, 29 Sep 2004, Paul wrote:
> On Wed, 2004-09-29 at 08:55, Kostas Kalevras wrote:
> > On Tue, 28 Sep 2004, Paul wrote:
> >
> > >
> > > What i need is failover for accounting and session tracking.
> > > Auth will be done using LDAP.
> > >
> > > Mirroring the DB is not possibe because accounting has alot of inserts,
> > > that would need to have multi-master replication.
> > >
> > > I would like to use application data mirroring instead, having 2
> > > databases and both servers writing(acct)to both but not fail unless
> > > both databases are not accessible, reading (session) from local
> > > only and failover to the other DB in case of failure only.
> >
> > For accounting replication your best choise is radrelay. For failover, just use
> > a failover section in radiusd.conf
> >
>
> Radrelay is only for text file accounting, not SQL. (unless i'm wrong)
> I would like to use sql instead, that why i want to write to both DB's.
You keep two radius/sql servers and use radrelay to sync them. radrelay sends
data to a *radius* server so you can log the data to whatever you want.
>
> How would you configure accounting to only fail when both entries,
> sql1 and sql2, fail ?
If you don't want to use radrelay:
accounting{
redundant{
sql1
sql2
}
}
>
>
>
> > session{
> > redundant{
> > sql1
> > sql2
> > }
> > [...]
> > }
> >
> > authorize{
> > [...]
> > redundant{
> > ldap1
> > ldap2
> > }
> > }
> >
> > We have a similar setup working just fine in the Greek School Network (two
> > radius servers, serving different regions but keeping full accounting).
> >
> > --
> > Kostas Kalevras Network Operations Center
> > [EMAIL PROTECTED] National Technical University of Athens, Greece
> > Work Phone: +30 210 7721861
> > 'Go back to the shadow' Gandalf
> >
> > -
> > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
--
Kostas Kalevras Network Operations Center
[EMAIL PROTECTED] National Technical University of Athens, Greece
Work Phone: +30 210 7721861
'Go back to the shadow' Gandalf
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
On Wed, 2004-09-29 at 08:55, Kostas Kalevras wrote:
> On Tue, 28 Sep 2004, Paul wrote:
>
> >
> > What i need is failover for accounting and session tracking.
> > Auth will be done using LDAP.
> >
> > Mirroring the DB is not possibe because accounting has alot of inserts,
> > that would need to have multi-master replication.
> >
> > I would like to use application data mirroring instead, having 2
> > databases and both servers writing(acct)to both but not fail unless
> > both databases are not accessible, reading (session) from local
> > only and failover to the other DB in case of failure only.
>
> For accounting replication your best choise is radrelay. For failover, just use
> a failover section in radiusd.conf
>
Radrelay is only for text file accounting, not SQL. (unless i'm wrong)
I would like to use sql instead, that why i want to write to both DB's.
How would you configure accounting to only fail when both entries,
sql1 and sql2, fail ?
> session{
> redundant{
> sql1
> sql2
> }
> [...]
> }
>
> authorize{
> [...]
> redundant{
> ldap1
> ldap2
> }
> }
>
> We have a similar setup working just fine in the Greek School Network (two
> radius servers, serving different regions but keeping full accounting).
>
> --
> Kostas Kalevras Network Operations Center
> [EMAIL PROTECTED] National Technical University of Athens, Greece
> Work Phone: +30 210 7721861
> 'Go back to the shadow' Gandalf
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
Yup that’s what I'm getting also... now..
I'm glad I'm not alone
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Edgars
Sent: Wednesday, September 29, 2004 11:30 AM
To: [EMAIL PROTECTED]
Subject: Re: SQL db failover
i'm also trying to make this configuration. But something doesn't stick
together.
i've added the following lines in the modules section:
sql sql1 {
}
sql sql2 {
}
but when starting the server the following appears:
Module: Instantiated preprocess (preprocess)
radiusd.conf[11] Failed to link to module 'rlm_sql1': rlm_sql1.so:
cannot open shared object file: No such file or directory
where could be the problem?
thanks!
Edgars
Kostas Kalevras wrote:
>On Tue, 28 Sep 2004, Paul wrote:
>
>
>
>>What i need is failover for accounting and session tracking.
>>Auth will be done using LDAP.
>>
>>Mirroring the DB is not possibe because accounting has alot of inserts,
>>that would need to have multi-master replication.
>>
>>I would like to use application data mirroring instead, having 2
>>databases and both servers writing(acct)to both but not fail unless
>>both databases are not accessible, reading (session) from local
>>only and failover to the other DB in case of failure only.
>>
>>
>
>For accounting replication your best choise is radrelay. For failover, just
use
>a failover section in radiusd.conf
>
>session{
> redundant{
> sql1
> sql2
> }
> [...]
>}
>
>authorize{
> [...]
> redundant{
> ldap1
> ldap2
> }
>}
>
>We have a similar setup working just fine in the Greek School Network (two
>radius servers, serving different regions but keeping full accounting).
>
>--
>Kostas KalevrasNetwork Operations Center
>[EMAIL PROTECTED] National Technical University of Athens, Greece
>Work Phone:+30 210 7721861
>'Go back to the shadow'Gandalf
>
>-
>List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
>
>
>
>
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
"Cris Boisvert" <[EMAIL PROTECTED]> wrote:
> I have been trying to do the same thing I have the references in the
> radius.conf as notated in the Doc's although I cannot get it to
> Connect to the second sql server
> I have this in the radius.conf
>
> $INCLUDE ${confdir}/sql.conf
> $INCLUDE ${confdir}/sql2.conf
>
> modules {
> sql sql {
> }
> sql sql2 {
...
You have to list "sql2" in the section where you want it to be used.
> I just need it to authenticate off one database and fail to the
> other if = the first is unavailable ...
Do:
authorize {
...
redundant {
sql
sql2
}
...
}
This is taken from "doc/configurable_failover".
Alan DEkok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL db failover
i'm also trying to make this configuration. But something doesn't stick
together.
i've added the following lines in the modules section:
sql sql1 {
}
sql sql2 {
}
but when starting the server the following appears:
Module: Instantiated preprocess (preprocess)
radiusd.conf[11] Failed to link to module 'rlm_sql1': rlm_sql1.so:
cannot open shared object file: No such file or directory
where could be the problem?
thanks!
Edgars
Kostas Kalevras wrote:
On Tue, 28 Sep 2004, Paul wrote:
What i need is failover for accounting and session tracking.
Auth will be done using LDAP.
Mirroring the DB is not possibe because accounting has alot of inserts,
that would need to have multi-master replication.
I would like to use application data mirroring instead, having 2
databases and both servers writing(acct)to both but not fail unless
both databases are not accessible, reading (session) from local
only and failover to the other DB in case of failure only.
For accounting replication your best choise is radrelay. For failover, just use
a failover section in radiusd.conf
session{
redundant{
sql1
sql2
}
[...]
}
authorize{
[...]
redundant{
ldap1
ldap2
}
}
We have a similar setup working just fine in the Greek School Network (two
radius servers, serving different regions but keeping full accounting).
--
Kostas Kalevras Network Operations Center
[EMAIL PROTECTED] National Technical University of Athens, Greece
Work Phone: +30 210 7721861
'Go back to the shadow' Gandalf
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
On Tue, 28 Sep 2004, Paul wrote:
>
> What i need is failover for accounting and session tracking.
> Auth will be done using LDAP.
>
> Mirroring the DB is not possibe because accounting has alot of inserts,
> that would need to have multi-master replication.
>
> I would like to use application data mirroring instead, having 2
> databases and both servers writing(acct)to both but not fail unless
> both databases are not accessible, reading (session) from local
> only and failover to the other DB in case of failure only.
For accounting replication your best choise is radrelay. For failover, just use
a failover section in radiusd.conf
session{
redundant{
sql1
sql2
}
[...]
}
authorize{
[...]
redundant{
ldap1
ldap2
}
}
We have a similar setup working just fine in the Greek School Network (two
radius servers, serving different regions but keeping full accounting).
--
Kostas Kalevras Network Operations Center
[EMAIL PROTECTED] National Technical University of Athens, Greece
Work Phone: +30 210 7721861
'Go back to the shadow' Gandalf
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
On Tue, 2004-09-28 at 15:16, Paul wrote:
> What i need is failover for accounting and session tracking.
> Auth will be done using LDAP.
>
> Mirroring the DB is not possibe because accounting has alot of inserts,
> that would need to have multi-master replication.
>
> I would like to use application data mirroring instead, having 2
> databases and both servers writing(acct)to both but not fail unless
> both databases are not accessible, reading (session) from local
> only and failover to the other DB in case of failure only.
Also forgot to mention that both servers would be used, since they are
in 2 different locations used by 2 different access servers that are in
the same POP.
> Thanx
> Paul
>
> On Tue, 2004-09-28 at 13:19, Cris Boisvert wrote:
> > I have been trying to do the same thing I have the references in the
> > radius.conf as notated in the Doc's although I cannot get it to
> > Connect to the second sql server
> > I have this in the radius.conf
> >
> > $INCLUDE ${confdir}/sql.conf
> > $INCLUDE ${confdir}/sql2.conf
> >
> > modules {
> > sql sql {
> > }
> > sql sql2 {
> > }
> > always handled {
> > rcode = handled
> > }
> > }
> >
> > I'm guessing I'm doing something wrong. Any help would be great..
> >
> > I just need it to authenticate off one database and fail to the other if the
> > first is unavailable ...
> > All accounting can be written to the first databse... I'm not worried about
> > that for now..
> >
> > Thanx
> >
> >
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of EROS
> > Sent: Tuesday, September 28, 2004 12:53 PM
> > To: [EMAIL PROTECTED]
> > Subject: SQL db failover
> >
> > In doc/configurable_failover you have your answer.
> >
> > I've tried the same schema you wanna do and it works perfectly
> > The mirroring between the two mysql db work with the replication
> > mechanism of mysql (read the mysql doc) and I have two sql.conf to
> > describe the way to connect to each db and right config on radiusd.conf
> > to tell it how using this two db.
> >
> > If you want some more help ask ...
> >
> >
> > :]
> >
> > -Message d'origine-
> > De : [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] De la part de Paul
> > Envoyé : mardi 28 septembre 2004 18:12
> > À : [EMAIL PROTECTED]
> > Objet : SQL db failover
> >
> >
> >
> > Hi folks.
> >
> >
> > We need to have a 2 server deployment with accounting and session
> > tracking using an SQL db (mysql or postgres).
> >
> > What i would like to do is:
> >
> > - Each server has a local DB running
> > - Both DB's should have data synchronized for sessino tracking
> > - All accounting is written to local DB and remote DB with the
> > following logic: write local then remote, if local fails ignore
> > failure, for session query only local DB, if local fails then query
> > the remote DB.
> >
> > Anyone tried something simillar ? Any suggestions to
> > sql.conf/radiusd.conf syntax ?
> >
> > Thanx
> > Paul
> >
> >
> >
> > -
> > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
> >
> >
> > -
> > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
> >
> > ---
> > Incoming mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
> >
> >
> >
> > -
> > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
What i need is failover for accounting and session tracking.
Auth will be done using LDAP.
Mirroring the DB is not possibe because accounting has alot of inserts,
that would need to have multi-master replication.
I would like to use application data mirroring instead, having 2
databases and both servers writing(acct)to both but not fail unless
both databases are not accessible, reading (session) from local
only and failover to the other DB in case of failure only.
Thanx
Paul
On Tue, 2004-09-28 at 13:19, Cris Boisvert wrote:
> I have been trying to do the same thing I have the references in the
> radius.conf as notated in the Doc's although I cannot get it to
> Connect to the second sql server
> I have this in the radius.conf
>
> $INCLUDE ${confdir}/sql.conf
> $INCLUDE ${confdir}/sql2.conf
>
> modules {
> sql sql {
> }
> sql sql2 {
> }
> always handled {
> rcode = handled
> }
> }
>
> I'm guessing I'm doing something wrong. Any help would be great..
>
> I just need it to authenticate off one database and fail to the other if the
> first is unavailable ...
> All accounting can be written to the first databse... I'm not worried about
> that for now..
>
> Thanx
>
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of EROS
> Sent: Tuesday, September 28, 2004 12:53 PM
> To: [EMAIL PROTECTED]
> Subject: SQL db failover
>
> In doc/configurable_failover you have your answer.
>
> I've tried the same schema you wanna do and it works perfectly
> The mirroring between the two mysql db work with the replication
> mechanism of mysql (read the mysql doc) and I have two sql.conf to
> describe the way to connect to each db and right config on radiusd.conf
> to tell it how using this two db.
>
> If you want some more help ask ...
>
>
> :]
>
> -----Message d'origine-
> De : [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] De la part de Paul
> Envoyé : mardi 28 septembre 2004 18:12
> À : [EMAIL PROTECTED]
> Objet : SQL db failover
>
>
>
> Hi folks.
>
>
> We need to have a 2 server deployment with accounting and session
> tracking using an SQL db (mysql or postgres).
>
> What i would like to do is:
>
> - Each server has a local DB running
> - Both DB's should have data synchronized for sessino tracking
> - All accounting is written to local DB and remote DB with the
> following logic: write local then remote, if local fails ignore
> failure, for session query only local DB, if local fails then query
> the remote DB.
>
> Anyone tried something simillar ? Any suggestions to
> sql.conf/radiusd.conf syntax ?
>
> Thanx
> Paul
>
>
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
>
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL db failover
I have been trying to do the same thing I have the references in the
radius.conf as notated in the Doc's although I cannot get it to
Connect to the second sql server
I have this in the radius.conf
$INCLUDE ${confdir}/sql.conf
$INCLUDE ${confdir}/sql2.conf
modules {
sql sql {
}
sql sql2 {
}
always handled {
rcode = handled
}
}
I'm guessing I'm doing something wrong. Any help would be great..
I just need it to authenticate off one database and fail to the other if the
first is unavailable ...
All accounting can be written to the first databse... I'm not worried about
that for now..
Thanx
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of EROS
Sent: Tuesday, September 28, 2004 12:53 PM
To: [EMAIL PROTECTED]
Subject: SQL db failover
In doc/configurable_failover you have your answer.
I've tried the same schema you wanna do and it works perfectly
The mirroring between the two mysql db work with the replication
mechanism of mysql (read the mysql doc) and I have two sql.conf to
describe the way to connect to each db and right config on radiusd.conf
to tell it how using this two db.
If you want some more help ask ...
:]
-Message d'origine-
De : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] De la part de Paul
Envoyé : mardi 28 septembre 2004 18:12
À : [EMAIL PROTECTED]
Objet : SQL db failover
Hi folks.
We need to have a 2 server deployment with accounting and session
tracking using an SQL db (mysql or postgres).
What i would like to do is:
- Each server has a local DB running
- Both DB's should have data synchronized for sessino tracking
- All accounting is written to local DB and remote DB with the
following logic: write local then remote, if local fails ignore
failure, for session query only local DB, if local fails then query
the remote DB.
Anyone tried something simillar ? Any suggestions to
sql.conf/radiusd.conf syntax ?
Thanx
Paul
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
SQL db failover
In doc/configurable_failover you have your answer. I've tried the same schema you wanna do and it works perfectly The mirroring between the two mysql db work with the replication mechanism of mysql (read the mysql doc) and I have two sql.conf to describe the way to connect to each db and right config on radiusd.conf to tell it how using this two db. If you want some more help ask ... :] -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Paul Envoyé : mardi 28 septembre 2004 18:12 À : [EMAIL PROTECTED] Objet : SQL db failover Hi folks. We need to have a 2 server deployment with accounting and session tracking using an SQL db (mysql or postgres). What i would like to do is: - Each server has a local DB running - Both DB's should have data synchronized for sessino tracking - All accounting is written to local DB and remote DB with the following logic: write local then remote, if local fails ignore failure, for session query only local DB, if local fails then query the remote DB. Anyone tried something simillar ? Any suggestions to sql.conf/radiusd.conf syntax ? Thanx Paul - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
SQL db failover
Hi folks. We need to have a 2 server deployment with accounting and session tracking using an SQL db (mysql or postgres). What i would like to do is: - Each server has a local DB running - Both DB's should have data synchronized for sessino tracking - All accounting is written to local DB and remote DB with the following logic: write local then remote, if local fails ignore failure, for session query only local DB, if local fails then query the remote DB. Anyone tried something simillar ? Any suggestions to sql.conf/radiusd.conf syntax ? Thanx Paul - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
