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