• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Shell DB backup Script

Rate my Script

  • GREAT !

    Votes: 0 0.0%
  • You script is bad and you should feel bad!

    Votes: 0 0.0%
  • KILL IT WITH FIRE!

    Votes: 0 0.0%

  • Total voters
    2

fullauto2012

Active Member

Thanks: 27
Messages: 168

#1
Not real good with scripting. But, I through my hat in the ring.
I was in dire need of a DB backup script that wrote to an inserted, but mostly unmounted rdx drive, regardless of mount situation. I also needed it to keep sanity in that backups older than 30 days needed to be pruned. Logs needed to be easily searchable and any unrecoverable errors needed to be emailed to me immediately...

I'm looking for honest critique as well us suggestions as to how I can make the script more efficient/fool proof.

File is dumpdbs.sh and it is 700 seeing how my SQL password is in it.
I originally wrote it with 'bashisms' ([[ ]]), then ran:
cat /root/bin/dumpdbs.sh | sed 's/\[\[/\[\g' | sed 's/\]\]/\]/g'
to get a file without the 'bashisms', which runs perfectly fine so that is the one I will run twice a day.


root@kif:~/bin # ls -la
total 48
drwxr-xr-x 2 root wheel 512 Jan 16 00:41 .
drwxr-xr-x 10 root wheel 1024 Jan 16 00:49 ..
-rwxr-x--- 1 root wheel 679 Dec 9 06:48 batton.sh
-rwxr-x--- 1 root wheel 1978 Dec 9 06:48 box.sh
-rwxr-x--- 1 root wheel 402 Jan 14 17:17 certbot.renew
-rwx------ 1 root wheel 0 Jan 16 00:25 dumpdbs.sh
-rwxr-x--- 1 root wheel 374 Dec 9 06:48 kick.sh
-rw-r--r-- 1 root wheel 0 Dec 12 08:53 mail
-rw-r--r-- 1 root wheel 4140 Jan 16 00:41 newfile.sh
-rwx------ 1 root wheel 4176 Jan 16 00:17 nightly_dumps.sh
-rwxr-x--- 1 root wheel 3342 Dec 9 06:48 skelup.sh
-rwxr-xr-x 1 root wheel 1425 Dec 15 12:23 spam.sh



#! /usr/local/bin/bash

#set -x

### User Defined Variables ###

# Directory you want to store your dumps in
DIR="/var/mnt/rdx/DB_dumps"

# MySQL user name used for dumps
SQL_USER="root"

# MySQL password used for DBs
SQL_PASS="XXXXXXXXXXXX"

# Where to store log files
LOGDIR="/root/LOGS"

# email to use in case of errors
EMAIL="me@mydomain.com"

# How many days of dumps to keep
DAYS=30

### System Defined Variables

FULLDATE="$( date )"
DATE=$( date +%Y%m%d )
LOGFILE="$LOGDIR/$DATE-dumps.log"

### Script Begins ###

# Functions

time.stamp() {
TIME="$( date "+%H:%M:%S" )"
}

log() {
time.stamp
echo "$TIME - $1" >> $LOGFILE
}

mailto() {
echo "$1" | mail -s "$FULLDATE - Nightly Dumps" $EMAIL
}

err.msg() {
# $1 = Exit Status; $2 = Error Message
STAT=$1; ERRMSG="$2"
if [ $STAT -ne 0 ]; then
log "$ERRMSG"
mailto "$TIME ==> $0 failed with message: $ERRMSG"
exit $STAT
fi
}

# Check for logfile and perform some setup
# Errors email due to the lack of Log files until this passes

if [ ! -d $LOGDIR ]; then
mail "!! $0 FAILED - $LOGDIR does not exist"
exit 1
fi

if [ ! -e $LOGFILE ]; then
touch $LOGFILE && chmod 750 $LOGFILE
if [ $? -ne 0 ]; then
mail "!! $0 FAILED - Could not create or chmod $LOGFILE"
exit 1
fi
else
log " "
log "--== $0 LOG STARTED ON $FULLDATE ==--"
log " "
fi

# Populate DB array with names of DBs
DB=( $( mysql -u $SQL_USER --password="$SQL_PASS" -e 'show databases' \
2>/dev/null | cut -d "|" -f 2 ) )

