MySQL DB & HTML files Backup Script

LAST UPDATED:  10-MARCH-2019

Following is a customized backup script [Tested with Ubuntu 12/16] to create backup of your mysql DB and HTML files in local storage & on cloud using DROPBOX (make sure you have dropbox running),

This script will EXCLUDE data in radacct and radacct_archive tables. It will only export table structure without data. to reduce database size, you can modify this function.

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 fast. Plus 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 add some logs in /var/log/syslog
• It will make a copy of files in /root/Dropbox folder
• It will take backup of RADIUS Database excluding radacct , only radacct structure will be exported (to save disk space by lower size of backup)
• It will also check mysql Service & DB status, if it found inaccessible, it will send email
• It will also delete files older than 20 days from /backup & root/Dropbox folder
• It will check File size, if Less than 1 byte it will alert, modify it as required
• all backups will be saved in /backup & /root/Dropbox folder [dropbox if exists]
• In the end, It will send email and SMS with details
• It will also upload the backup in your FTP server, I used filezilla FTP server (free version in local LAN)

As usually, like all other scripts of mine it contains lots of junk. 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
touch fullbackup.sh
chmod +x fullbackup.sh

Now open the file

nano /temp/fullbackup.sh

and paste the following code.

#!/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/etc
#set -x
# Version 1.1 / 10th January, 2014
# Last Modified / 10-MARCH-2019
# Syed Jahanzaib / Web: https://aacable.wordpress.com / Email: aacable@hotmail.com
# This script creates FULL Backup of MySQL DB (Radius) and MYSQL DBR related DATA files & copy it in local system / usb / FTP
# 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
clear
# Colors Config ... [[ JZ ... ]]
ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_GREEN=$ESC_SEQ"32;01m"

# IF YOU HAVE FEDORA or CENTOS or new version of UBUNTU, Change the /var/www to /var/www/html/
# Following is only RADIUS HTML FILES, mysqldb is separate from this, will come later in the script with /usr/bin/mysqldump
TARGET="/var/www/html /sql_backup /etc/freeradius"
SAVEDIR_FULL="/backup"
DROPBOX="/root/Dropbox"
HOSTNAME=`hostname`
#MYSQL DETAILS
SRV="mysql"
SQLUSER="root"
SQLPASS="passwordhere"
export MYSQL_PWD=$SQLPASS
DB="radius"
SQL_ACCOUNTING_TABLE="radacct"
SQL_DIR="sql_backup"
CMD="mysql -u$SQLUSER --skip-column-names -s -e"

#SET DATE TIME
set $(date)
time=`date |awk '{print $4}'`
DT=`date +%d.%b.%Y_time_%H.%M`
DATE=$(date +%Y-%m-%d)
DT_HMS=$(date +'%H:%M:%S')
FULL_DATE=`date`
TODAY=$(date +"%Y-%m-%d")
TODAYYMD=`date +"%d-%b-%Y"`
#Get ip which have default route
IPADD=`ip route get 1 | awk '{print $NF;exit}'`
logger Backup of mysqldb / html SCRIPT has been started @ $DATE / $DT_HMS

# Adding OS level Details in email message
# disk we want to monitor, make sure to change this
DISK="/dev/mapper/Billing--vg-root"
# Get DB size in MB
MYSQLDBSIZE=`mysql -u$SQLUSER --skip-column-names -e "SELECT table_schema "$DB", sum(data_length + index_length)/1024/1024 FROM information_schema.TABLES WHERE table_schema='radius' GROUP BY table_schema;" | cut -f1 -d"." | sed 's/[^0-9]*//g'`
SESSIONS=`mysql -u$SQLUSER --skip-column-names -e "use radius; SELECT username FROM $SQL_ACCOUNTING_TABLE WHERE acctstoptime IS NULL;" |wc -l`
DISKTOT=`df -h $DISK |awk '{print $2}'| sed -n 2p`
DISKUSED=`df -h $DISK |awk '{print $3}'| sed -n 2p`
DISKAVA=`df -h $DISK |awk '{print $4}'| sed -n 2p`
DISKUSEPER=`df -h $DISK |awk '{print $5}'| sed -n 2p`
MEMTOT=`free -m |awk '{print $2}'| sed -n 2p`
MEMUSED=`free -m |awk '{print $3}'| sed -n 2p`
MEMAVA=`free -m |awk '{print $4}'| sed -n 2p`
MEMUSEDPER=`free -m | grep Mem | awk '{print $3/$2 * 100.0}'`
MEMAVAPER=`free -m | grep Mem | awk '{print $4/$2 * 100.0}'`

#Update script execution Time Stamp / specific to PAKRAD billing system made by jz
#$CMD "use $DB; insert into scripts_exec (script_name, lastupdate) VALUES(' fullbackup.sh','$TODAY $DT_HMS') on duplicate key update lastupdate='$TODAY $DT_HMS';"

#GMAIL Details
GMAILID="MYGMAILID@gmail.com"
GMAILPASS="GMAILPASS"
ADMINMAIL1="aacableAThotmail.com"
COMPANY="ZAIB"
SMTP="64.233.184.108:587"

# KANNEL SMS Gateway - Details if you want to send SMS
KHOST="10.0.0.1:13013"
KID="kannel"
KPASS="PASSWORDOFKANNEL"
CELL1="03333021909"
EMAILMSG="/tmp/mysql.backup.result.email.txt"
SMSMSG="/tmp/mysql.backup.result.sms.txt"
SRVDOWNEMAIL="/tmp/srvdownemail.txt"
DBDOWNEMAIL="/tmp/dbdownemail.txt"
SQL_FILE="$HOSTNAME.$DB.mysql.db.$DT.sql"
FINAL_TAR_FILE="$HOSTNAME.$DB.final.file.$DT.tgz"
#LOG FILE, IF REQUIRED
SYSLOG="/var/log/syslog"

> $EMAILMSG
> $SMSMSG
> $SRVDOWNEMAIL
> $DBDOWNEMAIL
> $SQL_FILE
> $FINAL_TAR_FILE

# START the BACKUP PROCESS ... #######
# Start counting start time
start_time=`date +%s`
echo "
Welcome ! This is mysql and html Backup Script,it wil backup following folders ...
$TARGET

It will EXCLUDE radacct & radacct_archive folder from the backup to ensure reduced file size of Backup.

Powered by JZ
"
# Checking if $SAVEDIR_FULL folder is previously present or not ...
{
if [ ! -d "$SAVEDIR_FULL" ]; then
echo "- $SAVEDIR_FULL folder not found, Creating it so all backup's should be placed there ... "
mkdir $SAVEDIR_FULL
else
echo "- $SAVEDIR_FULL folder is already present , so no need to create it, Proceeding further ... "
fi
}

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

# Check if $SRV (in this case mysql) is running or not, if NOT, then exit the script
SRVSTATUS=`service $SRV status |grep running |wc -l`
if [ "$SRVSTATUS" -ne 1 ];
#if [ -z "$SRVSTATUS" ];
then
echo "- ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT .Exiting ..."
echo "- ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT .Exiting ..." >> $SYSLOG

# SRV down EMail BODY
echo "- ALERT:

- $HOSTNAME
- $IPADD
- $SRV not responding ***
- $DATE $DT

Exiting ..." > $SRVDOWNEMAIL
sendemail -t $email -u "ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$SRVDOWNEMAIL -o message-content-type=text
exit 1
else
echo "- $SRV service is accessible OK. Proceeding further ..."
fi

# Check if $DB (in this case radius )is accessible or not, if NOT, then exit the script
RESULT=`$CMD "SHOW DATABASES LIKE '$DB'"`
if [ "$RESULT" != "$DB" ]; then
echo "- ALERT: $HOSTNAME - $IPADD - DB $DB not accessible/exists - $DATE $DT"
echo "- ALERT: $HOSTNAME - $IPADD - DB $DB not accessible/exists - $DATE $DT" >>$SYSLOG
echo "- ALERT: $HOSTNAME - $IPADD - DB $DB not accessible/exists - $DATE $DT" >>$DBDOWNEMAIL

# DB down EMail BODY
echo "- ALERT:

- $HOSTNAME
- $IPADD
- Service $SRV is OK BUT
- Database $DB not accessible/exists ***
- $DATE $DT

