Creating Redundant FreeRadius Servers with MySQL Replication

If you read my previous blog post on configuring DaloRadius you may be wondering what now?

It is bad practice to be reliant on a single radius server with no redundancy in place so this guide is written to supplement this.

The goal of this guide is to create TWO FreeRadius (DaloRadius Optional) Servers with a MySQL backend, then to utilize Multi-master MySQL replication to keep both databases in sync.

This will allow two way replication that permit’s management from both, modifications on both, and survivability in the event one goes down. Note that this provides availability but not recovery. In short, it means that you must still keep backups in case of an application or user error that wipes the database.

The high level steps are to setup 1-way replication (master) on Server-01, setup 1-way replication (slave) on Server-02, Start the replication, then convert this to a TWO-Way Multi-Master replication.

Assumptions

  • You have TWO servers with Linux installed (this guide was done with Ubuntu 12.04 but you can alter to fit any Linux install)
  • Both have appropriate IP addresses
  • You have IP connectivity between the two servers
  • You have login information to the MySQL databases on both servers
  • MySQL has been setup with initial configuration on BOTH servers (Following this my previous guide will suffice DaloRadius )

The two servers will hence-forth be referenced as Server-01 and Server-02

Step 1 : Preliminary MySQL Replication config on Server-01

In order for replication to work, MySQL must be bound to your network interfaces. For this guide we will just bind MySQL to all available interfaces.

Start by editing /etc/mysql/my.cnf

comment out bind-address=127.0.0.1
Append these lines within the [mysqld] directive:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=radius
server-id=1

Now on Server-01, we will set up the replication log file and apply the proper permissions.

touch /var/log/mysql/mysql-bin.log
chmod 666 /var/log/mysql/mysql-bin.log
chown mysql:mysql /var/log/mysql/mysql-bin.log

Restart MYSQL Server

service mysql restart

Connect to MySQL instance via CLI (replace root and mypass with your credentials):

mysql –host=localhost –user=root–password=mypass

For MySQL replication, we need a new username/password combo. We will use the username slave_user with a corresponding password ‘mypass‘. Replace with credentials of your choosing. We are also assuming the database name of ‘radius‘ the default for DaloRadius.

Within the MySQL CLI, run these commands:

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user‘@’%’ IDENTIFIED BY ‘mypass
FLUSH PRIVILEGES;
USE radius;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;        (NOTE: write down the FILE and POSITION columns, we will need these later)
QUIT;

Step 2 : Preliminary MySQL Replication config on Server-02

This is essential “Step 1” with a few modifications. For one, the server-id parameter is set to 2. Additionally we use the MySQL CLI and pull the logs and data from the primary.

Start by By editing /etc/mysql/my.cnf

comment out bind-address=127.0.0.1
Append these lines within [mysqld] directive:

log-bin = /var/log/mysql/mysql-bin.log
server-id=2
replicate-do-db=radius

Save and quit.

Next run these Linux shell commands

touch /var/log/mysql/mysql-bin.log
chmod 666 /var/log/mysql/mysql-bin.log
chown mysql:mysql /var/log/mysql/mysql-bin.log

Restart MYSQL Server

service mysql restart

Connect to MySQL instance via CLI (once again replace root and mypass with your credentials):

mysql –host=localhost –user=root –password=mypass

Run these MySQL Commands replacing the correct username, password, Log file name, and Log file position from Step 1 where appropriate.

CHANGE MASTER TO MASTER_HOST=”IP-ADDR-of-Server01“, MASTER_USER=”slave_user”, MASTER_PASSWORD=”mypass”, MASTER_LOG_FILE=”mysql-bin.000001″, MASTER_LOG_POS=107;        (NOTE: Replace the FILE and LOG POSITION values here with the values from Server-01)
Stop slave;
QUIT;

Step 3 : Manually Synchronize the DB across both the servers

On Server-01

backup the Radius DB (replace username root and db radius with the appropriate MySQL credentials):

mysqldump -uroot -p radius > mysqldump.sql

Copy DB backup you just made to Server-02 (replace the username and IP address/Hostname with the SSH login information of Server-02)

scp mysqldump.sql user@server-02:~

On Server-02

Import the DB Backup into MySQL:

mysql –user=root -password=mypass radius < mysqldump.sql

Connect to MySQL instance via CLI (replace root and mypass with your credentials):

mysql –host=localhost –user=root –password=mypass

Execute MySQL commands:

start slave;
quit:

Step 4 : Implement TWO-Way or MultiMaster replication

Assuming no errors in the above, we now have Active/Passive Multi-Master replication. You can test this by making a change on Server-01 and it should immediately be replicated to Server-02 automatically. This is a good way to set up but we are limited to only being able to make changes on a central server. Lets convert this to Multi-Master

On Server-02

Connect to MySQL instance via CLI (replace root and mypass with your credentials):

mysql –host=localhost –user=root–password=mypass

Execute these MySQL commands:

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user‘@’%’ IDENTIFIED BY ‘mypass
SHOW MASTER STATUS;        (NOTE: Once again write down the FILE and POSITION columns, we will need these later)
FLUSH PRIVILEGES;
QUIT;

On Server-01

Connect to MySQL instance via CLI (replace root and mypass with your credentials):

mysql –host=localhost –user=root–password=mypass

Execute these MySQL commands Replacing the parameters where apt:

CHANGE MASTER TO MASTER_HOST=”IP-ADDR-of-Server02“, MASTER_USER=”slave_user“, MASTER_PASSWORD=”mypass“, MASTER_LOG_FILE=”mysql-bin.000001”, MASTER_LOG_POS=100;       (NOTE: Replace the FILE and LOG POSITION values here with the values from Server-01)

Step 5 : Validate Replication

You are now complete! On both servers we should validate the Slave status. We can validate this synchronization is two way with the following procedure:

Connect to MySQL instance via CLI (replace root and mypass with your credentials):

mysql –host=localhost –user=root –password=mypass

Validate binary logging:

Show binary logs;

Validate MySQL Slave Status:

show slave status\G; (ensure Slave_IO_State is waiting for master)

Finally we can test with an innocuous change. For example on the DaloRadious of Server-01, change a NAS entry and run this query on Server-02

SELECT * FROM radius.nas LIMIT 5;

Validate the change exists, then perform a similar check with DaloRadious on Server-02 and the SQL query on Server-01

If everything looks good, congratulations. You can now simply point your radius endpoint to both servers as either a Round-Robin or Failover radius auth server. Dependin on whatever mechanism your client supports. You now can feel save that you have a very resilient setup now!