| |
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 |
|
|
|