当前位置：首页 > news >正文

官方文档搬运 MAXMIND IP定位 mysql导入简单使用

news 文章来源：https://blog.csdn.net/chai471793/article/details/139703758 2024/11/7 3:42:51

官方文档地址：

官方文档

文件下载

1. 导入mysql可能报错

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

查看配置

    SHOW GLOBAL VARIABLES LIKE '%secure%';

secure_file_priv 原来是NULL 依旧报错

 mysql --help | grep my.cnf                                                            /etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf#修改配置
vim /opt/homebrew/etc/my.cnf#追加或修改
secure-file-priv = "/"#重启mysql
mysql.server restart

将 GeoIP2 和 GeoLite2 数据库导入 MySQL

在本页

下载并提取数据库
- 保持数据库更新
- 提取 CSV 文件
为网络数据创建表
转换网络字段
架构
- 将数据加载到网络表中
- 通过查询来测试我们的表
- 对表格进行排序以便更快地进行搜索
- 分解查询以加快搜索速度
可选：创建位置数据表
- 位置表架构
- 将数据加载到位置表中
- 查询我们的表格

本指南将向您展示如何将 GeoIP2 或 GeoLite2 数据库导入 MySQL，以便在您的服务器上轻松查询和操作它们。

导入 CSV 数据库包括下载数据库、提取数据库、创建表来保存数据以及为加快查询速度而对这些表进行索引。

下载并提取数据库

首先，请确保您已下载要导入的 GeoIP2 或 GeoLite2 数据库的最新版本。您可以通过您的帐户门户下载数据库。CSV 格式的数据库以单个 zip 文件的形式提供。有关存档的 zip 结构和内容的详细信息，请参阅我们的 CSV 数据库文档。在本教程中，我们将使用 GeoIP2 City CSV 文件，但您可以获取有关我们的任何 CSV 格式数据库的信息并相应地调整以下说明。

保持数据库更新

如果您要导入数据库以供持续使用，您将需要自动下载和提取 CSV 文件的过程，以确保您的数据库始终是最新的。

提取 CSV 文件

下载数据库后，将 zip 文件解压到所需目录中。如果要导入 GeoIP2 City 数据库，您将获得许多文件。在本教程中，我们将使用以下文件：

GeoIP2-City-Blocks-IPv4.csv
GeoIP2-City-Blocks-IPv6.csv
GeoIP2-City-Locations-en.csv

如果您使用英语以外的语言，则可以Locations从 zip 存档中选择适当的文件。例如，如果您想将中文位置名称加载到 MySQL，则可以使用 GeoIP2-City-Locations-zh-CN.csv而不是GeoIP2-City-Locations-en.csv。我们关于 CSV 格式数据库的文档包括 GeoIP2 和 GeoLite2 数据库中当前包含的所有位置文件的列表。

为网络数据创建表

GeoIP2-City-Blocks-IPv4.csv首先我们创建一个表来保存和中包含的网络信息 GeoIP2-City-Blocks-IPv6.csv。

转换网络字段

您可以在GeoIP2 和 GeoLite2 CSV 数据库文件部分Blocks中找到这些文件架构的完整描述。如果您使用的是其他数据库，则可以找到相应数据库的Blocks文件架构，并调整表格以满足该结构。

数据库的字段network使用 CIDR 表示法。遗憾的是，MySQL 不提供处理该格式数据的任何功能，因此我们必须先将网络转换为其他格式，以便以后轻松查询。我们选择将网络表示为一对 IP 地址，它们分别是网络中的第一个和最后一个地址。我们将转换此字段，以便这两个 IP 地址都表示为十六进制数。

我们可以使用数据库转换工具将此字段转换为十六进制数。下载程序并将其安装到提取 CSV 文件的同一目录中后，即可运行它：


$ ./geoip2-csv-converter -block-file GeoIP2-City-Blocks-IPv4.csv -include-hex-range -output-file GeoIP2-City-Blocks-IPv4-Hex.csv
$ ./geoip2-csv-converter -block-file GeoIP2-City-Blocks-IPv6.csv -include-hex-range -output-file GeoIP2-City-Blocks-IPv6-Hex.csv

架构

现在我们可以创建一个名为的表geoip2_network来保存我们刚刚转换的数据。我们将使用类型来表示 IP 地址varbinary(16)，该类型足够大，可以表示 128 位（16 字节）IPv6 地址。

create table geoip2_network (network_start varbinary(16) not null,network_end varbinary(16) not null,geoname_id int,registered_country_geoname_id int,represented_country_geoname_id int,is_anonymous_proxy bool,is_satellite_provider bool,postal_code text,latitude float,longitude float,accuracy_radius int,is_anycast bool,index(network_start),index(network_end)
);

network_start请注意，我们为和添加了两个单独的索引network_end。如果我们在这两列上都使用复合索引，我们将无法加快稍后使用的查询速度。

将数据加载到网络表中

GeoIP2-City-Blocks-IPv4.csv我们现在可以将和的内容导入GeoIP2-City-Blocks-IPv6.csv到我们刚刚创建的表中。

我们首先加载转换后的 IPv6 数据：

load data infile '/var/maxmind/GeoIP2-City-Blocks-IPv6-Hex.csv'
into table geoip2_network
fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows
(@network_start, @network_end, @geoname_id, @registered_country_geoname_id, @represented_country_geoname_id,@is_anonymous_proxy, @is_satellite_provider, @postal_code, @latitude, @longitude, @accuracy_radius)
set network_start = unhex(@network_start),network_end = unhex(@network_end),geoname_id = nullif(@geoname_id, ''),registered_country_geoname_id = nullif(@registered_country_geoname_id, ''),represented_country_geoname_id = nullif(@represented_country_geoname_id, ''),is_anonymous_proxy = nullif(@is_anonymous_proxy, ''),is_satellite_provider = nullif(@is_satellite_provider, ''),postal_code = nullif(@postal_code, ''),latitude = nullif(@latitude, ''),longitude = nullif(@longitude, ''),accuracy_radius = nullif(@accuracy_radius, '');

我们可以用同样的方式加载转换后的IPv4数据：

load data infile '/var/maxmind/GeoIP2-City-Blocks-IPv4-Hex.csv'
into table geoip2_network
fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows
(@network_start, @network_end, @geoname_id, @registered_country_geoname_id, @represented_country_geoname_id,@is_anonymous_proxy, @is_satellite_provider, @postal_code, @latitude, @longitude, @accuracy_radius)
set network_start = unhex(@network_start),network_end = unhex(@network_end),geoname_id = nullif(@geoname_id, ''),registered_country_geoname_id = nullif(@registered_country_geoname_id, ''),represented_country_geoname_id = nullif(@represented_country_geoname_id, ''),is_anonymous_proxy = nullif(@is_anonymous_proxy, ''),is_satellite_provider = nullif(@is_satellite_provider, ''),postal_code = nullif(@postal_code, ''),latitude = nullif(@latitude, ''),longitude = nullif(@longitude, ''),accuracy_radius = nullif(@accuracy_radius, '');

请注意，即使 CSV 文件中有表中缺少的额外列，数据也会被导入。

通过查询来测试我们的表

所有内容加载完毕后，我们现在可以在数据库中查找 IP 地址。由于我们将 IP 地址表示为varbinary(16)，因此我们首先必须使用 MySQL 的内置inet6_aton函数将我们感兴趣的 IP 地址的文本表示转换为相同类型。

select geoname_id, registered_country_geoname_id, represented_country_geoname_id,postal_code, latitude, longitude, accuracy_radius
from geoip2_network
where inet6_aton('214.0.0.0') between network_start and network_end
limit 1;

+------------+-------------------------------+--------------------------------+-------------+----------+-----------+-----------------+
| geoname_id | registered_country_geoname_id | represented_country_geoname_id | postal_code | latitude | longitude | accuracy_radius |
+------------+-------------------------------+--------------------------------+-------------+----------+-----------+-----------------+
|    6252001 |                       6252001 |                           NULL | NULL        |   37.751 |   -97.822 |            1000 |
+------------+-------------------------------+--------------------------------+-------------+----------+-----------+-----------------+
1 row in set (0.03 sec)

虽然这会产生正确的结果，但我们注意到查询性能可以更好。让我们改进它。

对表格进行排序以便更快地进行搜索

加快查询速度的一种方法是添加order by network_end如下内容：

select geoname_id, registered_country_geoname_id, represented_country_geoname_id,postal_code, latitude, longitude, accuracy_radius
from geoip2_network
where inet6_aton('214.0.0.0') between network_start and network_end
order by network_end
limit 1;

+------------+-------------------------------+--------------------------------+-------------+----------+-----------+-----------------+
| geoname_id | registered_country_geoname_id | represented_country_geoname_id | postal_code | latitude | longitude | accuracy_radius |
+------------+-------------------------------+--------------------------------+-------------+----------+-----------+-----------------+
|    6252001 |                       6252001 |                           NULL | NULL        |   37.751 |   -97.822 |            1000 |
+------------+-------------------------------+--------------------------------+-------------+----------+-----------+-----------------+
1 row in set (0.00 sec)

