GuutonG

[HOW TO] Script Backup Mysql Log (Shell Script)

#!/bin/bash
###################################################
#      ____             _               ____      #
#     / ___|_   _ _   _| |_ ___  _ __  / ___|     #
#    | |  _| | | | | | | __/ _ \| '_ \| |  _      # 
#    | |_| | |_| | |_| | || (_) | | | | |_| |     #
#     \____|\__,_|\__,_|\__\___/|_| |_|\____|     #
#                                                 #
###################################################
#            ScriptMySqlLogBackup.sh              #
###################################################

timestamp=$(date +"%Y%m%d")
MyHost="localhost"
MyUser="root"
MyPass=""
logDir="/var/log/mysql"
moveToDir=""

echo "[INFO] $(date +"%Y%m%d %T") : mysql log backup start";

mv $logDir/mysql.log $logDir/mysql.log.$timestamp
mv $logDir/mysql_error.log $logDir/mysql_error.log.$timestamp
mv $logDir/mysql-slow.log $logDir/mysql-slow.log.$timestamp

if [ -f "$logDir/mysql.log" ]
then
   echo "[WARN] $(date +"%Y%m%d %T") : mysql.log Move Failed";
else
   echo "[INFO] $(date +"%Y%m%d %T") : mysql.log Move Successed";
fi

if [ -f "$logDir/mysql_error.log" ]
then
   echo "[WARN] $(date +"%Y%m%d %T") : mysql_error.log Move Failed";
else
   echo "[INFO] $(date +"%Y%m%d %T") : mysql_error.log Move Successed";
fi

if [ -f "$logDir/mysql-slow.log" ]
then
   echo "[WARN] $(date +"%Y%m%d %T") : mysql-slow.log Move Failed";
else
   echo "[INFO] $(date +"%Y%m%d %T") : mysql-slow.log Move Successed";
fi

if [ -n "$MyPass" ]
then
   /usr/bin/mysqladmin --host=$MyHost --user=$MyUser --password=$MyPass flush-logs
else 
   /usr/bin/mysqladmin --host=$MyHost --user=$MyUser flush-logs
fi

echo "[INFO] $(date +"%Y%m%d %T") : flush-logs Successed";

zip $logDir/mysql.log.$timestamp.zip $logDir/mysql.log.$timestamp
zip $logDir/mysql_error.log.$timestamp.zip $logDir/mysql_error.log.$timestamp
zip $logDir/mysql-slow.log.$timestamp.zip $logDir/mysql-slow.log.$timestamp

if [ -f "$logDir/mysql.log.$timestamp.zip" ]
then
   echo "[INFO] $(date +"%Y%m%d %T") : mysql.log.$timestamp zip Successed";
   rm -rf $logDir/mysql.log.$timestamp
   echo "[INFO] $(date +"%Y%m%d %T") : mysql.log.$timestamp Deleted";
else 
   echo "[WARN] $(date +"%Y%m%d %T") : mysql.log.$timestamp zip Failed";
fi

if [ -f "$logDir/mysql_error.log.$timestamp.zip" ]
then
   echo "[INFO] $(date +"%Y%m%d %T") : mysql_error.log.$timestamp zip Successed";
   rm -rf $logDir/mysql_error.log.$timestamp
   echo "[INFO] $(date +"%Y%m%d %T") : mysql_error.log.$timestamp Deleted";
else 
   echo "[WARN] $(date +"%Y%m%d %T") : mysql_error.log.$timestamp zip Failed";
fi

if [ -f "$logDir/mysql-slow.log.$timestamp.zip" ]
then
   echo "[INFO] $(date +"%Y%m%d %T") : mysql-slow.log.$timestamp zip Successed";
   rm -rf $logDir/mysql-slow.log.$timestamp
   echo "[INFO] $(date +"%Y%m%d %T") : mysql-slow.log.$timestamp Deleted";
else 
   echo "[WARN] $(date +"%Y%m%d %T") : mysql-slow.log.$timestamp zip Failed";
fi


if [ -n "$moveToDir" ]
then
   if [ -f "$logDir/mysql.log.$timestamp.zip" ]
   then
       mv $logDir/mysql.log.$timestamp.zip $moveToDir/mysql.log.$timestamp.zip
       echo "[INFO] $(date +"%Y%m%d %T") : move mysql.log.$timestamp.zip to $moveToDir";
   else 
      echo "[WARN] $(date +"%Y%m%d %T") : mysql.log.$timestamp.zip not found or move Failed";
   fi

   if [ -f "$logDir/mysql_error.log.$timestamp.zip" ]
   then
      mv $logDir/mysql_error.log.$timestamp.zip $moveToDir/mysql_error.log.$timestamp.zip
      echo "[INFO] $(date +"%Y%m%d %T") : move mysql_error.log.$timestamp.zip to $moveToDir";
   else 
      echo "[WARN] $(date +"%Y%m%d %T") : mysql_error.log.$timestamp.zip not found or move Failed";
   fi

   if [ -f "$logDir/mysql_error.log.$timestamp.zip" ]
   then
       mv $logDir/mysql_slow.log.$timestamp.zip $moveToDir/mysql_slow.log.$timestamp.zip
       echo "[INFO] $(date +"%Y%m%d %T") : move mysql_slow.log.$timestamp.zip to $moveToDir";
   else 
      echo "[WARN] $(date +"%Y%m%d %T") : mysql_slow.log.$timestamp.zip not found or move Failed";
   fi
else 
   echo "[WARN] $(date +"%Y%m%d %T") : Move Failed";
fi

echo "[INFO] $(date +"%Y%m%d %T") : mysql log backup finish";

#============================
#edit ScriptMySqlLogBackup.sh
#============================
MyHost="localhost"
MyUser="root"
MyPass=""
logDir="/var/log/mysql"

#============================
#edit crontab
#============================
crontab -e

0 23 * * * /path/to/ScriptMySqlLogBackup/ScriptMySqlLogBackup.sh > /path/to/ScriptMySqlLogBackup/ScriptMySqlLogBackup.log

 5.ต่อไปเป็นการตั้ง host domain มาเลือกที่ Sharing

 6.ให้เปลี่ยนจาก localhost.localdomain เป็นตามที่ต้องการ ในที่นี้เป็น ol7 (ส่วน .localdomain ก็ถ้ามี domain ก็ลองใส่ดูครับ)


 7. ถ้าเราเปลี่ยนได้ให้สังเกตที่ Terminal ว่าเป็น ol7 หรือยัง


 8.คราวนี้ลอง ping ol7 ดูว่าผ่านไหมถ้ายังไม่ได้เราต้องไปแก้ไฟล์ hosts ของ OS โดยใช้คำสั่ง
 9.ให้เพิ่มที่บรรทัดสุดท้ายว่า 192.168.56.101 ol7 แล้วเซฟ (ในการใช้ vi หรือ text editor ของ linux 
ต้องศึกษาเพิ่มเติม การใช้งาน vi editor เบื้องต้น)

 10.ลอง ping ol7 ใหม่ดูคราวนี้ก็จะ ping ผ่าน