Join the community today
Become a Member

Sysadmin Simple Database Backup script with pushover and email notifications

Discussion in 'System Administration' started by hitman, Nov 16, 2016.

  1. hitman

    hitman Member

    110
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    6:53 AM
    hello
    i am sharing a very simple script for backing up your databases on your servers.
    It can be added as a cron job to run at specific times
    You can use your pushover account with it and your email account to receive notifications that the backup went well
    I have included a check on mysqldump so you would know if the process ended with an error.

    • it creates the path to store the databases
    • it deletes files in this path that are older than 5 days
    • it echoes all databases
    • it skips schema databases and all databases with underscore
    • it backups up databases and notifies if any database dump ends with error
    • it emails the results to you, and send them through pushover too
    Code:
    #!/bin/bash
    datenow=`date +"%Y%m%d"`;
    USER=""
    PASSWORD=""
    path="/root/dbs/$datenow"
    DELPATH="/root/dbs/"
    STATUS="/root/dbs/statusfile.$datenow"
    TOKEN=""
    USERID=""
    MAIL=""
    
    
    # if the directory does not exist, make it please
    if [ ! -d $path ]; then
    mkdir -p $path
    else
    :
    fi
    
    
    DBS="$(mysql -u$USER -p$PASSWORD -Bse 'show databases')"
    echo "$DBS"
    
    errorcounter=0 #innitializing a variable to count errors in mysqldump
    
    #if your db starts with an _ then ignore it this way you can create dbs with _ if you want them ignored
    #need to add a list of databases so you can add a database you need to be ignored
    for db in $DBS; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] && [[ "$db" != "performance_schema" ]] ; then
    printf "Dumping database: $db\n"
    mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $path/`date +%Y%m%d`.$db.sql
    exitcode=$?
    gzip $path/`date +%Y%m%d`.$db.sql
    FILESIZE=$(stat -c%s "$path/`date +%Y%m%d`.$db.sql.gz")
    printf "$db Backed up with EXITCODE [ $exitcode ] and with size $FILESIZE bytes\n";
    
    #create an if to notify us in case of bad mysqldump
    if [ $exitcode -ne 0 ]; then
    curl -s \
    -F "token=$TOKEN" \
    -F "user=$USERID" \
    -F "message=$db ERROR BACKING UP === errorcode = $exitcode " \
    -F "priority=1"\
    https://api.pushover.net/1/messages.json
    printf "\n\n$db ERROR BACKING UP === errorcode = $exitcode \n\n" >> $STATUS
    ((errorcounter++))
    else
    printf "\n$db Backed up with EXITCODE [ $exitcode ] and with size $FILESIZE bytes\n" >> $STATUS
    fi
    
    fi
    done
    
    
    #method to remove old database backups
    if [ $errorcounter -ne 0 ]; then
    printf "Not deleting anything due to errors in mysqldump\n" >> $STATUS
    else
    find $DELPATH* -mtime +5 -exec rm {} \;
    printf "\nFiles older than 5 days have been deleted\n" >> $STATUS
    fi
    
    #notify me of all the results
    curl -s \
    -F "token=$TOKEN" \
    -F "user=$USERID" \
    -F "message=server is $(hostname) $(cat $STATUS)" \
    -F "priority=0"\
    https://api.pushover.net/1/messages.json
    
    #by mail too
    printf "Server hostname is $(hostname)\n$(cat $STATUS)" | mail -s "Database Backup Results" $MAIL
    

    i have uploaded to github also => here

    please feel free to make suggestions to improove this code if you feel like it

    eva2000 if this is against a rule or anything please delete it
     
    Last edited: Nov 16, 2016
    • Informative Informative x 2
    • Like Like x 1
  2. eva2000

    eva2000 Administrator Staff Member

    29,732
    6,713
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,023
    Local Time:
    1:53 PM
    Nginx 1.13.x
    MariaDB 5.5
    to tag a user use @ in front of username ;)

    system admin forum is correct place to share such scripts :)

    Just a tip, move the old find file deletion routine until after a successful new backup is made (add error check for success/failed backup). Otherwise if a backup fails, you won't have the previous backup anymore ! So say if your backup failed 5 days in a row, you won't have any backups left as over the past 5 days you would of kept removing old backups before doing a new backup (which failed).

    That's what i do for dbbackup.sh script, deletion routine will only run if a successful backup is made
     
    Last edited: Nov 16, 2016
    • Winner Winner x 1
  3. hitman

    hitman Member

    110
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    6:53 AM
    will do so and update as soon as i have it ready
    you are right it should first check if the backup is proper before deleting
     
    • Like Like x 1
  4. pamamolf

    pamamolf Well-Known Member

    2,675
    240
    63
    May 31, 2014
    Ratings:
    +425
    Local Time:
    6:53 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Thanks for sharing it :)
     
  5. hitman

    hitman Member

    110
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    6:53 AM
    updated it and it now checks if mysqldump is done properly before deleting databases
    it also has some minor fixes so the notifications are prettier :D
     
    • Like Like x 1