script to load GeoLite2 Database into MySQL

loadGeoIp.sh

#!/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