Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Server \ Access.log Apache access log analysis and how to import it into MySQL     - Zombie process under Linux (Linux)

- Incremental garbage collection mechanism for Ruby 2.2 (Programming)

- Linux server operating system security configuration (Linux)

- Impact test noatime Linux file access time (Linux)

- Lucene Getting Started Tutorial (Server)

- Forwarding module with Apache reverse proxy server (Server)

- CentOS install Memcached (Server)

- Supervisor Installation and Configuration (Server)

- Difference between TCP and UDP protocols (Linux)

- Python programmers most often committed ten errors (Programming)

- RedHat Linux 6.4 installation RTL8188CUS wireless network card driver (Linux)

- How to upgrade Docker 1.6 on Fedora / CentOS (Server)

- Ten correct use Redis skills (Database)

- Linux Command Study Notes (Linux)

- How to Create a file can not be changed under Linux (Linux)

- Linux, modify the fstab file system can not start causing solve one case (Linux)

- Linux system security check notes on performance (Linux)

- GAMIT baseline solution using batch were counted (Linux)

- Linux system started to learn: the Linux syslog (Linux)

- Android custom controls create the simplest skid menu in the history (Programming)

 
         
  Access.log Apache access log analysis and how to import it into MySQL
     
  Add Date : 2018-11-21      
         
         
         
  First of all, we have a apache access log interpretation,

220.181.108.92 - - [22 / Aug / 2013: 23: 59: 59 +0800] "GET /min/f=/media/js/jquery-1.7.2.media/js/global_interaction.js&201308221836 HTTP / 1.1" 200 70550 "http: //adfdfs.com.cn1071-2130-1.html" "Mozilla / 5.0 (X11; U; Linux x86_64; en-US; rv: 1.9) Gecko Minefield / 3.0"

The first message is the address of the remote host that it shows that access to the site who actually yes.

The second is blank, with a "-" placeholder instead. In fact most of the time this one is true. This position is used to record the identity of the viewer, it's not just the viewer's login name, but the viewer's email address or other unique identifier, usually just "-";

The third is to use a blank placeholder "-" instead. This position is used to authenticate the recording viewer provided when names. Of course, if some of the content of the site requires users to authenticate, then this information is not blank. However, for most sites, the majority of log file this one is still blank.

Fourth, represents access time record visitors, no more explanation, the last time information "+800" indicates that the server 8 hours after which the area is located in UTC.

Fifth, the entire log is the most useful information, it tells us is that the server receives a request for what

Sixth, the status code 200

Seventh, 70500, is the total number of bytes sent by the server to the client

Eighth, when customers request the same directory or URL.

For more information ninth, the client

Tables can be built according to the needs


CREATE TABLE IF NOT EXISTS `apache_log` (
  `Id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'id number'
  `Hostip` char (32) NOT NULL DEFAULT '0.0.0.0' COMMENT 'IP address of the remote host'
  `Marking` char (30) COMMENT 'identity viewer'
  `Client_name` char (30) COMMENT 'browse' names'
  `Addtime` char (64) NOT NULL COMMENT 'browsing time'
  `Time_zone` char (8) NOT NULL COMMENT 'time zone'
  `Method` char (8) NOT NULL COMMENT 'method'
  `Resource` char (64) NOT NULL COMMENT 'requested resource'
  `Protocol` char (8) COMMENT 'request protocol used'
  `Status` int (5) COMMENT 'status code'
  `Bytes` int (5) COMMENT 'total number of bytes sent to the client'
  `Refer` char (128) COMMENT 'when customers request the same directory or URL',
  `Client_info` char (128) COMMENT 'when customers request the same directory or URL',
  PRIMARY KEY ( `id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

First deal with awk apache logs, each of which will be separated by a space, then we see the following lot numbers on the confused, which in the end is what is it. First awk '{print 0, like text apache logs per line writes a 0, because the first field in the table structure is an auto-increment, as long as I import 0 0 indicates the default value, it can automatically increased.
cat apache_log | awk '{print 0 "" $ 1 "" $ 2 "" $ 3 "" for (i = 4; i <= NF; i ++) printf $ i; printf " n"}' >> apache_log.txt


mysql> load data infile "/root/apache_log.txt" into table apache_log fields terminated by "" lines terminated by ' n';

ERROR 13 (HY000): Can not get stat of '/root/apache_log.txt' (Errcode: 13 - Permission denied)

mysql> load data local infile "/root/apache_log.txt" into table apache_log fields terminated by "" lines terminated by ' n';

Query OK, 2000 rows affected, 2672 warnings (0.61 sec)
Records: 2000 Deleted: 0 Skipped: 0 Warnings: 2672
Incidentally compare the load data infile and load data local infile difference

If you do not give local, the server follows its positioning:
1) If your filename is an absolute path, the server from the root directory to locate the file.
2) If your filename is a relative path, the server looks for the file from the beginning of the data directory database.
If you are given a local, then the file will be positioned in the following manner:
1) If your filename is an absolute path, the client begins to look for the file from the root directory.
2) If your filename is a relative path, the client from the current directory to find the file.

mysql> desc apache_log;
+ -------------- + ----------- + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ -------------- + ----------- + ------ + ----- + --------- + ---------------- +
| Id | int (11) | NO | PRI | NULL | auto_increment |
| Hostip | char (32) | NO | | 0.0.0.0 | |
| Marking | char (30) | YES | | NULL | |
| Browser_name | char (30) | YES | | NULL | |
| Addtime | char (64) | NO | | NULL | |
| Time_zone | char (8) | NO | | NULL | |
| Method | char (8) | NO | | NULL | |
| Resource | char (64) | NO | | NULL | |
| Protocol | char (8) | YES | | NULL | |
| Status | int (5) | YES | | NULL | |
| Bytes | int (5) | YES | | NULL | |
| Refer | char (128) | YES | | NULL | |
| Client_info | char (128) | YES | | NULL | |
+ -------------- + ----------- + ------ + ----- + --------- + ---------------- +
13 rows in set (0.00 sec)


mysql> select * from apache_log limit 3;
+ ---- + ----------------- + --------- + -------------- + - ---------------------- ----------- + ------ + -------- + -------------------------------------- + ---------- + -------- + ------- + ------- + ------------------------- -------------------------------------------------- --------- +
| Id | hostip | marking | browser_name | addtime | time_zone | method | resource | protocol | status | bytes | refer | client_info |
+ ---- + ----------------- + --------- + -------------- + - ---------------------- ----------- + ------ + -------- + -------------------------------------- + ---------- + -------- + ------- + ------- + ------------------------- -------------------------------------------------- --------- +
| 1 | 220.181.108.116 | - | - | [21 / Aug / 2013: 23: 59: 59 | +0800] | "GET | /search/prj/tag/2089-2124-2350-2354-1.html | HTTP / 1.1 | 200 | 8181 | "-" | "Mozilla / 5.0 (compatible; Baiduspider / 2.0; + http: //www.baidu.com/search/spider.html)" |
| 2 | 220.181.165.5 | - | - | [21 / Aug / 2013: 23: 59: 59 | +0800] | "GET | /event/bushanping/admin_login.php | HTTP / 1.1 | 200 | 32095 |" - "|" Mozilla / 5.0 (Windows; U; WindowsNT5.1; zh-CN; rv: 1.9.1.1) Gecko / 20090715Firefox / 3.5.1 "|
| 3 | 220.181.165.136 | - | - | [21 / Aug / 2013: 23: 59: 59 | +0800] | "GET | / search / diy / server-status / | HTTP / 1.1 | 404 | 21795 |" - "|" Mozilla / 5.0 (Windows; U; WindowsNT5.1; zh-CN; rv: 1.9.1.1) Gecko / 20090715Firefox / 3.5.1 "|
+ ---- + ----------------- + --------- + -------------- + - ---------------------- ----------- + ------ + -------- + -------------------------------------- + ---------- + -------- + ------- + ------- + ------------------------- -------------------------------------------------- --------- +
3 rows in set (0.00 sec)

Import successful
     
         
         
         
  More:      
 
- Linux server alarms using Java (Server)
- Ubuntu users install the video driver Nvidia Driver 334.21 (Linux)
- Installation Elementary OS Freya 20 things to do (Linux)
- MySQL full-index scan bug (Database)
- System Security: Build Linux with LIDS steel castle (Linux)
- Upload the project to GitHub, synchronous remote repository Github (Linux)
- Intrusion analysis and prevention tools Knark under Linux platform (Linux)
- Ubuntu installation module Python rq (Linux)
- When the master key encounter NULL (Database)
- Binding unofficial Google Drive and Ubuntu 14.04 LTS (Linux)
- How to use scientific data Docker quickly configure the development environment (Server)
- How to modify the Emacs Major Mode Shortcuts (Linux)
- IBM Data Studio to create objects using ---- double quotes / sensitive issues and the table / column renaming (Database)
- Ubuntu 14.04, 13.10 install OpenCV 2.4.9 (Linux)
- To install GCC development environment under SUSE11 (Linux)
- Source code compiled by the installation program under Linux (Linux)
- Configuration OpenOCD + FT2232 under Ubuntu (Linux)
- CentOS yum source configuration (Linux)
- php How to prevent being injected (Linux)
- Configure the Linux kernel and use iptables to do port mapping (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.