On Mon, Apr 29, 2002 at 05:57:41PM +0300, Do-Risika RAFIEFERANTSIARONJY wrote:
>
> For those using mysql, how do you replicate your authentication
> (and eventually your accounting) tables to a backup server ? I'm
> looking for the best method.
This problem hunted me for a long time. I have managed to string up
a solution that may not be the best but works fine.
I have three boxes lets call them A, B and C. All of them run mysql
3.23.xx. A and B run FreeRADIUS. A is the primary radius auth/acc
server and B is the secondary/backup auth/acc server. C is purely a
database backup. The following diagram shows the inter-relation
between the boxes:
A: auth <--- mysql A ---> replicate to mysql B
---> replicate to mysql C
A: accn ---> detail ---> mysql A ---> replicate to mysql C
B: auth <--- mysql B
B: accn ---> detail ---> rsync to A ---> mysql A
Management scripts: ---> mysql A
Billing queries: <--- mysql C
LEGEND: mysql A means MySQL running on box A. Replication
is mysql's built-in one way replication.
A cron job on box A runs every X minutes, reads the detail files and
inserts stop records to the accounting database. The script is
clever enough to skip over the part of detail file it already put
into the database. Management scripts (account add/delete, pass
change) only changes the auth db in box A. Billing software (with
long SELECTs) only queries box C.
I know there will be a few questions, here are the answers in
advance:
Q1. FR can put acc records directly to mysql, why the hell are ya
doing the same stuff from the detail file?
A1. First, I have learned the hard way "plain text" files are the most
reliable things you can have in computers. When I screw up
mysql I can reliably reproduce it from the detail files without
any more coding.
Second, integrating accounting records from box B is possible
this way. Otherwise our billing and some management scripts
would have to read acct databases from both A and B.
Q2. What happens when A goes down?
A2. Auth/acc records go to B. Auth works instantly. Acc records
are accumulated in the detail file of B. I can take all the
time I need to bring back A without worrying of lost acc
records. The only problem while I do this is no one can change
their passwords and users can't see their latest usage records.
Not a big problem because bringing A back shouldn't take more
than a few hours.
When A comes back it will automatically integrate the accounting
records from B.
Q3. What happens when B goes down?
A3. No problemma. When it comes back, mysql's built-in replication
will send the changes it missed while it was down.
Q4. What happens when you need to upgrade MySQL in A?
A4. Relax! This is one of the reasons I am doing this in a round
way with the detail file. Edit sql.conf in A to point to mysql
B, kill -HUP radius, take as much time as I need to upgrade
MySQL A, have lunch, point sql.conf back to mysql A, kill -HUP
radius. Database upgraded witout one second of down time.
Upgrading FR is also very simple with this scheme.
Q5. This looks way too cumbersome, is this the best method to have
high availability?
A5. Buddy, I have tried many approaches and looked into even more.
Hit the limits everywhere, mostly with mysql.
Oracle with High Availability would be ideal in this case. But
it's too much more complicated than mysql. Also, we would need
to hire a good Oracle DBA who will have nothing to do the whole
year, just waiting for a possible accident.
If you have a better approach, please let me know.
--
Mojahed
System Administrator, Agni Systems Limited
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
