mysql dump slave 备份_MySQL 备份脚本--mysqldump在slave 上进行备份

#!/bin/sh#create by hq on 20160531

show_slave_status(){

echo-e"-----master.info:-----" >>${LOG_FILE}cat${MAIN_PATH}/data/master.info | sed -n '2,3p' >>${LOG_FILE}echo-e"----show slave status:----">>${LOG_FILE}echo"show slave status\G" |${MYSQL_CMD} | egrep "Slave_IO_Running|Slave_SQL_Running|Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Relay_Log_File|Relay_Log_Pos" >>${LOG_FILE}echo-e "" >> ${LOG_FILE}}

source/data/mysqldata/scripts/mysql_env.ini

HOST_PORT=3306HOST_IP=127.0.0.1MAIN_PATH=/data/mysqldata/${HOST_PORT}DATA_PATH=/data/mysqldata/backup/mysql_full

DATA_FILE=${DATA_PATH}/dbfullbk_`date +%F`.sql.gz

LOG_FILE=${DATA_PATH}/dbfullbk_`date +%F`.log

MYSQL_PATH=/usr/local/mysql/bin

MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -h${HOST_IP} -P${HOST_PORT} -S ${MAIN_PATH}/mysql.sock"MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -h${HOST_IP} -P${HOST_PORT} -A -R -S ${MAIN_PATH}/mysql.sock --single-transaction"echo> ${LOG_FILE}echo-e "====jobs started at `date +%F' '%T' '%w`====\n">>${LOG_FILE}

if [ ! -d ${DATA_PATH}]; then

mkdir-p ${DATA_PATH}echo-e "===`${DATA_PATH}` does not exsits,created them!" >> ${LOG_FILE}fi

echo-e "**** started postion===:" >>${LOG_FILE}echo"stop slave SQL_THREAD;"| ${MYSQL_CMD}show_slave_status

echo-e "**** executed command:${MYSQL_DUMP} |gzip > ${DATA_FILE}">>${LOG_FILE}

${MYSQL_DUMP} | gzip >${DATA_FILE}echo-e "**** execute finished at `date +%F' '%T' '%w`"==== >>${LOG_FILE}

echo -e"**** backup file size:`du -sh ${DATA_FILE}`====\n">>${LOG_FILE}

echo -e"**** recheck position====">>${LOG_FILE}

show_slave_status

echo"start slave SQL_THREAD;"| ${MYSQL_CMD}

echo -e"----find expired backup and delete those file ---">>${LOG_FILE}

for tfile in $(/usr/bin/find ${DATA_PATH} -mtime +30)

do

if [ -d $tfile ] ; then

rmdir $tfile

elif [ -f $tfile ] ; then

rm -f $tfile

fi

echo -e"---- delete file :$tfile---">>${LOG_FILE}

done

echo -e"\n===jobs ended at `date +%F' '%T' '%w` ====\n ">>${LOG_FILE}