Syed Jahanzaib Personal Blog to Share Knowledge !

June 26, 2015

MYSQL Redundancy by using Master-Master Replication

Filed under: Linux Related, Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 11:12 AM

Video Link: https://www.youtube.com/watch?v=2AnCVMnr7FQ

master-master

In this Guide, I will show you howto create replica of your radius server so that in case of any server failure , you can instantly switch to backup server with the latest data available. In this model we will use MYSQL master-master concept in which whatever changes / records you make on any server, it will replicate to other as well. Also in mikrotik we can use primary and secondary radius server entries OR we can make a script to detect both radius status and act accordingly, all depend on your network requirements & infrastructure.

Scenario:

In this example we have FREERADIUS base billing system which uses freeradius and MYSQL DB as its backend engine,  installed (with basic level of installation) on two servers. Now we want to create redundancy by replicating radius DB to each other so that in case of one server failure, second server should come to rescue.

Requirements:

  • I assume that you have working mysql installed on both PC and tested its working by creating users in it.

Components Used:

  • SERVER1 NAME = MASTER-RADIUS
    OS = Ubuntu
    IP = 101.11.11.241
  • SERVER2 NAME = REPLICA-RADIUS
    OS = Ubuntu
    IP = 101.11.11.245

 

Let’s Start

Step – 1

Server1 = ‘master-radius’ Configuration

Open mysql config file

for Ubuntu 12.4 use following location

nano /etc/mysql/my.cnf

for Ubuntu 16.4 use following location

nano /etc/mysql/mysql.conf.d/mysqld.cnf

and add following under [mysqld] section

log-bin=mysql-bin
#(uncomment below line if you want only this DB replication)
#binlog-do-db=radius
server-id=1
log_bin = /var/log/mysql/mysql-bin.log
bind-address = 0.0.0.0  # Search this and change it manualy , donot copypaste 

SAVE and EXIT.

Now restart mysql service so changes can take effect.

service mysql restart

Now we need to create a user that will be used by mysql for replicating data between our two radius (or mysql) servers. As an example I am using id “zaib”. Replace “password” with the password you wish to use for replication.

create user 'zaib'@'%' identified by 'password';
grant replication slave on *.* to 'zaib'@'%'; 

Now we need to get some information about the current MySQL instance which we will later provide to server2 (replica).

The following command will output a few pieces of important information, which we will need to make note of:

show master status;

The output will look similar to the following, and will have two pieces of critical information: [file and position note it down)

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      336 | radius       |                  |
+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

We need to make a note of the above file name and position which will be used in the later step.

Now Lock tables to so that we can dump our db/tables

We can do that by typing the following:

FLUSH TABLES WITH READ LOCK;

Now dumpt the DB/Tables

mysqldump -u root -p --all-databases --master-data > dbdump.sql

Now UNLOCK the tables by

UNLOCK TABLES;

Upload this DBDUMP.SQL to secondary replica server, you can use scp command as well.

 


Step – 2

Server2 = ‘replica-radius’ Configuration

Open mysql config file

for Ubuntu 12.4 use following location

nano /etc/mysql/my.cnf

for Ubuntu 16.4 use following location

nano /etc/mysql/mysql.conf.d/mysqld.cnf

and add following under [mysqld] section

log-bin=mysql-bin
#(uncomment this if you want only this DB replication)
#binlog-do-db=radius
server-id=2
log_bin = /var/log/mysql/mysql-bin.log
bind-address = 0.0.0.0  # Search this and change it manualy , donot copypaste 

Make sure server-id is different then primary server

SAVE and EXIT.

Now restart mysql service so changes can take effect.

service mysql restart

Here we are going to create the user which will be responsible for the replication. Replace “password” with the password you wish to use.

create user 'zaib'@'%' identified by 'password';
grant replication slave on *.* to 'zaib'@'%'; 

IMPORT THE DB

mysql -u root -p < dbdump.sql

The next step involves taking the information that we took a note of earlier and applying it to our mysql instance. This will allow replication to begin. The following should be typed at the mysql shell:

stop slave;
CHANGE MASTER TO MASTER_HOST = '101.11.11.241', MASTER_USER = 'zaib', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 336;
start slave; 

Your values for MASTER_LOG_FILE and MASTER_LOG_POS may differ than those above. You should copy the values that “SHOW MASTER STATUS” returns on Server-1.

The last thing we have to do before we complete the mysql master-master replication is to lock tables and make note of the master log file and position to use to replicate in the other direction (from Server 2 to Server 1).

We can do that by typing the following:

SHOW MASTER STATUS; 

The output will look similar to the following:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      125 | radius       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Take note of the file and position, as we will have to enter this entry on server 1, to complete the two-way replication.

Step – 3

Completing Replication on Server1 [Master-radius]

Back on Server 1, we need to finish configuring replication on the command line.

Running this command will replicate all data from Server 2.

stop slave;
CHANGE MASTER TO MASTER_HOST = '101.11.11.245', MASTER_USER = 'zaib', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 125;
start slave; 

Keep in mind that your values may differ from those above. Please also replace the value of MASTER_PASSWORD with the password you created when setting up the replication user.

The output will look similar to the following:

Query OK, 0 rows affected (0.01 sec)

 

Now test the status by issuing command to mysql cli

show slave status\G

and you should see something similar to this. [don’t get confused with different numbers of log file file and position number, as this snap was taken in another lab]

replica-status


 

TEST

The last thing to do is to test that replication is working on both servers.

Open server1 radius panel, and try to create new user, after creation, it will be automatically replicated to server2 : )

As shown in the images below …

At a moment no users have been created.

server1-empty

 

Now create test user

server1-users-create

 

After creation, Goto Server2 (Replica) and check Users List, and you will find the user replicated.

server2-new0user0replicate-ok

and when you will create any user , it will replicate back to server1.


Adding both Radius Server entries in Mikrotik

Add both radius server

add-radius

and at radius manager, add the NAS (mikrotik)

add-nas

Don’t forget to rebuild clients.conf (from the menu) at secondary radius as well.

Now test by connecting any client , once successful, disconnect the primary radius, and try to connect the client again, once mikrotik will be unable to find primary entry, it will auto contact secondary server. as showed in the images below …

2radius

I will add few more details later….


Tips for Radius Manager

Tip: Make sure you have same credentials for both servers, that includes, root, mysql users, and in config files as well.

Example:

Change MYSQL Root ID password (old password to new)

mysql -u root -pROOTPASSWORD
use mysql;
update user set password=PASSWORD('MYNEWPASSWORD') where User='root';
flush privileges;

CHANGE RADIUS USER PASSWORD in mysql;

use mysql;
SET PASSWORD FOR 'radius'@'localhost' = PASSWORD('MYNEWPASSWORD');

=====================================

Then change password in following files for user ‘radius’

/etc/radiusmanager.cfg
/var/www/radiusmanager/config/system_cfg.php
/usr/local/etc/raddb/sql.conf

TIP: Remove MYSQL Replication

# Stop Mysql Service
service mysql stop
# Remove mysql replication entries
cd /var/lib/mysql
rm -f master.info relay-*`
# Start Mysql Service
service mysql start

To check MYSQL table Health and Auto Repair

show warnings;
SHOW TABLE STATUS;
mysqlcheck -uroot -pSQLROOTPASS --all-databases --auto-repair

DMA Note:

Please note that you may need to rebuild clients.conf from DMA web panel in order to initialize NAS list.

8 Comments »

  1. great thanks. was looking for something like it

    Like

    Comment by David — June 26, 2015 @ 4:40 PM

  2. Salam Alylkum
    Thanks for your great information you put in your blog.

    i had setup MYSQL Master-Master Replication on Ubuntu server 12.04 X86_64 LTS and works great
    but we had electricity problem in my area if power cut without proper shutdown replication stop working and I have to repeat from start
    is there any procedure to fix this issue
    also noticed that all data before replication setup didn’t sync i mean if i create users before setup replication those users will not transfer to the other data base

    Thanks In Advance

    Like

    Comment by Ahmed — July 12, 2015 @ 3:35 PM

    • I do want to setup the replication as well. Can someone confirm if what Salam indicated above is the case. If yes, will it work if I first copy the database on the first server to the second before setting up replication? and if power get lost like Salam indicated whats the fastest way to resume replication?

      Like

      Comment by Seun — October 8, 2015 @ 4:21 PM

  3. Excellent!

    Like

    Comment by woronti — October 12, 2015 @ 7:48 AM

  4. Thanks Syed.

    We are running master – master replication with 2 Radius Manager servers but keep getting errors in the MySQL logs and then the slave thread stops. The two tables are rm_onlinecm and rm_wlan:

    151112 15:35:22 [ERROR] Slave SQL: Error ‘Can’t find file: ‘./radius/rm_onlinecm1.frm’ (errno: 2)’ on query. Default database: ‘radius’. Query: ‘RENAME TABLE rm_onlinecm1 TO rm_onlinecm’, Error_code: 1017

    and

    151112 15:40:01 [ERROR] Slave SQL: Error ‘Table ‘rm_wlan1′ already exists’ on query. Default database: ‘radius’. Query: ‘CREATE TABLE rm_wlan1 (
    maccpe VARCHAR(17), `signal` SMALLINT, ccq SMALLINT, snr SMALLINT, apip VARCHAR(15), timestamp TIMESTAMP,
    KEY (maccpe)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8’, Error_code: 1050

    Any idea why this is happening?

    Like

    Comment by Nick — November 12, 2015 @ 6:42 PM

  5. i configured this one but if the master is stopped i cant able to login into switch . wtaht is the problem what i should do for this

    Like

    Comment by krishnaprasad — November 10, 2016 @ 4:38 PM

  6. Hi, thank you very much for the dedication and contributions you make to the community. I have followed all the instructions for radius redundancy and it works for me except the clients that do not log me in well. I tell you, the main radius is running with all the clients and the secondary one is a copy of the database and it was imported. We follow the steps and it doubles well when we create a new client but it does not show clients well online, when activating the secondary radius the primary loses the line data of the clients and so on. How could you solve this? Thank you very much in advance

    Like

    Comment by Carlos Miguel Garcia — December 29, 2016 @ 12:06 AM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: