load geoip database into mysql

db-setup.sql

DROP TABLE IF EXISTS location;

CREATE TABLE IF NOT EXISTS `location` (
  `locId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(2) DEFAULT NULL,
  `region` varchar(2) DEFAULT NULL,
  `city` varchar(1000) DEFAULT NULL,
  `postalCode` varchar(10) DEFAULT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `metroCode` int(11) DEFAULT NULL,
  `areaCode` int(11) DEFAULT NULL,
  PRIMARY KEY (`locId`),
  KEY `city` (`city`(767))
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS blocks;

CREATE TABLE `blocks` (
  `startIpNum` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `endIpNum` int(11) DEFAULT NULL,
  `locId` int(11) DEFAULT NULL,
  PRIMARY KEY (`startIpNum`)
) ENGINE=InnoDB AUTO_INCREMENT=3758096129 DEFAULT CHARSET=latin1;

update.sh

DATABASE=geoip
DB_USER=root
mysql -u $DB_USER $DATABASE < db-setup.sql
wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip
unzip -j GeoLiteCity-latest.zip
mv GeoLiteCity-Location.csv location.csv
mv GeoLiteCity-Blocks.csv blocks.csv
mysqlimport -u $DB_USER --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local $DATABASE location.csv
mysqlimport -u $DB_USER --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local $DATABASE blocks.csv
rm GeoLiteCity-latest.zip

Hi, Thanks for sharing this information, it helped me solve a problem. I was trying to import the New version of the Maxmind Geoip Database, which uses IP/CIDR notation instead of 'start' and 'end' IP addresses. This really helped. My solution is here: http://davidkane.net/installing-new-geoip-database-sql-database/

Many thanks.

I am trying to run through this code for translating ip to user agent, lat, long, zip code - but the join is slowing down overall processing. If we have 5M rows to process in hourly buckets. One option i am thinking is to run outer join ahead of my processing and use that combined data set for lookups, are there other options that could be considered? OR do you see any issues with the join planned?

-Thanks

This gist is obsolete and doesn't work.

Yeap, MySQL 8 doesn't work