Tran Nghi's Site  - Make notes and share experience

Share bash script to perform backup mysqldump from remote server

This post is also available in: English

#! /bin/bash
 
BK_TIME=`date +\%Y%m%d%H%M`
FILE_DATE=`date +"%Y%m%d%H%M"`.sql.gz
HOST=xxx.xxx.xxx.xxx
PORT=3306
DATABASES="database1 database2 database3"
USER='noname'
PASS='someth1ng'
BACKUPFOLDER=/home/backups/databases
NUMBER_OF_BKDAYS=+182
 
for DB in $DATABASES
do
    printf "\nPreparing to perform mysqldump backup for %s\n" $DB
    BK_DIR=$BACKUPFOLDER/$DB
 
    if [ ! -e $BK_DIR ]; then
        echo "Dir" $BK_DIR "is not exited. Create new dir..."
        mkdir $BK_DIR
        chown root:users $BK_DIR
        chmod 755 $BK_DIR
    fi
 
    NUMBER_EXISTING_BKFILE=`find $BK_DIR -name $DB* -type f | wc -l`
    NUMBER_EXISTING_BKFILE_OVERTHAN6MONTHS=`find $BK_DIR -name $DB* -mtime $NUMBER_OF_BKDAYS -type f | wc -l`
    printf "Number of existing backups: %02d ; to be removed: %02d backups that is over than 06 months\n" $NUMBER_EXISTING_BKFILE $NUMBER_EXISTING_BKFILE_OVERTHAN6MONTHS
 
    printf "Performing backup %s --> %s.sql.gz\n" $DB $DB
 
 
    #Below command will perform mysqldump backup for the database in DATABASES
    #--single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes.
    #                     Those incoming changes do not become part of the dump.
    #                     That ensures the same point-in-time for all tables.
    #--routines dumps all stored procedures and stored functions
    #--triggers dumps all triggers for each table that has them
 
   #mysqldump -h${HOST} --port=${PORT} --user=${USER} --password=${PASS} --single-transaction ${DB} | gzip -c  > ${BACKUPFOLDER}/${DB}/${DB}_${FILE_DATE}
 
    if [ $NUMBER_EXISTING_BKFILE_OVERTHAN6MONTHS -ge 1 ]; then
    #Below command will find the old backups with over than 182 days (06 months), then perform delete
       for i in $( find $BK_DIR -name $DB* -mtime $NUMBER_OF_BKDAYS -type f )
       do
           printf "Performing delete "; find $BK_DIR -name $DB* -mtime $NUMBER_OF_BKDAYS -type f -print -quit; # -delete
       done
    fi
 
done
 
#To restore from a *.gz mysqldump file: gunzip < database_filename.gz | mysql --host=localhost --port=3306 --user=noname --password=someth1ng
 
#Below command will make rsync the source from '[email protected]:/media/' to internal server \\192.168.1.254
#This source I already have another crontab to directly perform a mysqldump backup on server.
rsync -avzh -e 'ssh -i .ssh/id_rsa.pem' '[email protected]:/media/' /home/backups/ > /home/backups/bk_logs/media_backup_${BK_TIME}.log
 
rsync -avzh -e 'ssh -i .ssh/id_rsa.pem' '[email protected]:/root' /home/backups/ > /home/backups/bk_logs/root_backup_${BK_TIME}.log
Message