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