mysql dump slave 备份_MySQL 5.5 Slave节点备份脚本（mysqldump）

[root@localhost script]# vim slave_full_backup_by_mysqldump.sh

#!/bin/sh

show_slave_status(){

echo -e "---- master.info: ----" >> $LOG_FILE

cat ${MAIN_PATH}/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

}

HOST_PORT=5505

MAIN_PATH=/mysql_data/5505

DATA_PATH=/backup/

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

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

MYSQL_PATH=/data/bin

MYSQL_USER=system

MYSQL_PASS=Mysql#2015

MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /var/lib/mysql/mysql.sock "

MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -S /var/lib/mysql/mysql.sock -A -R --single-transaction -l --default-character-set=utf8"

echo > $LOG_FILE

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

echo -e "**** started position: ====" >> $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 "**** Executed finished at `date +%F' '%T' '%w` ====" >> ${LOG_FILE}

echo -e "**** Backup file size: `du -sh ${DATA_PATH}` ****\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 files ----" >> ${LOG_FILE}

for tfile in $(/bin/find $DATA_PATH/ -mtime +6)

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}