Apache紀錄寫入資料庫(轉入)

目的:將當前存放於access_log的紀錄透過ShellScript方式讀出並轉入至DB資料庫中，作為將來統計與稽核的RawData。

前置環境
OS:CentOS 7.3
Http Service:apache 2.4.6
DB:mariadb 5.5.52

1.建立資料表

使用SQL語句建立資料表

CREATE TABLE IF NOT EXISTS `access_detail` (
`acc_dip` varchar(15) NOT NULL COMMENT '目的IP',
`acc_sip` varchar(15) NOT NULL COMMENT '來源IP',
`acc_date` varchar(20) NOT NULL COMMENT '連線時間',
`acc_page` varchar(2048) NOT NULL COMMENT '連線頁面',
`acc_stus` varchar(5) NOT NULL COMMENT '連線結果'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `access_detail`
ADD UNIQUE KEY `acc_dip` (`acc_dip`,`acc_sip`,`acc_date`,`acc_page`(255)) USING BTREE;

2.編寫ShellScript

編輯transfer.sh

#!/bin/bash
# 資料庫參數
host="[DB主機]";
port="[埠號]";
user="[帳號]";
password="[密碼]";
dbname="[資料庫]";

# access_log檔案路徑
log_file="/var/log/httpd/access_log";

# 排除內文字串
str_exclude1='phpmyadmin';
str_exclude2='127.0.0.1';

# 查詢資料在access_log內的欄位數(可依實際檔案欄位排序修改)
# 來源IP
remote_ip='1';
# 讀取時間
read_date='4';
# 讀取頁面
read_page='7';
# 讀取狀態
read_stus='9';
# 來源頁面
referer='11';

# 本機IP
if [ $(uname -a | awk '{print substr($3,0,2)}') == "3." ]; then
 # CentOS 7
 local_ip=$(ifconfig | awk '/inet /{print $2}' | grep -v '127.0.0.1' | cut -b 1-);
elif [ $(uname -a | awk '{print substr($3,0,2)}') == "2." ]; then
 # Before CentOS 7
 local_ip=$(ifconfig | awk '/inet addr:/{print $2}' | grep -v '127.0.0.1' | cut -b 6-);
else
 local_ip="0.0.0.0";
fi
acc_dip=$local_ip;

# 取得紀錄
while read line; do
# 排除本地的phpmyadmin連線
if [[ $line != *$str_exclude1* && $line != *$str_exclude2* ]]; then
 # 篩選出200與304的連線
 if [[ $(echo "$line" | awk '{print $'$read_stus'}') == "200" || $(echo "$line" | awk '{print $'$read_stus'}') == "304" ]]; then
  acc_ref=$(echo "$line" | awk '{print $'$referer'}' | cut -b 2-);
  acc_ref=$(echo "$acc_ref" | cut -b 1-$((${#acc_ref}-1)));
  # 來源路徑過濾，排除本地與指定domain來源
  if [[ $acc_ref != "-" && $acc_ref != *"//localhost/"* && $acc_ref != *"//"$acc_dip"/"* && $acc_ref != *"cyut.edu.tw"* ]]; then
   acc_sip=$(echo "$line" | awk '{print $'$remote_ip'}');
   acc_date=$(echo "$line" | awk '{print $'$read_date'}' | cut -b 2-);
   acc_page=$(echo "$line" | awk '{print $'$read_page'}');
   acc_stus=$(echo "$line" | awk '{print $'$read_stus'}');
   echo $acc_ref;

   # 將結果寫入資料庫
   mysql --host=$host --port=$port --protocol=TCP --user=$user --password=$password $dbname << EOF
   insert ignore access_detail (acc_dip,acc_sip,acc_date,acc_page,acc_stus) values('$acc_dip','$acc_sip','$acc_date','$acc_page','$acc_stus');
EOF
  fi
 fi
fi
done < $log_file

注意：紅色標示的EOF前方不可放置空格，須由上一行結束時直接Enter換行，否則會出現錯誤。
使用root身分執行ShellScript

sh transfer.sh;

3.補充說明

實際使用時，建立的資料表結構可與ShellScript搭配調整出自己所需內容，適當的篩選資訊可降低資料庫的成長速度。