Exiting ..." > $DBDOWNEMAIL
sendemail -t $email -u "ALERT: $HOSTNAME - $IPADD - DB $DB not accessible/exists - $DATE $DT" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$DBDOWNEMAIL -o message-content-type=text
exit 1
else
echo "- $DB - database exist OK. Proceeding further ..."
fi

echo "Starting Mysql DUMP ... since its INNODB , therefore we are using --single-transaction=TRUE featre
Powered by Syed.Jahanzaib
"
#sleep 5

# Creating MYSQL dump of databases & ignore DATA inside some tables
/usr/bin/mysqldump -u$SQLUSER --single-transaction=TRUE --ignore-table={$DB.radacct,$DB} $DB > /$SQL_DIR/$SQL_FILE
#/usr/bin/mysqldump -u$SQLUSER --no-data $DB radacct >> /$SQL_DIR/$SQL_FILE
#/usr/bin/mysqldump -u$SQLUSER --no-data $DB radacct_archive >> /$SQL_DIR/$SQL_FILE

# Check mysql File Size, if its less than 1 byte, consider it invalid
SIZE=`ls -lh /$SQL_DIR/$SQL_FILE | awk '{print $5}'`
SIZEB=`ls -l /$SQL_DIR/$SQL_FILE | awk '{print $5}'`
{
if [ $SIZEB -lt 1 ]
then
echo "SQL file size is invalid"
echo "SQL file size is invalid" >> $SYSLOG
SQL_RESULT="INVALID"
else
echo "SQL file size is OK"
echo "SQL file size is OK" >> $SYSLOG
SQL_RESULT="OK"
fi
}

#echo sleeping 30 seconds
#sleep 30

