Mysqldump备份脚本for slave

Mysqldump在很多场景下是作为主力备份工具使用，不过mysqldump创建一致性备份会带有全局锁定(flush table with read lock)，而在slave节点上就可以不用担心全局锁定的影响，因为可以控制slave节点在创建备份时不承担前段业务。

备份过程中，可以借助于slave中的复制线程，使得备份期间slave根本不产生变更，这样的话mysqldump无需使用全局锁定也可以创建一致性备份，即只需要停止sql_thread线程，这样，slave节点仍然能接收master节点的日志，但并不会应用。在此期间创建的备份理论上应为一致状态。而且master节点新产生的修改时间仍能同步到本地(io_thread仍在工作)。待备份任务完成后重新启动sql_thread即可。 

 

大致步骤如下：

A . 停止slave服务中的sql_thread线程

B. 记录当前接收和应用二进制日志文件及位置

C. 执行备份命令

D. 再次记录当前接收和应用二进制日志文件及位置

E. 启动slave服务中的sql_thread线程

备份脚本：

#!/bin/sh # show_slave_status(){    echo -e "----  master.info：----" >>$LOG_FILE    cat ${MYSQL_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}MYSQL_USER=rootMYSQL_PASSWORD=mysqldDATA_PATH=/backup/bakDATE=$(date +%Y%m%d)  DATA_FILE=$DATA_PATH/dbfullbak_$DATE.sql.gzLOG_FILE=$DATA_PATH/dbfullbak_$DATE.logMYSQL_PATH=/u01/my3306MYSQL_CMD="$MYSQL_PATH/bin/mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} -S ${MYSQL_PATH}/run/mysql.sock"MYSQL_DUMP="$MYSQL_PATH/bin/mysqldump -u${MYSQL_USER} -p${MYSQL_PASSWORD} -S ${MYSQL_PATH}/run/mysql.sock -A -R -x --default-character-set=utf8"echo > $LOG_FILEecho -e "==== Jobs started at $(date +"%y-%m-%d %H:%M:%S") ===\n" >> $LOG_FILEecho -e "*** started position：====" >> $LOG_FILEecho "stop slave SQL_THREAD;" |$MYSQL_CMDshow_slave_statusecho -e "*** Excuted commend：${MYSQL_DUMP} |gzip > $DATA_FILE" >> $LOG_FILE${MYSQL_DUMP} |gzip > $DATA_FILEecho -e "*** Excuted finished：${MYSQL_DUMP} |gzip > $DATA_FILE" >> $LOG_FILEecho -e "*** Bachkup file size："`du -sh $DATA_FILE`" ===\n" >> $LOG_FILEecho -e "*** recheck position：====" >> $LOG_FILEecho "stop slave SQL_THREAD;" |$MYSQL_CMDshow_slave_statusecho -e "----Find expired backup and delete those file ----" >> $LOG_FILEfor tfile in $(/usr/bin/find $DATA_PATH/ -mtime +7)do       if [-d $tfile ] ; then              rmdir $tfile          elif [-f $tfile] ; then              rm $tfile          fi      echo "----Delete the file $tfile ----" >> $LOG_FILEdone echo -e "==== Jobs ended at $(date +"%y-%m-%d %H:%M:%S") ===\n" >> $LOG_FILE

备份日志：

[mysql@mysql_slave bak]$ cat dbfullbak_20171130.log ==== Jobs started at 17-11-30 17:39:23 ===*** started position：====----  master.info：----binlog.000023195---- show slave status: ----              Master_Log_File: binlog.000023          Read_Master_Log_Pos: 195               Relay_Log_File: relaylog.000005                Relay_Log_Pos: 355        Relay_Master_Log_File: binlog.000023             Slave_IO_Running: Yes            Slave_SQL_Running: No          Exec_Master_Log_Pos: 195      Slave_SQL_Running_State:  *** Excuted commend：/u01/my3306/bin/mysqldump -uroot -pmysqld -S /u01/my3306/run/mysql.sock -A -R -x --default-character-set=utf8 |gzip > /backup/bak/dbfullbak_20171130.sql.gz*** Excuted finished：/u01/my3306/bin/mysqldump -uroot -pmysqld -S /u01/my3306/run/mysql.sock -A -R -x --default-character-set=utf8 |gzip > /backup/bak/dbfullbak_20171130.sql.gz*** Bachkup file size：180K /backup/bak/dbfullbak_20171130.sql.gz ===*** recheck position：====----  master.info：----binlog.000023195---- show slave status: ----              Master_Log_File: binlog.000023          Read_Master_Log_Pos: 195               Relay_Log_File: relaylog.000005                Relay_Log_Pos: 355        Relay_Master_Log_File: binlog.000023             Slave_IO_Running: Yes            Slave_SQL_Running: No          Exec_Master_Log_Pos: 195      Slave_SQL_Running_State:  ----Find expired backup and delete those file ----==== Jobs ended at 17-11-30 17:39:23 ===

可以在备份完成后检查备份前后，读取和应用master节点的二进制位置有无变化，无变化则可认为备份集是一致的。