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     - Git Getting Started tutorial (Linux)

- How to install the latest version of the Eclipse in Ubuntu 14.04 (Linux)

- How do I cancel (almost) any operations in Git, (Linux)

- ElasticSearch - Basic Concepts (Server)

- 256 with rich colors decorate your terminal (Linux)

- CentOS7 install and configure Nagios (Server)

- Use value type build better applications Swift (Programming)

- Shell commonly commands (Linux)

- Linux installation beautify early experience (Linux)

- Keepalived + HAProxy high availability load balancing (Server)

- Java developers question (Programming)

- TPCC-MySQL Benchmark (Database)

- Git uses a small mind (Linux)

- Specifies the open ports of the SUSE firewall settings (Linux)

- independently configurable PHP environment under CentOS6.5 (Server)

- iostat command Detailed (Linux)

- PXE install CentOS 6.4 (Linux)

- Vim Getting Started Tutorial (Linux)

- Storm basic framework for analysis (Programming)

- ImageMagick Tutorial: How to cut images in Linux command line (Linux)

 
         
  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:      
 
- Build ftp server under CentOS 6.5 (Server)
- Getting Started with Linux: Nginx Web Server How to Block Specific User Agents (UA) (Server)
- Ubuntu user use PPA to install Uget 2.0.5 (Linux)
- Oracle in the add & split partition on the impact of global & local index (Database)
- Linux print file and send mail (Linux)
- Binary tree traversal algorithm summary (recursive and non-recursive) (Programming)
- CentOS yum source as the default setting methods in DVD (Linux)
- Configuring DNS process under CentOS 6.5 (Server)
- Docker in the development and practice of IFTTT (Server)
- Linux System Tutorial: Ubuntu on the desktop is disabled by default keyring to unlock tips (Linux)
- Learn to read the source code of vmstat (Linux)
- osprofiler use OpenStack Cinder Lane (Server)
- netfilter- in kernel mode network packet operation (Linux)
- ASM Disk Space Check (Database)
- Linux system performance analysis and top command ps.pstree Comments (Linux)
- Linux FTP setting file description (Server)
- dmidecode command Detailed (Hardware information) (Linux)
- Ubuntu install Tonido private cloud services (Server)
- Formatted output printf command (Programming)
- How to make a U disk to install Ubuntu (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.