# GZIP MYSQL D.B & all other files like html etco
# TAR GZIP (mysql)
echo " - TAR Compressing all Backup Folders to $SAVDIR_FULL ... "
tar cfzv $SAVEDIR_FULL/$FINAL_TAR_FILE $TARGET
# Deleting sql db from $SQL_DIR because its zipped with the above command already and now all data available in single file : )
{
if [ -d "/$SQL_DIR" ]; then
rm -fr /$SQL_DIR/*
fi
}
# Print END time
echo "
- Backup ended at $6-$2-$3 Time $time .
"
echo "- Backup completed to $SAVEDIR_FULL ...
"
echo "- Backup ended at $6-$2-$3 Time $time ...
"
end_time=`date +%s`

# Delete files older then 20 days
echo "- Deleting Older files than 20 Days from $SAVEDIR_FULL and $DROPBOX (if any) to save disk space ..."
{
if [ -d "$SAVEDIR_FULL" ]; then
find $SAVEDIR_FULL/* -mtime +10 -exec rm {} \;
fi
}

# Delete files older then 20 days
{
if [ -d "$DROPBOX" ]; then
echo "- DROPBOX $DROPBOX folder found, making backup file copy in $DROPBOX too ... "
cp $SAVEDIR_FULL/$FINAL_TAR_FILE $DROPBOX
echo "- Deleting Older files then 20 days from $DROPBOX, to save disk space ..."
find $DROPBOX -mtime +10 -exec rm {} \;
fi
}
# Delete duplicate backup files .sql and .tgz from current folder to avoid file duplication
if [ -f $SQL_FILE ]; then
echo "- Deleting $SQL_FILE to avoid duplication"
rm $SQL_FILE
fi
if [ -f $FINAL_TAR_FILE ]; then
echo "- Deleting $FINAL_TAR_FILE to avoid duplication"
rm $FINAL_TAR_FILE
fi

# Print Complete Timings
echo "- Backup script completion Time was `expr $end_time - $start_time` s. "

# CHECK FILE SIZE AND COMPARE, IF ITS LESS , THEN ALERT
SIZE=`ls -lh $SAVEDIR_FULL/$FINAL_TAR_FILE | awk '{print $5}'`
SIZEB=`ls -l $SAVEDIR_FULL/$FINAL_TAR_FILE | awk '{print $5}'`
{
if [ $SIZEB -lt 1 ]
then
echo "- Final tgz Backup file size is invalid"
echo "- Final tgz Backup file size is invalid" >> $SYSLOG
TGZ_RESULT="INVALID"
else
echo "- Final tgz Backup file size is OK"
echo "- Final tgz Backup file size is OK" >> $SYSLOG
TGZ_RESULT="OK"
fi
}

# List files created Today & TRIM its reulst
LIST=`ls -lh $SAVEDIR_FULL --time-style=+"%d-%b-%Y" |grep $TODAYYMD | awk '{print $5,$6,$7}' | column -t`

#Email File with all details
echo "Mysql/HTML Backup Info for

===============
NETWORK DETAILS:
===============
HOST: $HOSTNAME
IP: $IPADD

==========================
RADIUS SERVER MYSQL REPORT:
==========================
MYSQL 'RADIUS' DB SIZE = $MYSQLDBSIZE MB
RADIUS Online Users = $SESSIONS Users

====================
DIS / STORAGE REPORT:
====================
Total Disk Space = $DISKTOT
Total Disk Space Used = $DISKUSED
Total Disk Space Available = $DISKAVA
Total Disk Space = $DISKUSEPER

==============
MEMORY REPORT:
==============
Total RAM = $MEMTOT MB
Total RAM Used = $MEMUSED MB
Total RAM Available = $MEMAVA MB
Total RAM Used Percent = $MEMUSEDPER %
Total RAM Available Percent = $MEMAVAPER %

- List of files created in Today's Date [$TODAY]

$LIST

- SQL_RESULT = $SQL_RESULT
- TGZ_REULST = $TGZ_RESULT
- Target Folders = $TARGET
- FileName = $SAVEDIR_FULL/$FINAL_TAR_FILE
- Size = $SIZE
- Backup Time = `expr $end_time - $start_time` s.

$COMPANY
Powered by JZ" > $EMAILMSG

#SMS file with lesser details
echo "$HOSTNAME - Backup Info
SQL_RESULT = $SQL_RESULT
TGZ_RESULT = $TGZ_RESULT
FileName = $FINAL_TAR_FILE
Size = $SIZE
Bkp Time = `expr $end_time - $start_time` s.
$COMPANY
Powered by JZ" > $SMSMSG

# PRINT INFO SECTION #########
echo "- Job Log for:
"
# Print Fetched Information on Screen , for info to see
cat $EMAILMSG

# 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 " - Sending SMS/EMAIL ALERT ..."
#curl "http://$KHOST/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$CELL2+$CELL3+$CELL4" -G --data-urlencode text@$SMSMSG
sendemail -t $email -u "$HOSTNAME - $IPADD - mySQL Backup - $DT / Size=$SIZE" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$EMAILMSG -o message-content-type=text

# Print Credits : )
echo "

- Syed Jahanzaib / aacable@hotmail.com / https://aacable.wordpress.com "
DATE=$(date +%Y-%m-%d)
DT_HMS=$(date +'%H:%M:%S')
# log entyr in /var/log/syslog
logger Backup of mysqldb / html have been end @ $DATE / $DT_HMS
#########################
# FTP SECTION
#########################
FTP_HOST='192.168.0.1'
FTP_USER='ftpuser'
FTP_PASSWORD='ftppass'
FTP_REMOTE_DIR='/linux'
cd $SAVEDIR_FULL
ftp -n $FTP_HOST <<EOF
quote USER $FTP_USER
quote PASS $FTP_PASSWORD
cd $FTP_REMOTE_DIR
prompt
mput $FINAL_TAR_FILE
quit
EOF

---

Now execute this script  & see if any error occurs

/temp/fullbackup.sh

 

---

HOWTO SCHEDULE THE SCRIPT TO RUN ON DAILY BASIS

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

crontab -e

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

Now paste following code …

@daily  /temp/fullbackup.sh  >/dev/null 2>&1  # Run Daily in night at 00:00 hours

or if you want to run it every 6th hours (and on 15th minute) use following

# Run backup at 4:30 am in morning
#30 00 * * * /temp/fullbackup.sh >/dev/null 2>&1

# Run backup at 15th minute every 6th hours
15 */6 * * * /temp/fullbackup.sh >/dev/null 2>&1

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]

 

> SMS   ALERT   EXECUTION … [old sample]

 

---

Regard’s
Syed Jahanzaib