ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql备份脚本

mysql备份脚本

原创 Linux操作系统 作者:wang_0720 时间:2013-11-06 14:31:31 0 删除 编辑
随着数据量的增大,mysql每次dump全备的数据都很大,时间也很长。基于这个原因,利用mysqlbinlog进行增量备份是个节省时间和空间的好方法。
    下面这个脚本是在mysql slave上运行的,slave开启log-bin和log-slave-updates选项,用来记录slave的日志。每周日一次全备,周一,二,四,五,六增量备份,周三差异备份
cat mysqlbackup.sh
#!/bin/bash
#Author: Andy
#Time: 20130906
#for mysql backup,full and increment
#file:./mysqlbackup.sh
DATE=`date +%Y%m%d%H%M%S`
WEEK=`date +%w`
BACK_DIR=/home/mysqlback
FILE_DIR=/home/poslog
DATA_DIR=/var/lib/mysql
DATABASE=test
HOST=localhost
USER=root
PASSWORD=123456
export PATH=$PATH:/usr/local/mysql/bin

if [ ! -d ${BACK_DIR} ];then
        mkdir -p ${BACK_DIR}
fi
if [ ! -d ${FILE_DIR} ];then
        mkdir -p ${FILE_DIR}
fi
echo "${DATE}" >/var/log/mysqlbackup.log
#==============full backup=================
full ()
{
mysql -h ${HOST} \
      -u ${USER} \
      -p${PASSWORD} \
      -D ${DATABASE} \
      -e "FLUSH TABLES WITH READ LOCK"
mysqldump -h ${HOST} \
          -u ${USER} \
          -p${PASSWORD} \
          --default-character-set=utf8 \
          --opt \
          --flush-logs ${DATABASE} >${BACK_DIR}/full_${DATE}.sql
#get position
mysql -h ${HOST} \
      -u ${USER} \
      -p${PASSWORD} \
      -e "show master status \G"|\
      awk 'NR>1 && NR<4 {print $0}'>${FILE_DIR}/full_position
#create increment backup point
cat ${FILE_DIR}/full_position >${FILE_DIR}/incre_position
mysql -h ${HOST} \
      -u ${USER} \
      -p${PASSWORD} \
      -e "UNLOCK TABLES"
}
#=============different backup===============
diff()
{
if [ ! -f "${FILE_DIR}"/full_position ];then
    echo "${FILE_DIR}/full_position is not exist">>/var/log/mysqlbackup.log && exit 0
fi
mysql -h ${HOST} \
      -u ${USER} \
      -p${PASSWORD} \
      -e "show master status \G"|\
      awk 'NR>1 && NR<4 {print $0}'>${FILE_DIR}/current_position
#create inrement backup point
cat ${FILE_DIR}/current_position >${FILE_DIR}/incre_position
startbinlog=`awk -F ": " 'NR==1 {print $2}' ${FILE_DIR}/full_position`
startposition=`awk -F ": " 'NR==2 {print $2}' ${FILE_DIR}/full_position`
stopbinlog=`awk -F ": " 'NR==1 {print $2}' ${FILE_DIR}/current_position`
stopposition=`awk -F ": " 'NR==2 {print $2}' ${FILE_DIR}/current_position`
if [ "${startbinlog}" == "${stopbinlog}" ];then
    mysqlbinlog --start-position="${startposition}" \
                --stop-position="${stopposition}" \
                -d ${DATABASE} \
                ${DATA_DIR}/${startbinlog}>>${BACK_DIR}/diff_${DATE}.sql
    else
    startlog=`awk "/${startbinlog}/ {print NR}" ${DATA_DIR}/mysql-bin.index`
    stoplog=`wc -l ${DATA_DIR}/mysql-bin.index|awk '{print $1}'`
    for log in `seq ${startlog} ${stoplog}`;do
        binlog=`sed -n "${log}"p ${DATA_DIR}/mysql-bin.index |sed 's/.\///g'`
        case "${binlog}" in
                "${startbinlog}")
                            mysqlbinlog --start-position="${startposition}" \ 
                            -d ${DATABASE} \     
                            ${DATA_DIR}/${binlog} >>${BACK_DIR}/diff_${DATE}.sql
                            ;;
                 "${stopbinlog}")
                            mysqlbinlog --stop-position="${stopposition}" \
                            -d ${DATABASE} \
                            ${DATA_DIR}/${binlog} >>${BACK_DIR}/diff_${DATE}.sql
                            ;;
                               *)
                            mysqlbinlog -d ${DATABASE} \
                            ${DATA_DIR}/${binlog} >>${BACK_DIR}/diff_${DATE}.sql
                            ;;
        esac
    done
fi
}
#=============increment backup==================
increment()
{
if [ ! -f "${FILE_DIR}"/incre_position ];then
    echo "${FILE_DIR}/incre_position is not exist" && exit 0
fi
mysql -h ${HOST} \
      -u ${USER} \
      -p${PASSWORD} \
      -e "show master status \G"|\
      awk 'NR>1 && NR<4 {print $0}'>${FILE_DIR}/current_position
startbinlog=`awk -F ": " 'NR==1 {print $2}' ${FILE_DIR}/incre_position`
startposition=`awk -F ": " 'NR==2 {print $2}' ${FILE_DIR}/incre_position`
#create increment backup point for next
cat ${FILE_DIR}/current_position >${FILE_DIR}/incre_position
stopbinlog=`awk -F ": " 'NR==1 {print $2}' ${FILE_DIR}/current_position`
stopposition=`awk -F ": " 'NR==2 {print $2}' ${FILE_DIR}/current_position`
if [ "${startbinlog}" == "${stopbinlog}" ];then
        mysqlbinlog --start-position="${startposition}" \
                    --stop-position="${stopposition}" \
                    -d ${DATABASE} \
                    ${DATA_DIR}/${startbinlog}>>${BACK_DIR}/incre_${DATE}.sql
        else
        startlog=`awk "/${startbinlog}/ {print NR}" ${DATA_DIR}/mysql-bin.index`
        stoplog=`wc -l ${DATA_DIR}/mysql-bin.index|awk '{print $1}'`
        for log in `seq ${startlog} ${stoplog}`;do
                binlog=`sed -n "${log}"p ${DATA_DIR}/mysql-bin.index |sed 's/.\///g'`
                case "${binlog}" in
                         "${startbinlog}")
                                   mysqlbinlog --start-position="${startposition}" \
                                   -d ${DATABASE} \
                                   ${DATA_DIR}/${binlog} >>${BACK_DIR}/incre_${DATE}.sql
                                   ;;
                          "${stopbinlog}")
                                    mysqlbinlog --stop-position="${stopposition}"\
                                    -d ${DATABASE} \
                                    ${DATA_DIR}/${binlog} >>${BACK_DIR}/incre_${DATE}.sql
                                    ;;
                                        *)
                                    mysqlbinlog ${DATA_DIR}/${binlog}\
                                    -d ${DATABASE} \
                                    >>${BACK_DIR}/incre_${DATE}.sql
                                    ;;
                esac
        done
fi
}
#================================================
ls ${BACK_DIR}|grep "full_*">/dev/null 2>&1
[ $? -ne 0 ] && [ "${WEEK}" -ne 0 ] && echo "full"
case "${WEEK}" in
        0)
        full
        ;;
        3)
        diff
        ;;
        1|2|4|5|6)
        increment
        ;;
        *)
        exit 0
        ;;
esac

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27181165/viewspace-775840/,如需转载,请注明出处,否则将追究法律责任。

上一篇: shell中IFS用法
请登录后发表评论 登录
全部评论

注册时间:2013-11-05

  • 博文量
    111
  • 访问量
    909090