Syed Jahanzaib Personal Blog to Share Knowledge !

November 16, 2011

MySQL DB & HTML files Backup Script

LAST UPDATED:  13-Dec-2017

Following is a customized backup script [Made for Ubuntu] to create backup of your mysql DB and HTML files.  It creates FULL Backup of all configuration files related to RM & mysql database (radius) in local storage, you can connect your USB or secondary HDD. You can also configure cloud like DROPBOX so that copy of backup should be copied to CLOUD as well, MOST RECOMMENDED.

NOTE: Using S.S.D disk (or raid) are highly recommended as there are less chances of  media failure, and above all read/write rates is incredibly good. If you have configured RAID , then it will provide redundancy as well.

Modify the script as per your requirements …

  • I am using KANNEL as sms gateway & sendEmail app to send email via GMAIL.
  • It will make a copy of files in USB disk mounted at /USB folder
  • It will also delete files older then 30 days
  • It will send email and SMS with details
  • all backups will be saved in /backup folder

Please be-aware that I sometimes modify this file as per network requirements, so you may see revisions in this file. This is just an sample dummy, you can add remove any function in this script as per your requirements 🙂 ~ zaib

Let’s Start …

First create temp folder and create script in it.

mkdir /temp
cd /temp
chmod +x

Now open the file

nano /temp/

and paste the following code.

# set -x
# Version 1.1 / 10th January, 2014
# Last Modified / 17-DEC-2017
# Syed Jahanzaib / Web: / Email:
# This script creates FULL Backup of MySQL DB (Radius) and MYSQL DBR related DATA files.
# We can adjust it to do incremental basis backup too, but I based on my personnel experiences, I prefer to have FULL backup instead of incremental,
# Because you never know what you will going to need in case of disaster recovery
# Adjust below DATA fields accordingly. remove / add desired folders.
# Settings various VARIABLES for the script

# Colors Config . . . [[ JZ . . . ]]

# Following is only www RADIUS HTML FILES, mysqldb is separate from this, will come later in the script with mysqldump
# change target to match your's like /var/www/html
TARGET="/var/www  /sql_backup"

# Temporay folder where sql backup file will be replaced temporary



set $(date)
time=`date |awk '{print $4}'`
DT=`date +%d.%b.%Y__time_%H.%M`
YESTERDAY=`date --date='yesterday' +%Y-%m-%d`

#GMAIL Details

# KANNEL SMS Gateway Details if you want to send SMS using Local KANNEL sms gw

# START the BACKUP PROCESS ... #######

# Start counting start time
start_time=`date +%s`
echo -e "$COL_RED Welcome ! This is Radius Manager Backup Script, It will take backup of RADIUS Database and FoldersFollowing ...
Backup started at $6-$2-$3 Time $time . . .

Powered by Syed.Jahanzaib $COL_RESET"

# Checking if $SAVEDIR_FULL folder is previously present or not . . .
if [ ! -d "/$SAVEDIR_FULL" ]; then
echo -e "$COL_RED $SAVEDIR_FULL folder not found, Creating it so all backup's should be placed there . . . $COL_RESET"
echo -e "$COL_GREEN ********* /$SAVEDIR_FULL folder is already present , so no need to create it, Proceeding further . . . $COL_RESET"

# Checking if $SQL_DIR folder is previously present or not . . .
if [ ! -d "/$SQL_DIR" ]; then
echo -e "$COL_RED /$SQL_DIR folder not found, Creating it MSQL EXPORT/DUMP backup should be placed there . . . $COL_RESET"
mkdir /$SQL_DIR
echo -e "$COL_GREEN ********* /$SQL_DIR folder is already present , so no need to create it, Proceeding further . . . $COL_RESET"

# Checking if USB Is mounted or not...
#USBCHK=`mount |grep usb`
#if [ -z "$USBCHK" ]; then
#echo "USB is not mounted !!"
#echo "Mounting USB now..."
#mkdir /$USB
#mount -t auto /dev/$USBDEVICE /$USB
#echo "USB is mounted ! OK"

#if [ ! -d "/$USB" ]; then
#echo -e "$COL_RED /$USB folder not found, Creating it. . . $COL_RESET"
#mkdir /$USB
#echo -e "$COL_GREEN ********* /$USB folder is already present , so no need to create it, #Proceeding further . . . $COL_RESET"

# Creating MYSQL dump of databases
echo -e "$COL_GREEN ********* Exporting MYSQL DUMP to $SQL_DIR ... $COL_RESET"
mysqldump -u$SQLUSER -p$SQLPASS radius > /$SQL_DIR/mysql_db_full_$DT.sql

