GeoLite2를 mySQL 데이터베이스에 설치하십시오.

어떤 프록시 서버 의 유용성을 결정할 때 여러 번 , 그 프록시 서버의 위치를 ​​알아야합니다. IP 주소의 정확한 지리적 위치를 보여주는 온라인 웹 사이트가 많이 있지만, 많은 수의 IP 주소에 대해 대량 쿼리를 수행 할 수있는 사이트는 거의 없습니다. 그렇게하기 위해서, 우리는 "ip-to-location"데이터베이스를 알아 내고 우리 자신의 컴퓨터 / 서버로부터 질의를하는 모든 것을 할 필요가 있습니다. 그와 같은 기능을 제공합니다 사용할 수있는 가장 인기있는 제품 중 하나는이다 GeoIP2 MaxMind에 의해.

관심있는 데이터베이스는 여기 에서 다운로드 할 수있는 CSV 형식 의 GeoLite2시 입니다 .

 

wget http://geolite.maxmind.com/download/geoip/database/GeoLite2-City-CSV.zip
unzip GeoLite2-City-CSV.zip

 

 

다운로드하여 압축을 풀면 GeoLite2-City-CSV_20160503 디렉토리 또는 CSV 파일 이 들어있는 디렉토리 와 비슷한 디렉토리가 생성됩니다. 다음 단계는 모든 데이터를 데이터베이스로 가져올 준비를하는 것입니다.

먼저 데이터베이스에 "ip_blocks"및 "ip_locations"라는 두 개의 테이블을 만듭니다. 모든 IP 주소 데이터 열과 해당 데이터 유형의 목록은 해당 웹 사이트의 GeoIP2 도시 및 국가 CSV 데이터베이스 페이지에 설명되어 있습니다. 아래의 모든 코드는이 글이 작성되었을 당시에 변경되었을 수있는 데이터 구조를 기반으로합니다. 

 

 

CREATE TABLE `ip_blocks` (

`ip_from` int unsigned NOT NULL,
`ip_to` int unsigned NOT NULL,

  `network` varchar(32) NOT NULL,
  `geoname_id` int unsigned NOT NULL,
  `registered_country_geoname_id` int unsigned NOT NULL,
  `represented_country_geoname_id` int unsigned NOT NULL,
  `is_anonymous_proxy` tinyint(1) NOT NULL,
  `is_satellite_provider` tinyint(1) NOT NULL,
  `postal_code` varchar(32) NOT NULL,
  `latitude` float(8,4) NOT NULL,
  `longitude` float(8,4) NOT NULL,
  `accuracy_radius` smallint unsigned NOT NULL
);

 

과

CREATE TABLE `ip_locations` (
  `geoname_id` int unsigned NOT NULL,
  `locale_code` varchar(32) NOT NULL,
  `continent_code` char(2) NOT NULL,
  `continent_name` varchar(32) NOT NULL,
  `country_iso_code` char(2) NOT NULL,
  `country_name` varchar(64) NOT NULL,
  `subdivision_1_iso_code` varchar(3) NOT NULL,
  `subdivision_1_name` varchar(128) COLLATE 'utf8_unicode_ci' NOT NULL,
  `subdivision_2_iso_code` varchar(3) NOT NULL,
  `subdivision_2_name` varchar(128) COLLATE 'utf8_unicode_ci' NOT NULL,
  `city_name` varchar(128) COLLATE 'utf8_unicode_ci' NOT NULL,
  `metro_code` smallint unsigned NOT NULL,
  `time_zone` varchar(64) NOT NULL
);

 

 

다음 단계는 실제로 두 테이블에 데이터를 가져 오는 것입니다. 수백만 행의 가져 오기에는 어느 정도 시간이 걸릴 수 있으므로 터미널을 사용하는 것이 가장 좋습니다.

 

20160503/GeoLite2-City-Blocks-IPv4.csv' INTO TABLE ip_blocks COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (
@network,
geoname_id,
registered_country_geoname_id,
represented_country_geoname_id,
is_anonymous_proxy,
is_satellite_provider,
postal_code,
latitude,
longitude,
accuracy_radius) SET 
ip_from = INET_ATON(SUBSTRING(@network, 1, LOCATE('/', @network) - 1)),
ip_to = (INET_ATON(SUBSTRING(@network, 1, LOCATE('/', @network) - 1)) + (pow(2, (32-CONVERT(SUBSTRING(@network, LOCATE('/', @network) + 1), UNSIGNED INTEGER)))-1));

 

 

"이 MySQL 버전에는 사용 된 명령이 허용되지 않습니다"라는 오류가 발생하면 --local-infile 옵션을 사용하여 mySQL을 실행해야합니다.

 

mysql --local-infile -u root -p your_database_name

 

이제 ip_locations 테이블에 데이터를로드 합니다. 아래 쿼리 코드는 모든 열을 테이블에 포함하고자한다고 가정합니다. 데이터베이스에 특정 필드를 저장하지 않으려는 경우 원하지 않는 열 앞에 @를 추가하십시오.

 

LOAD DATA LOCAL INFILE '/root/geoip/GeoLite2-City-CSV_20160503/GeoLite2-City-Locations-en.csv' INTO TABLE ip_locations CHARACTER SET UTF8 COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (
geoname_id,
locale_code,
continent_code,
continent_name,
country_iso_code,
country_name,
subdivision_1_iso_code,
subdivision_1_name,
subdivision_2_iso_code,
subdivision_2_name,
city_name,
metro_code,
time_zone);

 

모두 성공하면 두 테이블 모두 CSV 파일의 데이터로 채워야합니다. 다음 단계는 쿼리를 훨씬 빠르게 만드는 인덱스를 추가하는 것입니다.

 

ALTER TABLE `ip_blocks` ADD PRIMARY KEY `ip_to` (`ip_to`);
ALTER TABLE `ip_locations` ADD PRIMARY KEY `geoname_id` (`geoname_id`);

 

 

마지막으로이 헬퍼 프로 시저를 추가하여 향후 쿼리를 단순화합니다.

 

DELIMITER $$

CREATE FUNCTION `IP2Location`(`ip` varchar(50))
	RETURNS int(11)
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN

DECLARE loc_id INT;

SELECT geoname_id INTO loc_id FROM ip_blocks WHERE ip_to >= INET_ATON(TRIM(ip)) ORDER BY ip_to LIMIT 1;

RETURN IFNULL(loc_id, 0);

END $$

 

 

 사용법 

SELECT * FROM ip_locations 어디 geoname_id = IP2Location ( "198.199.81.169");