Master-slave mysql redundancy

[Daniele Mantovani]

Thank for the reply Alan

  TBH, the simplest thing is to have the second RADIUS server write
> directly to the main SQL accounting database.  Then if the database is
> down, have it wrote to a "detail" file.
>
>   See raddb/sites-available/decoupled-accounting for some documentation
> and examples.
>

That sound interesting :)

I'm looking at the decoupled-accounting and if I've understood well, it's a
sort of pipe:
You send the accounting to a detail log file, then a virtual server read
that detail log file and send that to the sql.

This method is suggested even on the master server? (So the accounting
virtual server end the request in less time, and then the sql insert is
done in another virtualserver)

One thing that I don't see is how it work in case of the sql server is not
working:
Does it use a sort of pointer/position, to read the data from the detail
log, like the mysql binary log?
So if the sql is not available, when it come back online, it start to
insert the data from the last un-inserted data?
Or is a sort of pipe, and it only try to insert to sql once, when the data
is written to the log file?

Thanks again
Cheers

2017-10-23 18:03 GMT+02:00 Alan DeKok <aland at deployingradius.com>:

> On Oct 23, 2017, at 11:20 AM, Daniele Mantovani <
> dmantovani at salesianisesto.it> wrote:
> >
> > I've setup two servers with freeradius 3, to have a redundant setup.
> > On the first server there a mysql master instance, and on the second
> server
> > there's the slave instance.
> > I'm using sql in radius to do some check, with the sql xlat, and to do
> some
> > accounting.
> >
> > My goal is to find a setup for the second server, that would use the
> local
> > slave instance to do read-only operation (like the sql xlat query), and
> the
> > master instance to write the accounting data.
>
>   That should be possible.  Typically you set up master-slave replication
> for the authorization information, too.
>
> > My initial idea was to use two sql instance in the setup, and use the
> right
> > one in the various places, but reading around I've found some information
> > about rml_sql_log and radsqlrelay, and I want to understand if it's a
> > better option in my case.
>
>   Database replication is not a simple thing, unfortunately.  It's often
> better to let RADIUS do the replication, as it's smarter.
>
>   TBH, the simplest thing is to have the second RADIUS server write
> directly to the main SQL accounting database.  Then if the database is
> down, have it wrote to a "detail" file.
>
>   See raddb/sites-available/decoupled-accounting for some documentation
> and examples.
>
> > So I'm looking at the man pages but I haven't understand how to setup
> > radsqlrelay:
> > -Does I need to start the radsqlrelay command as a daemon? I haven't seen
> > any start script, or a way to make freeradius start that for me...
> > -Does I need to setup the rlm_sql_log module + radsqlrelay on all the
> > servers?
> > Or only on the slave server, to communicate with the database?
> > -Does the two radsqlrelay instance need to communicate eachother?
>
>   I wouldn't run radsqlrelay.  It's better to just use RADIUS packets, and
> have the server figure out what queries to run.
>
>   See raddb/mods-config/sql/mysql/queries.conf for some reasons why.
> That file contains a whole set of queries which are run in order to get the
> data *correctly* into SQL.
>
>   If you let FR use those queries to write to SQL, then the data in SQL
> will be good.  If you use sql_log, it will write only ONE query to the
> file, and if that query fails, the data is lost.
>
>   Don't use radsqlrelay.  It should probably be removed from 3.0.
>
>   Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/
> list/users.html




-- 
Daniele Mantovani
Opere Sociale don Bosco - Salesiani Sesto san Giovanni
02 26 292 397