MySQL binlog 自動清理腳本

# vim /data/scripts/delete_mysql_binlog.sh

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

#!/bin/bash #======================================================================================= #   用于删除 MySQL Master 端已經同步完的 binlog【需在 Master 端運作】，以減少磁盤空間 #   每天淩晨 5:30 分運作一次 # #   注：需在 Slave 端添加允許 Master 端通路的帳号【帳号：check_binlog，密碼：binlog_2356】 #          運作于 MySQL Master 端【目前隻用于一主一從的同步模式，對于多從的情況暫時未考慮】 #======================================================================================= PATH= /sbin : /bin : /usr/sbin : /usr/bin : /usr/local/sbin : /usr/local/bin ## Slave端連接配接資訊 SLAVE_ADDR= "XXX.XXX.XXX.XXX" SLAVE_USER= "check_binlog" SLAVE_PWD= "binlog_2356" LOGFILE= "/data/logs/db_sync_info.log" PINGFILE= "/tmp/mysqlping.log" ## MySQL狀态資訊檢視指令 SQLCMD= "show slave status" #======================================================================================= ## 檢查MySQL是否已經運作 if [[ ` ps aux |  grep mysql[d] |  wc -l` - eq 0 ]];  then echo The MySQL is not running at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 測試Slave端的連通性 nohup mysqladmin -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD}  ping > ${PINGFILE} retval=` grep "^error" ${PINGFILE}` rm -f ${PINGFILE} if [[  "${retval}X" !=  "X" ]];  then echo The MySQL Slave can not be connected at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 檢查是否合法的Slave MASTER_ADDR=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e  "${SQLCMD}\G;" |  awk '$1=="Master_Host:" {print $2}' ` LOCAL_ADDR=` /sbin/ifconfig eth1 |  awk -F  ':' '/inet addr/{print $2}' |  sed 's/[a-zA-Z ]//g' ` if [[  "${MASTER_ADDR}" !=  "${LOCAL_ADDR}" ]];  then echo The MySQL Slave is not lawful at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 獲得Slave端資訊，以此來确定是否處于正常同步的情況 IO_STATUS=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e  "${SQLCMD}\G;" |  awk '$1=="Slave_IO_Running:" {print $2}' ` SQL_STATUS=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e  "${SQLCMD}\G;" |  awk '$1=="Slave_SQL_Running:" {print $2}' ` if [[  "${IO_STATUS}" !=  "Yes" ||  "${SQL_STATUS}" !=  "Yes" ]];  then echo The MySQL Replication is not synchronous at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 再做一次判斷，以保證資料同步絕對正常【建立測試資料】 mysql -uroot -e  "create database if not exists mytestdb;" sleep 3 retval=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e  "show databases;" |  grep mytestdb` mysql -uroot -e  "drop database if exists mytestdb;" if [[  "${retval}X" =  "X" ]];  then echo The MySQL Replication is not synchronous at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 在已經同步的情況，還需要判斷目前同步的binlog，以此來确定哪些已經是過期的binlog SLAVE_BINLOG1=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e  "${SQLCMD}\G;" |  awk '$1=="Master_Log_File:" {print $2}' ` SLAVE_BINLOG2=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e  "${SQLCMD}\G;" |  awk '$1=="Relay_Master_Log_File:" {print $2}' ` ## 獲得Master端，目前的binlog檔案以及binlog路徑 MASTER_BINLOG=`mysql -uroot -e  "show master status;" |  grep - v '^+' |  tail -1 |  awk '{print $1}' ` ## 主從端已經同步到相同的binlog if [[  "${SLAVE_BINLOG1}" =  "${SLAVE_BINLOG2}" &&  "${SLAVE_BINLOG1}" =  "${MASTER_BINLOG}" ]];  then CURR_BINLOG= "${MASTER_BINLOG}" ## 主從端已經同步，但從端的binlog還沒有追趕到主端最新的binlog elif [[  "${SLAVE_BINLOG1}" =  "${SLAVE_BINLOG2}" &&  "${SLAVE_BINLOG1}" !=  "${MASTER_BINLOG}" ]];  then CURR_BINLOG= "${SLAVE_BINLOG1}" ## 主從端已經同步，主從端的binlog一緻，但relaylog還不一緻 elif [[  "${SLAVE_BINLOG1}" !=  "${SLAVE_BINLOG2}" &&  "${SLAVE_BINLOG1}" =  "${MASTER_BINLOG}" ]];  then CURR_BINLOG= "${SLAVE_BINLOG2}" else echo Has noknown error at：` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi mysql -uroot -e  "purge binary logs to '${CURR_BINLOG}';" if [[ $? - eq 0 ]];  then echo Clear MySQL binlog is ok at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} fi

# crontab -e 

30 05 * * * /data/scripts/delete_mysql_binlog.sh >/dev/null 2>&1