Database backup - is it there?

There is no point in talking about how relevant this is. Today we will tell you how we have organized a backup of mysql databases.
And one of the most important is the test, but is the backup made? Did you successfully dump? 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 I’ll try to tell you how we do it when checking for errors when performing backup.
')
At once I will make a reservation that, possibly, in principle, standard schemes have been used, but perhaps you will find for yourself something new that you can implement in yourself.

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 eg 2002-09-21 DOW=`date +%A` # Day of the week eg Monday DNOW=`date +%u` # Day number of the week 1 to 7 where 1 represents Monday DOM=`date +%d` # Date of the Month eg 27 M=`date +%B` # Month eg January W=`date +%V` # Week Number eg 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 configuration file. Often there is a need not only in the global settings, but also in the specific for a particular project. We decided to split it into several files.

mysql-backup.conf.dist - the file stores global settings common to all projects

mysql-backup.conf - file for specific settings for the project

mysql-backup.local.conf - file for local backup settings

Check if there is a config - we will make a 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 should all speak for themselves.

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 make 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. Since for different projects you need a different backup, for whom it is incremental, for whom users sleep at night - just a dump of the night.

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 the function of compression will be taken out separately. For what? - Feng Shui. Unknown customer mood, and what he wants compression. 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, the requirements for the shelf life 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 execute a command before starting a backup. Suspend the service, show users a message about the work. Report search engines and dump a huge database. Provide this 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 backup.

 # 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 needs no 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 will read all the databases and remove the exceptions from the backup 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 the backup yourself

 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 down to the dumps

 if [ "$DO_SQL_DUMP" = "yes" ]; then echo Backup Start Time `date` echo ====================================================================== 

It is also convenient to split temporary types of backup, so what’s 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 backup. I suppose that 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 HOT BACKUP? Let's start:

 #### 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? - we will 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 

Calculate how many dumps take, the place has a habit of running out.

 echo Total disk space used for backup storage.. echo Size - Location echo `du -hs "$BACKUPDIR"` echo 

Sometimes you need to run a command after backup, for example, re-open the site. Provide this opportunity:

 # Run command when we're done if [ "$POSTBACKUP" ] then echo ====================================================================== echo "Postbackup command output." echo eval $POSTBACKUP echo echo ====================================================================== fi 

Let's start the backup log analysis

 #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. Remove it from our log.

 sed -i '/Using a password on the command line interface can be insecure/d' $LOGERR 

Next, we have several options: send a script log, send a log and dump files, show the output to the console (debugging, where without it), send a log by 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 

We clean 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 options:

 # Username to access the MySQL server eg dbuser USERNAME=root # Username to access the MySQL server eg password if [ -f "/root/.mysql" ]; then PASSWORD=`cat /root/.mysql` else exit 0 fi # Host name (or IP address) of MySQL server eg localhost DBHOST=localhost # List of DBNAMES for Daily/Weekly Backup eg "DB1 DB2 DB3" DBNAMES="all" # Backup directory location eg /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. eg 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 aim to “discover America”, but the topic of backups does not cease to be relevant. And it often happens that by 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. Did you check your backup?



Author: Igor Medynsky