Best way to backup MySQL database then rsync

[CBG UK]

Hi,

What is the best way to backup a MySQL database then rsync it off.

Thanks,
CBG

 

[nace]

Cron + script
1. create /backup directory
2. create startbackup.sh script file:

rm -f /backup/mysql*
### System Setup ###
BACKUP=/backup
NOW=$(date +"%Y-%m-%d")

### MySQL Setup ###
MUSER="mysqluser"
MPASS="password"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"


### Start MySQL Backup ###
# Get all databases name
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
FILE=$BACKUP/mysql-$db.$NOW-$(date +"%T").gz
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

 

[CBG UK]

Thank you

 

[twhiting9275]

cPanel backups are adequate for SQL, but sometimes you want just a bit more. For example, you really don't want just 24h backup, you want a few day's backup, JUST in case something screws up. This is a much, much more in depth system than previously supplied, but it will do much more and you'll find that IF you need a mysql backup, it'll be something that's not exactly from today.

#edit variables here
BACKUP="Backup Directory Here"
DAYS="DAYSTOKEEPBACKUP"
MYEMAIL="YOUREMAILADDRESS"
SQLP="YOURSQLPASSWORD"
BACKUPUSER="backupuser"
BACKUPHOST="backuphost"
BACKUPPATH="path for the backups on the backup server"
#no need to edit anything after here
DATE=`date +%m%d%y`
THISNEWDATE=`date +%m%d%y-%T`
THISMONTH=`date +%b-%y`
BACKMONTH=$BACKUP/$THISMONTH
BACKDEST=$BACKMONTH/$DATE
FIND=`which find`
SSH=`which ssh`
MAILFILE="/tmp/mail.txt"
SUBJ="$HOSTNAME archive [$DATE]";
echo "$HOSTNAME Archive $BACKDATE log:" >> $MAILFILE
echo "Started: $THISNEWDATE" >> $MAILFILE

if [ ! -d $BACKMONTH ];then
echo "CREATING $BACKMONTH" >> $MAILFILE
mkdir $BACKMONTH
fi

if [ $BACKDEST ];then
rm -rf $BACKDEST
echo "$BACKDEST already existed, removing!" >> $MAILFILE
fi

if [ ! -d $BACKDEST ];then
echo "Creating $BACKDEST" >> $MAILFILE
mkdir $BACKDEST
fi

echo "Directories Removed: " >> $MAILFILE
find $BACKUP/* -maxdepth 3 -type d -mtime $DAYS >> /tmp/tempfile

#the /* is necessary to retain backup directory
find $BACKUP/* -maxdepth 3 -type d -mtime $DAYS -exec rm -r {} \;

echo "Backup Logs: " >> $MAILFILE
echo "=====================================================================" >> $MAILFILE

for var in `find /var/lib/mysql/ -type d | \
sed -e "s/\/var\/lib\/mysql\///"`; do
mysqldump -p$SQLP --log-error=$MAILFILE  --add-drop-table --single-transaction $var >> $BACKDEST/$var.sql
done
echo "=====================================================================" >> $MAILFILE

THISENDATE=`date +%m%d%y-%T`
echo "SQL Backup Ended : $THISENDATE">> $MAILFILE
rsync -azv -e$SSH --delete $BACKUP [email protected]$BACKUPHOST:/$BACKUPPATH
mailx -s "$SUBJ" $MYEMAIL < $MAILFILE
rm -rf $MAILFILE

This will
A> Store sql backups
B> Delete sql backups when time comes (I have mine set for 5 days)
C> Synchronize backups to your backup server (incl. deleting the ones not on the existing server)
D> Email you when complete, with a log of everything done.