You can edit the script below in accordance with the needs in your infrastructure for having any of the databases nested in your MySQL server to be backuped and copied to a preferred location. You may define this script inside the crontab and install it to make these backup operations performed periodically. If you’re in a search of a central backup solution to store, manage and have these backups reported regularly in same or in a different server at remote locations, I recommend you to read the article about my project named Automatic Backup And Synchronization Solution with Rsync On FreeBSD.

While performing backups, our script compresses MYSQL databases whose names and total numbers have been listed and also removes previous backup files older than the day specified. Our script contains an alternative username & password control statement (:31) for “mysqldump” command which is the first of the piped command groups that we can’t see a STDERR redirection in the line (:71) that backup takes place. It also initiates a rotation for the log files which has exceeded the size given in MB. and removes the log files older than the day designated.

#!/usr/local/bin/bash
# Sezgin Bayrak (sbayrak@ipsure.com)
# http://www.ipsure.com (2010)
#
# db-backup.sh; Processes MySQL database(s) backups and logs
#
MyUSER="root"           # USERNAME
MyPASS="password"       # PASSWORD
MyHOST="localhost"      # Hostname
COUNT=0                 # Counter reset. Do not edit.
###################
# Local Databases #
###################
DB[1]="company"                 # Database 1
DB[2]="dblife"                  # Database 2
DB[3]="dbmatch"                 # Database 3
DB[4]="facebook"                # Database 4
DB[5]="mail"                    # Database 5
DB[6]="intrapub"                # Database 6
DB[7]="pureftp"                 # Database 7
DB[8]="phplist"                 # Database 8
DBN=8                           # Total number of databases
LOGDATE=`date +"%d.%m.%Y %H:%M"`
GZIP="gzip"
MBD="/space/mysql-backups"      # Backup path and folder
LOGFILE="backup.log"
MDAY=15                         # Max. day that backups will be kept on disk
LSZ=5                           # Max. size of logfile in MB. before initiation of rotation
LDAY=21                         # Max. day that rotated logs will be kept on disk
# Checking MySQL authentication
DBCONN=$(mysql -u $MyUSER --password=$MyPASS -e "show databases;" | grep "mysql")
if [[ $DBCONN != "mysql" ]]
 then
 echo "MySQL DB connection can not not be established. Check you username and password and try again."
 exit
fi
# Checking if backup directory exists. If it doesn't, we'll create it.
if  [ -e $MBD ]
then
 echo "Backup directory already exists"
else
 mkdir $MBD
fi
# Checking if logfile exists. If it doesn't, we'll create it.
if  [ -e $MBD/$LOGFILE ]
then
 echo "$LOGFILE is with us"
else
 touch $MBD/$LOGFILE
fi
# Checking if mysqldump exists
mysqldump --help > /dev/null 2>&1
if [ $? -ne 0 ]; then
 echo "$LOGDATE Error: mysqldump can not be located" >> $MBD/$LOGFILE;
 exit 1;
fi;
#
# Checking if GZIP exists
$GZIP --help > /dev/null 2>&1
if [ $? -eq 127 ]; then
 echo "$LOGDATE Error: $GZIP can not be located" >> $MBD/$LOGFILE;
 exit 1;
fi;
##################
# Backup process #
##################
for (( c=1; c<=$DBN; c++ ))
do
FNUM=$c
 FILENAME="${DB[$c]}-backup-`date +%Y.%m.%d.%H.%M`.sql.gz"
 mysqldump -u $MyUSER -h $MyHOST --password=$MyPASS ${DB[$c]} | $GZIP -f -9 > $MBD/$FILENAME
 if [ $? -eq 0 ]
 then
 COUNT=$(($COUNT+1))
 echo "$LOGDATE Backup process completed succesfully for $COUNT. of $DBN databases: $FILENAME" >> $MBD/$LOGFILE
 # Removing backups older than $MDAY
 rm -f `find $MBD/ -type f -mtime +$MDAY -name "*-backup*.gz"`
 else
 echo "$LOGDATE An error occured while processing database backup: $?" >> $MBD/$LOGFILE
 fi
done
if [ $COUNT -eq $FNUM ]
 then
 echo "All backup processes for total of $FNUM databases were completed successfully"
 else
 if [ $COUNT -eq 0 ]
 then
 echo "None of databases were processed as expected, please inspect $MBD/$LOGFILE for further details"
 else
 echo "Backup processes were completed for $COUNT of $FNUM databases, please inspect $MBD/$LOGFILE for further details"
 fi
fi
################
# Log rotation #
################
RLOG="$LOGFILE-`date +%Y.%m.%d.%H.%M`.rotated.bak"
if [ $(du -m $MBD/$LOGFILE | awk '{ print $1 }') -ge $LSZ ]
 then
 echo "Logfile size is greater than $LSZ MB., rotating... "
 mv $MBD/$LOGFILE $MBD/$RLOG
 touch $MBD/$LOGFILE
 rm -f `find $MBD/ -type f -mtime +$LDAY -name "*.rotated.bak"`
 else
 echo " " >> $MBD/$LOGFILE
fi
exit

If any table among existing databases generates “mysqldump: Got error: 145: Table ‘./dbname/tablename’ is marked as crashed and should be repaired when using LOCK TABLES” error while taking backups by using “mysqldump” command inside your scripts or by using it directly at a command line interface, relevant database won’t be processed. To fix the situation, you can use the command below;

# mysqlcheck -u root -p –repair dbname tablename
Enter password: ********

dbname.tablename OK

Related Posts

  No related posts.