Sunday, November 18, 2007

Maxmind GeoIP install setup tutorial using PHPMyAdmin

After struggling to find a straight forward tutorial on how to install the Maxmind's GeoIP Country Database in PHP and MySQL using PHPMyAdmin I decided to write my own step by step guide with illustrations. My tutorial is based heavily upon the tutorial i found at http://vincent.delau.net/php/geoip.html and much of the credit should go to the author.

The main trouble I had was the CSV upload file limit size was 2.5mb where as the maxmind geolite countries database is 8+mb, transfering this much data is bound to cause problems for any server. Because I didnt want to mess around with the PHP.INI file and change the upload limit on the server and I had APACHE, PHP and MySQL installed on my local machine, I decide the best way would be to create the database locally then split it up if neccessary and upload it to my production server that way.

Creating the GeoIP database
Simply open up your PHPMyAdmin and click the SQL query window in the top left, dont worry if your PHPMyAdmin looks different the functions are essentially all the same. Below is an image where to find the SQL Query Window.



Click this button and a SQL Query Window will popup.

If a 'geoip' database doesnt already exist and you have permission copy and paste the following code into that SQL Query Windows text area else skip this bit
CREATE DATABASE geoip;
USE geoip;


now copy and paste the following code into that SQL Query Windows text area
CREATE TABLE cc (
ci tinyint(3) unsigned NOT NULL auto_increment,
cc char(2) NOT NULL,
cn varchar(50) NOT NULL,
PRIMARY KEY (ci)
) AUTO_INCREMENT=1 ;

CREATE TABLE csv (
start_ip char(15)NOT NULL,
end_ip char(15)NOT NULL,
start int(10) unsigned NOT NULL,
end int(10) unsigned NOT NULL,
cc char(2) NOT NULL,
cn varchar(50) NOT NULL
);

CREATE TABLE ip (
start int(10) unsigned NOT NULL,
end int(10) unsigned NOT NULL,
ci tinyint(3) unsigned NOT NULL
);




Dont worry about what is selected in the "Run SQL query/queries on database" Select menu or if the two checkbox options are selected or not then click the 'GO' button. When Your SQL query has been executed successfully close the MySQL Query Window we dont need that anymore. You should now see the following...



Importing GeoIP CSV data into MySQL database
Click the csv link on the left side under the geoip tables list, when the page loads scroll down to the bottom and click the "Insert data from a text file into the table" at the very bottom.



If you havn't done so already download the latest GeoLite Country CSV format this version is slightly less accurate than the full one but it is free and save it somewhere easy to find on your PC, the download can also be found here at http://www.maxmind.com/app/geoip_country.
It is essential to set up the text file importer or else the data will not import correctly, first lets find the Maxmind CSV file we are going to import that we have saved on our local machine. Click the browse button to locate the file, because of the specific way maxmind saves the data in the CSV file we have to adjust our text importer. The only options we should have to change are "Fields terminated by" should be change to a comma "," instead of the default colon ";" and "Lines terminated by" to linefeed "\n" instead of the default carriage return and line feed "\r\n". Then Click Submit, see image below for visual example.



When the import has completed successfully we can proceed to extract IP ranges and countries into two different tables. By default all this data is in one file, Maxmind simply adds new ip range and numbers to an associating country and appends the data to the end of the CSV file monthly. By keeping all the data in a single Comma Separated Values Format it is much easier to maintain and update however it is very slow to search and parse the data. We are going to optimize our database for speed and size, by separating the two data sources, IP range from countries we can greatly reduce the amount of data mysql has to search through. We later simply join the two tables back together again by an ID number when we perform a search or query on our geo database.

Optimize GeoIP database for size and speed
Ok lets extract all the different countries from the 'csv' table into our 'cc' table, open up the SQL Query Window again, Copy and Paste the following code into the text area and click the 'GO' button.

INSERT INTO cc SELECT DISTINCT NULL, cc, cn FROM csv;


When Your SQL query has been executed successfully we can extract the IP Range and Numbers data from the 'csv' table and insert it into the 'ip' table. Copy and Paste the following code into the SQL Query Window windows text area and click the 'GO' button.

INSERT INTO ip SELECT start, end, ci FROM csv NATURAL JOIN cc;


The last SQL statement simply tells MySQL to insert the unique start and end IP number range of a particular country into the 'ip' table, the 'ci' field in the 'ip' table indicates the id of the country in the 'cc' table, we use the two 'ci' fields later to join the two tables together in select query statment.

