Syed Jahanzaib Personal Blog to Share Knowledge !

April 22, 2019

MySql Database Recovery from Raw Files

Filed under: Linux Related, Radius Manager — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 2:31 PM

mysql recovery.PNG

Disclaimer: This worked under particular case. It may or may not work for everyone.


OS: Ubuntu 12.4 Servedit Edition / x86

MYSQL: Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.2

The OP was running radius for AAA. The disk got faulty for some unknown reasons and were unable to boot from it. There was no database backup [Real example of bad practices] So restoration from mysqldump to new system was not an option there !


We need to restore the Database using mysql raw files. Luckily the faulty disk was able to got attached to other system & we were able to copy the core /var/lib/mysql/ folders (along with all sub folders in it)

Quick & Dirty Restoration Step !

Requires some good level of Linux / DB knowledge]

  • Setup a test SANDBOX, Install same level of OS along with MYSQL on new system/disk. Create databases / tables as required. Verify all is working by logging to mysql
  • Stop the MYSQL service.
  • Copy the folder /var/lib/mysql [copied from faulty disk] to this new box under /var/lib/mysql/  
  • Set the permission on newly copied files/folders
    chown mysql -R /var/lib/mysql/

After this point Try to start the MYSQL service , IF it starts successfully & you can see your DATA , then skip below steps , ELSE continue through below steps …

  • Edit the /etc/mysql/my.cnf & add following line under [mysqld] section
    innodb_force_recovery = 6
  • Start MYSQL service & the service will start in Safe Mode with limited working support. Verify if you can able to login to MYSQL service by
    mysql -uroot -pPASS
  • If above step works, Export the Database backup using mysqldump cmd e.g:
    mysqldump -uroot -pSQLPASS   radius  >  radius_db_dump_.sql
  • Once done, Open the file in nano or any other text editor, & verify if it contains the required data.

Now copy the radius_db_dump_.sql to safe location & you know what to do next 🙂

  • Import this mysqldump file to your working radius system !



Make sure you have multistage backup strategies in place for any mission critical server.

Example for mysql Database, You can do following

  • If your server is VM, then VEEAM B&R will be your best friend & guardian, go for it
  • 1st Stage Backup: [Highly recommended for live replication]
    ideally, you should have at least 2 Replica servers & configure either Master-Master or Master-Slave Replication
  • 2nd Stage backup:
    Create bash scripts to export DB backup in local folder on a daily basis, (or hourly basis if required]
  • 3rd Stage backup:
    Attach external USB disk to the server, and in your backup script, add this usb as additional backup repository
  • 4th Stage backup:
    Configure DROPBOX and add it as additional backup repository
  • 5th Stage backup:
    The admin should manually copy the backup folders to his desktop so that if all other backups gets failed , this should come in handy.

Syed Jahanzaib




March 25, 2019

Mikrotik with Freeradius/mySQL # Part-21 – Weird Trigger for Duplicate Users

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

dup user.jpg


Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others.

Syed Jahanzaib~


  • We have DMASOFTLAB radius manager installed as a billing system in Ubuntu 12.04 server
  • Mikrotik version 6.4x.x is acting as Hotspot NAS and connected with radius for AAA

Requirement: [A Weird one really]

As operator demanded

“We are running Hotspot on mikrotik, & client login to hotspot using his mobile/laptop. If logged-in client leaves his primary location without logout, & move to another location, & if he try to login from another device, his request will gets DENY because of Single user limit. We increased it to 2 by using SIM-USE=2 directive in user properties,It allows second session to login, but both sessions can use the bandwidth, therefore we want that once second session is established its old first live session should get kicked. If it was single Hotspot we could have used the script on LOGIN, but there are several NAS spreaded across various location using single radius.”

if the user uses same device then we could have used

