Master-slave mysql redundancy

[Alan DeKok]

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.