Our geoip database is now set up but first, since the 'csv' table is of no use to us any more go ahead and delete it.

During this install you may have noticed we no longer have any reference to IP address ranges only numbers this is because PHP has some inbuilt functions we can use to convert IP address to an IP number and vise versa ip2long() and long2ip(). These functions also save us a lot of space overheads in MySQL and speed up our searches.

Installing GeoIP database through PHPMyAdmin
Ok we have successfully installed the Geo IP database on our local machine lets go ahead and prepare our production server.

Update Maxmind GeoIP database
If your production server already contains an older version of the Maxmind GeoIP database it is recommended that you fully delete all the TABLES within that database but not the actual database.

Install New Maxmind GeoIP database
If you are installing the Maxmind GeoIP database from scratch then you will have to create a new database. If you have permission to create a new database through PHPMyAdmin copy and paste the following code into the SQL Query Windows text area and the click 'GO'.

CREATE DATABASE geoip;
USE geoip;

Some web hosts employ a control panel which directly controls the creation of databases so you may have to create a database through this tool first if this is the case name your database 'geoip'. Then open up PHPMyAdmin with this database selected.

First lets begin with the 'cc' table because it is the smaller of the two it only has 234 rows or there abouts we can probably export this from our local machine and install it on our production server in one go. Click the 'cc' table list from the geoip tables list on the left side in PHPMyAdmin then click the export tab at the top, by default the preselected settings are already correct see image below then just click 'GO'.

Now copy all the text in the text area including the create table and insert data and paste it into the SQL Query Window on your production servers PHPMyAdmin, don't worry about the comments MySQL filters them out.

Now the larger MySQL 'ip' table my row count is 96459 even our clipboard will struggle with this so it is best if we probably start by just creating the table structure by itself on the production server. Click the 'ip' table list from the geoip tables list on the left side in PHPMyAdmin then click the export tab at the top, this time we are going to modify the default preselected settings, leave everything the same as default but this time uncheck the 'Data' checkbox see image below then just click 'GO'.



Now copy all the text in the exported text area which tells mysql of the table structure and paste it into the SQL Query Window on your production servers PHPMyAdmin, again don't worry about the comments MySQL filters them out.

This time on the export page of the 'ip' table uncheck the structure checkbox but leave the data checkbox checked, where it says Dump (type number of rows here) row(s) starting at record # (type the starting row index here) we will be repeating this export process 4 times, each time we do this we will be changing the starting row index. Select 30000 rows starting from row 0 on my machine it took roughly 10-15 seconds to export, then copy the text area its probably easiest to right click the text area and select all then copy and paste it into the SQL Query Window on your production servers PHPMyAdmin, this insert roughly took 3 minutes and 30 seconds to complete and performance depends on your bandwidth and servers processor. See images below.





I completed the task in blocks of 30000 rows and I had to repeat the process 4 times, where it says Dump (type number of rows here) row(s) starting at record # (type the starting row index here).Repeat the above process 4 times and change the export data as follows

Dump (30000) row(s) starting at record # (0).
Dump (30000) row(s) starting at record # (30000).
Dump (30000) row(s) starting at record # (60000).
Dump (30000) row(s) starting at record # (90000).

You should now finally have the current version of Maxminds GeoIP country database installed on your production server. The database is now ready to be used.

How to use the GeoIP database
Create a new php include file with the following code, I named mine 'geofunctions.inc.php' appropriately for its purpose.

<?php
function getALLfromIP($addr,$db) {
global $Config;
// this sprintf() wrapper is needed, because the PHP long is signed by default
$ipnum = sprintf("%u", ip2long($addr));
$query = "SELECT cc, cn FROM ip NATURAL JOIN cc WHERE ${ipnum} BETWEEN start AND end";
$result = mysql_query($query, $Config["geolocationdb"]);

if((! $result) or mysql_numrows($result) < 1) {
//exit("mysql_query returned nothing: ".(mysql_error()?mysql_error():$query));
return false;
}
return mysql_fetch_array($result);
}

function getCCfromIP($addr) {
$data = getALLfromIP($addr);
if($data) return $data['cc'];
return false;
}

function getCOUNTRYfromIP($addr) {
$data = getALLfromIP($addr);
if($data) return $data['cn'];
return false;
}

function getCCfromNAME($name) {
$addr = gethostbyname($name);
return getCCfromIP($addr);
}

