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     - How to implement large-scale distributed Yahoo depth study on the Hadoop cluster (Server)

- jQuery get value drop-down list and select text (Programming)

- Getting Started with Linux: CentOS7 Install Mono with the yum command (Server)

- AngularJS application unit testing started (Programming)

- Java Builder mode (Programming)

- Linux system security Comments (Linux)

- The basic method RHEL6 (CentOS6) used in the source package compiled RPM: Upgrade OpenSSH articles (Linux)

- Install Apache streaming media services on CentOS 6.4 (Server)

- Linux system font color change (Linux)

- Python Django model within the class meta Detailed (Programming)

- Lsblk command lists using Linux block device information (Linux)

- dmidecode command Detailed (Hardware information) (Linux)

- count (*) function in MySQL optimization of InnoDB storage engine (Database)

- shell script error dirname: invalid option - b (Database)

- Linux file permissions chmod chown (Linux)

- The new features of MySQL 5.7 Generated Column (index function) (Database)

- How to fix the Ubuntu Error script returned error exit status 1 (Linux)

- JavaScript function definition mode (Programming)

- Compile and install the latest version of Redis Stable (Database)

- GNU / Linux enable Intel Rapid Start (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:      
 
- Git Tutorial Comments (Linux)
- Docker commonly used commands Description (Linux)
- Nodejs command-line program development tutorial (Programming)
- Linux, MySQL / MariaDB Galera Cluster Setup Process (Database)
- Linux RPM default installation path (Linux)
- C # socket udp broadcast (Programming)
- Search Linux commands and files - which, whereis, locate, find (Linux)
- Heartbeat cluster components Overview (Server)
- Depth study and understanding for individual users suicide DDoS attacks (Linux)
- How MAT Android application memory leak analysis (Programming)
- Sublime Text 3 (Linux)
- CentOS7 install JAVA notes (Linux)
- Linux common network tools: ping host sweep (Linux)
- git checkout generated in the use of temporary br (Linux)
- JDK installation notes under CentOS (Linux)
- Linux System Getting Started Learning: Using the Linux command line detected DVD burner name and write speeds (Linux)
- Forwarding module with Apache reverse proxy server (Server)
- Using Maven to download Spring (Linux)
- Close common port to protect server security (Linux)
- Linux character device - a simple character device model (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.