RSS-Feed anzeigen

jahlives

geolocation mit MYSQL

Bewerten
Im Zuge unseres OpenVPN-Monitor Scriptes bin ich damals auf geolocation gestossen. Damit kann man als Webseitenbetreiber recht einfach die grobe Herkunft einer IP ermitteln. Es gibt sicher viele Anbieter für diese Daten, persönlich verwende ich jedoch die Datenbestände von http://www.maxmind.com/app/geolitecity In der freien Version sind die Daten zwar nicht ganz so exakt, aber es reicht eigentlich für die meisten Fälle.
Die folgende Beschreibung basiert also auf den Daten des obengenannten Anbieters, dürfte sich aber sicherlich anpassen lassen, um Daten anderer Anbieter zu verarbeiten.

1. csv Files herunterladen

zuerst einmal laden wir uns die aktuellsten Datenbestände von http://geolite.maxmind.com/download/...oLiteCity_CSV/ und speichern diese auf der DS. So z.B.
Code:
mkdir /root/geolite && cd /root/geolite
wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity_20120306.zip
unzip ./GeoLiteCity_20120306.zip
mv ./GeoLiteCity_20120306/* ./ && rm -R ./GeoLiteCity_20120306
2. mysql Datenbank vorbereiten

als nächstes muss man die mysql Datenbank vorbereiten d.h. die Tabellen erstellen wo nachher der Import der csv-Files stattfindet
Code:
mysql -uUSER -pPASSWORT
obiges verbindet Euch auf eine mysql Konsole als USER mit PASSWORT. Die folgenden Kommandos werden dann direkt in dieser mysql Konsole abgesetzt
Code:
CREATE DATABASE geodata;
USE geodata;
CREATE TABLE geo_blocks (
  startIpNum int(10) unsigned NOT NULL,
  endIpNum int(10) unsigned NOT NULL,
  locId int(10) unsigned NOT NULL,
  PRIMARY KEY (endIpNum)
);

CREATE TABLE geo_location(
  locId int(10) unsigned NOT NULL,
  country char(2) NOT NULL,
  region char(2) NOT NULL,
  city varchar(50),
  postalCode char(5) NOT NULL,
  latitude float,
  longitude float,
  dmaCode integer,
  areaCode integer,
  PRIMARY KEY (locId)
 );
damit wären die Tabellen angelegt

3. Daten importieren

Wenn die Tabellen erstellt sind muss man sich noch mit Daten befüllen. Wiederum alle Kommandos auf der mysql Konsole
Code:
LOAD DATA LOCAL INFILE '/root/geolite/GeoLiteCity-Blocks.csv'
  INTO TABLE geodata.geo_blocks
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\n';
    
LOAD DATA LOCAL INFILE '/root/geolite/GeoLiteCity-Location.csv'
  INTO TABLE geodata.geo_location
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\n';
das ganze dauert ein Weilchen, denn gerade geo_blocks wird so ca 1.7 Mio Zeilen erhalten :-)

4. IP abfragen

Um jetzt eine IP abzufragen kann man verschiedene Ansätze wählen z.B. via PHP oder direkt mit einer MYSQL-Query auf der Konsole. Ich zeig hier erstmal letzteres und danach noch einen Weg mit PHP

4.1 Mysql-Konsole resp PHPMyAdmin

Code:
SELECT loc.country FROM geodata.geo_location loc
 JOIN geodata.geo_blocks b ON (loc.locId = b.locId)
 WHERE b.endIpNum >= INET_ATON("8.8.8.8")
ORDER BY b.endIpNum
LIMIT 1;
sollte Euch als Resultat "US" also Amerika ausgeben

4.2 Abfragen via PHP

Damit man eine solche Abfrage auch mit PHP machen kann muss man eigentlich nur obige Query mittels PHP an mysql schicken
PHP-Code:
$db mysql_connect 'localhost''USER''PASSWORT' ) or die ( mysql_error () );
mysql_select_db 'geoip'$db ) or die ( mysql_error () );
$sql 'SELECT loc.country FROM geodata.geo_location loc 
JOIN geodata.geo_blocks b ON (loc.locId = b.locId) 
WHERE b.endIpNum >= INET_ATON("8.8.8.8") 
ORDER BY b.endIpNum LIMIT 1'
;
$res mysql_fetch_object mysql_query $sql$db ) );
echo 
$res->country
Dafür dass jedesmal eine DB für jede IP abgefragt werden muss ist das Ganze sehr performant. Eigentlich sogar gefühlt ein wenig schneller als wenn man das binary-dat File vom Hersteller verwendet. Ich frage bei mir pro Aufruf gut 3000 IPs in der DB ab und es ist in Sekundenbruchteilen erledigt.
Kategorien
etc ppt , Linux

Kommentare