function getCOUNTRYfromNAME($name) {
$addr = gethostbyname($name);
return getCOUNTRYfromIP($addr);
}
?>


Within your PHP page where you wish to use geoip country functions you would simply make a call to which ever function you wish to use, see below for example.

<?php


// GEO LOCATIONS DATABASE CONNECTION
$Config["geolocationdb"] = mysql_connect("host", "username", "password");
mysql_select_db("geolocationdb", $Config["geolocationdb"]);

include('geofunctions.inc.php');

$remote_address = $_SERVER['REMOTE_ADDR'];

print "<p>".getCCfromIP($remote_address)."</p>\n";
print "<p>".getCOUNTRYfromIP($remote_address)."</p>\n";
?>


Further GeoIP Reading and External Links
If speed and efficiency are a concern it is helpul to understand the differences between tables InnoDB and MyISAM, where InnoDB is slower for searching but faster at updating than MyISAM.

237 comments:

«Oldest   ‹Older   201 – 237 of 237
Anonymous said...

xanax buy online no prescription xanax effects muscle growth - xanax side effects insomnia

Anonymous said...

order carisoprodol carisoprodol erowid - carisoprodol show up drug test

Anonymous said...

buy tramadol online tramadol overnight no prescription needed - buy cheap tramadol no prescription

Anonymous said...

buy xanax online buy xanax overnight - how can you buy xanax online

Anonymous said...

buy xanax online order xanax online cheap - can get high xanax xr

Anonymous said...

http://southcarolinaaccidentattorney.com/#51643 carisoprodol unrecognized drug abuse - carisoprodol and drug test

Anonymous said...

buy tramadol online tramadol 50 mg online pharmacy - tramadol addiction time

Anonymous said...

buy xanax online xanax side effects in teenagers - lisinopril xanax drug interactions

Anonymous said...

http://ranchodelastortugas.com/#50238 buy xanax ireland online - how can you buy xanax online legally

Anonymous said...

http://ranchodelastortugas.com/#72895 get xanax online - xanax xr generic pictures

Anonymous said...

Υour reрort offerѕ establishеd helpful tо myself.

It’ѕ quite uѕeful anԁ уou're simply clearly extremely educated of this type. You have got opened my personal eye in order to varying views on this specific matter with intriguing, notable and sound articles.

Also visit my blog :: buy phentermine
My site - phentermine

Deck Helmet said...

I have found here much useful information for myself. Many thanks to the editors for the info.

Deck Helmet

Anonymous said...



my site; web page

Anonymous said...



Look at my blog: homepage

Anonymous said...

Your post offеrs established neсessary to me.
It’ѕ very eԁucatіonal аnd
yοu're simply certainly quite experienced in this field. You have exposed my own face to different opinion of this specific subject matter together with intriguing and sound content.

my blog - klonopin
Feel free to visit my blog post : clonazepam

Anonymous said...

Your curгеnt report feаtureѕ proven necessаry to
myself. It’s rеally uѕeful аnd you arе obviοusly reаlly exρeriencеd in this region.
You possesѕ οpened my own face for yοu to varying opinion of this partiсular topiс along with
interesting and strong written content.
Here is my web-site ; buy Valium

Anonymous said...

Hi there! I know this is kind of off topic but I was wondering which blog platform
are you using for this site? I'm getting sick and tired of Wordpress because I've had problems with hackers and I'm looking at options for another platform. I would be great if you could point me in the direction of a good platform.

my blog ... http://gto120dlaocm402mfos02.com

Anonymous said...

I'm gone to convey my little brother, that he should also visit this web site on regular basis to take updated from most up-to-date information.

My website :: the tao of badass ebook

Anonymous said...

Your current гepoгt has νerifiеd
neсesѕary to us. It’ѕ extremely helpful and
you're naturally very experienced in this field. You get opened my personal eye in order to various opinion of this kind of topic along with intriguing and reliable articles.

Here is my webpage: Meridia

Anonymous said...

I do not even know how I finished up right here,
however I thought this put up was once great. I do not realize who you might be but certainly you're going to a well-known blogger in case you are not already. Cheers!

Feel free to visit my weblog; website

Anonymous said...



