mysql_backup.sh

   1 #!/bin/bash
   2 ##########################################################################
   3 #
   4 # mysql_backups.sh: A shell script to back up all MySQL databases in
   5 #                   one shot, nightly, and keep a rolling 3 weeks of
   6 #                   backups hot, online in the backup archive.
   7 #
   8 # Written by: David A. Desrosiers
   9 # Contact: desrod@gnu-designs.com
  10 # Last updated: Mon Feb 12 14:08:33 EST 2007
  11 #
  12 # Copyright 1998-2007.  This may be modified and distributed on the same
  13 #                       terms as the GPL itself. This copyright header
  14 #                       must remain intact if you use this script.
  15 #
  16 ##########################################################################
  17
  18 #####################################
  19 ### MySQL Configuration Variables ###
  20 #####################################
  21
  22 . /etc/conf.d/backup
  23
  24 # Local directory for dump files
  25 MYSQL_BACKUP_DIR=/var/backups/mysql/
  26
  27 #####################################
  28 ### Edit Below If Necessary #########
  29 #####################################
  30 cd $MYSQL_BACKUP_DIR
  31 DATE=`eval date +%Y-%m-%d`
  32 NOW=`date +'%Y-%m-%d.%H:%M:%S'`
  33
  34 # Optimize the table structure nightly
  35 #mysqlcheck -u$DBUSER -p$DBPASSWD -h$DBHOST -oA
  36
  37 DBS=`mysql -u$DB_USER -p$DB_PASS -h$DB_HOST -e"show databases"`
  38
  39 for DATABASE in $DBS
  40 do
  41         if [ $DATABASE != "Database" ]; then
  42                 echo "Dumping $DATABASE now..."
  43                                                                 #BASE=${DATE}.${DATABASE}
  44                                                                 BASE=${DATABASE}
  45                 ${MYSQLDUMP} -u$DB_USER -p$DB_PASS -h$DB_HOST --lock-tables --add-drop-table --skip-dump-date -e $DATABASE > ${BASE}.sql
  46
  47                 ${BZIP2} -f9 ${BASE}.sql
  48                 chmod 0400 ${BASE}.sql.bz2
  49                 #7za a -t7z -m0=lzma -mx=9 -mfb=64 -md=32m -ms=on ${BASE}.7z ${BASE}.sql && rm ${BASE}.sql
  50                 #chmod 0400 ${BASE}.7z
  51
  52         fi
  53 done
  54
  55 # Delete files older than 21 days
  56 for i in `find $MYSQL_BACKUP_DIR -mtime +21|sort`; do (rm $i); done;
  57
  58 exit 0