Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
script to load GeoLite2 Database into MySQL
#!/usr/bin/env bash
#apt-get install -y unzip
wget http://geolite.maxmind.com/download/geoip/database/GeoLite2-City-CSV.zip
unzip GeoLite2-City-CSV.zip
cd GeoLite2-City-CSV_*
mv GeoLite2-City-Blocks-IPv4.csv /tmp/GeoLite2-City-Blocks-IPv4.csv
mv GeoLite2-City-Locations-en.csv /tmp/GeoLite2-City-Locations-en.csv
mysqladmin create geoip
mysql geoip << EndSQL
DROP TABLE IF EXISTS geoipBlocks;
CREATE TABLE geoipBlocks (
from_ip INT(10) UNSIGNED NOT NULL,
to_ip INT(10) UNSIGNED NOT NULL,
network VARCHAR(18) NOT NULL,
geoname_id MEDIUMINT(8) UNSIGNED NOT NULL,
registered_country_geoname_id MEDIUMINT(7) UNSIGNED NOT NULL,
represented_country_geoname_id MEDIUMINT(7) UNSIGNED NOT NULL,
is_anonymous_proxy TINYINT(1) UNSIGNED NOT NULL,
is_satellite_provider TINYINT(1) UNSIGNED NOT NULL,
postal_code VARCHAR(8),
latitude FLOAT NOT NULL,
longitude FLOAT NOT NULL,
accuracy_radius SMALLINT(4) UNSIGNED NOT NULL
);
DROP TABLE IF EXISTS geoipLocs;
CREATE TABLE geoipLocs (
geoname_id MEDIUMINT(8) UNSIGNED NOT NULL,
locale_code ENUM ('en') NOT NULL,
continent_code ENUM ('AF', 'AN', 'AS', 'EU', 'NA', 'OC', 'SA') NOT NULL,
continent_name ENUM ('Africa', 'Antarctica', 'Asia', 'Europe', 'North America', 'Oceania', 'South America') NOT NULL,
country_iso_code CHAR(2),
country_name VARCHAR(44),
subdivision_1_iso_code CHAR(3),
subdivision_1_name VARCHAR(72),
subdivision_2_iso_code CHAR(3),
subdivision_2_name VARCHAR(38),
city_name VARCHAR(49) NOT NULL,
metro_code CHAR(3),
time_zone VARCHAR(30)
);
TRUNCATE geoipBlocks;
LOAD DATA INFILE '/tmp/GeoLite2-City-Blocks-IPv4.csv'
INTO TABLE geoipBlocks
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
(@var1, geoname_id, registered_country_geoname_id, represented_country_geoname_id, is_anonymous_proxy, is_satellite_provider, postal_code, latitude, longitude, accuracy_radius)
SET network = @var1,
from_ip = INET_ATON(SUBSTRING_INDEX(@var1, '/', 1))
& 0xffffffff ^ ((0x1 << (32 - SUBSTRING_INDEX(@var1, '/', -1))) - 1),
to_ip = INET_ATON(SUBSTRING_INDEX(@var1, '/', 1))
| ((0x100000000 >> SUBSTRING_INDEX(@var1, '/', -1)) - 1);
TRUNCATE geoipLocs;
LOAD DATA INFILE '/tmp/GeoLite2-City-Locations-en.csv'
INTO TABLE geoipLocs
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES;
ALTER TABLE geoipBlocks
ADD INDEX ipx (from_ip, to_ip);
ALTER TABLE geoipLocs
ADD UNIQUE INDEX geoname_idx (geoname_id);
EndSQL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment