Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Import MAXMIND GEOLITE COUNTRY csv format into mysql .
--
-- this sql structure is for knowledgebase or educational only.
-- please do this on your localhost first before ypu test on your server
--
-- @author : awan
--
-- github : https://github.com/aufa/
--
-- @description this sql is for importing the MaxminD GeoLite Country to mysql data
-- first! we must download the csv format on maxmind developer site :http://dev.maxmind.com/geoip/legacy/geolite/
-- choose the CSV / zip , and extract it on your Computer drive
--
-- --------------------------------------------------------
-- creating the table
--
-- Table structure for table `Maxmind_geoIP`
--
-- /// MAXMIND GEOIP
CREATE TABLE IF NOT EXISTS `Maxmind_geoIP` (
`id` INT(1) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, -- the id just for numeric
`maxmind_ipstart` VARCHAR(50) COLLATE UTF8_GENERAL_CI NOT NULL, -- the ip start from maxmind data
`maxmind_ipend` VARCHAR(50) COLLATE UTF8_GENERAL_CI NOT NULL, -- the ip end of maxmind data
`maxmind_locid_start` INT(1) UNSIGNED ZEROFILL NOT NULL, -- the start of maxmind location id
`maxmind_locid_end` INT(1) UNSIGNED ZEROFILL NOT NULL, -- the end of maxmind location id
`maxmind_country_code` VARCHAR(4) COLLATE UTF8_GENERAL_CI NOT NULL, -- the country code
`maxmind_country` VARCHAR(100) COLLATE UTF8_GENERAL_CI NOT NULL, -- the country name
PRIMARY KEY( `id`,`maxmind_ipstart`,`maxmind_ipend`, `maxmind_locid_end`, `maxmind_country` )
) DEFAULT CHARSET=UTF8 COLLATE=UTF8_GENERAL_CI AUTO_INCREMENT=1 ;
--
-- IMPORT THE CSV DATA with load data
LOAD DATA INFILE 'E:\\PROJECT\\GeoIPCountryWhois.csv' INTO TABLE `Maxmind_geoIP` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'(
`maxmind_ipstart` , `maxmind_ipend` , `maxmind_locid_start` , `maxmind_locid_end` , `maxmind_country_code` , `maxmind_country`
);
-- IMPORTANT
-- please change the 'E:\\PROJECT\\GeoIPCountryWhois.csv' with your path of the GEOLITE COuntry CSV file.
-- if on windows the directory separator is backslash , so .. you must fill with double backslash on the file structures like
-- the file import.
-- example if you have on linux os , and your file of GeoIPCountryWhois.csv , is on /home/admin/ directory
-- just change the 'E:\\PROJECT\\GeoIPCountryWhois.csv' with : '/home/admin/GeoIPCountryWhois.csv'
@timersys

This comment has been minimized.

Copy link

timersys commented Oct 28, 2014

Hi Aufa thanks for the script. Could you please add some sample queries on how to make this work?

@SimonEast

This comment has been minimized.

Copy link

SimonEast commented Feb 19, 2016

After importing the data, you can make a query like this...

SELECT * FROM Maxmind_geoIP
WHERE maxmind_locid_start <= INET_ATON('203.11.201.12') 
AND   maxmind_locid_end   >= INET_ATON('203.11.201.12');

It's inaccurate to call those columns "locid". They are actually a numerical representation of the IP address which is necessary to perform >= and <= searches.

@StayAt

This comment has been minimized.

Copy link

StayAt commented Jul 31, 2016

Thanks this was very helpful http://stayat.org/bootstrap05.nsf/Country.xsp

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.