虽然这解决了我们对上一个查询的性能担忧，但对于 GeoIP2 数据库中不包含的地址，此查询的性能仍然很差：

elect geoname_id, registered_country_geoname_id, represented_country_geoname_id,postal_code, latitude, longitude, accuracy_radius
from geoip2_network
where inet6_aton('127.0.0.1') between network_start and network_end
order by network_end
limit 1;

Empty set (4.45 sec)

分解查询以加快搜索速度

我们可以解决这个问题，将查询分成两部分，这样 MySQL 就能更有效地使用我们创建的索引：

select geoname_id, registered_country_geoname_id, represented_country_geoname_id,postal_code, latitude, longitude, accuracy_radius
from (select *from geoip2_networkwhere inet6_aton('214.0.0.0') >= network_startorder by network_start desclimit 1
) net
where inet6_aton('214.0.0.0') <= network_end;

+------------+-------------------------------+--------------------------------+-------------+----------+-----------+-----------------+
| geoname_id | registered_country_geoname_id | represented_country_geoname_id | postal_code | latitude | longitude | accuracy_radius |
+------------+-------------------------------+--------------------------------+-------------+----------+-----------+-----------------+
|    6252001 |                       6252001 |                           NULL | NULL        |   37.751 |   -97.822 |            1000 |
+------------+-------------------------------+--------------------------------+-------------+----------+-----------+-----------------+
1 row in set (0.00 sec)

select geoname_id, registered_country_geoname_id, represented_country_geoname_id,postal_code, latitude, longitude, accuracy_radius
from (select *from geoip2_networkwhere inet6_aton('127.0.0.1') >= network_startorder by network_start desclimit 1
) net
where inet6_aton('127.0.0.1') <= network_end;

Empty set (0.00 sec)

使用该构造可以为所有地址提供良好的查询性能，无论 GeoIP2 数据库是否包含有关这些地址的任何信息。根据您的应用程序，您可能需要考虑将这种复杂性/冗长性封装在一个函数中。或者，MySQL 还提供可用于实现类似性能的空间数据类型，同时允许更自然地表达查询。

可选：创建位置数据表

如果postal_code、latitude、longitude和accuracy_radius是我们感兴趣的所有内容，那么到此为止，我们的应用程序将能够轻松查询所需的内容。但是，GeoIP2 数据库提供了额外的位置信息。请注意geoname_id我们网络表中的字段。此字段可用于从我们之前下载的文件中查找有关地理位置的其他信息Locations。接下来我们将这些数据加载到 MySQL 中。

位置表架构

我们首先像以前一样创建一个表。与文件一样Blocks，GeoIP2 和 GeoLite2 城市位置文件的架构可以在数据库文档的 CSV 部分中找到。

我们将此表命名为geoip2_location：

create table geoip2_location (geoname_id int not null,locale_code text not null,continent_code text,continent_name text,country_iso_code text,country_name text,subdivision_1_iso_code text,subdivision_1_name text,subdivision_2_iso_code text,subdivision_2_name text,city_name text,metro_code int,time_zone text,is_in_european_union bool,primary key (geoname_id, locale_code(5))
);

将数据加载到位置表中

然后，我们geoip2_location从LocationsCSV 文件填充表格。在本例中，我们将从填充表格GeoIP2-City-Locations-en.csv。使用带有后缀的文件-en将为我们提供英文的地理位置名称：

导入城市CSV

load data infile '/var/lib/mysql-files/GeoIP2-City-Locations-en.csv'
into table geoip2_location
fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows (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,is_in_european_union
)
set country_iso_code = nullif(@country_iso_code, ''),country_name = nullif(@country_name, ''),subdivision_1_iso_code = nullif(@subdivision_1_iso_code, ''),subdivision_1_name = nullif(@subdivision_1_name, ''),subdivision_2_iso_code = nullif(@subdivision_2_iso_code, ''),subdivision_2_name = nullif(@subdivision_2_name, ''),city_name = nullif(@city_name, ''),metro_code = nullif(@metro_code, ''),time_zone = nullif(@time_zone, '');

导入国家

load data infile '/var/maxMind/GeoLite2-Country-CSV_20240614/GeoLite2-Country-Locations-en.csv'
into table geoip2_locationfields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows (geoname_id, locale_code, continent_code, continent_name,@country_iso_code, @country_name,is_in_european_union
)
set country_iso_code = nullif(@country_iso_code, ''),country_name = nullif(@country_name, '');

