Maxmind GeoLite2 to Mysql

Автор: ProxyNova.com, перевод Феськов Кузьма

Скачиваем файл с CSV данными: GeoLite2-City-CSV_xxxxx.zip, где xxxxx какая-то дата, из личного кабинета Maxmind.

Распаковываем.

Теперь необходимо создать две таблицы:

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 NOT NULL,
  `is_satellite_provider` tinyint NOT NULL,
  `postal_code` varchar(32) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float 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 'utf8mb4_general_ci' NOT NULL,
  `subdivision_2_iso_code` varchar(3) NOT NULL,
  `subdivision_2_name` varchar(128) COLLATE 'utf8mb4_general_ci' NOT NULL,
  `city_name` varchar(128) COLLATE 'utf8mb4_general_ci' NOT NULL,
  `metro_code` smallint unsigned NOT NULL,
  `time_zone` varchar(64) NOT NULL
);

В первой будут храниться диапазоны IP адресов, а во второй текстовые данные городов и стран. Maxmind предлагает на выбор несколько вариантов перевода: английский, русский, немецкий, испанский, французский, японский, португальский, и китайский.

Следующим этапом мы будем загружать наши данные в базу. Это довольно длительная процедура, так как нам предстоит импортировать миллионы строк.

Если в настройках вашей базы запрещен прямой импорт из файлов, то необходимо эту функцию включить:

SET GLOBAL local_infile=1;

Теперь переходим непосредственно к импорту данных из CSV файла:

LOAD DATA LOCAL INFILE 'd:/GeoLite2/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));

В первой строке укажите путь до вашего файла на диске:

LOAD DATA LOCAL INFILE 'd:/GeoLite2/GeoLite2-City-Blocks-IPv4.csv'

Импорт данных займет какое-то время. Зависит от мощности вашего компьютера.

Теперь импортируем текстовые данные:

LOAD DATA LOCAL INFILE 'd:/GeoLite2/GeoLite2-City-Locations-en.csv' INTO TABLE ip_locations CHARACTER SET UTF8MB4 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);

Выберите нужный вам языковой вариант файла.

Теперь, когда все данные загружены в базу, давайте создадим два поисковых индекса:

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

Работать с функцией можно так:

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

Если по какой-то причине вы не любите функции, можно воспользоваться запросом:

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

Пользуйтесь на здоровье!

Перевел и исправил Феськов Кузьма

Оставить комментарий

Maxmind GeoLite2 to Mysql

Остались вопросы?