实现mysqlldump+binlog日志全量备份和增量备份脚本
#!/bin/bash
###########################################################################################
# @programe : backup_mysql.sh
# @version : 0.0.3
# @function@ : backup of the mysql database with full and incremental
# @campany : YOUGOU
# @dep. : Operation and maintenance Team
# @writer : Huang Ling Fei
# @date : -03-12
############################################################################################
PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
export PATH
Datetime=$(date +%Y%m%d_%H%M)
Week=$(date +%u)
Log="/data/sh/logs/backupmyql.log"
Mysqldopt="-R -E -e --single-transaction --master-data=1 --flush-logs --default-character-set=utf8"
Mysqldnet="--max_allowed_packet=167772160 --net_buffer_length=163840"
Mysqldu="mysqlbak"
Mysqldp="passwd"
Mysqldfullbak="yitian_b2c_db_full.${Datetime}"
Mysqldpath="/data/backup_db"
Mysqldatapath="/data/mysql/data"
if [ ! -d /data/sh/logs ];then
mkdir -p /data/sh/logs
fi
if [ ! -d /data/backup_db ];then
mkdir -p /data/backup_db
fi
if [ ! -f /data/backup_db/startpostion ];then
touch /data/backup_db/startpostion
fi
fullbackup(){
echo Start full backup the databases++++++++$Datetime
mysql -u$Mysqldu -p$Mysqldp -e "slave stop"
mysql -u$Mysqldu -p$Mysqldp -e "set global max_allowed_packet=167772160"
mysql -u$Mysqldu -p$Mysqldp -e "set global net_buffer_length=163840"
mysqldump -u$Mysqldu -p$Mysqldp $Mysqldopt $Mysqldnet --database yitian_b2c_db >${Mysqldpath}/${Mysqldfullbak}
mysql -u$Mysqldu -p$Mysqldp -e "slave start"
################get postion###############
head -n22 ${Mysqldpath}/${Mysqldfullbak}|tail -n1|awk -F"=|'|,|;" '{print$3,$6}' >${Mysqldpath}/startpostion
}
cat ${Mysqldpath}/startpostion
echo ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
incrementalbackup(){
echo Start incremental backup the database++++++++$Datetime
###############get start postion##########
echo =========================================================
Startbinlog=$(awk '{print $1}' ${Mysqldpath}/startpostion)
echo "Startbinlog $Startbinlog"
Startpostion=$(awk '{print $2}' ${Mysqldpath}/startpostion)
echo "Startpostion $Startpostion"
###############get end postion##########
mysql -u$Mysqldu -p$Mysqldp -e "show master status\G"|awk '{print $2}' >${Mysqldpath}/endpostion
echo --------------------------------------------------------
Endbinlog=$(sed -n '2p' ${Mysqldpath}/endpostion)
echo "Endbinlog $Endbinlog"
Endpostion=$(sed -n '3p' ${Mysqldpath}/endpostion)
echo "Endpostion $Endpostion"
echo =========================================================
if [ "$Startbinlog" == "$Endbinlog" ]; then
echo "start-position=$Startpostion stop-position=$Endpostion"
mysqlbinlog --start-position=$Startpostion --stop-position=$Endpostion ${Mysqldatapath}/${Startbinlog} >>${Mysqldpath}/incremental_$Datetime.sql
else
Startline=$(awk "/${Startbinlog}/{print NR}" ${Mysqldatapath}/mysql-bin.index)
Endline=$(wc -l ${Mysqldatapath}/mysql-bin.index |awk '{print $1}')
echo "Startline $Startline - Endline $Endline"
for i in `seq $Startline $Endline`
do
Binlog=$(sed -n "$i"p ${Mysqldatapath}/mysql-bin.index |sed 's/.\///g')
case "$Binlog" in
"$Startbinlog")
mysqlbinlog --start-position=$Startpostion ${Mysqldatapath}/${Binlog} >>${Mysqldpath}/incremental_$Datetime.sql
;;
"$Endbinlog")
mysqlbinlog --stop-position=$Endpostion ${Mysqldatapath}/${Binlog} >>${Mysqldpath}/incremental_$Datetime.sql
;;
*)
mysqlbinlog ${Mysqldatapath}/${Binlog} >>${Mysqldpath}/incremental_$Datetime.sql
esac
done
fi
echo "Start set the startpostion is to Endbinlog $Endbinlog - Endpostion $Endpostion"
echo "$Endbinlog $Endpostion" >${Mysqldpath}/startpostion
}
if [ $Week -ne 1 ];then
echo "today is $Week and begin incremental backup the database"
incrementalbackup
else
echo "today is $Week and begin full backup of the database"
fullbackup
fi
阅读(672) | 评论(0) | 转发(0) |
0
上一篇：没有了
下一篇：没有了
如果觉得《mysql binlog增量备份脚本_实现mysqlldump+binlog日志全量备份和增量备份脚本》对你有帮助，请点赞、收藏，并留下你的观点哦！