请注意，有许多不同的Locations文件可用。其他具有不同语言后缀的文件包含-en 某些geoname_ids 的不同语言的本地化版本数据。根据应用程序的需求，您可以决定将其他Locations文件导入本地化表。例如，您可以加载GeoIP2-City-Locations-en.csv 到名为的表中geoip2_location-en，并加载 GeoIP2-City-Locations-zh-CN.csv到名为的表中geoip2_location-zh。然后，您可以分别查询您需要的英语或中文位置表。

查询我们的表格

我们现在可以使用我们的geoip2_location表来解析表geoname_id提供的内容geoip2_network。例如：

select latitude, longitude, accuracy_radius, continent_name, country_name, subdivision_1_name, city_name
from (select *from geoip2_networkwhere inet6_aton('214.0.0.0') >= network_startorder by network_start desclimit 1
) net
left join geoip2_location location on (net.geoname_id = location.geoname_id and location.locale_code = 'en'
)
where inet6_aton('214.0.0.0') <= network_end;

+----------+-----------+-----------------+----------------+---------------+--------------------+-----------+
| latitude | longitude | accuracy_radius | continent_name | country_name  | subdivision_1_name | city_name |
+----------+-----------+-----------------+----------------+---------------+--------------------+-----------+
|   37.751 |   -97.822 |            1000 | North America  | United States | NULL               | NULL      |
+----------+-----------+-----------------+----------------+---------------+--------------------+-----------+
1 row in set (0.00 sec)

这里我们只对英语结果感兴趣，但如果我们对不同或其他语言感兴趣，我们可以调整我们的连接条件。

请注意左外连接是如何使用的。这是因为我们的geoip2_network 表的任何给定行可能都没有可用的附加位置信息。例如，某些 IP 地址无法解析为城市或分区。如果可用，使用左连接我们仍会收到latitude、longitude和 accuracy_radius作为查询结果，而如果没有可用的附加位置信息，则内连接将导致零行。

除了geoname_id提供网络位置信息的列之外，还有和registered_country_geoname_id， represented_country_geoname_id分别提供有关 ISP 注册网络的国家/地区和 IP 地址用户所代表的国家/地区的位置信息。两者的位置数据都可以通过其他连接来包含：

select latitude, longitude, accuracy_radius,location.continent_name as location_continent_name,location.country_name as location_country_name,location.subdivision_1_name as location_subdivision_1_name,location.city_name as location_city_name,registered_country.continent_name as registered_country_continent_name,registered_country.country_name as registered_country_country_name,represented_country.continent_name as represented_country_continent_name,represented_country.country_name as represented_country_country_name
from (select *from geoip2_networkwhere inet6_aton('214.0.0.0') >= network_startorder by network_start desclimit 1
) net
left join geoip2_location location on (net.geoname_id = location.geoname_id and location.locale_code = 'en'
)
left join geoip2_location registered_country on (net.registered_country_geoname_id = registered_country.geoname_idand registered_country.locale_code = 'en'
)
left join geoip2_location represented_country on (net.represented_country_geoname_id = represented_country.geoname_idand represented_country.locale_code = 'en'
)
where inet6_aton('214.0.0.0') <= network_end;

+----------+-----------+-----------------+-------------------------+-----------------------+-----------------------------+--------------------+-----------------------------------+---------------------------------+------------------------------------+----------------------------------+
| latitude | longitude | accuracy_radius | location_continent_name | location_country_name | location_subdivision_1_name | location_city_name | registered_country_continent_name | registered_country_country_name | represented_country_continent_name | represented_country_country_name |
+----------+-----------+-----------------+-------------------------+-----------------------+-----------------------------+--------------------+-----------------------------------+---------------------------------+------------------------------------+----------------------------------+
|   37.751 |   -97.822 |            1000 | North America           | United States         | NULL                        | NULL               | North America                     | United States                   | NULL                               | NULL                             |
+----------+-----------+-----------------+-------------------------+-----------------------+-----------------------------+--------------------+-----------------------------------+---------------------------------+------------------------------------+----------------------------------+
1 row in set (0.00 sec)

node 使用

github 文档地址https://github.com/maxmind/GeoIP2-node#city-example

1. 导入模块

npm install @maxmind/geoip2-node

2. node使用

import { Reader } from "@maxmind/geoip2-node";const path = require("path");
const databasePath = path.resolve(__dirname, "../../../GeoLite2-Country.mmdb");export default class IpCenterV2 {//获取国家code
public async getIP(ip: any) {const result = Reader.open(databasePath).then((reader) => {return reader.country(ip)?.country?.isoCode;}).catch((error) => {console.error("Error opening MMDB:", error);return undefined;});return result || undefined;}}#调用const bIpDb = new IpCenterV2();et res = await bIpDb.getCountryISOCode(ip);

官方文档地址：