Learn about Centmin Mod LEMP Stack 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

    117
    10
    18
    Jul 18, 2014
    Ratings:
    +14
    Local Time:
    1:57 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

    30,563
    6,851
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,268
    Local Time:
    9:57 AM
    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

    117
    10
    18
    Jul 18, 2014
    Ratings:
    +14
    Local Time:
    1:57 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,767
    245
    63
    May 31, 2014
    Ratings:
    +436
    Local Time:
    1:57 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Thanks for sharing it :)
     
  5. hitman

    hitman Member

    117
    10
    18
    Jul 18, 2014
    Ratings:
    +14
    Local Time:
    1:57 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