if (User-Name){
if("%{sql:UPDATE radacct set AcctStopTime=ADDDATE(AcctStartTime,INTERVAL AcctSessionTime SECOND), AcctTerminateCause='Clear-Stale Session' WHERE UserName='%{User-Name}' and CallingStationId='%{Calling-Station-Id}' and AcctStopTime is NULL}"){

but things are different in hotspot as I have observed, if devices are different then it will give us already logged-in error, if we use sim-use=2 then second device can be logged-in but old session will also be alive and both ids will suck the bandwidth at a time.

Also using idle-timeout or keep-alive timeout is the simplest way to achieve this , but for some weird reasons and to avoid long arguments dueto accent issues, I made one customized solution for the operator.


Login to mysql with root

mysql -uroot -pXXXX

and switch to radius database

use radius;

Now create new table that will hold duplicate users record

MYSQL Table to hold duplicate users list

-- Table structure for table `rm_dupusers`

DROP TABLE IF EXISTS `rm_dupusers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `rm_dupusers` (
`username` varchar(64) NOT NULL,
`ip` varchar(16) NOT NULL,
`nas` varchar(16) NOT NULL,
`comments` varchar(64) DEFAULT NULL,
KEY `dupid` (`dupid`)
/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `rm_dupusers`

MYSQL TRIGGER to check duplicate users sessions

Now we will create a new Trigger that will be executed when any record is inserted in radacct, it will check for existing duplicate session of user and if it found , it will add its entry in the mysql table of rm_dupusers

drop trigger chk_dup_user;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `chk_dup_user` BEFORE INSERT ON `radacct` FOR EACH ROW BEGIN
SET @dupuserchk = (SELECT count(*) from radacct where username=New.username and acctstoptime is NULL);
IF (@dupuserchk = 1) THEN
SET @dupusername = (SELECT username from radacct where username=New.username and acctstoptime is NULL);
SET @dupuserip = (SELECT framedipaddress from radacct where username=New.username and acctstoptime is NULL);
SET @dupusernas = (SELECT nasipaddress from radacct where username=New.username and acctstoptime is NULL);
INSERT into rm_dupusers (dupid,username,ip,nas,comments) values ('',@dupusername,@dupuserip,@dupusernas,'Duplicate User');
END */;;

Mysql Part is Done.

Now we will create a BASH script that will scheduled to run every minute.

BASH script !

Create bash script in desired folder, in this example I am using /temp folder as default

mkdir /temp
touch /temp/
chmod +x /temp/
nano /temp/

& paste following, make sure to modify credentials

#set -x
# Following script is made specifically for Dmasoftlab radius manager 4.1.x
# When any new user will login, it will simply check if exists session of same user found, it will kick previous session
# it requires custom trigger on radacct table, this script will be schedule to run every minute
# Created: 25-MARCH-2019
# Tested on Ubuntu OS Only
# CHANGE these
currenttime=$(date +%H:%M:%S)
# Add Script start execution entry in the /var/log/syslog to see if the script got executed or not
logger "Duplicate User poller script Started @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"
echo "- Script Start Time - $currenttime"
echo "- Checking Duplicate Users in $DUP_TABLE table ..."
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information

# Checking if /temp folder is previously present or not . . .
if [ ! -d "$TEMP" ]; then
echo "- INFO: $TEMP folder not found, Creating it now to store logs ..."
mkdir $TEMP
echo -e "- INFO: $TEMP folder is already present to store logs."



IPADD=`ip route get 1 | awk '{print $NF;exit}'`
SRVSTATUS=`service $SRV status |grep running |wc -l`
if [ "$SRVSTATUS" -ne 1 ];
#if [ -z "$SRVSTATUS" ];
echo "- ALERT:

- $SRV not responding ***
- $currenttime

Exiting ..."
exit 1
echo "- INFO: $SRV service is accessible. Proceeding further ... OK"

# Check if table exists
if [ $($CMD \
"select count(*) from information_schema.tables where \
table_schema='$DB' and table_name='$DUP_TABLE';") -eq 1 ]; then
echo "- INFO: $DUP_TABLE Table exists ..."
echo "- WARNING: $DUP_TABLE Table does not exists ..."
# Enable following line so that it will update all users simultanous-use to '2' so that two sessions can be established
# $CMD "use $DB; UPDATE  radius.radcheck SET value = '2' where Attribute = 'Simultaneous-Use';
# pull user record
$CMD "use $DB; select username,ip,nas from $DUP_TABLE WHERE datetime >= NOW() - INTERVAL $INT MINUTE;" >> $TMPUSRINFO
if [ ! -s $TMPUSRINFO ]
endtime=$(date +%H:%M:%S)

echo "
- INFO: No Duplicate User found in DMA RADIUS MANAGER TABLE '$DUP_TABLE' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - $endtime
exit 1

# Apply Count Loop Formula while deleting first line which have junk text
cat $TMPUSRINFO | while read users
username=`echo $users | awk '{print $1}'`
USER_IP=`echo $users | awk '{print $2}'`
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where framedipaddress ='$USER_IP' AND acctstoptime is NULL;"`
NAS_IP=`echo $users | awk '{print $3}'`
NAS_SECRET=`$CMD "use $DB; select secret from nas where nasname = '$NAS_IP' ;"`

# Print Info on screen
echo "Duplicate User Found: USER: $username , IP: $USER_IP, ID: $ACCTSESID, $NAS: $NAS+IP @ $currenttime ... KICKING him now ..."
echo "Duplicate User Found: USER: $username , IP: $USER_IP, ID: $ACCTSESID, $NAS: $NAS+IP @ $currenttime ... KICKING him now ..." >> $DUP_LIST_FILE
#echo User-Name=$USERNAME,Acct-Session-Id=$ACCTSESID,Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | $RADCLIENT -q -c 1 $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
#for hotspot, enable following line
echo Framed-IP-Address=$USER_IP | radclient -x -c 1 $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
# once done, we should delete the tmp files to clear the garbage

CRON scheduler to run the above script every minute. Edit crontab by

crontab -e

& add following entry

* * * * * /temp/ >/dev/null 2>&1

Testing …

Using same credentials, Login to first device, and then on second ,

& run this script,

root@radius:/temp# /temp/
- Script Start Time - 10:52:03
- Checking Duplicate Users in rm_dupusers table ...
- INFO: /temp folder is already present to store logs.
- INFO: mysql service is accessible. Proceeding further ... OK
- INFO: rm_dupusers Table exists ...
Duplicate User Found: USER: test , IP:, ID: 81d00057, : +IP @ 10:52:03 ... KICKING him now ...
Sending Disconnect-Request of id 58 to port 1700
Framed-IP-Address =
rad_recv: Disconnect-ACK packet from host port 1700, id=58, length=32
NAS-Identifier = "ZAIB_CCR_GW"

older session will be removed

radclient dc the first user.PNG

Weirdo …. but its fun to learn !


Command to view duplicate users session in freeradius using CLI

mysql -uroot -pMYPASS --skip-column-names -e 'use radius; SELECT username FROM radacct WHERE acctstoptime IS NULL;' > 1.txt && sort 1.txt | uniq -cd

Syed Jahanzaib

July 17, 2018

August 15, 2017

Enable `radpostauth` table for Radius Manager

Filed under: Radius Manager — Syed Jahanzaib / Pinochio~:) @ 3:40 PM

Following post was tested with DMA,

For better approach you may want to see following …

Freeradius External Auth BASH Script & RADPOSTUATH logging with customized reply message !

Freeradius is a well known billing system which is commonly used by ISP’s worldwide due to its reliability,  highly customizable and versatility. Many 3rd party vendors have made some good GUI fronted to manage the FR back-end engine.

It also sues mysql to facilitate logging of various users details. One of the table called radpsotauth which can hold information about users successfull/failed login attempts. Using this table, we can compliment our own GUI or 3rd party fronted (for easy management of freeradius engine) like DMASoftlab radius manager Authentication Log section so that admin / support can see users authentication logs. With some modification we can add useful information for quick troubleshooting example why user denied the authentication request, either invalid mac, wrong password, or account expired.

Note to *.*

  • You can add UNLAG query as well to apply IF statement, and add REPLY result according to your requirements.
  • This post was written for some reference purposes & will be updated as per request.
  • This guide is incomplete post. But it can be used as a reference as well for future retrieval


showing auth logs with errors numbers.JPG

As we can see in above image that in Radius Reply column, it clearly showing why user is denied like invalid mac address , account expires, invalid service reference (when user account id disabled in dma).

To enable these features we have to perform few steps as following …



First we need to edit the default sites config file for raddb.

Edit following file

nano /usr/local/etc/raddb/sites-available/default

Now search for “post-auth {” section

To make it simple and copy-paste format, Use following

post-auth {
Post-Auth-Type REJECT {

As showed in the image below …

psot-auth section

Save & Exit.


Edit the post-auth section in /usr/local/etc/raddb/sql/mysql/dialup.conf file

At the end of this file you will see “postauth_query” section. You need to change it

Old post-auth query


After changing


or copy paste text as below…

# Authentication Logging Queries
# postauth_query - Insert some info after authentication

postauth_query = "INSERT INTO ${postauth_table} \
(username, pass, reply, authdate, nasipaddress, mac) \
'%{User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{reply:Packet-Type} - %{reply:Reply-Message}', \
'%S', \
'%{NAS-IP-Address}', \

Save & Exit.

#Alter the RADPOSTAUTH table using mysql cmd …

Using mysql cmd, we will perform 2 functions

  1. Increase the REPLY column length to accommodate longer reply messages display properly
  2. Add the MAC Address column so we can detect the calling user device mac address
mysql -uroot -pPASSWORD
use radius;
ALTER TABLE radpostauth MODIFY `reply` VARCHAR( 100 );

Restart the RADIUSD service

service radiusd restart

using CMD, you can now see the authentication log table

mysql -uroot -pPASSWORD -e “use radius; select * from radpostauth;”

& you will see the information

phpmyadmin query for table

1 JOHN     123456     Access-Reject - The account has expired=21      2017-08-15 [14:14:05         10:FE:ED:33:BD:AX


  • You can modify the messages appearing in the different columns, you can add your own customized columns as well like called station, or others
  • You can add UNLAG query as well to apply IF statement, and add REPLY result according to your requirements.
  • It can chew up your disk space, so try to make text shortest possible, like error codes only, not the whole text.
  • scheduled a cron job so that it can empty the table on weekly/monthly basis so that it may not swallow disk space plus performance should remain optimal.
  • If used in heavy environment it can put considerable strain on your system resources by putting large quantity of mysql INSERT queries into the table.



Syed Jahanzaib


June 7, 2017

Generating Refill Cards in Radius Manager

Filed under: Radius Manager — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 2:29 PM

On Demand Guide!

DMASoftlab Radius Manager is a nice billing system for any ISP. It works on Linux base OS like Ubuntu / Centos etc. It uses Free-radius as back-end engine and PHP as fronted GUI. It provides variety of ways to manage users. You can manually create users . Or it also provide self registration service for user via registration option where user can sign up, choose his Id / Package & renew it using refill card.

Radius manager provides 2 type of Cards system.

  1. Prepaid Cards
  2. Refill Cards [Scratch card]


1- Prepaid cards are simpler as they contains username / password , and starts counting expiry/quota after first usage / login from user. they also binds with selected service. This is suitable for environment where users join.leave very frequently, like Cafe’s / restaurants / public hotspot places.

2- Refill cards does not contains any user name or password. they contains only amount, which user can deposit in his account himself by login to the UCP , user control panel and use redeem voucher option). Refill cards are useful if you have more permanent type of clients who renews on monthly basis. Using refill cards, you can providers with liberty to change his service on his own if required. off course he would required higher amount of refill card to renew his new upscale service. but user can do it all on his own if required. so its kind of fully automatic system as well.

Refill cards does not binds with any specific service, they contains only AMOUNT. Once the user add deposit using refill card code, his account wont get auto renewed (although we can use script for it) . He have to activate the service as well after depositing amount. So in short user have to perform 2 steps in order to renew his account. Using refill cards, you just have to only sell cards to user, rest user will maintain on his own.


We have created a service with 1mb speed, 15gb quota & 30 days expiry limit. We want to create refill cards so that we sell refill card to user, and user will renew his account by using the portal.

Service Example:

2- services

To create refill cards, Goto Card System / Generate Cards

1- refill card menu


3- refill generation process

To view these generated cards, Goto Cards System  / List Refill Cards

view refill

As you can see in above screenshot, it have generated cards. You can view it in CSV which will contains cards details in the text format (for local customized card printing in bulk), and if you click on PDF button, it will show you cards in ready to print format using PDF viewer As showed in the image below

refill card in pdf

Now you can sell this card to users and they can use it from there user panel called UCP.

Using REFILL card to renew account.

From user end, login to user portal, and you will see something like this for new / expired user

1- user.PNG

As we can see that user account is in expired state, to renew it ,

Goto REDEEM VOUCHER & enter the code of refill card.

2- refill number

Once submit, it will add 100 Rs deposit in user account. Goto HOME tab and you will see following


Notice that now user have 100 rs in DEPOSIT but the account is still in EXPIRED status. We will use this deposit amount to renew / activate the account.

Goto Purchase credits, and select 1 in AMOUNT field. As showed in the image below …

1- purchase credits


2- refill done


3- refill done

Go back to HOME tabe

4- done

Account is now active, and user can login by hotspot / ppp or whatever auth method you have in your mikrotik or other NAS.

You can also refill cards by sending SMS to the radius system connected gsm modem. You will be requiring KANNEL along with Play SMS as well. I shared the idea here.





June 2, 2017

Howto install DMASoftlab Radius Manager in Centos 7 – 64bit

Filed under: Radius Manager — Tags: , — Syed Jahanzaib / Pinochio~:) @ 12:17 PM



No doubt, Radius billing is one of the most important point of focus in any ISP network. ISP finance depends on this system to work smoothly. There are various ready made radius application based on LINUX are available in the market to cater different requirements of various operators. Among many, DMASoftlab Radius Manager is one of the most cheapest but feature rich radius candidate starting from 99$ only. It provide variety of options for any ISP. best thing is its ONE TIME COST only & it works life time 🙂 , you can install it locally on your system or on cloud as well, it requires Linux base OS like Debian/Ubuntu/Centos. Its full features can be viewed at Radius Manager web site. It required very little resources to run, provide plenty of user administration functions, backup / restoration / management is quite simple as well. plus in background you can accompany it with many scripting of your own to perform various customized functions.

We all know that cloud is every where now and almost all hosting providers are now providing latest versions of operating system like Centos 7. Some of us are well aware that DMA Radius Manager is quite comfortable with following flavors of nix …

  • Ubuntu 12.4 / 32bit [*** Best OS & my personnel choice, as recovery & troubleshooting is much easier in this variant]
  • Centos 6.5/6.6

But now a days many operators are switching to cloud where hosting providers are not providing older versions of centos/ubuntu. As i had to do few installation on cloud for few clients & I had to recall all the bits & bytes to do the installation every time, therefore I am posting step by step copy paste friendly version of guide on howto install Radius Manager in Centos 7. I posted ready made scripts for Ubuntu 12 Installation Script &Centos 6 in previous guides but This one is for Centos 7 to make installation quicker. I will make its script but better to do it manually as Centos 7 is a bit tricky which will give you some tough time in various aspects

  • OS Used in this guide = CentOS Linux release 7.3.1611 (Core)


1# Disabling Firewall to avoid interruption

First we will update YUM & install nano for easy text editing, as many of us are not much comfortable with VI. NANO is better …

yum install update
yum -y install nano

* Disable IPTABLES

service iptables stop
chkconfig iptables off

* Disable SELINUX

So that it may not interfere with our installation/accessing. To disable builtin firewall permanently in centos, edit following

nano /etc/selinux/config

and change the




Save & Exit .

*Disable Firewall(Daemon)

Now Disable Firewall’Daemon’ so that it may not interfere with our installation/accessing

systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld

Reboot system to take effect.

2# Installing various supporting modules !

1- Installing EPEL Repo to download various other supporting packages like mysql,apache etc

rpm -ivh
yum -y install mc wget crontabs make gcc libtool-ltdl curl mysql-devel php php-mysql php-gd php-snmp php-process ntp alpine mariadb-server mariadb php-mcrypt cronie wget net-tools psmisc
yum -y install glibc.i686
systemctl start mariadb.service
systemctl enable mariadb.service

3# Configuring Mysql Section

Now initiate mysql initial setup


Above command will ask few queries, read and select accordingly. ** Make sure to setup mysql root password **

*Now start / enable APACHE services 

systemctl start httpd.service
systemctl enable httpd.service

4# Adding IONCUBE module in PHP

Verify your PHP version  by php -v & copy the iocube load accordingly.
At my station, I had `PHP 5.4.16 (cli) (built: Nov  6 2016 00:29:02)` version installed by YUM.

tar -xvzf ioncube_loaders_lin_x86-64.tar.gz
cd ioncube
mv /usr/lib64/php/modules/
chmod 777 /usr/lib64/php/modules/

Now add ioncube library in php

nano /etc/php.ini

and add following line after [PHP] heading, usually this heading is in first line.

zend_extension = /usr/lib64/php/modules/

& restart APACHE service so it can load the new module

systemctl restart httpd.service

re-run ​​’php -v to verify you have something like [if successfull]

PHP 5.4.16 (cli) (built: Nov 6 2016 00:29:02)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies
with the ionCube PHP Loader (enabled) + Intrusion Protection from unconfigured) v6.1.0 (), Copyright (c) 2002-2017, by ionCube Ltd.

5# Download FREERADIUS & compile.

cd /temp
tar -xvzf freeradius-server-2.2.0-dma-patch-2.tar.gz
cd freeradius-server-2.2.0
make install

Above may take some time to compile. After its done, issue following command to initiate radius

radiusd -X

If you get error in first attempt, try to run it again, and you may see following message on success


Now press CTRL+C to return to command prompt.


6- Radius DB creation in MYSQL

Create mysql DATABASES for radius usage. Make sure to change the passwords where required, example mysql root password. It’s OK to use default passwords for the test flight, but for production server Its Highly recommended to use different passwords while you do the installation.

mysql -u root -pYOURMYSQLPASS
CREATE USER 'radius'@'localhost' IDENTIFIED BY 'radius123';
CREATE USER 'conntrack'@'localhost' IDENTIFIED BY 'conn123';
GRANT ALL ON radius.* TO radius@localhost;
GRANT ALL ON conntrack.* TO conntrack@localhost;

7# Download & Install RADIUS MANAGER 4.1.0

cd /temp/
tar zxvf radiusmanager-4.1.0.tgz
cd radiusmanager-4.1.0
chmod 755
# Now start the Radius Install Script.
# once you will run INSTALL.SH , it will ask various queries, select according to your OS choice 


8# Access Radius Manager Administration Panel

Now try to access your radius manager admin panel from any browser pointing to ..


If all ok , you will see following window in your browser


* Default ID Password 
ID = admin
Password = 1111



If you see error or blank page, use following Apache error/access logs for further trouble shoot. Most common causes are invalid license files / in-correct php ioncube files.

tail -f /var/log/httpd/error_log
tail -f /var/log/httpd/access_log




TIP#1 – How to create Virtual NIC with customized / cloned MAC address

First check what ethernet name you have currently, mostly its eth0, [change it according to your network].

ip link add link eth0 address 00:11:22:33:44:55 eth0.1 type macvlan
ifconfig eth0.1 up
ifconfig eth0.1

Verify if NIC is created with our desired NIC as well.


# Restart APACHE service if required, YKWIM ; )

systemctl restart httpd.service

TIP#2 – Deploying PATCH 4.1.5 for Centos

DMA released few patches for its radius application. Latest version is 4.1.6 , but I usually prefer to go with 4.1.5. Following is quick copy paste version to upgrade. Make sure that you have proper working installation of radius manager & if you are not much familiarized with the restoration process then you should backup your current installation before proceeding.

mkdir /temp
cd /temp
tar zxvf /temp/radiusmanager-4.1-cumulative_patch.tgz
cd /temp/radiusmanager-4.1-cumulative_patch/
cp -vrf www/radiusmanager/* /var/www/html/radiusmanager
chmod 755 bin/rm*
service rmpoller stop
cp bin/* /usr/local/bin
cp raddb/acct_users /usr/local/etc/raddb
chmod 640 /usr/local/etc/raddb/acct_users
chown root.root /usr/local/etc/raddb/acct_users
service radiusd restart
service rmpoller start

For more information, please refer to

Dmasoftlab Radius Manager 4.1.5 Patch

Note: If you find any errors in this guide, please do let me know

Thanks & Best Regard’s


March 2, 2017

RM Reseller Monthly Report via GMAiL

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


Disclaimer: This is not a reliable method for some OP to acquire the monthly sale report done by reseller. It requires some criteria to match like strict account creation by specific reseller id only. Better to use provided GUI in DMA_RM to inquire the proper report.

This is just a personal way of collecting reporting via automated email which MUST be cross verified by the OP .



Following is a bash script which will simply query invoices, categorize it according to reseller account, format it using html coding, sort, total and send email admin.


When you will execute this script, it will fetch the reports from rm_users database using last month dates, it will auto calculate last month dates.


Current month is March,2017. the script will pick Last Month which was February,2017 and will calculate number of days as well using CAL function.

Sample of Report is as follows.



RM Reseller Monthly Report via email


RM Reseller Monthly Report via email

Schelde following script via crontab @monthly. Modify information as per your local network.

# Script to query INVOICES section of Radius manager mysql, and
# categorize it according to resellers , calculate accounts,sort and total it.
# These are my personal way of doing things, and no one is bound to agree with these.
# Created: Year 2014
# set -x

LASTMY=`date +'%Y-%m' -d 'last month'`
LAST_MONTH_DIGIT=`date +'%m' -d 'last month'`
YEAR_DIGIT=`date +'%Y' -d 'last month'`
FMONTH=`date +'%B-%Y' -d 'last month'`


This System is powered by Syed_Jahanzaib"
BODY_TITLE="Report For Dealer Account asof $FMONTH"

echo "
<pre>" > $FILE
echo "<b>$BODY_TITLE</b>" >> $FILE
echo "<b>DEALER User's_Activated Used_Amount Balance</b>
" >> $FILE

mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select managername from rm_managers;" | while read dealer
DEALER=`echo $dealer | awk '{print $1}'`

ACTIVEUSERSNO=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '' AND rm_users.owner = '$DEALER';" | sed '/credited/d' | wc -l`
USEDAMOUNT=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '' AND rm_users.owner = '$DEALER';" | sed '/credited/d' | awk '{ sum+=$1} END {print sum}'`
BALANCE=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select balance from rm_managers WHERE managername = '$DEALER';" |cut -f1 -d"."`
MOBILE=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select mobile from rm_managers WHERE managername = '$DEALER';"`
SRV=`mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.service FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '2' ) AND (invgroup = '0' OR invgroup = '1' ) AND invnum != '' AND rm_users.owner = '$DEALER' ORDER BY id LIMIT 0, 10000;" | sed '/credited/d' | awk '{print $1}' | sort | uniq -c`

#if [ ! -n "$USEDAMOUNT" ]; then
#if [ "USEDAMOUNT == "" ]; then

#------------------------------------------------------------------------" >> $FILE


Qty Service&nbsp;Name


------------------------------------------------------------------------" >> $FILE


sed -e 's/\t//g' $FILE | column -t | sed 's/ //g' | sed 's/ User/User/g' > $FINALFILE
TOTNO=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '';" | sed '/credited/d' | wc -l`
SALES=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '';" | sed '/credited/d' | paste -sd+ | bc | cut -f1 -d"."`

echo "Total Users Activated/Renewed in $FMONTH = <b>$TOTNO</b>" >> $FINALFILE
echo "Total SALES Done in $FMONTH = <b>$SALES</b>" >> $FINALFILE
echo "
echo "Generated on $DATE" >> $FINALFILE
echo "</pre>

##Finally send email with all the data gathered USING SEND_EMAIL TOOL
/temp/sendEmail-v1.56/sendEmail -t $TO1 -u "INFO: GT DEALERS MONTHLY BILLING INFO for $FMONTH" -o tls=yes -s -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$FINALFILE -o message-content-type=$CONTENT_TYPE
# Type file for view


February 27, 2017

Radius Manager Theme/Skin Collection

Filed under: Radius Manager — Tags: , — Syed Jahanzaib / Pinochio~:) @ 3:33 PM

Following are few customized themes for DMASOFTLAB Radius manager , Designed by various web developers.

  1. Digital Theme [Responsive as well, made by KANGNDO]
  2. Green Theme
  3. Blue Theme


General Steps to install theme

I assume you have DMASoftlab Radius Manager 4.1.x version installed and must be fully functional.

  • Download the theme file, unrar it in any temporary folder. you can use `tar zxvf filename.tar.gz /destination_folder`
  • Copy (or let’s say Overwrite) all the contents including files/folders from the (unzipped) folder name radiusmanager in /var/www/radiusmanager (or for CENTOS, its /var/www/html/radiusmanager). You can cp -vr syntax /source /target
  • Clear the browser cache, and reload the ACP administration control panel) page.


1- Digital Theme

Download Link: Radius Manager KANGNDO Theme Official Link 


Sample Images:





2- Green Theme

Green Theme Download Link



Will update more later


Syed Jahanzaib

December 19, 2016

Incorrect key file for table ‘./conntrack/xxxx@xxxxxx@xxxxxx.MYI’; try to repair it

Filed under: Radius Manager, Uncategorized — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 1:05 PM


Incorrect key file for table ‘./conntrack/2016@002d12@002d16.MYI’; try to repair it

If you see the above error while accessing Connection Tracking Reports in Radius Manager then …


  • Make sure you aave plenty of Disk Space. Storage drive should be fast for high load servers preferably SSD or RAID based storage ,
  • Plenty of Memory is usually a good idea for DB systems ,
  • If its a heavy production server, try to move connection tracking to separate host as it takes lots of space and it adds good amount of payload to the CPU as well.
  • Perform tuning to enhance the mysql engine

To repair table …

Goto the conntrack folder [Ubuntu]
cd /var/lib/mysql/conntrack/
service mysql stop
# Change the file number as shown in the error 
myisamchk -r -v -f 2016@002d12@002d16.MYI

Once repair is done, start mysql / restart apache2 services , and test the desired tracking report again.

~Syed Jahanzaib~

August 24, 2016

Radius Manager Connection Tracking System for Mikrotik

Filed under: Mikrotik Related, Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 10:35 AM


PTA LETTER for log recording

As requested by few OP, following is a short technical reference guide on how you can enable TCP/UDP connections in Mikrotik to be stored in Radius manager connection tracking database so that you can view user base connection tracking report. Recently PTA also issues letter to all operators to comply with the order as showed in above scanned image …

In my personal view, it’s not much useful and at low end hardware it creates bottleneck, but if you have good resources with some fast storage (like SSD or RAID-10) it is better to set it up.

CTS is best described in the manual by dmasoftlab manual as mentioned below

Radius Manager has a special feature: the Connection Tracking System. It is available only in Radius Manager CTS version or higher. With the help of it the system can track and log all the TCP and UDP connections for all registered (online) users.

By default when You install the CTS enabled version of Radius Manager, it will use the default CTS database (CONNTRACK). It is strongly recommended to use a separate database host for the CONNTRACK database, due to the enormous amount of data stored daily. It can be even a 100-500 MegaBytes (and in my personal experience it can grow as much as 3-5 GB on busy network, ZAIB) per day. Fast disks (like SSD in RAID 10 mode,  zaib) are also recommended to be able to seek and store the data in real time. Radius Manager periodically stores the traffic data to CONNTRACK database (typically in
every 5–60 seconds).

Mikrotik (6.x) Configuration to enable Firewall Logging to remote server (RM)

If you have already configured the radius manager, then the conntrack database also get configured via the installation script. Next step is to enable the firewall logging in the mikrotik router so that mikrotik can send the categorized TCP/UDP data to radius manager conntrack database.

In mikrotik, open terminal and issue following commands …

In below example, we have following IP scheme.

PPPoE users ip pool =
Radius Manager IP  =

# Better to use address list rather then defining ip range
/ip firewall filter add chain=forward src-address= protocol=tcp connection-state=new action=log
# You may want to EXCLUDE UDP packets to avoid excessive logging
/ip firewall filter add chain=forward src-address= protocol=udp connection-state=new action=log
#Change the Radius server IP
/system logging
add name=rmctszaib remote= target=remote remote-port=4950
add topics=firewall action=rmctszaib
# Following line will avoid printing of logs on LOG window of TIK
set 0 action=memory disabled=no prefix="" topics=info,!firewall

If you don’t see any errors , you are good to Go.


1. Radius Manager should be licensed with CTS level. Basic license wont gonna work ..

2. Make sure that  RMCONNTRACK service is UP and running.To make sure it’s running , get its process by

ps aux |grep rmconntrack

and you should get result something like following

root@radius:/# ps aux |grep rmconntrack
root xxxx 0.0 0.0 xxxxxx xxx ? Ssl 13:22 0:00 /usr/local/bin/rmconntrack

if you see its running, proceed further , otherwise you may start it manually by

rmconntrack –x

Now login to RM ACP  >  Reports  >  Connection Report

Here you can get report for all or individual user.

As showed in the image below …





Syed Jahanzaib




Older Posts »

%d bloggers like this: