2009-12-22, 05:04 PM
This is a script I use to backup MySQL every night via cron. I greedily adapted it from a script I got off Source Forge. It's GPL so have at it!
This makes daily backups saved for one week, weekly backups saved for one month, and monthly backups saved forever. So eventually you will run out of disk space if you don't manually maintain those.
One problem I had run into was if the machine I was backing up to was down, the script would write the backup files to the mount point anyway, so I added a special file it looks for to check if it's mounted. There is probably a better way, if you know it please let me know!
The code is in the attachment but here's a printout for you. You need to search for XXX throughout and replace with your information to make this work.
Cheers,
BentFranklin
#!/bin/bash
#
# A MySQL Backup Script
#
# Adapted greedily from:
# VER. 3.0 - http://sourceforge.net/projects/automysqlbackup/
# Copyright © 2002-2003 [email protected]
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
#=====================================================================
# Set the following variables to your system needs
# (Detailed instructions below variables)
#=====================================================================
# Username to access the MySQL server e.g. dbuser
USERNAME=XXX
# Username to access the MySQL server e.g. password
PASSWORD=XXX
# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost
# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="all"
# Already means the mount point is already mounted
ALREADY="no"
# Location of mounted share
MNTDIR=XXX
# Backup directory location e.g /backups
BACKUPDIR=$MNTDIR/MySQLDumps
# List of DBNAMES to EXCLUDE if DBNAMES are set to all (must be in quotes)
DBEXCLUDE=""
# Include CREATE DATABASE in backup?
CREATE_DATABASE=yes
# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
DOWEEKLY=6
# Compress communications between backup server and MySQL server?
COMMCOMP=no
# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)
MAX_ALLOWED_PACKET=
# For connections to localhost. Sometimes the Unix socket file must be specified.
SOCKET=
#=====================================================================
# Options documantation
#=====================================================================
# Set USERNAME and PASSWORD of a user that has at least SELECT permission
# to ALL databases.
#
# Set the DBHOST option to the server you wish to backup, leave the
# default to backup "this server".
#
# Put in the list of DBNAMES(Databases)to be backed up. If you would like
# to backup ALL DBs on the server set DBNAMES="all". (f set to "all" then
# any new DBs will automatically be backed up without needing to modify
# this backup script when a new DB is created).
#
# If the DB you want to backup has a space in the name replace the space
# with a % e.g. "data base" will become "data%base"
#
# You can change the backup storage location from /backups to anything
# you like by using the BACKUPDIR setting..
#
#
# === Advanced options doc's ===
#
# If you set DBNAMES="all" you can configure the option DBEXCLUDE.
# Otherwise this option will not be used. This option can be used if
# you want to backup all dbs, but you want exclude some of them (e.g.,
# a db is to big).
#
# Set CREATE_DATABASE to "yes" (the default) if you want your SQL-Dump to create
# a database with the same name as the original database when restoring.
# Saying "no" here will allow your to specify the database name you want to
# restore your dump into, making a copy of the database by using the dump
# created with automysqlbackup.
#
# To set the day of the week that you would like the weekly backup to happen
# set the DOWEEKLY setting, this can be a value from 1 to 7 where 1 is Monday,
# The default is 6 which means that weekly backups are done on a Saturday.
#
# COMMCOMP is used to enable or disable mysql client to server compression, so
# it is useful to save bandwidth when backing up a remote MySQL server over
# the network.
#
# If the DB's being backed up make use of large BLOB fields then you may need
# to increase the MAX_ALLOWED_PACKET setting, for example 16MB..
#
# When connecting to localhost as the DB server (DBHOST=localhost) sometimes
# the system can have issues locating the socket file.. This can now be set
# using the SOCKET parameter.. An example may be SOCKET=/private/tmp/mysql.sock
#
# Use PREBACKUP and POSTBACKUP to specify Per and Post backup commands
# or scripts to perform tasks either before or after the backup process.
#
#
#=====================================================================
# Backup Rotation..
#=====================================================================
#
# Daily Backups are rotated weekly.
# Weekly Backups are run by default on Saturday Morning when
# cron.daily scripts are run. Can be changed with DOWEEKLY setting.
# Weekly Backups are rotated on a 5 week cycle.
# Monthly Backups are run on the 1st of the month.
# Monthly Backups are NOT rotated automatically.
# It may be a good idea to copy Monthly backups offline or to another
# server.
#
#=====================================================================
# Restoring
#=====================================================================
# Firstly you will need to uncompress the backup file.
# eg. gunzip file.gz (or bunzip2 file.bz2)
#
# Next you will need to use the mysql client to restore the DB from the
# sql file. eg.
# mysql --user=username --pass=password --host=dbserver database < /path/file.sql
# or
# mysql --user=username --pass=password --host=dbserver -e "source /path/file.sql" database
#
# NOTE: Make sure you use "<" and not ">" in the above command because
# you are piping the file.sql to mysql and not the other way around.
#
# Lets hope you never have to use this..
#
PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin
DATE=
DOW=
DNOW=
DOM=
M=
W=
# OPT string for use with mysqldump ( see man mysqldump )
OPT="--default-character-set=latin1 --add-drop-table --databases"
# Hostname for LOG information
if [ "$DBHOST" = "localhost" ]; then
HOST=
if [ "$SOCKET" ]; then
OPT="$OPT --socket=$SOCKET"
fi
else
HOST=$DBHOST
fi
LOGFILE=$BACKUPDIR/logs/$HOST.mysql.$DATE.log # Logfile Name
LOGERR=$BACKUPDIR/logs/$HOST.mysql.$DATE.err # Errorfile Name
# Add --compress option to $OPT
if [ "$COMMCOMP" = "yes" ];
then
OPT="$OPT --compress"
fi
# Add --max_allowed_packet option to $OPT
if [ "$MAX_ALLOWED_PACKET" ];
then
OPT="$OPT --max_allowed_packet=$MAX_ALLOWED_PACKET"
fi
# If the backup share isn't mounted, send a warning email and exit
# $MNTDIR/XXX is a file that exists only on the mounted share
# This prevents the backup from being written to the filesystem that the mount point is on
# There's probably a better way!
if [ -e $MNTDIR/XXX ]
then
ALREADY="yes"
else
ALREADY="no"
mount $MNTDIR
if [ ! -e $MNTDIR/XXX ];
then
COMPOSE=/home/XXX/tmp/compose-email.tmp
echo >> $COMPOSE
echo "MySQL backup for $DATE did not run because XXX was not mounted." >> $COMPOSE
echo >> $COMPOSE
cat $COMPOSE | mail -s "MySQL backup for $DATE did not run" [email protected]
rm -f $COMPOSE
exit 1
fi
fi
# Create required directories
if [ ! -e "$BACKUPDIR" ] # Check Backup Directory exists
then
mkdir -p "$BACKUPDIR"
fi
if [ ! -e "$BACKUPDIR/daily" ] # Check Daily Directory exists
then
mkdir -p "$BACKUPDIR/daily"
fi
if [ ! -e "$BACKUPDIR/weekly" ] # Check Weekly Directory exists
then
mkdir -p "$BACKUPDIR/weekly"
fi
if [ ! -e "$BACKUPDIR/monthly" ] # Check Monthly Directory exists
then
mkdir -p "$BACKUPDIR/monthly"
fi
if [ ! -e "$BACKUPDIR/logs" ] # Check Logs Directory exists
then
mkdir -p "$BACKUPDIR/logs"
fi
if [ ! -e "$BACKUPDIR/latest" ] # Check Latest Directory exists
then
mkdir -p "$BACKUPDIR/latest"
fi
# IO redirection for logging.
touch $LOGFILE
exec 6>&1 # Link file descriptor #6 with stdout
# Saves stdout
exec > $LOGFILE # stdout replaced with file $LOGFILE
touch $LOGERR
exec 7>&2 # Link file descriptor #7 with stderr
# Saves stderr
exec 2> $LOGERR # stderr replaced with file $LOGERR
# If backing up all DBs on the server
if [ "$DBNAMES" = "all" ]; then
DBNAMES="
# If DBs are excluded
for exclude in $DBEXCLUDE
do
DBNAMES=
done
fi
echo ======================================================================
echo
echo MySQL backup: /home/XXX/bin/Backup-MySQL
echo
echo Backup of Database Server - $HOST
echo Backup Start
# Monthly Full Backup of all Databases
if [ $DOM = "01" ]; then
echo
echo ======================================================================
echo
echo Monthly full Backup of \( $DBNAMES \)...
echo
echo "EXECUTING: mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/monthly/$DATE.$M.sql"
mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/monthly/$DATE.$M.sql
echo
echo "EXECUTING: gzip -c $BACKUPDIR/monthly/$DATE.$M.sql > $BACKUPDIR/monthly/$DATE.$M.sql.gz"
gzip -c $BACKUPDIR/monthly/$DATE.$M.sql > $BACKUPDIR/monthly/$DATE.$M.sql.gz
if [ -s $BACKUPDIR/monthly/$DATE.$M.sql.gz ];
then
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/monthly/$DATE.$M.sql"
eval rm -f $BACKUPDIR/monthly/$DATE.$M.sql
fi
echo
echo ======================================================================
echo
echo Update pointer to latest backup...
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/latest/*"
eval rm -f $BACKUPDIR/latest/*
echo
echo "EXECUTING: touch $BACKUPDIR/latest/monthly-$DATE.$M"
touch $BACKUPDIR/latest/monthly-$DATE.$M
# Weekly Backup
elif [ $DNOW = $DOWEEKLY ]; then
echo
echo ======================================================================
echo
echo Weekly Backup of Databases \( $DBNAMES \)
echo
echo Rotating 5 weeks Backups...
if [ "$W" -le 05 ];then
REMW=
elif [ "$W" -lt 15 ];then
REMW=0
else
REMW=
fi
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/weekly/week.$REMW.*"
eval rm -f $BACKUPDIR/weekly/week.$REMW.*
echo
echo "EXECUTING: mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/weekly/week.$W.$DATE.sql"
mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/weekly/week.$W.$DATE.sql
echo
echo "EXECUTING: gzip -c $BACKUPDIR/weekly/week.$W.$DATE.sql > $BACKUPDIR/weekly/week.$W.$DATE.sql.gz"
gzip -c $BACKUPDIR/weekly/week.$W.$DATE.sql > $BACKUPDIR/weekly/week.$W.$DATE.sql.gz
if [ -s $BACKUPDIR/weekly/week.$W.$DATE.sql.gz ];
then
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/weekly/week.$W.$DATE.sql"
eval rm -f $BACKUPDIR/weekly/week.$W.$DATE.sql
fi
echo
echo ======================================================================
echo
echo Update pointer to latest backup...
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/latest/*"
eval rm -f $BACKUPDIR/latest/*
echo
echo "EXECUTING: touch $BACKUPDIR/latest/monthly-$DATE.$M"
touch $BACKUPDIR/latest/weekly-$W.$DATE.$M
# Daily Backup
else
echo
echo ======================================================================
echo
echo Daily Backup of Databases \( $DBNAMES \)
echo
echo Rotating last weeks Backup...
echo
echo "EXECUTING: eval rm -f "$BACKUPDIR/daily/*.$DOW.*" "
eval rm -f $BACKUPDIR/daily/*.$DOW.*
echo
echo "EXECUTING: mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/daily/$DATE.$DOW.sql"
mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/daily/$DATE.$DOW.sql
echo
echo "EXECUTING: gzip -c $BACKUPDIR/daily/$DATE.$DOW.sql > $BACKUPDIR/daily/$DATE.$DOW.sql.gz"
gzip -c $BACKUPDIR/daily/$DATE.$DOW.sql > $BACKUPDIR/daily/$DATE.$DOW.sql.gz
if [ -s $BACKUPDIR/daily/$DATE.$DOW.sql.gz ];
then
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/daily/$DATE.$DOW.sql"
eval rm -f $BACKUPDIR/daily/$DATE.$DOW.sql
fi
echo
echo ======================================================================
echo
echo Update pointer to latest backup...
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/latest/*"
eval rm -f $BACKUPDIR/latest/*
echo
echo "EXECUTING: touch $BACKUPDIR/latest/daily-$DATE.$DOW"
touch $BACKUPDIR/latest/daily-$DATE.$DOW
fi
echo
echo ======================================================================
echo
echo Backup End Time
echo
echo ======================================================================
echo
echo Total disk space used for backup storage..
echo Size - Location
du -hs $BACKUPDIR
if [ -s "$LOGERR" ]
then
STATUS=1
else
STATUS=0
fi
echo
echo Backup-MySQL with status $STATUS
echo
echo ======================================================================
#Clean up IO redirection
exec 1>&6 6>&- # Restore stdout and close file descriptor #6
exec 1>&7 7>&- # Restore stdout and close file descriptor #7
# Unmount XXX is it wasn't originally mounted
if [ $ALREADY = "no" ];
then
umount $MNTDIR
fi
# Done
exit $STATUS
This makes daily backups saved for one week, weekly backups saved for one month, and monthly backups saved forever. So eventually you will run out of disk space if you don't manually maintain those.
One problem I had run into was if the machine I was backing up to was down, the script would write the backup files to the mount point anyway, so I added a special file it looks for to check if it's mounted. There is probably a better way, if you know it please let me know!
The code is in the attachment but here's a printout for you. You need to search for XXX throughout and replace with your information to make this work.
Cheers,
BentFranklin
#!/bin/bash
#
# A MySQL Backup Script
#
# Adapted greedily from:
# VER. 3.0 - http://sourceforge.net/projects/automysqlbackup/
# Copyright © 2002-2003 [email protected]
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
#=====================================================================
# Set the following variables to your system needs
# (Detailed instructions below variables)
#=====================================================================
# Username to access the MySQL server e.g. dbuser
USERNAME=XXX
# Username to access the MySQL server e.g. password
PASSWORD=XXX
# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost
# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="all"
# Already means the mount point is already mounted
ALREADY="no"
# Location of mounted share
MNTDIR=XXX
# Backup directory location e.g /backups
BACKUPDIR=$MNTDIR/MySQLDumps
# List of DBNAMES to EXCLUDE if DBNAMES are set to all (must be in quotes)
DBEXCLUDE=""
# Include CREATE DATABASE in backup?
CREATE_DATABASE=yes
# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
DOWEEKLY=6
# Compress communications between backup server and MySQL server?
COMMCOMP=no
# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)
MAX_ALLOWED_PACKET=
# For connections to localhost. Sometimes the Unix socket file must be specified.
SOCKET=
#=====================================================================
# Options documantation
#=====================================================================
# Set USERNAME and PASSWORD of a user that has at least SELECT permission
# to ALL databases.
#
# Set the DBHOST option to the server you wish to backup, leave the
# default to backup "this server".
#
# Put in the list of DBNAMES(Databases)to be backed up. If you would like
# to backup ALL DBs on the server set DBNAMES="all". (f set to "all" then
# any new DBs will automatically be backed up without needing to modify
# this backup script when a new DB is created).
#
# If the DB you want to backup has a space in the name replace the space
# with a % e.g. "data base" will become "data%base"
#
# You can change the backup storage location from /backups to anything
# you like by using the BACKUPDIR setting..
#
#
# === Advanced options doc's ===
#
# If you set DBNAMES="all" you can configure the option DBEXCLUDE.
# Otherwise this option will not be used. This option can be used if
# you want to backup all dbs, but you want exclude some of them (e.g.,
# a db is to big).
#
# Set CREATE_DATABASE to "yes" (the default) if you want your SQL-Dump to create
# a database with the same name as the original database when restoring.
# Saying "no" here will allow your to specify the database name you want to
# restore your dump into, making a copy of the database by using the dump
# created with automysqlbackup.
#
# To set the day of the week that you would like the weekly backup to happen
# set the DOWEEKLY setting, this can be a value from 1 to 7 where 1 is Monday,
# The default is 6 which means that weekly backups are done on a Saturday.
#
# COMMCOMP is used to enable or disable mysql client to server compression, so
# it is useful to save bandwidth when backing up a remote MySQL server over
# the network.
#
# If the DB's being backed up make use of large BLOB fields then you may need
# to increase the MAX_ALLOWED_PACKET setting, for example 16MB..
#
# When connecting to localhost as the DB server (DBHOST=localhost) sometimes
# the system can have issues locating the socket file.. This can now be set
# using the SOCKET parameter.. An example may be SOCKET=/private/tmp/mysql.sock
#
# Use PREBACKUP and POSTBACKUP to specify Per and Post backup commands
# or scripts to perform tasks either before or after the backup process.
#
#
#=====================================================================
# Backup Rotation..
#=====================================================================
#
# Daily Backups are rotated weekly.
# Weekly Backups are run by default on Saturday Morning when
# cron.daily scripts are run. Can be changed with DOWEEKLY setting.
# Weekly Backups are rotated on a 5 week cycle.
# Monthly Backups are run on the 1st of the month.
# Monthly Backups are NOT rotated automatically.
# It may be a good idea to copy Monthly backups offline or to another
# server.
#
#=====================================================================
# Restoring
#=====================================================================
# Firstly you will need to uncompress the backup file.
# eg. gunzip file.gz (or bunzip2 file.bz2)
#
# Next you will need to use the mysql client to restore the DB from the
# sql file. eg.
# mysql --user=username --pass=password --host=dbserver database < /path/file.sql
# or
# mysql --user=username --pass=password --host=dbserver -e "source /path/file.sql" database
#
# NOTE: Make sure you use "<" and not ">" in the above command because
# you are piping the file.sql to mysql and not the other way around.
#
# Lets hope you never have to use this..
#
PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin
DATE=
date +%Y%m%d # Datestamp e.g 20090127DOW=
date +%A # Day of the week e.g. MondayDNOW=
date +%u # Day number of the week 1 to 7 where 1 represents MondayDOM=
date +%d # Date of the Month e.g. 27M=
date +%B # Month e.g JanuaryW=
date +%V # Week Number e.g 37# OPT string for use with mysqldump ( see man mysqldump )
OPT="--default-character-set=latin1 --add-drop-table --databases"
# Hostname for LOG information
if [ "$DBHOST" = "localhost" ]; then
HOST=
hostnameif [ "$SOCKET" ]; then
OPT="$OPT --socket=$SOCKET"
fi
else
HOST=$DBHOST
fi
LOGFILE=$BACKUPDIR/logs/$HOST.mysql.$DATE.log # Logfile Name
LOGERR=$BACKUPDIR/logs/$HOST.mysql.$DATE.err # Errorfile Name
# Add --compress option to $OPT
if [ "$COMMCOMP" = "yes" ];
then
OPT="$OPT --compress"
fi
# Add --max_allowed_packet option to $OPT
if [ "$MAX_ALLOWED_PACKET" ];
then
OPT="$OPT --max_allowed_packet=$MAX_ALLOWED_PACKET"
fi
# If the backup share isn't mounted, send a warning email and exit
# $MNTDIR/XXX is a file that exists only on the mounted share
# This prevents the backup from being written to the filesystem that the mount point is on
# There's probably a better way!
if [ -e $MNTDIR/XXX ]
then
ALREADY="yes"
else
ALREADY="no"
mount $MNTDIR
if [ ! -e $MNTDIR/XXX ];
then
COMPOSE=/home/XXX/tmp/compose-email.tmp
echo >> $COMPOSE
echo "MySQL backup for $DATE did not run because XXX was not mounted." >> $COMPOSE
echo >> $COMPOSE
cat $COMPOSE | mail -s "MySQL backup for $DATE did not run" [email protected]
rm -f $COMPOSE
exit 1
fi
fi
# Create required directories
if [ ! -e "$BACKUPDIR" ] # Check Backup Directory exists
then
mkdir -p "$BACKUPDIR"
fi
if [ ! -e "$BACKUPDIR/daily" ] # Check Daily Directory exists
then
mkdir -p "$BACKUPDIR/daily"
fi
if [ ! -e "$BACKUPDIR/weekly" ] # Check Weekly Directory exists
then
mkdir -p "$BACKUPDIR/weekly"
fi
if [ ! -e "$BACKUPDIR/monthly" ] # Check Monthly Directory exists
then
mkdir -p "$BACKUPDIR/monthly"
fi
if [ ! -e "$BACKUPDIR/logs" ] # Check Logs Directory exists
then
mkdir -p "$BACKUPDIR/logs"
fi
if [ ! -e "$BACKUPDIR/latest" ] # Check Latest Directory exists
then
mkdir -p "$BACKUPDIR/latest"
fi
# IO redirection for logging.
touch $LOGFILE
exec 6>&1 # Link file descriptor #6 with stdout
# Saves stdout
exec > $LOGFILE # stdout replaced with file $LOGFILE
touch $LOGERR
exec 7>&2 # Link file descriptor #7 with stderr
# Saves stderr
exec 2> $LOGERR # stderr replaced with file $LOGERR
# If backing up all DBs on the server
if [ "$DBNAMES" = "all" ]; then
DBNAMES="
mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST --batch --skip-column-names -e "show databases"| sed 's/ /%/g'"# If DBs are excluded
for exclude in $DBEXCLUDE
do
DBNAMES=
echo $DBNAMES | sed "s/\b$exclude\b//g"done
fi
echo ======================================================================
echo
echo MySQL backup: /home/XXX/bin/Backup-MySQL
echo
echo Backup of Database Server - $HOST
echo Backup Start
date# Monthly Full Backup of all Databases
if [ $DOM = "01" ]; then
echo
echo ======================================================================
echo
echo Monthly full Backup of \( $DBNAMES \)...
echo
echo "EXECUTING: mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/monthly/$DATE.$M.sql"
mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/monthly/$DATE.$M.sql
echo
echo "EXECUTING: gzip -c $BACKUPDIR/monthly/$DATE.$M.sql > $BACKUPDIR/monthly/$DATE.$M.sql.gz"
gzip -c $BACKUPDIR/monthly/$DATE.$M.sql > $BACKUPDIR/monthly/$DATE.$M.sql.gz
if [ -s $BACKUPDIR/monthly/$DATE.$M.sql.gz ];
then
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/monthly/$DATE.$M.sql"
eval rm -f $BACKUPDIR/monthly/$DATE.$M.sql
fi
echo
echo ======================================================================
echo
echo Update pointer to latest backup...
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/latest/*"
eval rm -f $BACKUPDIR/latest/*
echo
echo "EXECUTING: touch $BACKUPDIR/latest/monthly-$DATE.$M"
touch $BACKUPDIR/latest/monthly-$DATE.$M
# Weekly Backup
elif [ $DNOW = $DOWEEKLY ]; then
echo
echo ======================================================================
echo
echo Weekly Backup of Databases \( $DBNAMES \)
echo
echo Rotating 5 weeks Backups...
if [ "$W" -le 05 ];then
REMW=
expr 48 + $Welif [ "$W" -lt 15 ];then
REMW=0
expr $W - 5else
REMW=
expr $W - 5fi
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/weekly/week.$REMW.*"
eval rm -f $BACKUPDIR/weekly/week.$REMW.*
echo
echo "EXECUTING: mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/weekly/week.$W.$DATE.sql"
mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/weekly/week.$W.$DATE.sql
echo
echo "EXECUTING: gzip -c $BACKUPDIR/weekly/week.$W.$DATE.sql > $BACKUPDIR/weekly/week.$W.$DATE.sql.gz"
gzip -c $BACKUPDIR/weekly/week.$W.$DATE.sql > $BACKUPDIR/weekly/week.$W.$DATE.sql.gz
if [ -s $BACKUPDIR/weekly/week.$W.$DATE.sql.gz ];
then
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/weekly/week.$W.$DATE.sql"
eval rm -f $BACKUPDIR/weekly/week.$W.$DATE.sql
fi
echo
echo ======================================================================
echo
echo Update pointer to latest backup...
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/latest/*"
eval rm -f $BACKUPDIR/latest/*
echo
echo "EXECUTING: touch $BACKUPDIR/latest/monthly-$DATE.$M"
touch $BACKUPDIR/latest/weekly-$W.$DATE.$M
# Daily Backup
else
echo
echo ======================================================================
echo
echo Daily Backup of Databases \( $DBNAMES \)
echo
echo Rotating last weeks Backup...
echo
echo "EXECUTING: eval rm -f "$BACKUPDIR/daily/*.$DOW.*" "
eval rm -f $BACKUPDIR/daily/*.$DOW.*
echo
echo "EXECUTING: mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/daily/$DATE.$DOW.sql"
mysqldump --verbose --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $DBNAMES > $BACKUPDIR/daily/$DATE.$DOW.sql
echo
echo "EXECUTING: gzip -c $BACKUPDIR/daily/$DATE.$DOW.sql > $BACKUPDIR/daily/$DATE.$DOW.sql.gz"
gzip -c $BACKUPDIR/daily/$DATE.$DOW.sql > $BACKUPDIR/daily/$DATE.$DOW.sql.gz
if [ -s $BACKUPDIR/daily/$DATE.$DOW.sql.gz ];
then
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/daily/$DATE.$DOW.sql"
eval rm -f $BACKUPDIR/daily/$DATE.$DOW.sql
fi
echo
echo ======================================================================
echo
echo Update pointer to latest backup...
echo
echo "EXECUTING: eval rm -f $BACKUPDIR/latest/*"
eval rm -f $BACKUPDIR/latest/*
echo
echo "EXECUTING: touch $BACKUPDIR/latest/daily-$DATE.$DOW"
touch $BACKUPDIR/latest/daily-$DATE.$DOW
fi
echo
echo ======================================================================
echo
echo Backup End Time
dateecho
echo ======================================================================
echo
echo Total disk space used for backup storage..
echo Size - Location
du -hs $BACKUPDIR
if [ -s "$LOGERR" ]
then
STATUS=1
else
STATUS=0
fi
echo
echo Backup-MySQL with status $STATUS
echo
echo ======================================================================
#Clean up IO redirection
exec 1>&6 6>&- # Restore stdout and close file descriptor #6
exec 1>&7 7>&- # Restore stdout and close file descriptor #7
# Unmount XXX is it wasn't originally mounted
if [ $ALREADY = "no" ];
then
umount $MNTDIR
fi
# Done
exit $STATUS