代码之家 › 专栏 › 技术社区 › Code_Help

mysql external shell命令,用于从IP地址获取GeoIP信息

geoip external command-line shell mysql

Code_Help · 技术社区 · 7 年前

我在Centos 7上安装了geoiplookup,我想从mysql表中选择一个IP,然后从命令行获取IP地址的位置。

例如,我会打字!geoiplookup’123.45.36.35,并获取城市、国家等信息。

我可以从 MaxMind 然后将其上载到mysql,并运行本文所述的查询 site 或者这个 site 但我不想每月维护和更新另一个表。这是最好的解决方案吗?

这 link 显示了我在fail2ban日志中使用的几个python示例。

我想这样做:

SELECT udf_geoip_lookup(ip) AS 'City',  
     udf_geoip_lookup(ip) AS 'State'
     udf_geoip_lookup(ip) AS 'Region
FROM table-name
 ;

3个函数调用太多。

CREATE FUNCTION `udf_geoip_lookup` (ip varchar(15))
RETURNS varchar(100)
BEGIN

      SET @sql = CONCAT('\! geoiplookup', @ip);
      -- I need to parse the city, region, country etc, somehow.
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;   

RETURN @sql;
END;

非常感谢!

2 回复 | 直到 7 年前

ByteSlinger 7 年前

正如您所提到的,如果要使用geoiplookup实用程序并保持最新的IP信息,您需要定期(每月)从下载并维护IP更新 MaxMind公司 (使用cron作业)。

您可能想做的是调用 curl 实用程序,并从super cool中检索数据 ipinfo.io 网站:

$ curl ipinfo.io/47.144.148.253
{
  "ip": "47.144.148.253",
  "city": "Hermosa Beach",
  "region": "California",
  "country": "US",
  "loc": "33.8622,-118.3990",
  "org": "AS5650 Frontier Communications of America, Inc.",
  "postal": "90254"
}

然后需要解析JSON输出。但您也可以通过ip地址传递GET参数,并准确获取您想要检索的内容。

例如,我跑步 卷曲 使用以下参数:

$ curl ipinfo.io/47.144.148.253/country
US
$ curl ipinfo.io/47.144.148.253/region
California
$ curl ipinfo.io/47.144.148.253/city
Hermosa Beach

因此,您的SQL函数如下所示:

CREATE FUNCTION `curl_lookup_country` (ip varchar(15))
RETURNS varchar(100)
BEGIN
      SET @sql = CONCAT('\! curl', 'ipinfo.io/',@ip,'/country');
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;   // country
RETURN @sql;
END;

CREATE FUNCTION `curl_lookup_region` (ip varchar(15))
RETURNS varchar(100)
BEGIN
      SET @sql = CONCAT('\! curl', 'ipinfo.io/',@ip,'/region');
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;   // region or state
RETURN @sql;
END;

CREATE FUNCTION `curl_lookup_city` (ip varchar(15))
RETURNS varchar(100)
BEGIN
      SET @sql = CONCAT('\! curl', 'ipinfo.io/',@ip,'/city');
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;   // city
RETURN @sql;
END;

您的SQL查询将是:

SELECT curl_lookup_country(ip) AS 'Country',  
     curl_lookup_region(ip) AS 'State'
     curl_lookup_city(ip) AS 'City
FROM table-name

这样,您就不必解析JSON输出。

但我必须警告你 :此做对发出3个HTTP请求每个 IP地址查询。如果它是一个大表,那么为表中的每一行执行3个HTTP curl请求将对性能造成很大的影响。

如果需要多次访问此IP数据,则应考虑在原始表中创建国家、地区和城市列以及IP地址,然后运行UPDATE语句仅更新这些列为空的行,并使用groovy new curl\u lookup\u xxx 功能。

类似这样:

UPDATE table_name
SET country = curl_lookup_country(ip),
    region = curl_lookup_region(ip),
    city = curl_lookup_city(ip)
WHERE ip IS NOT NULL AND
  (country IS NULL OR
   region IS NULL OR
   city IS NULL)

Jonathan Benjamin 3 年前

如果您希望在数据库级别高效地执行此操作,请随时阅读我们最新的博客。

我们基本上将IP范围数据集转换为每八位字节3个键值数据集(没有第四个),并通过多个仓库中的SQL在超过10MM的记录上运行良好。

我们发现,我们的客户大多需要国家或美国各州,我们的数据集仅针对这些特定需求设计。

免责声明:我是这篇文章的作者。