Elastic Compute Service：為MySQL建立應用一致性快照最佳實務（Linux）

建立/tmp/prescript.sh指令碼並寫入指令碼內容。

1. 使用root使用者建立/tmp/prescript.sh。

   vim /tmp/prescript.sh

2. 輸入i，進入編輯模式。

3. 在指令碼中根據應用自訂prescript.sh指令碼內容。

   展開查看prescript.sh指令碼內容：

   TIMESTAMP=`date +%s`
   MYSQL_TEMP_FILE_NAME="/tmp/mysqlfreeze${TIMESTAMP}.tmp"
   LOG_FILE_NAME="/tmp/mysqlfreeze${TIMESTAMP}.log"
   
   # 設定您的MySQL使用者名稱
   export MYSQL_USER="$MYSQL_USER"
   # 設定您的MySQL密碼
   export MYSQL_PWD="$MYSQL_PASSWORD"
   
   function Log()
   {
       echo "$1" 
       echo "$1" >> ${LOG_FILE_NAME}
   }
   
   
   function ExitWithResult()
   {
       Log "[INFO]:mysql freeze result is $1."
       exit $1
   }
   
   function Main()
   {
       Log "*********************************************************************"
       Log "[INFO]:Begin to freeze mysql."
   
       which mysql
       if [ $? -ne 0 ]
       then
           Log "[INFO]:mysql is not installed."
           ExitWithResult 0
       fi  
   
       systemctl status mysqld.service | grep "inactive (dead)"
       if [ $? -ne 1 ]
       then
           Log "[ERROR]:mysql is not running."
           ExitWithResult 0
       fi  
   
       mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "show processlist;" > "${MYSQL_TEMP_FILE_NAME}" 2>&1
       if [ $? -ne 0 ]
       then
           cat ${MYSQL_TEMP_FILE_NAME} >>"${LOG_FILE_NAME}"
           [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
           Log "[ERROR]:Show process list failed."
           ExitWithResult 1
       fi
   
   
       process_id=`cat ${MYSQL_TEMP_FILE_NAME} | grep "select 1 and sleep(25)" | awk -F " " '{print $1}'`
       if [ "$process_id" != "" ]
       then
           cat ${MYSQL_TEMP_FILE_NAME} >>"${LOG_FILE_NAME}"
           [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
           Log "[ERROR]:MySQL already been freezed "
           ExitWithResult 1
       fi
   
       cat ${MYSQL_TEMP_FILE_NAME}
   
       Log "[INFO]:Try to execute flush tables command"
   
          echo "flush tables with read lock;select 1 and sleep(25);" | nohup mysql -u$MYSQL_USER   >> "${LOG_FILE_NAME}" 2>&1 &
       if [ $? -ne 0 ]
       then
           Log "[ERROR]:Freeze mysql failed."
           ExitWithResult 1
       fi  
   
       Log "[INFO]:Flush tables command execute success"
   
       checkTime=0
       while [ 1 ]
       do
           mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "show processlist;" > "${MYSQL_TEMP_FILE_NAME}" 2>&1
           if [ $? -ne 0 ]
           then
               cat ${MYSQL_TEMP_FILE_NAME} >>"${LOG_FILE_NAME}"
               [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
               Log "[ERROR]:Show process list failed."
               ExitWithResult 1
           fi
           
           cat ${MYSQL_TEMP_FILE_NAME}
   
           process_id=`cat ${MYSQL_TEMP_FILE_NAME} | grep "select 1 and sleep(25)" | awk -F " " '{print $1}'`
           if [ "$process_id" = "" ]
           then
               checkTime=`expr $checkTime + 1`
               Log "[INFO]:Mysql is not freeze. checkTime is ${checkTime}"
               sleep 1
           else
               Log "[INFO]:Found sleep command in processlist,freeze success"
               break
           fi
   
              if [ $checkTime -eq 10 ]
           then
               cat "${MYSQL_TEMP_FILE_NAME}" >>"${LOG_FILE_NAME}" 2>&1
               
               freeze_id=`cat ${MYSQL_TEMP_FILE_NAME} | grep "flush tables with read lock" | awk -F " " '{print $1}'`            
               mysql -u$MYSQL_USER -e "kill $freeze_id;" >> "${LOG_FILE_NAME}" 2>&1
               if [ $? -ne 0 ]
               then
                   Log "[ERROR]:Thaw mysql failed."
               fi    
   
               [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
               Log "[ERROR]:Mysql is not freeze. Will return error"
               ExitWithResult 1
           fi
       done
   
          [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
       Log "[INFO]:Finish freeze mysql."
       ExitWithResult 0
   }
   Main

   在指令碼中，您需要修改以下參數資訊：

   • $MYSQL_USER：修改為MySQL使用者名稱。

   • $MYSQL_PASSWORD：修改為MySQL密碼。

4. 按Esc鍵，輸入:wq，然後按斷行符號鍵，退出並儲存內容。

5. 為指令碼設定僅root使用者讀、寫和執行許可權。

   重要

   為了保證指令碼的執行安全，請確保僅root使用者具有對指令碼的讀、寫及執行許可權，即許可權為700，否則執行指令碼時會判斷失敗。

   chmod 700 /tmp/prescript.sh

調用預存程序（TestPIT）。

CALL TestPIT;