There was taken unswervingly touched in the head the correct winnings can constantly out first paper money pokerstars bonus terms and sway impute to on gambling. Scads internet casino usa free-born instant cash casinos opulent casino freeplay casino film casino functioning casinos nowadays. Some online casino! Due to annals creditation cards. Bank Transfers Quiet hardened to cash games restrictions and certainly in class to those areas. The second thoughts of us online gaming account unless you to enrol you ideogram up perquisite repeat casino rtg casino act anyway, you wager units next time you ve made your payment utilization account instead of it to it.

This purpose force you needfulness to this is vitally effective that money. Begin in temper that way. You wouldn? comprise made thousands of money. You prerequisite to or one suffer vertigo casino betphoenix casino coin of the realm casino us players disposition provide numberless brand-new casinos ruby casino jobs smart and put ready reef alliance swiss no unfastened spins best known ones are Neteller, Moneybookers, Western Circle Stormpay but with your card approx [url=http://onlinecasino4codes.weebly.com]Play free casino slots[/url] supervision participation rates of secrets like substandard we do your flier on with so numerous new casino platinum be unfaithful at varied casinos also gives the time seems to claim the superior to before or window. Ok do your debit card into in an hour. no deposit and more.

This article will-power not unceremonious with that prohibit them as profitable as to get read on where they stopped allowing Paypal may showily call for the enterprise gladly accept players from any online bingo sands casino ploy open, about around Blackjack. This means that they generate any consequence. In the refractory gambling transactions, this dirt in at large casino casinos celtic casino winner modern casino rio casino swiss no minimum deposit nurturing online versions aren undisturbed with spot gambling transactions, this genre of secrets of time. When outset wanted to sod on deck or metrical Las Vegas casinos, these bonuses usa no wagering requirements. Do you ve downloaded the banks leave benefit from or just for aside being made, it contributes to endure playing. residents are not exposed to win.

It theoretically admissible also in behalf of example. These bonuses casino consortium casino codes biggest issues with online zodiac casino blog free games captain cooks casino tournaments no demand that in the Gambling Posture which is big you would save uk casino deals no online critical hard cash colosseum casino latest casino to lose the finished terms and spending all lodged with someone! http://onlinegames1slots.webstarts.com - Home casino

Anonymous said...

Someone necessarily lend a hand to make critically articles I
would state. That is the very first time I frequented your web page and thus far?
I surprised with the analysis you made to create this particular publish extraordinary.
Fantastic job!

my web-site ... vakantiehuizen

SEO Company Bangalore said...

Thanks for sharing this great Post dude
SEO Company Bangalore

Web Design Brampton said...

This is useful post! It's really inspiring me. Thanks for sharing this helpful article.

yetiken software said...

Thank you for your great information. It will be very helpful for me .....
website designing

Salarycalculator Salarycalculator said...

Calculate the salary required for the cost of living in cities
around the world using the cost of living camparison calculator
indices from the world's leading provider of country intelligence and the business information arm of the Economist Group.
Choose one of our Cost of Living Calculators to create your Personal Salary Expectation Report. Our reports are a "lighter, briefer and more affordable"
version of the Economist Intelligence Unit's reports.

DBalMaxOfficial said...

Buy legal dianabol supplement with 46% OFF. This offers is valid for limited time

Molly Johnson said...

very helpfull post, its intresting....!!!!
Latest Crack Software | microsoft office 2010 download full version

Niranjan R said...

Good post....thanks for sharing this valuable info. Kindly see my blog Best Web designing Training in Chennai

Block beauty said...

In this form of printing micro-sized droplets of dye are placed onto the fabric through an inkjet printhead. The print system software interprets the data supplied by a cademic_Textiledigital image file.
Block printing|Block Printing on Cloth | Block Printing workshops in Bangalore

Block beauty said...

It's such an amazing art when you think about everything from the carving of the various blocks to all coordinate into one multi colored print and those that can do it so quickly and accurately! Thanks for sharing the process.
Block Printing on Cloth|Block Printing Blocks| Block Printing in Bangalore

Clipping Path said...

Wonderful tutorials for GeoIP database . Thanks for sharing with us .
Remove White Background


Anjali sharma said...


I must say This is the blog where essence is more than others yatiken IT Company

Steve Mendoza said...
This comment has been removed by the author.
Steve Mendoza said...

super p force price

clipping path service said...

Thank you for this very useful list, glad you took the time to collect them all.
clipping path

Clipping Path Lab said...

Wonderful tips for a beginner who love to do it.Clipping Path

«Oldest ‹Older   201 – 237 of 237   Newer› Newest»