#!/bin/bash
#
# MySQL Backup Script ver. 2.1
#
#  By Phil2k@gmail.com
#

mysqldump_cmd="/usr/bin/mysqldump"
mysql_cmd="/usr/bin/mysql"
mysqldump_args="-c --create-options --skip-extended-insert --hex-blob --skip-add-locks --skip-lock-tables --skip-disable-keys --skip-add-drop-table --skip-add-drop-database -R -B"
mysql_defaults="/etc/conf.d/mysql_manage.conf"
bzip2_cmd="/bin/bzip2"
bzip2_args="-z -9"
skip_databases=("test" "test_old")

backup_dir="/backup"
date=`date "+%Y-%m-%d"`
backup_dir_day="$backup_dir/$date/mysql"

start_backups=`date "+%s"`

if [ ! -d "$backup_dir_day" ]; then
 mkdir -p "$backup_dir_day"
fi

# identify databases
databases=`$mysql_cmd --defaults-extra-file=$mysql_defaults --skip-column-names -B -e "SHOW DATABASES"`
if [ "$?" != "0" ]; then
 echo "$mysql_cmd error ! Cannot do mysql backups !" >&2
 exit 1
fi

databases=`(echo "$databases"|while read db; do
 valid=1
 for((i=0;i<${#skip_databases[*]};i++)); do
  if [ "$db" = "${skip_databases[$i]}" ]; then
   valid=0
   break
  fi
 done
 if [ "$valid" = "1" ]; then
  echo "$db"
 fi
done)`

n=0
tsize=0
for db in $databases; do
 echo -n "Backuping $db ... "
 start_backup=`date "+%s"`
 backup_file="$backup_dir_day/$db.sql"
 #echo "Executing: $mysqldump_cmd --defaults-extra-file=$mysql_defaults $db"
 $mysqldump_cmd --defaults-extra-file=$mysql_defaults $mysqldump_args "$db" > "$backup_file"
 if [ "$?" != "0" ]; then
  echo "$mysqldump error ! Cannot do mysql backups !" >&2
  exit 1
 fi
 size=`stat -c "%s" "$backup_file"`
 if [ "$size" -gt 1023 ]; then
  size=$[$size/1024]
  if [ "$size" -gt 1023 ]; then
   size=$[$size/1024]
   unit="m"
  else
   unit="k"
  fi
 else
  unit=""
 fi
 $bzip2_cmd $bzip2_args $backup_file
 if [ "$?" != "0" ]; then
  echo "$bzip2_cmd failed ! Cannot do mysql backups !" >&2
  exit 1
 fi
 archive_file="$backup_file.bz2"
 if [ ! -f "$archive_file" ]; then
  echo "$bzip2_cmd failed to make $archive_file from $backup_file !" >&2
  exit 1
 fi
 if [ -f "$backup_file" ]; then
  \rm "$backup_file"
 fi
 asize=`stat -c "%s" "$archive_file"`
 tsize=$[$tsize+$asize]
 if [ "$asize" -gt 1023 ]; then
  asize=$[$asize/1024]
  if [ "$asize" -gt 1023 ]; then
   asize=$[$asize/1024]
   aunit="m"
  else
   aunit="k"
  fi
 else
  aunit=""
 fi
 end_backup=`date "+%s"`
 elapsed=$[$end_backup-$start_backup]
 echo "done ( sql size = $size "$unit"bytes , archive size = $asize "$aunit"bytes , elapsed = $elapsed sec )."
 n=$[$n+1]
done

end_backups=`date "+%s"`
elapsed=$[$end_backups-$start_backups]

if [ "$tsize" -gt 1023 ]; then
 tsize=$[$tsize/1024]
 if [ "$tsize" -gt 1023 ]; then
  tsize=$[$tsize/1024]
  if [ "$tsize" -gt 1023 ]; then
   tsize=$[$tsize/1024]
   unit="g"
  else
   unit="m"
  fi
 else
  unit="k"
 fi
else
 unit=""
fi

echo "$n databases were backup-saved in $backup_dir_day of $tsize ${unit}bytes in $elapsed seconds."