# TAR GZIP (mysql)
echo -e "$COL_GREEN ********* TAR - Compressing all Backup Folders to $SAVDIR_FULL ... $COL_RESET"

# Deleting sql db from $SQL_DIR because its zipped with the above command already and now all data available in single file : )
rm -fr /$SQL_DIR/*

# Print END time
echo "MAIN Backup ended at $6-$2-$3 Time $time"
echo -e "$COL_GREEN ********* Backup completed to /$SAVEDIR_FULL ... $COL_RESET"
echo -e "$COL_RED Backup ended at $6-$2-$3 Time $time . . . $COL_RESET"
end_time=`date +%s`

# Delete files older then 30 days, ONLY IF /$SAVDIR_FULL EXISTS, FOR FAIL SAFE , CHECK
echo "Deleting Older files then 30 days from $SAVEDIR_FULL and /$USB, to save disk space . . ."
if [ -d "/$SAVEDIR_FULL" ]; then
find /$SAVEDIR_FULL/* -mtime +30 -exec rm {} \;
#if [ -d "/$USB" ]; then
#find /$USB/* -mtime +30 -exec rm {} \;

# Print Complete Timings
echo MAIN Backup Completion Time was `expr $end_time - $start_time` s.
SIZE=`ls -lh /$SAVEDIR_FULL/$FILENAME | awk '{print $5}'`
SIZEB=`ls -l /$SAVEDIR_FULL/$FILENAME | awk '{print $5}'`

if [ $SIZEB -lt 1 ]

# Print Fetched Information on Screen , for info to see

echo "Radius INFO: Backup OF Radius for $YESTERDAY is $RESULT.
Target Folders = $TARGET
Size = $SIZE
Backup Completion Time was `expr $end_time - $start_time` s.

Powered by Syed Jahanaib"

############## SMS SECTION ##############
echo "Radius INFO: Backup OF Radius for $YESTERDAY is $RESULT.
Target Folders = $TARGET
Size = $SIZE

Backup Completion Time was `expr $end_time - $start_time` s.

Powered by Syed Jahanaib" > $RMBACKUPSMSFILE

# Send SMS Alert via KANNEL
echo "Sending of EMAIL/SMS ALERT is disabled as this scheudle will run every 2 hours, and will generate many sms/emails ... zaib"
#echo "Sending SMS ALERT to $CELL1 & $CELL2 ..."
#curl "http://$KHOST:13013/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$CELL1+$CELL2" -G --data-urlencode text@$RMBACKUPSMSFILE

# EMAIL SECTION ##############
# Make sure you install sendEMAIL tool and test it properly before using email section.
#SEND EMAIL Alert As well using sendEMAIL tool using GMAIL ADDRESS.
# If you want to send email , use below ...
#echo ""
#echo "Sending SEMAIL ALERT to $ADMINMAIL1 & $ADMINMAIL2 ..."
#sendemail -t $email -u "Radius Backup Info for $YESTERDAY / Size=$SIZE" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RMBACKUPSMSFILE -o message-content-type=text
#sendemail -t $email -u "Radius Backup Info for $YESTERDAY / Size=$SIZE" -o tls=yes -s $SMTP -t $ADMINMAIL2 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RMBACKUPSMSFILE -o message-content-type=text

# Print Credits : )
echo -e "$COL_GREEN ********* Syed Jahanzaib / / $COL_RESET"

Now execute this script  & see if any error occurs




To run this file on daily basis (at 00:00 hours),
Open terminal, (make sure you are logged in with the root user.

crontab -e

(if it asks for text editor, select nano ,)

Now paste following code …

@daily  /temp/          # Run Daily in night at 00:00 hours

Save & Exit:

Now, based on above selection, cron job will run this command at selected scheduled timings and clear any memory cache

TIP: Remove files older than xx days

When you will implement this script, it will backup every day, and continue to do so, and its a good idea to remove backup files older then 1 month, to save disk space otherwise one day it will chew whole disk space 😀
You can add following in the same backup script at end, so that whenever it will execute backup script, it will delete older files too

echo Deleting Older files then 30 days, to save disk space
# echo Deleting Older files then 30 days, to save disk space  >> /var/log/fullbackup.log
find /backup/* -mtime +30 -exec rm {} \;

 > SCRIPT   EXECUTION   RESULT … [old sample result]



> EMAIL   ALERT   EXECUTION … [13-dec-2017 live sample]


mail backup sample

> SMS   ALERT   EXECUTION … [old sample]


2016-05-29 05.32.34

Syed Jahanzaib


September 6, 2011

Mikrotik Voucher Template !

Filed under: Mikrotik Related — Tags: , , , , , , , — Syed Jahanzaib / Pinochio~:) @ 1:57 PM

It would be great If MIKROTIK adds feature in USER MANAGER (UM) of adding serial numbers with each ID, So that we can print voucher with serial numbers, and can search ids by corresponding serial number.

I have customized the following vouchers but unable to add serial number.
I am working on MS ACCESS program to import ids passwds from .CSV file ,
then print it using my customized templates and add serial number to each id and store it in its database to prevent Duplication, and also add search function by serial. I will publish it soon.

Voucher Display Sample I am currently using

Mikrotik Voucher Sample

HOWTO Use this template to Print Vouchers for Single or Batch Users !!

First login to your user manager e.g
Now to use the template, goto Settings / Templates and in ROW section box, select all and delete , now paste the complete template in this box. and click on SAVE button.

Now we will add Batch Users (Add Users in Bulk).
Goto Users / Add / Batch Users. Select your desired options and click on ADD.

Now we will Generate Voucher file to print. (html format)
(See Following Image)

Click on DOWNLOAD AS FILE and click Generate. It will then download the vouchers.html file and you can print it.

Following is the Template Code:

<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>User Manager Voucher Page - By Syed Jahanzaib</title>
<meta name="description" content="User Manager Voucher Page - By Syed Jahanzaib">
<meta name="Microsoft Theme" content="none, default">

<body background="" topmargin="5" leftmargin="5" style="background-attachment: fixed">

<table style="color: black; font-size: 11px;" border="2" align="left" width="286" height="300" cellspacing="0" cellpadding="0" id="table1"> <tr>
<td colspan="2" height="69">
<p align="center">
<font size="1">
<img border="0" src="" width="107" height="67"></font></p>
</td> </tr> <tr height="5px">
<td colspan="2">
<p align="center"><u><b><font color="#0000FF" size="3">Package:
<font size="2" face="Arial">
<p align="center"><font size="2" face="Arial Black">Price</font></td>   </font>   <td>
<p align="center"><font size="2" face="Arial Black" color="#FF0000">%u_moneyPaid%</font></td>
<font size="2" face="Arial Black">
<p align="center">Prepaid time:</td>   <td>
<p align="center"><font color="#FF0000" face="Arial Black" size="2"><b>%u_timeLeft%</b></font></td>
<tr>   <td>
<font size="2" face="Arial Black">
<p align="center">Login:</td>   <td>
<p align="center"><font color="#FF0000"><b>
<font size="2" face="Arial Black">%u_username%</font></b></font></td>   </tr>
<font size="2" face="Arial">
<p align="center"><font size="2" face="Arial Black">Password:</font></td>   <td>
<p align="center">
<font face="Arial Black" color="#FF0000"><b><font size="2">%u_password%</font></b></font></td>
<tr height="20px">
<td colspan="2">
<p align="center"><b><u><font size="2" color="#0000FF">~ Howto Use
This Voucher ~</font></u><font color="#008000" size="2"><u><br>
</u></font></b><font size="2" color="#008000">Please open
<a href="http://ftp.local">http://ftp.local</a> and download <br>
aa-dialer.exe &amp; install it. After installation its icon will appear
on Desktop. Double click on it, and use the above mentioned ID and
Password&nbsp; to connect to the internet .<br>
</font><font size="2" face="Arial Black" color="#FF0000">This ID will
expire in 30 days after its first usage.</font><font size="2" color="#008000"><br>
</font><b><font size="2">Thank You For Choosing AA~Services</font></b></td></tr> <tr>
<td colspan="2" align="right" height="50">
<p align="center"><b><font size="1">AA Network Services</font></b><font size="1"><br>
Support : 92.333.3021909<br>
Web <a href=""></a> </font></td> </tr> </table>


Note: I used 2 images, one is logo, second is background image, Both files are residing on the Local web server. You have to modify these links and files name to match according to your structure.



August 9, 2011

July 5, 2011

A Success story with Mikrotik and DMASoftlab RADIUS MANAGER [Glass Line Pvt Ltd.] June, 2011

Article by Syed Jahanzaib !

Recently I was contacted by a friend who was really passionate in starting a mini-ISP type network setup for about 3000 users in the interior area of city. (soon it may expand up to 5000+ users). He asked my help to setup a scratch card base fully automatic system where user purchase scratch card, & using User self care portal web site, user may create his new ID or refresh his previous ID or change the service package according to the card package offers. I had previously setup this kind of scenario in a environment using Mikrotik built-in radius server called ‘User Manager’, but it have very limited basic features and all it can offer was a pre-paid type option and it doesn’t have many accounting features. So I thought I should give a try to more rich feature radius server and after a lot of googling i decided to go with  (FREERADIUS base ) DMASOFTLAB RADIUS MANAGER. A very famous radius server with all the option that a mini-ISP would required at unbelievably low price.

The hardware that I have used for this setup.

*Main Mikrotik = v4.17 x86 / Xeon 3.6Ghz Dual / 2 GB Ram / WD 500 GB Sata Hdd , This MT is serving as a PPPoE Server + NAT + bandwidth shaping. It also redirects HTTP traffic to Proxy server.

* Mikrotik RB750 = Just for HOTSPOT to redirect users to self care portal.
(This can be done on Main MT also, but I prefer it this way)

* Radius Server = DMASoftlab RM v3.9 installed on Fedora v10 / Xeon 3.6Ghz Dual / 4 GB Ram / WD 500 GB x2 Sata Hdd

* SQUID PROXY GW = SQUID v2.7 on UBUNTU Karmic Koala v9.10 / Xeon 3.6Ghz Dual / 8 GB Ram / WD 500 GB x3 SATA HDD (2 HDD reserved for Cache), This server acts as a proxy + Gateway machine for the Mikrotik, It also do URL Filtering blocking ads, it also have ZPH enabled so content available in squid cache should be downloaded at full speed (without package limitation) at user end. It also cache youtube videos using VIDEOCACHE.

* Linux Transparent BRIDGE firewall + DHCP + DNS + MRTG + WEB Server on FEDORA V10 / Xeon 3.6Ghz Dual / 4 GB Ram / WD 500 GB SATA HDD, This server sits between Mikrotik and Users , filtering unwanted traffic, ports and do some other stuff like lightweight DNSMASQ DNS Server,  DHCP server providing ips to users , Web Site with MRTG , Psychostats ranking system for Counter Strike Game, Server Monitoring Scripts and Alerts, PHPBB Forums for Users, and some other cool stuff. DNS+DHCP is hosted on this server to minimize load on main mikrotik machine, alos this machine filters unwanted traffic from passing by to main mikrotik.

In this setup , I have configured HOTSPOT on extra RB750 only to redirect user to my advertisement page, where he is informed that he is not logged in via dialer, either create / refresh his ID from RM User Self Care Portal, or if he already have an id, connect it via dialer. I don’t prefer HotSpot authentication due to various security reasons, mainly due to I had a very bad experience having HOTSPOT hit by ARP-POISONING and many virus flooder that requires default gateway.

When user first login , his PC MAC address is binded with his ID to prevent accessing it from different pcs. Multiple session of same ID is NOT allowed , I provide user with scratch card (with refill code) , which he can use to refill his account according to card amount/package from RM User self care portal. RM demo can be viewed at

When users with pppoe dialer tries to connect to main Mikrotik, MT verifies its credentials by asking Radius Server for the account validity, if the ID is valid, user connects okay and can use internet , otherwise he gets disconnected. When the User account is expired, he still can login via dialer, but then he is redirect to my local web server page where he is informed that his account is expired and he should visit billing.local page to renew his account using the card.

Please find along with attachment is my Network Diagram (This was initially designed, I made few changes afterward, I removed FTP from MT DMZ to user subnet lan to avoid load on MT , I moved ftp OS from windows to Linux and integrate it with radius authentication using APACHE.

Some other entertainment services that I setup here were:
2 FTP Media Sharing Servers ( 4 TB of data )
2 Live TV Channel streaming over LAN using VLC Media Player Broadcasting
1 Counter Strike 1.6 Dedicated Server with Psychostats Ranking System and adminmod/amxmod
1 Web Server (Ubunut) hosting site u-dear . com , an entertainment portal and hosting other features. It also features monitoring system with MRTG / SMS Alerts via attached Mobile.

About RM: Radius Manager uses a nice web interface for administering the users and the whole system (traffic accounting, tracking of online users, display statistics, maintenance ,account management etc.).

and to add that DMASoftlab customer support guys (specially Mr. Viktor.K) have excellent support and respond instantly even to the dumbest of questions. It is real value for money especially for those who do not have big wallet$.

Network Diagram Layout : (Complete setup guide can found at

GLASSLINE-Network-Presentation-by-zaib Update 03/08/2001

%d bloggers like this: