The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Best way to backup MySQL database then rsync

Discussion in 'Data Protection' started by CBG UK, Dec 25, 2010.

  1. CBG UK

    CBG UK Registered

    Joined:
    Jun 15, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    UK
    Hi,

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

    Thanks,
    CBG
     
  2. nace

    nace Registered

    Joined:
    Jun 7, 2005
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Cron + script
    1. create /backup directory
    2. create startbackup.sh script file:
    PHP:

    rm 
    -/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 -$FILE
    done
     
  3. CBG UK

    CBG UK Registered

    Joined:
    Jun 15, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    UK
    Thank you :)
     
  4. twhiting9275

    twhiting9275 Well-Known Member

    Joined:
    Sep 26, 2002
    Messages:
    538
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.

    Code:
    #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 $BACKUPUSER@$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.
     
Loading...

Share This Page