# Check to see if DB array is empty
if [ ! ${#DB[@]} -ge 1 ]; then
err.msg 1 "DB array was empty after mysql query"
else
log "Array DB polulated with ${#DB[@]} elements"
fi

# Set number of original elements in array
NUMDB=${#DB[@]}

# Give the first element the Axe; "Databases" <- output from mysql query
DB=( ${DB[@]:1} )

# Check to make sure it was indeed shifted
if [ ${#DB[@]} -ne $(( $NUMDB -1 )) ]; then
err.msg 1 "Wasn't able to shift first array element"
else
log "First element shifted off array for sanity"
fi

log "Array complete"

# Check to see if $DIR is on a mounted file system
# and if it is but not mounted, attempt to mount

FSTAB=( $( cat /etc/fstab | awk '{ print $2 }' ))

EMPTY=0
for (( c=0; c<${#FSTAB[@]}; c++ )); do
if [ "${FSTAB[$c]}" = "Device" ] || \
[ "${FSTAB[$c]}" = "none" ] || \
[ "${FSTAB[$c]}" = "/" ]; then
EMPTY=$(( $EMPTY + 1 ))
fi
done

FSTAB=( "${FSTAB[@]:$EMPTY}" )

for (( c=0; c<${#FSTAB[@]}; c++ )); do
STR="$( echo $DIR | grep "${FSTAB[$c]}" )"
if [ ! -z "$STR" ]; then
MNT="${FSTAB[$c]}"
if [ -z "$( mount | grep $MNT )" ]; then
log "$DIR is on a mountable file system but not mounted"
UNMOUNT="yes"; FILESYS="${FSTAB[$c]}"
mount ${FSTAB[$c]} &>/dev/null
if [ $? -gt 0 ]; then
err.msg 1 "Unable to mount ${FSTAB[$c]}. Aborting Script"
else
log "${FSTAB[$c]} successfully mounted"
fi
break
fi
fi
done

### Loop through all the DBs

for x in ${DB[@]}; do
FILE="$DATE-$x.sql"
if [ -e $DIR/$FILE ]; then
mv $DIR/$FILE $DIR/$FILE.old
if [ $? -ne 0 ]; then
err.msg 1 "Couldn't rename $DIR/$FILE to $DIR/$FILE.old"
else
log "Renamed $DIR/$FILE to $DIR/$FILE.old"
fi
fi

`mysqldump --single-transaction -u $SQL_USER --password="$SQL_PASS" \
--databases $x > $DIR/$FILE 2>/dev/null` &

if [ $? -ne 0 ]; then
err.msg 1 "Couldn\'t dump $x to file - ABORTING SCRIPT"
else
log "$x dumped to $DIR/$FILE"
fi

done

log "${#DB[@]} database dumped to $DIR on $FULLDATE"

# Dump Dir Sanity

NUM=$(( $(ls -la $DIR | wc -l ) -3 ))
log "You have $NUM files in $DIR. Will prune files older than $DAYS days"

REMOVED="$( find $DIR -type f -mtime +30 -print -exec rm {} \; & )"
if [ ! -z "$REMOVED" ]; then
log "Dumps older than 30 days were pruned"
fi

if [ "$UNMOUNT" = "yes" ]; then
sleep 10
umount $FILESYS &
if [ $? -gt 0 ]; then
err.msg 1 "Unable to unmount $FILESYS"
else
log "$FILESYS successfully unmounted"
fi
fi

exit 0




The logs are nice and clean too...


00:17:33 -
00:17:33 - --== /root/bin/nightly_dumps.sh LOG STARTED ON Tue Jan 16 00:17:33 ES T 2018 ==--
00:17:33 -
00:17:33 - Array DB polulated with 7 elements
00:17:33 - First element shifted off array for sanity
00:17:33 - Array complete
00:17:33 - /var/mnt/rdx/DB_dumps is on a mountable file system but not mounted
00:17:34 - /var/mnt/rdx successfully mounted
00:17:34 - Renamed /var/mnt/rdx/DB_dumps/20180116-information_schema.sql to /var /mnt/rdx/DB_dumps/20180116-information_schema.sql.old
00:17:34 - information_schema dumped to /var/mnt/rdx/DB_dumps/20180116-informati on_schema.sql
00:17:34 - Renamed /var/mnt/rdx/DB_dumps/20180116-mysql.sql to /var/mnt/rdx/DB_d umps/20180116-mysql.sql.old
00:17:34 - mysql dumped to /var/mnt/rdx/DB_dumps/20180116-mysql.sql
00:17:34 - Renamed /var/mnt/rdx/DB_dumps/20180116-nextcloud.sql to /var/mnt/rdx/ DB_dumps/20180116-nextcloud.sql.old
00:17:34 - nextcloud dumped to /var/mnt/rdx/DB_dumps/20180116-nextcloud.sql
00:17:34 - Renamed /var/mnt/rdx/DB_dumps/20180116-opencart.sql to /var/mnt/rdx/D B_dumps/20180116-opencart.sql.old
00:17:34 - opencart dumped to /var/mnt/rdx/DB_dumps/20180116-opencart.sql
00:17:34 - Renamed /var/mnt/rdx/DB_dumps/20180116-performance_schema.sql to /var /mnt/rdx/DB_dumps/20180116-performance_schema.sql.old
00:17:34 - performance_schema dumped to /var/mnt/rdx/DB_dumps/20180116-performan ce_schema.sql
00:17:34 - Renamed /var/mnt/rdx/DB_dumps/20180116-phpbb3.sql to /var/mnt/rdx/DB_ dumps/20180116-phpbb3.sql.old
00:17:34 - phpbb3 dumped to /var/mnt/rdx/DB_dumps/20180116-phpbb3.sql
00:17:34 - 6 database dumped to /var/mnt/rdx/DB_dumps on Tue Jan 16 00:17:33 EST 2018
00:17:34 - You have 24 files in /var/mnt/rdx/DB_dumps. Will prune files older th an 30 days
00:17:45 - /var/mnt/rdx successfully unmounted
00:23:21 -
00:23:21 - --== /root/bin/shell_dumps.sh LOG STARTED ON Tue Jan 16 00:23:21 EST 2018 ==--
00:23:21 -
00:23:21 - Array DB polulated with 7 elements
00:23:21 - First element shifted off array for sanity
00:23:21 - Array complete
00:23:21 - /var/mnt/rdx/DB_dumps is on a mountable file system but not mounted
00:23:23 - /var/mnt/rdx successfully mounted
00:23:23 - Renamed /var/mnt/rdx/DB_dumps/20180116-information_schema.sql to /var /mnt/rdx/DB_dumps/20180116-information_schema.sql.old
00:23:23 - information_schema dumped to /var/mnt/rdx/DB_dumps/20180116-informati on_schema.sql
00:23:23 - Renamed /var/mnt/rdx/DB_dumps/20180116-mysql.sql to /var/mnt/rdx/DB_d umps/20180116-mysql.sql.old
00:23:23 - mysql dumped to /var/mnt/rdx/DB_dumps/20180116-mysql.sql
00:23:23 - Renamed /var/mnt/rdx/DB_dumps/20180116-nextcloud.sql to /var/mnt/rdx/ DB_dumps/20180116-nextcloud.sql.old
00:23:23 - nextcloud dumped to /var/mnt/rdx/DB_dumps/20180116-nextcloud.sql
00:23:23 - Renamed /var/mnt/rdx/DB_dumps/20180116-opencart.sql to /var/mnt/rdx/D B_dumps/20180116-opencart.sql.old
00:23:23 - opencart dumped to /var/mnt/rdx/DB_dumps/20180116-opencart.sql
00:23:23 - Renamed /var/mnt/rdx/DB_dumps/20180116-performance_schema.sql to /var /mnt/rdx/DB_dumps/20180116-performance_schema.sql.old
00:23:23 - performance_schema dumped to /var/mnt/rdx/DB_dumps/20180116-performan ce_schema.sql
00:23:23 - Renamed /var/mnt/rdx/DB_dumps/20180116-phpbb3.sql to /var/mnt/rdx/DB_ dumps/20180116-phpbb3.sql.old
00:23:23 - phpbb3 dumped to /var/mnt/rdx/DB_dumps/20180116-phpbb3.sql
00:23:23 - 6 database dumped to /var/mnt/rdx/DB_dumps on Tue Jan 16 00:23:21 EST 2018
00:23:23 - You have 24 files in /var/mnt/rdx/DB_dumps. Will prune files older th an 30 days
00:23:33 - /var/mnt/rdx successfully unmounted



Give me you honest opinions... I really want to learn this stuff...
 

ShelLuser

Son of Beastie

Thanks: 1,260
Messages: 2,678

#2
It's a nice looking script, easy to follow, commented, looks good. I didn't go over every detail but one thing caught my eye: you're over complicating your database array a bit. Instead of using a variable I personally prefer using a nested construction, because this will definitely be less resource intensive on your system (you don't have to keep a variable filled with data).

Another thing: the mysql binary allows you to strip headers and such.

So I'd probably use something like this:

Code:
#!/bin/sh
for a in $(echo "show databases;" | mysql -u root -sp); do
  echo Now processing database: $a;
done;
You'll notice that it won't include the header. And as soon as the for loop is done a will also be gone, which won't be the case for your array. Which effectively makes this construct a bit less taxing. Doubt you'll notice a difference, but I prefer to keep things clean.
 

fullauto2012

Active Member

Thanks: 27
Messages: 168

#3
Interesting... Something else thats interesting is that while it runs flawlessly as root, cron errors out querying mysql even though it os set to run the file as root... Any ideas?
 

Preetpal

Active Member

Thanks: 25
Messages: 114

#4
Do not take my criticisms too harshly/personally.
  • You should format the script as code, as it's hard to read without indentation.
  • I do not like how you cat the fstab file and pipe it to awk (Like what if there is a comment in the fstab? It's not immediately obvious if bad things could happen.).
  • IMO you should know what mountable filesystem contains the DIR, and you should check if it is mounted before testing for the existence of the DIR. If you do this, you could get rid of some of the deeply nested if statements.
  • Mutation of the global TIME variable is not great.
These things bother me the most in your script.
 
Top