June 25, 2014 at 11:09Database backup - is it there?
It makes no sense to talk about how relevant this is. Today we will tell you how we backed up mysql databases.
And one of the most important is a check, but has backup been made? Was the dump successful? And were there any mistakes? Do I know about them?
In my opinion, today it is hardly possible to find someone who does not backup. But here to check for errors when performing backup, perhaps I’ll try to tell you how we do it .
I must say right away that, perhaps, in the implementation principle, standard schemes were used, but perhaps you will find for yourself something new that you can implement at home.
We describe the variables and paths:
PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin
DATE=`date +%Y-%m-%d_%Hh%Mm` # Datestamp e.g 2002-09-21
DOW=`date +%A` # Day of the week e.g. Monday
DNOW=`date +%u` # Day number of the week 1 to 7 where 1 represents Monday
DOM=`date +%d` # Date of the Month e.g. 27
M=`date +%B` # Month e.g January
W=`date +%V` # Week Number e.g 37
VER=2.5 # Version Number
LOGFILE=$BACKUPDIR/$DBHOST-`date +%N`.log # Logfile Name
LOGERR=$BACKUPDIR/ERRORS_$DBHOST-`date +%N`.log # Logfile Name
BACKUPFILES=""
OPT="--quote-names --opt --routines --single-transaction --events" # OPT string for use with mysqldump ( see man mysqldump )
DBEXCLUDE+=" information_schema performance_schema"
LOCATION="$(cd -P -- "$(dirname -- "$0")" && pwd -P)/.."
For all servers, the script is one, but where without the settings file. Often there is a need not only for global settings, but also specific for a particular project. We decided to split it into several files.
mysql-backup.conf.dist - the file stores global settings that are common to all projects
mysql-backup.conf - a file for specific settings for the project
mysql-backup.local.conf - a file for local backup settings
Check if there is a config - we will do backup on this server, and read our configs:
if [ -f "$LOCATION/etc/mysql-backup.conf.dist" ]; then
. "$LOCATION/etc/mysql-backup.conf.dist"
if [ -f "$LOCATION/etc/mysql-backup.conf" ]; then
. "$LOCATION/etc/mysql-backup.conf"
fi
if [ -f "$LOCATION/etc/mysql-backup.local.conf" ]; then
. "$LOCATION/etc/mysql-backup.local.conf"
fi
else
echo "mysql-backup.conf.dist not found"
exit 0
fi
Next, let's go read the config options and set the values
# Add --compress mysqldump option to $OPT
if [ "$COMMCOMP" = "yes" ];
then
OPT="$OPT --compress"
fi
# Add --compress mysqldump option to $OPT
if [ "$MAX_ALLOWED_PACKET" ];
then
OPT="$OPT --max_allowed_packet=$MAX_ALLOWED_PACKET"
fi
if [ ! "$BACKUP_DAYS" ];
then
BACKUP_DAYS=7
fi
if [ ! "$BACKUP_MONTH" ];
then
BACKUP_MONTH=4
fi
if [ ! "$DO_SQL_DUMP" ];
then
DO_SQL_DUMP="yes"
fi
if [ ! "$DO_HOT_BACKUP" ];
then
DO_HOT_BACKUP="no"
fi
I think there is no need to describe in detail, because Variable names must speak for themselves all.
Check and create directories for our backups
# Create required directories
if [ ! -e "/var/lib/mysql.backup" ] # Check Backup Directory exists.
then
mkdir -p "/var/lib/mysql.backup"
fi
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 [ "$LATEST" = "yes" ]
then
if [ ! -e "$BACKUPDIR/latest" ] # Check Latest Directory exists.
then
mkdir -p "$BACKUPDIR/latest"
fi
eval rm -fv "$BACKUPDIR/latest/*"
fi
One of the important points is not only to backup, but also to check errors in the process of its execution.
# 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.
echo $LOCATION
We describe our functions. Because For different projects, you need a different backup, for whom it is incremental, for whom users sleep at night - a night dump is enough.
Plain mysqldump
# Database dump function
dbdump () {
if [ "$SEPTABLE" = "yes" ]; then
TABLENAMES="`mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST --batch --skip-column-names -e "show tables" $1| sed 's/ /%/g'`"
for TABLENAME in $TABLENAMES ; do
OUTFILENAME=`echo $2 | sed "s~$3~$3.$TABLENAME~"`
mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 $TABLENAME > $OUTFILENAME
done
mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT --no-data $1 > $2
else
mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 > $2
fi
return 0
}
Using mydumper
# Database dump function
dbdump_mydumper () {
MYOPT=""
# Add --ignore-table options to $MYOPT
if [ -n "$TABLEEXCLUDE" ]; then
for table in $TABLEEXCLUDE ; do
MYOPT="${MYOPT}${table}|"
done
fi
if [ -n "$DBEXCLUDE" ]; then
for table in $DBEXCLUDE ; do
MYOPT="${MYOPT}${table}|"
done
fi
if [ -n "$MYOPT" ]; then
MYOPT="--regex '^(?!(${MYOPT}mysql.noptable))'"
fi
eval mydumper --user $USERNAME --password $PASSWORD -c -l 300 --kill-long-queries -s 500000 $MYOPT -o $1
if [ $? -gt 0 ]; then
echo "Error in mydumper backup stage" >&2
mysql -u root -p`cat /root/.mysql` -e "SHOW FULL PROCESSLIST" | sort -n -k 6 >&2
fi
/usr/bin/find "$BACKUPDIR/daily" -name "_mydumper*" -type d -mtime +$BACKUP_DAYS -print0 | xargs -0 rm -rf
/usr/bin/find "$BACKUPDIR/weekly" -name "_mydumper*" -type d -mtime +35 -print0 | xargs -0 rm -rf
/usr/bin/find "$BACKUPDIR/monthly" -name "_mydumper*" -type d -mtime +100 -print0 | xargs -0 rm -rf
return 0
}
Normal data copying, naturally with table locking
dbdump_h () {
echo First rsync started at `date`
rsync -avH --delete --numeric-ids /var/lib/mysql/ /var/lib/mysql.backup
echo First rsync finished at `date`
echo DB locked, second rsync started at `date`
echo "FLUSH TABLES WITH READ LOCK" | mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST
rsync -avH --delete --numeric-ids /var/lib/mysql/ /var/lib/mysql.backup
echo "UNLOCK TABLES" | mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST
echo DB unlocked, second rsync finished at `date`
return 0
}
And where without it, we all know the incremental backup
dbdump_h_xtra () {
if [ ! -f /usr/bin/innobackupex ]; then
yum -y install xtrabackup.x86_64
fi
if [ ! -f /var/lib/mysql-xtra/xtrabackup_checkpoints ]; then
echo Full backup stage started at `date`
#ionice -c3
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --throttle=40 --rsync /var/lib/mysql-xtra 2>&1
if [ $? -gt 0 ]; then
echo "Error in full backup stage" >&2
fi
#ionice -c3
/usr/bin/innobackupex --apply-log --redo-only --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --throttle=40 /var/lib/mysql-xtra 2>&1
if [ $? -gt 0 ]; then
echo "Error in apply log redo stage" >&2
fi
echo Full backup stage finished at `date` code $?
else
echo INC backup stage started at `date` code $?
rm -rf /var/lib/mysql-xtra-inc
#ionice -c3
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --throttle=40 --rsync --incremental /var/lib/mysql-xtra-inc --incremental-basedir=/var/lib/mysql-xtra 2>&1
if [ $? -gt 0 ]; then
echo "Error in inc stage" >&2
fi
#ionice -c3
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --throttle=40 --apply-log /var/lib/mysql-xtra --incremental-dir=/var/lib/mysql-xtra-inc 2>&1
if [ $? -gt 0 ]; then
echo "Error in inc apply stage" >&2
fi
/usr/bin/rsync -vaH --delete -f "+ */" -f "+ */**" -f "- *" /var/lib/mysql-xtra-inc/ /var/lib/mysql-xtra
rm -rf /var/lib/mysql-xtra-inc
echo INC backup stage finished at `date` code $?
fi
return 0
Perhaps we take out the function of the compression separately. For what? - Feng Shui. The client’s mood is unknown, and what kind of compression he wants. We give him the right to choose the desired type.
# Compression function plus latest copy
SUFFIX=""
compression () {
if [ "$SEPTABLE" = "yes" ]; then
TBDIR=`/usr/bin/dirname $2`
TFNAME=`/bin/basename $2`
if [ "$COMP" = "gzip" ]; then
TPWD=`pwd`
cd $TBDIR
tar -czvf "$1.tgz" ${TFNAME}*.sql 2>&1
cd $TPWD
SUFFIX=".tgz"
elif [ "$COMP" = "bzip2" ]; then
TPWD=`pwd`
cd $TBDIR
tar -cjvf "$1.tbz2" ${TFNAME}*.sql 2>&1
cd $TPWD
SUFFIX=".tbz2"
fi
rm -f ${2}*.sql
else
if [ "$COMP" = "gzip" ]; then
gzip -f "$1"
echo
echo Backup Information for "$1"
gzip -l "$1.gz"
SUFFIX=".gz"
elif [ "$COMP" = "bzip2" ]; then
echo Compression information for "$1.bz2"
bzip2 -f -v $1 2>&1
SUFFIX=".bz2"
else
echo "No compression option set, check advanced settings"
fi
fi
if [ "$LATEST" = "yes" ]; then
cp $1$SUFFIX "$BACKUPDIR/latest/"
fi
return 0
}
# Compression function plus latest copy
SUFFIX=""
compression_h () {
if [ "$COMP" = "gzip" ]; then
TPWD=`pwd`
cd /var/lib/mysql.backup
tar -czvf "$1.tgz" . 2>&1
cd $TPWD
SUFFIX=".tgz"
elif [ "$COMP" = "bzip2" ]; then
TPWD=`pwd`
cd /var/lib/mysql.backup
tar -cjvf "$1.tbz2" . 2>&1
cd $TPWD
SUFFIX=".tbz2"
else
echo "No compression option set, check advanced settings"
fi
if [ "$LATEST" = "yes" ]; then
cp $1$SUFFIX_H "$BACKUPDIR/latest/"
fi
return 0
}
We describe the rotation. Again, storage requirements are different.
## rotates monthly backups, set 'keep' to the last n backups to keep
rotateMonthly () {
mdbdir="$1"
## set to the number of monthly backups to keep
keep=$BACKUP_MONTH
(cd ${mdbdir}
totalFilesCount=`/bin/ls -1 | wc -l`
if [ ${totalFilesCount} -gt ${keep} ]; then
purgeFilesCount=`expr ${totalFilesCount} - ${keep}`
purgeFilesList=`/bin/ls -1tr | head -${purgeFilesCount}`
echo ""
echo "Rotating monthly: Purging in ${mdbdir}"
rm -fv ${purgeFilesList} | sed -e 's/^//g'
fi
)
}
It may be necessary to run a command before starting backup. Suspend the service, show users the message about the work. Report to search engines and dump a huge base. We will provide such an opportunity.
# Run command before we begin
if [ "$PREBACKUP" ]
then
echo ======================================================================
echo "Prebackup command output."
echo
eval $PREBACKUP
echo
echo ======================================================================
echo
fi
Sometimes not all tables need to be backed up.
# Add --ignore-table options to $OPT
if [ -n "$TABLEEXCLUDE" ]; then
for table in $TABLEEXCLUDE ; do
OPT="${OPT} --ignore-table=${table}"
done
fi
I think this part does not need a description
if [ "$SEPDIR" = "yes" ]; then # Check if CREATE DATABSE should be included in Dump
if [ "$CREATE_DATABASE" = "no" ]; then
OPT="$OPT --no-create-db"
else
OPT="$OPT --databases"
fi
else
OPT="$OPT --databases"
fi
# Hostname for LOG information
if [ "$DBHOST" = "localhost" ]; then
HOST=`hostname`
if [ "$SOCKET" ]; then
OPT="$OPT --socket=$SOCKET"
fi
else
HOST=$DBHOST
fi
We read all the databases and remove exclusions from the list for backup
# 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
MDBNAMES=$DBNAMES
fi
It's time to start backup itself
echo ======================================================================
echo AutoMySQLBackup VER $VER
echo http://sourceforge.net/projects/automysqlbackup/
echo
echo Backup of Database Server - $HOST
echo ======================================================================
Do we need dumps? Let's get to the dumps
if [ "$DO_SQL_DUMP" = "yes" ]; then
echo Backup Start Time `date`
echo ======================================================================
It is also convenient to separate temporary backup types by feng shui, well, what is the first number today? Let's make a monthly dump.
# Monthly Full Backup of all Databases
if [ $DOM = "01" ]; then
for MDB in $MDBNAMES
do
# Prepare $DB for using
MDB="`echo $MDB | sed 's/%/ /g'`"
if [ ! -e "$BACKUPDIR/monthly/$MDB" ] # Check Monthly DB Directory exists.
then
mkdir -p "$BACKUPDIR/monthly/$MDB"
fi
echo Monthly Backup of $MDB...
dbdump "$MDB" "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql" "$BACKUPDIR/monthly/$MDB/${MDB}"
compression "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql" "$BACKUPDIR/monthly/$MDB/${MDB}"
BACKUPFILES="$BACKUPFILES $BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql$SUFFIX"
echo ----------------------------------------------------------------------
TTT=`expr 33 \* $BACKUP_MONTH`
/usr/bin/find "$BACKUPDIR/monthly/$MDB" -name "*.sql.*" -mtime +$TTT -type f -delete
done
Not the first day of the month? - proceed to the daily-daily backup. I suppose that a line-by-line description is not required.
else
for DB in $DBNAMES
do
# Prepare $DB for using
DB="`echo $DB | sed 's/%/ /g'`"
# Create Seperate directory for each DB
if [ ! -e "$BACKUPDIR/daily/$DB" ] # Check Daily DB Directory exists.
then
mkdir -p "$BACKUPDIR/daily/$DB"
fi
if [ $BACKUP_DAYS -le 7 ]; then
if [ ! -e "$BACKUPDIR/weekly/$DB" ] # Check Weekly DB Directory exists.
then
mkdir -p "$BACKUPDIR/weekly/$DB"
fi
# Weekly Backup
fi
if [ $DNOW = $DOWEEKLY -a $BACKUP_DAYS -le 7 ]; then
echo Weekly Backup of Database \( $DB \)
echo Rotating 5 weeks Backups...
if [ "$W" -le 05 ];then
REMW=`expr 48 + $W`
elif [ "$W" -lt 15 ];then
REMW=0`expr $W - 5`
else
REMW=`expr $W - 5`
fi
eval rm -fv "$BACKUPDIR/weekly/$DB/${DB}_week.$REMW.*"
echo
dbdump "$DB" "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql" "$BACKUPDIR/weekly/$DB/${DB}"
compression "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql" "$BACKUPDIR/weekly/$DB/${DB}"
BACKUPFILES="$BACKUPFILES $BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql$SUFFIX"
echo ----------------------------------------------------------------------
# Daily Backup
else
echo Daily Backup of Database \( $DB \)
echo Rotating last weeks Backup...
/usr/bin/find "$BACKUPDIR/daily/$DB" -name "*.sql.*" -mtime +$BACKUP_DAYS -delete
echo
dbdump "$DB" "$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql" "$BACKUPDIR/daily/$DB/${DB}"
compression "$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql" "$BACKUPDIR/daily/$DB/${DB}"
BACKUPFILES="$BACKUPFILES $BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql$SUFFIX"
echo ----------------------------------------------------------------------
fi
done
fi
echo Backup End `date`
echo ======================================================================
fi
Do we need a HOT BACKUP? Proceed:
#### HOT BACKUP
if [ "$DO_HOT_BACKUP" = "yes" ]; then
echo HOT Backup Start `date`
echo ======================================================================
# Monthly HOT Full Backup of all Databases
if [ $DOM = "01" ]; then
echo Monthly full Backup of \( $MDBNAMES \)...
dbdump_h "$MDBNAMES" "$BACKUPDIR/monthly/$DATE.$M.all-databases.sql"
### compression_h "$BACKUPDIR/monthly/$DATE.$M.all-databases.sql"
BACKUPFILES="$BACKUPFILES $BACKUPDIR/monthly/$DATE.$M.all-databases.sql$SUFFIX"
echo ----------------------------------------------------------------------
### TTT=`expr 33 \* $BACKUP_MONTH`
### /usr/bin/find "$BACKUPDIR/monthly/" -name "*.all-databases.sql.*" -mtime +$TTT -type f -delete
else
# Weekly Backup
if [ $DNOW = $DOWEEKLY -a $BACKUP_DAYS -le 7 ]; then
echo Weekly Backup of Databases \( $DBNAMES \)
echo
echo Rotating 5 weeks Backups...
if [ "$W" -le 05 ];then
REMW=`expr 48 + $W`
elif [ "$W" -lt 15 ];then
REMW=0`expr $W - 5`
else
REMW=`expr $W - 5`
fi
eval rm -fv "$BACKUPDIR/weekly/week.$REMW.*"
echo
dbdump_h "$DBNAMES" "$BACKUPDIR/weekly/week.$W.$DATE.sql"
### compression_h "$BACKUPDIR/weekly/week.$W.$DATE.sql"
BACKUPFILES="$BACKUPFILES $BACKUPDIR/weekly/week.$W.$DATE.sql$SUFFIX"
echo ----------------------------------------------------------------------
# Daily Backup
else
echo Daily Backup of Databases \( $DBNAMES \)
echo
echo Rotating last weeks Backup...
/usr/bin/find "$BACKUPDIR/daily/$DB" -name "*.sql.*" -mtime +$BACKUP_DAYS -delete
echo
dbdump_h "$DBNAMES" "$BACKUPDIR/daily/$DATE.$DOW.sql"
### compression_h "$BACKUPDIR/daily/$DATE.$DOW.sql"
BACKUPFILES="$BACKUPFILES $BACKUPDIR/daily/$DATE.$DOW.sql$SUFFIX"
echo ----------------------------------------------------------------------
fi
fi
echo Backup End Time `date`
echo ======================================================================
fi
HOT XTRA BACKUP? - make it:
#### HOT XTRA BACKUP
if [ "$HOT_XTRA_BACKUP" = "yes" ]; then
dbdump_h_xtra
fi
And the last option is mydumper:
#### DO_MYDUMPER_BACKUP
if [ "$DO_MYDUMPER_BACKUP" = "yes" ]; then
# Monthly Full Backup of all Databases
BACKUPDIRM=$BACKUPDIR/daily/
if [ $DOM = "01" ]; then
BACKUPDIRM=$BACKUPDIR/monthly/
else
if [ $DNOW = $DOWEEKLY -a $BACKUP_DAYS -le 7 ]; then
BACKUPDIRM=$BACKUPDIR/weekly/
fi
fi
BACKUPDIRM=${BACKUPDIRM}_mydumper-`date +%F_%R`
dbdump_mydumper "$BACKUPDIRM"
OPT="$OPT --no-data"
for DB in $DBNAMES
do
# Prepare $DB for using
DB="`echo $DB | sed 's/%/ /g'`"
dbdump "$DB" "$BACKUPDIRM/$DB-schema.sql"
compression "$BACKUPDIRM/$DB-schema.sql"
done
fi
Let's count how many dumps take, the place has a habit of ending.
echo Total disk space used for backup storage..
echo Size - Location
echo `du -hs "$BACKUPDIR"`
echo
Sometimes it is necessary to execute a command, after backup, for example, re-open the site. We provide such an opportunity:
# Run command when we're done
if [ "$POSTBACKUP" ]
then
echo ======================================================================
echo "Postbackup command output."
echo
eval $POSTBACKUP
echo
echo ======================================================================
fi
Let's analyze backup log
#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.
Yes, we know, thanks. Let's remove this from our log.
sed -i '/Using a password on the command line interface can be insecure/d' $LOGERR
Further, we have several options: send a log of the script, send a log and dump files, show output to the console (debugging, where without it), send a log to the mail and if there are errors.
Perhaps in default we choose the last option. But we will describe everything.
if [ "$MAILCONTENT" = "files" ]
then
if [ -s "$LOGERR" ]
then
# Include error log if is larger than zero.
BACKUPFILES="$BACKUPFILES $LOGERR"
ERRORNOTE="WARNING: Error Reported - "
fi
#Get backup size
ATTSIZE=`du -c $BACKUPFILES | grep "[[:digit:][:space:]]total$" |sed s/\s*total//`
if [ $MAXATTSIZE -ge $ATTSIZE ]
then
BACKUPFILES=`echo "$BACKUPFILES" | sed -e "s# # -a #g"` #enable multiple attachments
mutt -s "$ERRORNOTE MySQL Backup Log and SQL Files for $HOST - $DATE" $BACKUPFILES $MAILADDR < $LOGFILE #send via mutt
else
cat "$LOGFILE" | mail -s "WARNING! - MySQL Backup exceeds set maximum attachment size on $HOST - $DATE" $MAILADDR
fi
elif [ "$MAILCONTENT" = "log" ]
then
cat "$LOGFILE" | mail -s "MySQL Backup Log for $HOST - $DATE" $MAILADDR
if [ -s "$LOGERR" ]
then
cat "$LOGERR" | mail -s "ERRORS REPORTED: MySQL Backup error Log for $HOST - $DATE" $MAILADDR
fi
elif [ "$MAILCONTENT" = "quiet" ]
then
if [ -s "$LOGERR" ]
then
cat "$LOGERR" | mail -s "ERRORS REPORTED: MySQL Backup error Log for $HOST - $DATE" $MAILADDR
cat "$LOGFILE" | mail -s "MySQL Backup Log for $HOST - $DATE" $MAILADDR
fi
else
if [ -s "$LOGERR" ]
then
cat "$LOGFILE"
echo
echo "###### WARNING ######"
echo "Errors reported during AutoMySQLBackup execution.. Backup failed"
echo "Error log below.."
cat "$LOGERR"
else
cat "$LOGFILE"
fi
fi
Let's clean it up for ourselves:
if [ -s "$LOGERR" ]
then
STATUS=1
else
STATUS=0
fi
# Clean up Logfile
eval rm -f "$LOGFILE"
eval rm -f "$LOGERR"
exit $STATUS
Now I will give the config file for the options:
# Username to access the MySQL server e.g. dbuser
USERNAME=root
# Username to access the MySQL server e.g. password
if [ -f "/root/.mysql" ]; then
PASSWORD=`cat /root/.mysql`
else
exit 0
fi
# 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"
# Backup directory location e.g /backups
BACKUPDIR="/var/backups/mysql"
# Mail setup
# What would you like to be mailed to you?
# - log : send only log file
# - files : send log file and sql files as attachments (see docs)
# - stdout : will simply output the log to the screen if run manually.
# - quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT="quiet"
# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE="4000"
# Email Address to send mail to? (user@domain.com)
MAILADDR="root"
# ============================================================
# === ADVANCED OPTIONS ( Read the doc's below for details )===
#=============================================================
# List of DBBNAMES for Monthly Backups.
MDBNAMES="mysql $DBNAMES"
# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)
DBEXCLUDE="information_schema performance_schema "
# List of tables to exclude from the backup (in form db.table)
TABLEEXCLUDE=""
# Include CREATE DATABASE in backup?
CREATE_DATABASE=no
# Separate backup directory and file for each DB? (yes or no)
SEPDIR=yes
# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
DOWEEKLY=0
# How many days keep backup
BACKUP_DAYS=3
# Choose Compression type. (gzip or bzip2)
COMP=gzip
# Compress communications between backup server and MySQL server?
COMMCOMP=no
# Additionally keep a copy of the most recent backup in a seperate directory.
LATEST=no
# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)
MAX_ALLOWED_PACKET=1GB
# For connections to localhost. Sometimes the Unix socket file must be specified.
OS=`uname`
if [ "$OS" = "FreeBSD" ]; then
SOCKET=/tmp/mysql.sock
else
SOCKET=/var/lib/mysql/mysql.sock
fi
# Command to run before backups (uncomment to use)
#PREBACKUP="/etc/mysql-backup-pre"
# Command run after backups (uncomment to use)
#POSTBACKUP="/etc/mysql-backup-post"
# (если не описано, по-умолчанию так и делать)
DO_SQL_DUMP=yes
# separate table to file ?
SEPTABLE=no
# (если не описано, по-умолчанию не делать)
DO_HOT_BACKUP=no
HOT_XTRA_BACKUP=no
DO_MYDUMPER_BACKUP=no
We did not set the goal of "discovering America", but the backup topic does not cease to be relevant. And it often happens that when adding a line to the database dump script, we forget to check for errors in the dump process. I hope this article will remind you of the need to once again check for backups. Have you checked your backup?
Author: Igor Medynsky