Master-slave mysql redundancy
Hi to all, 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. 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. 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? Thanks to all Best regards -- Daniele Mantovani Opere Sociale don Bosco - Salesiani Sesto san Giovanni 02 26 292 397
On Oct 23, 2017, at 11:20 AM, Daniele Mantovani <dmantovani@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.
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@deployingradius.com>:
On Oct 23, 2017, at 11:20 AM, Daniele Mantovani < dmantovani@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
On Oct 24, 2017, at 9:02 AM, Daniele Mantovani <dmantovani@salesianisesto.it> wrote:
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.
Yes.
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 virtual server)
The master can write directly to SQL.
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?
Read the file. Look for "track progress". This is documented. Alan DeKok.
participants (2)
-
Alan DeKok -
Daniele Mantovani