Code#!/bin/sh
#+++++++++++++mysqlback++++++++++++
#a mysql incremental backup script.
#by flute
#++++++++++++++++++++++++++++++++++
BAK_DIR="."
while getopts :u:p:d:c OPTION
do
case ${OPTION} in
u)
DB_USER=${OPTARG}
;;
p)
PASSWD=${OPTARG}
;;
d)
DB_NAME=${OPTARG}
;;
b)
BAK_DIR=${OPTARG}
;;
c)
INIT="true"
;;
\?)
echo "Usage: mysqlback.sh [OPTIONS]"
echo "-u user for login"
echo "-p Password to use when connecting to server"
echo "-d which database to backup"
echo "-b backup database files to directory,default is current directory"
echo "-c must be usage for first backup,is complete backup"
exit 1
;;
esac
done
MYSQL_BIN=/usr/local/mysql/bin
DATADIR=`${MYSQL_BIN}/mysql -u ${DB_USER} -p${PASSWD} -e "show variables like 'datadir'" | sed -n '2p' | awk '{print $NF}'`
upfile () #上传文件
{
_FNAME=$1
_TNAME=`echo ${_FNAME}|sed 's/\.sql//g'`
_TNAME="${_TNAME}.tgz"
tar -czf ${_TNAME} ${_FNAME}
if [ $? = 0 ]
then
scp ${_TNAME} 192.168.1.10:/usr/local/mysql/bak
if [ $? = 0 ]
then
echo "upfile success."
else
logger -f ~/mysqlbak.log -t MYSQLBAK 'upload file ${_TNAME} failed'
ssh 192.168.1.10 "logger -f ~/mysqlbak.log -t MYSQLBAK 'upload file ${_TNAME} failed'"
fi
else
echo "tar file failed."
fi
}
bak_file () #获取备份文件名
{
_DB_NAME=$1
_BAK_DIR=$2
_BDATE=`date "+%Y-%m-%d"`
_BAKFILE="${_BAK_DIR}/_${_DB_NAME}_${_BDATE}.sql"
if [ ! -f ${_BAKFILE} ]
then
echo ${_BAKFILE}
return 0
else
ID=1
while [ 1 ]
do
_BAKFILE="${_BAK_DIR}/_${_DB_NAME}_${_BDATE}_${ID}.sql"
if [ ! -f ${_BAKFILE} ]
then
echo ${_BAKFILE}
return 0
fi
ID=`expr $ID + 1`
done
fi
}
#进行完全备份
if [ "${INIT}" = "true" ]
then
BDATE=`date "+%Y-%m-%d"`
FILENAME="${DB_NAME}_${BDATE}.sql"
${MYSQL_BIN}/mysqldump --opt -x -u ${DB_USER} -p${PASSWD} ${DB_NAME} > ${FILENAME} &
BINLOG=`tail -n 1 ${DATADIR}*-bin.index | sed 's/\.\///g'`
logdate=`ls -l ${DATADIR}${BINLOG} | awk '{print $6, $7, $8}'`
logdate=`date -d "$logdate" "+%Y-%m-%d %H:%M:00"`
POSITION=`$MYSQL_BIN/mysqlbinlog --start-datetime="$logdate" --database=${DB_NAME} ${DATADIR}${BINLOG} | grep "# at [0-9]\{1,\}" | tail -n 1 | sed 's/#\ at\ //g'`
echo "${POSITION} ${BINLOG}" > mysqlback.ini #记录增量备份起始点
upfile ${FILENAME}
exit
fi
#增量备份
BEG_POS=`cat mysqlback.ini | awk '{print $1}'`
BEG_LOG=`cat mysqlback.ini | awk '{print $2}'`
END_LOG=`tail -n 1 ${DATADIR}/*-bin.index | sed 's/\.\///g'`
LOG_FILES=`grep -A 3 "${BEG_LOG}" ${DATADIR}/*-bin.index | sed 's/\.\///g'`
logdate=`ls -l ${DATADIR}${END_LOG} | awk '{print $6, $7, $8}'`
logdate=`date -d "$logdate" "+%Y-%m-%d %H:%M:00"`
END_POS=`$MYSQL_BIN/mysqlbinlog --start-datetime="$logdate" --database=$DB_NAME ${DATADIR}$END_LOG | grep "# at [0-9]\{1,\}" | tail -n 1 | sed 's/#\ at\ //g'`
echo "begain position:$BEG_POS"
FILENAME=`bak_file ${DB_NAME} ${BAK_DIR}`
if [ `echo ${FILENAME}|grep "_[0-9]\{1,\}.sql"` ]
then
OFFSET='--offset=1'
fi
END_POS=`expr ${END_POS} + 1`
err=0
for loop in ${LOG_FILES}
do
if [ ${BEG_LOG} = ${END_LOG} ]
then
${MYSQL_BIN}/mysqlbinlog -s -u ${DB_USER} -p${PASSWD} ${OFFSET} --start-position=${BEG_POS} --stop-position=${END_POS} --database=${DB_NAME} ${DATADIR}${loop} >> ${FILENAME}
elif [ ${END_LOG} = ${loop} ]
then
${MYSQL_BIN}/mysqlbinlog -s -u ${DB_USER} -p${PASSWD} --database=${DB_NAME} --stop-position=${END_POS} ${DATADIR}${loop} >> ${FILENAME}
elif [ ${BEG_LOG} = ${loop} ]
then
${MYSQL_BIN}/mysqlbinlog -s -u ${DB_USER} -p${PASSWD} ${OFFSET} --start-position=${BEG_POS} --database=${DB_NAME} ${DATADIR}${loop} > ${FILENAME}
else
${MYSQL_BIN}/mysqlbinlog -s -u ${DB_USER} -p${PASSWD} --database=${DB_NAME} ${DATADIR}${loop} >> ${FILENAME}
fi
if [ $? != 0 ]
then
err=1
fi
done
if [ $err = 0 ]
then
END_POS=`expr ${END_POS} - 1`
echo "end position:${END_POS}"
echo "${END_POS} ${END_LOG}" > mysqlback.ini
upfile ${FILENAME}
echo "mysql back success."
else
rm ${FILENAME}
logger -f ~/mysqlbak.log -t MYSQLBAK 'Bakckup to $FILENAME failed with start position:$BEG_POS and end position:$END_POS.'
ssh 192.168.1.10 "logger -f ~/mysqlbak.log -t MYSQLBAK 'Bakckup to $FILENAME failed with start position:$BEG_POS and end position:$END_POS.'"
fi