Mikrotik with Freeradius/mySQL – Trimming & Archiving RADACCT # Part-8

---

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s>

FREERADIUS WITH MIKROTIK – Part #2  – COA

FREERADIUS WITH MIKROTIK – Part #3 – Expiration

FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding

FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions

FREERADIUS  WITH MIKROTIK – Part # 6 – External Auth Script & RADPOSTAUTH

FREERADIUS WITH MIKROTIK – Part #7  – Quota Limit

FREERADIUS WITH MIKROTIK – Part #8  – RADACCT – Trimming & Archiving < You are here

---

Personnel Note:

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not an ROCKET SCIENCE. The only thing required is the ultimate passion to achieve the goal. And with the right search, reading, understanding logic’s, you can do all on your own. I strongly encourage to read the FR mailing list and Google

OS: Ubuntu 16.04.3 LTS / 64bit

---

Disclaimer:
There are some other neat-to-Perfect methods like archive users usage data from radacct table to the archive data , per user ONE row only.  This will take much lesser size , but I need to track some data therefore I duplicated the whole table. Else you can use TRIGGER on RADACCT too which can update other user table for the usage only. All depend son the scenario and requirements.

---

---

Scenario:

Radius accounting is stored in radacct table which contains the user usage related data . Over the period of time this data can take lot of space as time passes. It can also create performance related issues as well if you donot take care of it like slow or timeout queries because of hundreds of thousands of entries.

I received complains from few networks that radius is giving timeout because they were installed few years ago and there radacct table have grown enormously in size thus resulting ins slow queries and timeout in authentication specially in Radius Manager which uses external auth script to validate the user request.

Solution:

• Use SSD disks (or RAID10), they are more performance oriented storage,
• Adding more RAM is plus point for any database server.

We will create another table named  radacct_archive  and move accounting data older than 6 months from the radacct table to radacct_archive table.

This way queries will work faster dueto less data in radacct. Also we will delete data older than 12 months from `radacct_archive` table too so that it may not grow large later.

First we will clone the radacct table structure into new radacct_archive table.

# This is one time step.
mysql -uroot -pzaib1234 -s -e "use radius; create table radacct_archive LIKE radacct;"

Now you can create a bash script and schedule it to run DAILY in less busy timings like 5 am in the morning.

# --- Copy data from CURRENT radacct table to new radacct_archive table (for archive purposes)
mysql -uroot -pSQLPASS -s -e "use radius; INSERT INTO radacct_archive SELECT * FROM radacct WHERE acctstoptime > 0 AND date(acctstarttime)  0 AND date(acctstarttime) < (CURDATE() - INTERVAL 6 MONTH);"
# --- Now Delete data from RADACCT_ARCHIVE table so that it should not grow either more then we required i.e 1 Year - one year archived data is enough IMO
mysql -uroot -pSQLPASS -s -e "use radius; DELETE FROM radacct_archive WHERE date(acctstarttime) < (CURDATE() - INTERVAL 12 MONTH);"

---

BASH script for Radacct Trimming [Last Updated November 2019]

“Note to myself”

Following code was used in bash script which was configured to run every half hour

It will create another DB named radacct_db and table named table radacct_archive tables

#!/usr/bin/env bash
####!/bin/sh
#set -x
#MYSQL DETAILS
DATE=`date`
logger radacct_trim script started $DATE
SQLUSER="root"
SQLPASS="ROOTPASS"
DB="radius"
DB_ARCHIVE="radacct_db"
TBL_ARCH="radacct_archive"
TBL_ARCH_EXISTS=$(printf 'SHOW TABLES LIKE "%s"' "$TBL_ARCH")
MONTHS="12"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
# This is one time step.
echo "
Script Started @ $DATE
"
echo "- Step 1 : Checking for DB: radacct_db and TABLE: $TBL_ARCH ..."
DBCHK=`mysqlshow --user=$SQLUSER $DB_ARCHIVE | grep -v Wildcard | grep -o $DB_ARCHIVE`
if [ "$DBCHK" == "$DB_ARCHIVE" ]; then
echo " > $DB_ARCHIVE DB found"
else
echo " > $DB_ARCHIVE not found. Creating now ..."
$CMD "create database if not exists radacct_db;"
fi
if [[ $(mysql -u$SQLUSER -e "$TBL_ARCH_EXISTS" $DB_ARCHIVE) ]]
then
echo " > $TBL_ARCH TABLE found IN DB: $DB_ARCHIVE"
else
echo " > $TBL_ARCH TABLE not found IN DB: $DB_ARCHIVE. Creating now ..."
$CMD "use $DB_ARCHIVE; create table if not exists radacct_archive LIKE radius.radacct;"
fi

# Start Action: copy data from radacct to new db/archive table
NOTULL_COUNT=`$CMD "use $DB; select count(*) from radacct WHERE acctstoptime is not null;"`
echo "- Step 2 : Found $NOTULL_COUNT records in radacct table , Now copying $NOTULL_COUNT records to radacct_db / radacct_archive table ..."
$CMD "use $DB_ARCHIVE; INSERT IGNORE INTO radacct_archive SELECT * FROM radius.radacct WHERE acctstoptime is not null;"
echo "- Step 3 : Deleting $NOTULL_COUNT records old data from radacct table (which have acctstoptime NOT NULL) ..."
# --- Now Delete data from CURRENT RADACCT table so that it should remain fit and smart ins size
$CMD "use $DB; DELETE FROM radacct WHERE acctstoptime is not null;"
echo "- Step 4 : Copying old data from radacct_archive older then $MONTHS months ..."
# --- Now Delete data from RADACCT_ARCHIVE table so that it should not grow either more then we required i.e 1 Year - one year archived data is enough IMO
$CMD "use $DB_ARCHIVE; DELETE FROM radacct_archive WHERE date(acctstarttime) < (CURDATE() - INTERVAL $MONTHS MONTH);"
DATE=`date`
logger radacct_trim script ended with $NOTULL_COUNT records processed for trimming @ $DATE
echo "
radacct_trim script ended with $NOTULL_COUNT records processed for trimming @ $DATE

"

---

CRONTAB

Schedule it to run every hour (at 25 minutes, like 00:25 , 01:25 , 02:25 and so on)

25 * * * * /temp/radacct_trim.sh

---

Regard’s
Syed Jahanzaib