Shell Script to list the next tapes Bacula will ask for tape-libraries backup

Shell Script to list the next tapes Bacula will ask for tape-libraries backup
  • Post category:Uncategorized
  • Post comments:0 Comments

It’s not always easy to choose at the tape vault what tape to insert back in the tape-library. This scripts shows the order Bacula will fetch tapes for next backups within the pool.

Usage:

/etc/bacula/scripts/tape_list.sh --user=db_user --password=db_password --pool=pool_name

Script tape_list.sh:

#!/bin/bash
#
# Written by Ana Emilia Machado de Arruda <emiliaarruda at gmail dot com>
# Borja Chocarro del Olmo
#
# A script to list volumes from a Pool that could be used by Bacula for backup
# acording to Bacula rules for volume use and recycling.
#
# This script does not contemplate the following:
# - how many volumes will be used for a backup job;,
# - if PurgeOldestVolume or RecycleOldestVolume directives are set;
# - if automatic or manual prune or purge are configured or intended to be used
# - if volumes are InChanger or not, just for the case of VolStatus=Append
#
# This script intends to helps on situations as the described above:
#
# - 54 volumes(tapes) are used for a one year backup of a company.
# - autochanger has 15 slots available for the backups.
# - the company has rules that states that tapes that are not in use during
# the week must be keep in a tape vault storage.
#
# The script could help listing the number of volumes that is suitable to the
# autochanger and acording to the company rules. For example, the following
# commands will list the 5 volumes from File Pool that Bacula could use during
# one week (monday to friday) starting at 2015-10-21 22:00:00, considering
# the use of just one volume for a day of backup:
#
# list_available_volumes --pool=File --vols=5 --date="2015-10-21 22:00:00"
#
# For more information type list_available_volumes --help

declare -r cmdbasename=`basename $0`

showhelp(){
cat << EOF

Usage: $cmdbasename [OPTIONS]
List volumes from a Pool that could be used by Bacula for backup acording to Bacula rules for volume use and recycling.

OPTIONS:

--pool=POOLNAME Required. The Pool name for which volumes will be listed.
--vols=VOLUMESRETRIEVED Optional. Number of volumes that will be retrieved.
If omitted all the volumes from the Pool will be listed.
--date=EXPECTEDVOLUSE Optional. Date estimated for the volumes to be used.
If omitted current date/time will be considered.
--host=DBHOST Optional. The host address of the database server (catalog).
If omitted 'locahost' will be used.
--database=DATABASE Optional. The name of the Catalog's database.
If omitted 'bacula' will be used.
--user=USER Optional. The name of the user to use to log into
Catalog's database. If omitted 'bacula' will be used.
--password=PASSWORD Optional. The password of the user to use to log
into Catalog's database. If omitted no password will be used.
--help Display this help and exit.

Examples:

$cmdbasename --pool=Default --user=bacula
$cmdbasename --pool=File --vols=5 --user=bacula --date="2015-10-21 22:00:00"

EOF
}

# variables
: ${NUMBEROFVOLUMES:=0}
: ${EXPECTEDVOLUSE:=$(date --utc +"%s")}
: ${DBHOST:=localhost}
: ${DATABASE:=bacula}
AVAILABLEVOLUMES=()
NUMBEROFAVAILABLEVOLUMES=0
# number of columns used in the SELECT for purposes the array dimension
COLS=14

arguments=("$@")
for arg; do
case "$arg" in
--help) showhelp;
exit 0;;
--pool=*) POOLNAME=${arg#*=};;
--vols=*) NUMBEROFVOLUMES=${arg#*=};;
--date=*) EXPECTEDVOLUSE=$(date --utc --date="${arg#*=}" +"%s");;
--host=*) DBHOST=${arg#*=};;
--database=*) DATABASE=${arg#*=};;
--user=*) USER=${arg#*=};;
--password=*) PASSWORD=${arg#*=};;
*) printf "%s: unrecognized option '%s'" $cmdbasename ${arg%%=*};
showhelp;
exit 1;;
esac
done

if [ -z "$POOLNAME" ]; then
printf "%s: missing --pool=POOLNAME optionn" $cmdbasename
showhelp
exit 1
fi

# Fields returned by MySQL SELECT
# ${VOLUMES[COLS]} : MediaId
# ${VOLUMES[COLS+1]} : VolumeName
# ${VOLUMES[COLS+2]} : VolStatus
# ${VOLUMES[COLS+3]} + ${VOLUMES[COLS+4]} : FirstWritten
# ${VOLUMES[COLS+5]} : Unix Timestamp for FirstWritten
# ${VOLUMES[COLS+6]} + ${VOLUMES[COLS+7]} : LastWritten
# ${VOLUMES[COLS+8]} : Unix Timestamp for LastWritten
# ${VOLUMES[COLS+9]} : VolUseDuration
# ${VOLUMES[COLS+10]} : VolRetention
# ${VOLUMES[COLS+11]} : Recycle
# ${VOLUMES[COLS+12]} : VolBytes
# ${VOLUMES[COLS+13]} : InChanger
VOLUMES=($(mysql --host=$DBHOST --database=$DATABASE --user=$USER --password=$PASSWORD -N -e "SELECT MediaId, VolumeName, VolStatus, FirstWritten, UNIX_TIMESTAMP(CONVERT_TZ(FirstWritten, '+00:00', @@session.time_zone)), LastWritten, UNIX_TIMESTAMP(CONVERT_TZ(LastWritten, '+00:00', @@session.time_zone)), Media.VolUseDuration, Media.VolRetention, Media.Recycle, Media.VolBytes, Media.InChanger FROM Media,Pool WHERE (Pool.Name LIKE "$POOLNAME" AND Media.PoolId=Pool.PoolId AND Media.Enabled="1")"))

# if SELECT returns nothing, tells it and exit.
if [ ${#VOLUMES[*]} -eq 0 ]; then
printf 'There is no volume that matches the specified parametersn'
exit 1
else
VOLUMESRETRIEVED=$((${#VOLUMES[*]}/COLS))
fi

# if the number of volumes expected to be retrieved is zero,
# the script will consider the total volumes in the pool.
if [ $NUMBEROFVOLUMES -eq 0 ]; then
NUMBEROFVOLUMES=$VOLUMESRETRIEVED
fi

function SortByMediaIdAsc () {
for ((i=$2-1; i>=$1; i--)) do
for ((j=$1+1; j<=i; j++)) do
if [ ${AVAILABLEVOLUMES[(j-1)*COLS]} -gt ${AVAILABLEVOLUMES[j*COLS]} ]; then
for ((k=0; k<COLS; k++)) do
TEMP=${AVAILABLEVOLUMES[(j-1)*COLS+k]}
AVAILABLEVOLUMES[(j-1)*COLS+k]=${AVAILABLEVOLUMES[j*COLS+k]}
AVAILABLEVOLUMES[j*COLS+k]=$TEMP
done
fi
done
done
}

function SortByElapsedTimeSinceLastWrittenDesc {
for ((i=$2-1; i>=$1; i--)) do
for ((j=$1+1; j<=i; j++)) do
if [ ${AVAILABLEVOLUMES[(j-1)*COLS+8]} -gt ${AVAILABLEVOLUMES[j*COLS+8]} ]; then
for ((k=0; k<COLS; k++)) do
TEMP=${AVAILABLEVOLUMES[(j-1)*COLS+k]}
AVAILABLEVOLUMES[(j-1)*COLS+k]=${AVAILABLEVOLUMES[j*COLS+k]}
AVAILABLEVOLUMES[j*COLS+k]=$TEMP
done
elif [ ${AVAILABLEVOLUMES[(j-1)*COLS+8]} -eq ${AVAILABLEVOLUMES[j*COLS+8]} ]; then
if [ ${AVAILABLEVOLUMES[(j-1)*COLS]} -lt ${AVAILABLEVOLUMES[j*COLS]} ]; then
for ((k=0; k<COLS; k++)) do
TEMP=${AVAILABLEVOLUMES[(j-1)*COLS+k]}
AVAILABLEVOLUMES[(j-1)*COLS+k]=${AVAILABLEVOLUMES[j*COLS+k]}
AVAILABLEVOLUMES[j*COLS+k]=$TEMP
done
fi
fi
done
done
}

for ((i=0; i<VOLUMESRETRIEVED; i++)) do
# set VolStatus=Used for the volumes that match VolStatus=Append and FirstWritten is not NULL if
# VolUseDuration is not zero and is reached on EXPECTEDVOLUSE datetime
if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} != 0000-00-00 ]; then
if [ ${VOLUMES[((i*COLS+9))]} != 0 ] && [ $((${VOLUMES[((i*COLS+5))]}+${VOLUMES[((i*COLS+9))]})) -lt $EXPECTEDVOLUSE ] ; then
VOLUMES[((i*COLS+2))]="Used";
fi
fi
# if VolStatus=Used OR VolStatus=Full AND Recycle=yes
# if LastWritten+Retention < EXPECTEDVOLUSE then set VolStatus=Purged
if [ ${VOLUMES[((i*COLS+2))]} = Used ] || [ ${VOLUMES[((i*COLS+2))]} = Full ] && [ ${VOLUMES[((i*COLS+11))]} = 1 ]; then
if [ $((${VOLUMES[((i*COLS+8))]}+${VOLUMES[((i*COLS+10))]})) -lt $EXPECTEDVOLUSE ]; then
VOLUMES[((i*COLS+2))]="Purged";
fi
fi
done

# find all volumes for Append (already written) and InChanger and order them by LastWritten descendent order
STARTFROM=0
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} != 0000-00-00 ] && [ ${VOLUMES[((i*COLS+13))]} = 1 ]; then
for ((j=0; j<COLS; j++)) do
AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
done
((NUMBEROFAVAILABLEVOLUMES++))
fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all volumes for Append, never written and InChanger and order them by MediaId
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} = 0000-00-00 ] && [ ${VOLUMES[((i*COLS+13))]} = 1 ]; then
for ((j=0; j<COLS; j++)) do
AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
done
((NUMBEROFAVAILABLEVOLUMES++))
fi
done
SortByMediaIdAsc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all volumes for Append, never written and not InChanger and order them by MediaId
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} = 0000-00-00 ] && [ ${VOLUMES[((i*COLS+13))]} != 1 ]; then
for ((j=0; j<COLS; j++)) do
AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
done
((NUMBEROFAVAILABLEVOLUMES++))
fi
done
SortByMediaIdAsc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all volumes for Append (already written) and not InChanger and order them by LastWritten descendent order
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} != 0000-00-00 ] && [ ${VOLUMES[((i*COLS+13))]} != 1 ]; then
for ((j=0; j<COLS; j++)) do
AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
done
((NUMBEROFAVAILABLEVOLUMES++))
fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all volumes with VolStatus=Recycle and order them by LastWritten descendent order
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
if [ ${VOLUMES[((i*COLS+2))]} = Recycle ]; then
for ((j=0; j<COLS; j++)) do
AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
done
((NUMBEROFAVAILABLEVOLUMES++))
fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all purged volumes and order them by LastWritten descendent order
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
if [ ${VOLUMES[((i*COLS+2))]} = Purged ] && [ ${VOLUMES[((i*COLS+11))]} = 1 ]; then
for ((j=0; j<COLS; j++)) do
AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
done
((NUMBEROFAVAILABLEVOLUMES++))
fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all Used and Full volumes and order them by LastWritten descendent order
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
if ([ ${VOLUMES[((i*COLS+2))]} = Used ] || [ ${VOLUMES[((i*COLS+2))]} = Full ]) && [ ${VOLUMES[((i*COLS+11))]} = 1 ]; then
for ((j=0; j<COLS; j++)) do
AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
done
((NUMBEROFAVAILABLEVOLUMES++))
fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# Lists available volumes
printf 'MediaIdtVolumeNametVolStatustLastWrittenttVolRetentiontVolBytestInChangern'
for ((i=0; i<NUMBEROFVOLUMES; i++)) do
AVAILABLEVOLUMES[((i*COLS+12))]=$(echo $((${AVAILABLEVOLUMES[((i*COLS+12))]}/(1024*1024*1024))) | bc)
printf '%st%st%stt%s %st%stt%d GBtt%sn' ${AVAILABLEVOLUMES[((i*COLS))]} ${AVAILABLEVOLUMES[((i*COLS+1))]} ${AVAILABLEVOLUMES[((i*COLS+2))]} ${AVAILABLEVOLUMES[((i*COLS+6))]} ${AVAILABLEVOLUMES[((i*COLS+7))]} ${AVAILABLEVOLUMES[((i*COLS+10))]} ${AVAILABLEVOLUMES[((i*COLS+12))]} ${AVAILABLEVOLUMES[((i*COLS+13))]}
done

exit $?

Disponível em: pt-brPortuguês (Portuguese (Brazil))enEnglish

Leave a Reply