Remote Binlog Back-up. Quite similar feature is available since 5.6<\/p>\n
\r\n#!\/bin\/bash\r\n\r\n#\r\n# |||||||||||\r\n#\t-----------\r\n#\t [] []\r\n#\t |\r\n#\t \/\/|\\\\\r\n#\r\n# The Binlog Patrol\r\n\r\n# @auth: pb@press-service.com.pl\r\n# @background:\r\n# Why do not use rotate?\r\n# 1. Rotate does not turn off the instance.\r\n# 2. Rotate does not know:\r\n#\t- which file are to be archive;\r\n#\t- the slaves are alive and up to date;\r\n#\t- no guarantee that after change of the configuration it includes the current file.\r\n# 2.1 Rotate can:\r\n#\t- compress files\r\n#\t- move file to specified file into a given localization\r\n# @scenario\r\n# \t1. [done] Get connected slaves\r\n#\t2. [done] Check conditions:\r\n#\t 2.1 [done] Check maximal delay of the slave;\r\n#\t 2.2 [done] Check whether the number of slaves is correct;\r\n#\t3. [done] Get actual file \r\n#\t4. [done] Iterate files.\r\n#\t5. [done] Bzip the file\r\n#\t6. Purge binary logs<\/stroke> cause binary logs are purge conform to my.cnf policy (expire_logs_days)\r\n#\t7. [done] mv bzipped file to backup\r\n#\t8. [done] remove old files (on backup storage)\r\n#\t9. [done] Stop the server in case there is no space enough\r\n\r\nHOST=\"$1\";\r\nPORT=\"$2\";\r\n# 3 on db1 (db5 returned)\r\nSLAVE_EXPECTED_NO=3\r\nSLAVE_CHECKED_NO=0\r\nSLAVE_CHECKED=\"\"\r\nSLAVE_LAG_GLOBAL=0\r\nLOGBIN_NAME=\"inforia\"\r\nLOGBIN_DIR=\"\/storage_local\/mysql\/logbin\"\r\n# no trailing slash\r\nLOGBIN_DIR_BCK=\"\/backup_nfs\/logbin\/\"\r\nLOGBIN_PATTERN=\"$LOGBIN_NAME.[0-9]\\{1,\\}$\"\r\nLOGBIN_FILE_NO_MIN=2 # leave the minimal nr of file\r\nLOGBIN_CURRENT_FILE=\"\"\r\n\r\nTHIS_MASTER_SLAVES='' # Containes master's SHOW SLAVE HOSTS\r\nTHIS_MASTER_SLAVES_ERR='' # Contains any error\r\n\r\nSCRIPT_FILE=\"$0\"\r\nSCRIPT_FILE_LOG=\"${SCRIPT_FILE%.*}.log\"\r\nSCRIPT_FILE_LOG_MAX_SIZE=100000\r\n\r\nCURRENT_TIMESTAMP=$(date '+%s')\r\nLOGBIN_TIMESTAMP_LIMIT=0\r\nTIMESTAMP_TO_ADD=3600 # one hour\r\nMIN_VOLUME_CAPACITY_TO_RESTART_MB=200\r\n\r\nfunction compare_int {\r\n if [ $1 -gt $2 ]; then\r\n\treturn 1;\r\n else\r\n\treturn 0;\r\n fi \r\n}\r\n\r\n\r\nfunction strstr ( )\r\n{\r\n echo $1 | grep --quiet $2\r\n}\r\n\r\nfunction write_to_log {\r\n local INFO=$1\r\n local TIME=$(date '+%Y.%m.%d %H:%M');\r\n echo \"[$TIME] $INFO\" >> $SCRIPT_FILE_LOG\r\n}\r\n \r\nfunction write_to_syslog\r\n{\r\n logger -p daemon.error -t BINLOG_ROTATE \"$1\";\r\n}\r\n\r\n\r\n# cut the file where the scripts logs\r\nfunction rotate_log\r\n{\r\n\r\n local FILE_LOG=\"$1\"\r\n local FILE_EXT=\"${FILE_LOG##*.}\"\r\n local FILE_SIZE=$(stat -c\"%s\" $FILE_LOG)\r\n\r\n if [ ! \"$FILE_EXT\" = \"log\" ]; then\r\n\techo \"I cannot truncate file which has another ext than log ($FILE_LOG)\";\r\n\treturn 1\r\n fi\r\n\r\n if [ \"$FILE_SIZE\" -gt \"$SCRIPT_FILE_LOG_MAX_SIZE\" ]; then\r\n\tdate > \"$FILE_LOG\"\r\n\techo \"File truncatated by script owner\" >> \"$FILE_LOG\"\r\n fi\r\n return 0\r\n}\r\n\r\nif [ -z $HOST ]; then\r\n \/bin\/echo \"[Error] no host given (first argument)\" > \/dev\/stderr;\r\n exit 1;\r\nfi\r\nif [ -z $PORT ]; then\r\n PORT='3306';\r\nfi\r\n\r\nPRINT_RESULT=0;\r\nRESULT=\"\";\r\nif [ \"$3\" = \"print\" ]; then\r\n PRINT_RESULT=1;\r\nfi\r\n\r\nSCRIPT_NAME=`basename ${0%.*}`;\r\nSTANDARD_IFS=$IFS\r\n\r\nTMP_ERROR_DIR=\"\/tmp\/\";\r\nDIR_REPORT=\"\/var\/log\/overseer\/\";\r\nERR_FILE=\"$DIR_REPORT\"\"Mysql_\"$HOST\".Binlog\";\r\n# LAG_ERR_FILE=\"$DIR_REPORT\"\"Mysql_\"$HOST\".ReplicationLag\";\r\n\r\nMYSQL_HOMEDIR=\"\/usr\/local\/services\/mysql\/bin\/\";\r\nMYSQL_CMD=\"mysql\";\r\n\r\nMY_PASSWD=\"SWavuwUqupR8___\";\r\nMY_USER=\"overseer.sys\";\r\n\r\n\r\nrotate_log \"$SCRIPT_FILE_LOG\"\r\n\r\nrm -v \"$TMP_ERROR_DIR$SCRIPT_NAME$HOST\"\r\n# 1\r\nTHIS_MASTER_SLAVES=$($MYSQL_HOMEDIR$MYSQL_CMD --connect_timeout=1 -u$MY_USER --password=$MY_PASSWD -h $HOST --port=$PORT -e \"SHOW SLAVE HOSTS\\G\" 2>$TMP_ERROR_DIR$SCRIPT_NAME$HOST);\r\nif [ -f \"$TMP_ERROR_DIR$SCRIPT_NAME$HOST\" ]; then\r\n THIS_MASTER_SLAVES_ERR=$(\/bin\/cat < $TMP_ERROR_DIR$SCRIPT_NAME$HOST);\r\nfi\r\n\r\nif [ -n \"$SLAVE_ERR\" ]; then\r\n\t\/bin\/echo $SLAVE_ERR > $ERR_FILE;\r\n\texit 1;\r\nfi\r\n\r\n\r\nif [ \"$SLAVE_EXPECTED_NO\" -gt \"0\" ]; then\r\n\r\n PORT=\"\"\r\n HOST=\"\"\r\n IFS=$'\\n'\r\n THIS_MASTER_SLAVES=$(echo \"$THIS_MASTER_SLAVES\" | sed 's\/\\*\/#\/g')\r\n for item in ${THIS_MASTER_SLAVES}\r\n do\r\n # echo \"$item\"\r\n if ( strstr \"$item\" \"Host\"); then\r\n\tHOST=$(\/bin\/echo \"$item\" | \/bin\/grep -o -i -e '\\(Host:[[:space:]]\\)\\([0-9\\.]\\{3,\\}\\)' | \/bin\/awk -F \" \" '{print $2 }');\r\n\t# '\r\n fi\r\n if ( strstr \"$item\" \"Port\"); then\r\n\tPORT=$(\/bin\/echo \"$item\" | \/bin\/grep -o -i -e '\\(Port:[[:space:]]\\)\\([0-9]\\{3,\\}\\)' | \/bin\/awk -F \" \" '{print $2 }');\r\n\t# '\r\n fi\r\n \r\n if [ -n \"$PORT\" ]; then\r\n\trm \"$TMP_ERROR_DIR$SCRIPT_NAME$HOST\"\r\n\tSLAVE_INFO=$($MYSQL_HOMEDIR$MYSQL_CMD --connect_timeout=1 -u$MY_USER --password=$MY_PASSWD -h $HOST --port=$PORT -e \"show slave status\\G\" 2>$TMP_ERROR_DIR$SCRIPT_NAME$HOST); \r\n\tSLAVE_INFO=$(echo \"$SLAVE_INFO\" | sed 's\/\\*\/#\/g')\r\n\t# echo \"$SLAVE_INFO\"\r\n\tSLAVE_IO=$(\/bin\/echo $SLAVE_INFO | \/bin\/grep -o -i -e '\\(Slave_IO_Running:[[:space:]]\\)\\([A-Za-z]\\{3\\}\\)' | \/bin\/awk -F \" \" '{print $2 }'); \r\n\t# highlight '\r\n\tSLAVE_SQL=$(\/bin\/echo $SLAVE_INFO | \/bin\/grep -o -i -e '\\(Slave_SQL_Running:[[:space:]]\\)\\([A-Za-z]\\{3\\}\\)' | \/bin\/awk -F \" \" '{print $2 }'); \r\n\t# highlight '\r\n\tif [ -f \"$TMP_ERROR_DIR$SCRIPT_NAME$HOST\" ]; then\r\n\t SLAVE_ERR=$(\/bin\/cat < $TMP_ERROR_DIR$SCRIPT_NAME$HOST);\r\n\tfi\r\n\tif [ -n \"$SLAVE_ERR\" ]; then\r\n\t write_to_log \"Errors while asking about slaves\"\r\n\t \/bin\/echo $SLAVE_ERR > $ERR_FILE;\r\n\t exit 1;\r\n\tfi\r\n\tif [ \"$SLAVE_IO\" = \"Yes\" ] && [ \"$SLAVE_SQL\" = \"Yes\" ]; then \r\n\t SLAVE_LAG=$(\/bin\/echo $SLAVE_INFO | \/bin\/grep -o -i -e '\\(Seconds_Behind_Master:[[:space:]]\\)\\([0-9]\\{0,10\\}\\)' | \/bin\/awk -F \" \" '{print $2 }');\r\n\t # '\r\n\t SLAVE_LAG=$((0 + $SLAVE_LAG));\r\n\t write_to_log \"$HOST io: $SLAVE_IO, sql: $SLAVE_SQL, lag: $SLAVE_LAG\"\r\n\t if [ \"$SLAVE_LAG\" -gt \"$SLAVE_LAG_GLOBAL\" ]; then\r\n\t\tSLAVE_LAG_GLOBAL=$SLAVE_LAG\r\n\t fi\r\n\telse\r\n\t INFO=\"$HOST io: $SLAVE_IO, sql: $SLAVE_SQL, lag: $SLAVE_LAG\"\r\n\t write_to_log \"$INFO\"\r\n\t \/bin\/echo \"$INFO\" > $ERR_FILE;\r\n\t exit 1\r\n\tfi\r\n\tSLAVE_CHECKED=\"$SLAVE_CHECKED $HOST\" \r\n\tHOST=\"\"\r\n\tPORT=\"\"\r\n\tlet SLAVE_CHECKED_NO++\r\n fi\r\n done\r\nelse\r\n write_to_log \"I omit slave checking cause SLAVE_EXPECTED_NO=$SLAVE_EXPECTED_NO\"\r\nfi\r\nIFS=$STANDARD_IFS\r\n\r\nif [ ! \"$SLAVE_CHECKED_NO\" -eq \"$SLAVE_EXPECTED_NO\" ]; then\r\n echo \"Some slaves are down (SLAVE_EXPECTED: $SLAVE_EXPECTED_NO, is $SLAVE_CHECKED_NO). CHECKED: $SLAVE_CHECKED\" > $ERR_FILE\r\n exit 1\r\nfi\r\n\r\nLOGBIN_TIMESTAMP_LIMIT=$(($CURRENT_TIMESTAMP - $SLAVE_LAG_GLOBAL - $TIMESTAMP_TO_ADD))\r\n\r\n# echo \"now $CURRENT_TIMESTAMP\"\r\n# echo \"max lag $SLAVE_LAG_GLOBAL\"\r\n# echo \"limit: $LOGBIN_TIMESTAMP_LIMIT\"\r\n# exit 0\r\n# echo \"$LOGBIN_PATTERN\"\r\n\r\n# #3\r\n\r\nLOGBIN_CURRENT_FILE=`tail -n 1 $LOGBIN_DIR\/$LOGBIN_NAME.index`\r\n# Check if it exists\r\nCURRENT_EXISTS=`echo $LOGBIN_CURRENT_FILE | grep -c \"$LOGBIN_DIR\/$LOGBIN_PATTERN\"`\r\nif [ ! \"$CURRENT_EXISTS\" = \"1\" ]; then\r\n echo \"Current binlog file was not detected. I expected file: $LOGBIN_CURRENT_FILE. However CURRENT_EXISTS=$CURRENT_EXISTS\" > $ERR_FILE\r\n exit 1\r\nfi\r\nLOGBIN_CURRENT_FILE=$(basename $LOGBIN_CURRENT_FILE)\r\n\r\nfor binlog_file in \"$LOGBIN_DIR\"\/*; do\r\n proceed=`echo \"$binlog_file\" | grep -c \"$LOGBIN_PATTERN\"`\r\n if [ \"$proceed\" -eq \"1\" ]; then\r\n\tFILE_TO_PROCEED=\"$binlog_file\"\r\n\tFILE_TO_PROCEED_BASENAME=$(basename $binlog_file)\r\n\tif [ \"$FILE_TO_PROCEED_BASENAME\" = \"$LOGBIN_CURRENT_FILE\" ];then\r\n\t write_to_log \"I omit current file $FILE_TO_PROCEED_BASENAME\"\r\n\t break;\r\n\tfi\r\n\tFILE_TIMESTAMP=`stat -c\"%Z\" \"$FILE_TO_PROCEED\"`\r\n\tFILE_TIMESTAMP=$(($FILE_TIMESTAMP + 0))\r\n\tcompare_int $LOGBIN_TIMESTAMP_LIMIT $FILE_TIMESTAMP\r\n\tIS_GT=$?\r\n\tif [ \"$IS_GT\" -eq \"0\" ]; then\r\n\t write_to_log \"File is too fresh: $FILE_TO_PROCEED_BASENAME\"\r\n\t write_to_log \"$IS_GT : $LOGBIN_TIMESTAMP_LIMIT : $FILE_TIMESTAMP\"\r\n\t break;\r\n\tfi\r\n\twrite_to_log \"bzip2 $binlog_file\"\r\n\tbzip2 \"$binlog_file\"\r\n\tBZIP_RESULT=$?\r\n\tif [ \"$BZIP_RESULT\" -gt \"0\" ];then\r\n\t echo \"Bzip failed (with status $BZIP_RESULT): $LOGBIN_CURRENT_FILE\" > $ERR_FILE\r\n\t exit 1\r\n\tfi\r\n\twrite_to_log \"mv $binlog_file.bz2 $LOGBIN_DIR_BCK\"\r\n\tmv \"$binlog_file.bz2\" \"$LOGBIN_DIR_BCK\"\r\n\tMV_RESULT=$?\r\n\tif [ \"$MV_RESULT\" -gt \"0\" ];then\r\n\t echo \"mv failed (with status $MV_RESULT): $LOGBIN_CURRENT_FILE\" > $ERR_FILE\r\n\t exit 1\r\n\tfi\r\n fi\r\ndone\r\n\r\n# remove old files\r\nfind \"$LOGBIN_DIR_BCK\" -type f -mtime +31 -exec rm -v '{}' \\;\r\nAVAILABLE_STORAGE_SPACE=$(df -P \"$LOGBIN_DIR\" | tail -n 1 | awk -F \" \" '{print $4}')\r\nif [ -z \"$AVAILABLE_STORAGE_SPACE\" ]; then\r\n COMM=\"Left space for devide information error\"\r\n echo $COMM > $ERR_FILE\r\n write_to_log $COMM\r\n exit 1\r\nfi\r\n\r\nAVAILABLE_STORAGE_SPACE=$(($AVAILABLE_STORAGE_SPACE\/1024))\r\nwrite_to_log \"Available space $AVAILABLE_STORAGE_SPACE Mb\"\r\nif [ \"$AVAILABLE_STORAGE_SPACE\" -lt \"$MIN_VOLUME_CAPACITY_TO_RESTART_MB\" ]; then\r\n write_to_syslog \"Script $0 is to stop mysql server\"\r\n \/etc\/init.d\/mysql.server stop\r\nfi\r\n\r\n# echo \"$ERR_FILE\"\r\n# cat \"$ERR_FILE\"\r\nexit 0;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Remote Binlog Back-up. Quite similar feature is available since 5.6 #!\/bin\/bash # # ||||||||||| # ———– # [] [] # | # \/\/|\\\\ # # The Binlog Patrol # @auth: pb@press-service.com.pl # @background: # Why do not use rotate? # 1. Rotate does not turn off the instance. # 2. Rotate does not know: # […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/677"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=677"}],"version-history":[{"count":7,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/677\/revisions"}],"predecessor-version":[{"id":789,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/677\/revisions\/789"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=677"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=677"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=677"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}