Home > AWS, bash, CentOS, Fedora 15 64 bit, Fedora 17 64 bit, MySQL, Uncategorized > MySQL backup and cleanup bash scripts with mydumper

MySQL backup and cleanup bash scripts with mydumper

1. Backup script

#!/bin/sh
# Fri Jun 27 10:44:49 2014
# done by dragkh
# usage: 
# cat /etc/cron.d/backupmysql 
# 0  3  *  *  *       root    /root/bin/clean.backup.hyperion.mysql.mydumper.daily.sh >>  /var/log/clean.backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1
# 35  3  *  *  *       root    /root/bin/backup.hyperion.mysql.mydumper.daily.sh >> /var/log/backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1

ROOT_BACKUP_DIR="/home/mydumper"

seik_date () {
if [ -z $1 ]
then
# cdate=`date +%Y-%m-%d\ %H:%M:%S\ %Z`; export cdate; echo $cdate
cdate=`date -R`; export cdate; echo $cdate
else

if [ -z ${2} ]
then
cdate=`date +%Y-%m-%d.%H.%M.%S`; export cdate; echo $cdate
else
cdate=`date "+%Y-%m-%d %H:%M:%S"`; export cdate; echo $cdate
fi

fi
}


function check_dir {
 test ! -d "${1}" && mkdir -p "${1}"
}


function set_cpu_threads {
    # set the threads one less than the existing 
    threads=$(cat /proc/cpuinfo  |  grep processor | tail -1 | awk '{print $3}')
    test $threads -lt 1 && threads=1
}

function dump_schema {
    mysqldump -d --dump-date --all-databases > ${DATA_DIR}/${HOSTNAME}.only.sql
}


function dump_data {
    echo "$(seik_date f) : executing : mydumper -o $DATA_DIR --long-query-guard 120 -r 100000 -c -e -m -L ${DATA_DIR}/mysql-backup.log -t ${threads} -v 3"
    mydumper -o $DATA_DIR --long-query-guard 120 -r 100000 -c -e -m -L ${DATA_DIR}/mysql-backup.log -t ${threads} -v 3
}

DATA_DIR="${ROOT_BACKUP_DIR}/$(seik_date d)"
check_dir "${DATA_DIR}" && echo "$(seik_date f) : ${DATA_DIR} is missing, creating it now .."
set_cpu_threads
echo "$(seik_date f) : star dumping the schema at ${DATA_DIR}.."
dump_schema && echo "$(seik_date f) : end dumping the schema at ${DATA_DIR} .."
echo "$(seik_date f) : start dumping the data at ${DATA_DIR} via ${threads} parallel threads .."
dump_data && echo "$(seik_date f) : end dumping the data at ${DATA_DIR} via ${threads} parallel threads .."

2. Clean up script keeping always backup directories intact

#!/bin/bash
# Sat Jun 28 03:16:38 EEST 2014
# done by dragkh
# usage: 
# cat /etc/cron.d/backupmysql
# 0       3       *       *       *       root    /root/bin/clean.backup.hyperion.mysql.mydumper.daily.sh >> /var/log/clean.backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1
# 35      3      *       *       *       root    /root/bin/backup.hyperion.mysql.mydumper.daily.sh >> /var/log/backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1

ROOT_BACKUP_DIR="/home/mydumper"

seik_date () {
if [ -z $1 ]
then
cdate=`date -R`; export cdate; echo $cdate
else

if [ -z ${2} ]
then
cdate=`date +%Y-%m-%d.%H.%M.%S`; export cdate; echo $cdate
else
cdate=`date "+%Y-%m-%d %H:%M:%S"`; export cdate; echo $cdate
fi

fi
}

day_limit=7;  
ls -t ${ROOT_BACKUP_DIR} | \
while read dir
do 
    ((dir_num++))
    test $dir_num -gt $day_limit && test -d "${ROOT_BACKUP_DIR}/${dir}" &&  rm -rf "${dir}" && echo "$(seik_date d) : removed [${dir_num}]::[${dir}]" && continue 
    test -d "${ROOT_BACKUP_DIR}/${dir}" && echo "$(seik_date d) : skipping [${dir_num}]::[${dir}]"
done

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: