load geoip database into mysql

db-setup.sql

DROP TABLE IF EXISTS location;

CREATE TABLE `location` (
  `geoname_id` bigint(20) NOT NULL,
  `locale_code` varchar(5) DEFAULT NULL,
  `continent_code` varchar(5) DEFAULT NULL,
  `continent_name` varchar(50) DEFAULT NULL,
  `country_iso_code` varchar(5) DEFAULT NULL,
  `country_name` varchar(50) DEFAULT NULL,
  `subdivision_1_iso_code` varchar(5) DEFAULT NULL,
  `subdivision_1_name` varchar(50) DEFAULT NULL,
  `subdivision_2_iso_code` varchar(5) DEFAULT NULL,
  `subdivision_2_name` varchar(50) DEFAULT NULL,
  `city_name` varchar(100) DEFAULT NULL,
  `metro_code` varchar(50) DEFAULT NULL,
  `time_zone` varchar(100) DEFAULT NULL,
  `is_in_european_union` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`geoname_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS blocks;

CREATE TABLE `blocks` (
  `network` varchar(25) NOT NULL,
  `geoname_id` bigint(20) DEFAULT NULL,
  `registered_country_geoname_id` bigint(20) DEFAULT NULL,
  `represented_country_geoname_id,` bigint(20) DEFAULT NULL,
  `is_anonymous_proxy` tinyint(1) DEFAULT NULL,
  `is_satellite_provider` tinyint(1) DEFAULT NULL,
  `postal_code` varchar(30) DEFAULT NULL,
  `latitude` varchar(30) DEFAULT NULL,
  `longitude` varchar(30) DEFAULT NULL,
  `accuracy_radius` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`network`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

update.sh

DATABASE=geoip
DB_USER=root
mysql -u $DB_USER -p $DATABASE < db-setup.sql
wget https://geolite.maxmind.com/download/geoip/database/GeoLite2-City-CSV.zip
unzip -j GeoLite2-City-CSV.zip
mv GeoLite2-City-Locations-en.csv location.csv
mv GeoLite2-City-Blocks-IPv4.csv blocks.csv
mysqlimport -u $DB_USER -p --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local $DATABASE location.csv
mysqlimport -u $DB_USER -p --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local $DATABASE blocks.csv
rm GeoLite2-City-CSV.zip

Inspired by gka/db-setup.sql
Updated to 2019-04-29