GeoIP
wget http://www.maxmind.com/download/geoip/api/c/GeoIP.tar.gz
tar zxvf GeoIP.tar.gz
cd GeoIP-1.4.0/
./configure
make
make check
su
make install
geoiplookup jem.serveftp.com
とすると、
GeoIP Country Edition: JP, Japan
geoipupdate -v
vi /usr/local/etc/GeoIP.conf
geoipupdate というコマンドが付いてきますが、これで更新できるのは MaxMind社が有償で配布しているバージョンのデータベース
wget http://www.maxmind.com/download/geoip/database/GeoIP.dat.gz
gunzip GeoIP.dat.gz
/usr/local/src/geoip/GeoLiteCity-Blocks.csv
/usr/local/src/geoip/GeoLiteCity-Location.csv
CREATE DATABASE geoip;
CREATE TABLE locip (
start_ip INT UNSIGNED NOT NULL,
end_ip INT UNSIGNED NOT NULL,
locId INT UNSIGNED NOT NULL
);
CREATE TABLE lookup (
locId INT UNSIGNED NOT NULL,
country CHAR(15) NOT NULL,
region CHAR(2) NOT NULL,
city CHAR(30) NOT NULL,
postalcode CHAR(15) NOT NULL,
latitude CHAR(15) NOT NULL,
longitude CHAR(15) NOT NULL,
dmaCode CHAR(15) NOT NULL,
areaCode CHAR(15) NOT NULL
);
mysqlimport –delete -L -v -u geo -p -r –fields-terminated-by=’,’ –fields-enclosed-by=’"’ geoip locip.csv
mysql> select count(start_ip) from locip;
+—————–+
| count(start_ip) |
+—————–+
| 2815167 |
+—————–+
1 row in set (0.01 sec)
mysqlimport –delete -L -v -u geo -p -r –fields-terminated-by=’,’ –fields-enclosed-by=’"’ geoip lookup.csv
mysql> use geoip;
mysql> select count(*) from lookup;
+———-+
| count(*) |
+———-+
| 188641 |
+———-+
1 row in set (0.00 sec)
mysql>