mysql差异备份脚本_mysql备份脚本

随着数据量的增大，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