Import web logs to MySQL database

Access analysis is an important SEO work, but statistics, analysis tools, after all, the function is for the public, many times the SEO needs some specific data, is http://www.aliyun.com/zixun/aggregation/10341.html "> Statistical analysis software, procedures can not provide. In this way, direct Web log analysis is the most appropriate, the log will record each visit, as long as their own will extract, combination, you can get the data you want.

It was a long time to talk to the star Arrow, and finally agreed to import the logs into the SQL database (MySQL & SQL Server), using SQL statement analysis is the most convenient and what kind of data you need, as long as you use the appropriate SQL commands.

My server is Linux, and without SQL Server, it's not going to happen again. Today to talk about this topic seriously search for a moment, found the use of MySQL analysis log The first step, recorded here.

The next method of analysis I will post in the blog, interested friends can pay attention to, at the same time, recruit proficient in SQL statements of friends, discuss the various methods of analysis, and share to other friends.

The implementation of importing web logs to MySQL database

1. Modify Apache Log format

To modify the Web log format:

Logformat combined%>A,%UI,%UN,[%TL], "%rm%ru http/%rv",%hs,%h ","%{user-agent}>h ",%ss:%sh

Understand a friend should see it, SQL needs to import a specific separator, the Apache log by default is a space-delimited, and some content (such as 200 610 in the status code) also contains spaces, which can not be accurately imported.

Changing the log format to comma-delimited can be an accurate import. You can also eliminate unwanted content in the log format and reduce the log file size, depending on your needs.

2, the establishment of mysql6184.html "> data table

drop table if exists weblog;
CREATE TABLE Weblog (
ID int unsigned auto_increment PRIMARY KEY not NULL,
L_date date,
L_time time,
C_IP varchar (15),
S_IP varchar (15),
S_port varchar (5),
Method varchar (10),
Path varchar (255),
Query varchar (255),
Status varchar (3),
Domain varchar (50),
System varchar (200)
);

Note: Above is just a MySQL data table structure example, no corresponding to the previous log format, do not copy the use directly!

Create a new database with the data table structure corresponding to the log format.

3, import log to MySQL

LOAD DATA INFILE '/log location/log file ' into TABLE weblog FIELDS terminated by ', ';

Weblog corresponds to the data table name above.

The second step is to analyze, as mentioned above, to share after the experience.

Also, find a. PL program that imports IIS logs into the MySQL database from the Internet, and the principle is interlinked, and the points needed are downloaded here (Readme.txt for use).

Author: Si Shu
Original: Import Web log to MySQL database
Copyright, reprint must be linked to the form of the author and the original source and retain this statement.