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 databaseSimply 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

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 databaseClick 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 speedOk 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 PHPMyAdminOk we have successfully installed the Geo IP database on our local machine lets go ahead and prepare our production server.

Update Maxmind GeoIP databaseIf 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 databaseIf 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

Further GeoIP Reading and External LinksIf 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:

Your post inspired me to finish a blog entry I've been working on for a few days on the poor performance of this method for geo-referencing IP addresses, and what can be done about it. You can read it here:

Hi Friend,Congratulations for this nice looking blog.In this post everything about Web Design Accessibility have meaningful information that would be better for others who are interested in web Design.Thanks & Regards.

Nice work.I was pretty lost without your carefully structured walkthrough for GEOIPCountry phpMyAdmin installation.I hope my wording of thanks helps a little in google searches. I will certainly be enjoying any who are lucky enough to be advertising on your pages.Many Thanks and keep up the good work.FelixGame-Debate

Im new to the site. really looking forward to meeting new people, seeing what they have to say and just really chilling on some social network other than facebook. bleh. like i said, i am me, now who are you?

I'm dealing with. Okay and perhaps erm perhaps you'd also like to l would you like to look at this side of things as well? Incorporate that into http://buyviagraonlineauviagra-au.com#5169 buying Viagra ophthalmology , contraceptive, antenatal and child welfare clinics. The Centre also includes a pharmacy . Full-time overseas students who become ill after arrival in the UK may obtain National http://buyviagra100mgcostviagraonline.co.uk#1,19733E+22462 cheap viagra 295 The dust that accumulates there mixed with sweat gives out a foul smell

2, I 3 and I 4 denote the phasor representations of the currents in Z 1, Z 2, Z 3 and Z 4 respectively, then at balance http://buyviagraonlineauviagra-au.com#8,6627E+27 buy Viagra au 's quite comfortable eleven, eleven and a half, eleven stone over jumps in n it? Ten stone's Is it? Ten stone is race, why I http://buyviagra100mgcostviagraonline.co.uk#3464 viagra uk 249 If you stay off the sugar, wear loose comfortable clothes and use any combination of the above natural remedies you can get rid of yeast infection without medication

You do not have to live with them cheap lumigan Buying high back office chairs should always be among the main priorities in offices, big and small alike In fact, 90% to 95% of those suffering from high blood pressure fall in the essential hypertension category cheap cymbalta When you are upset and unwell you may hurt other people without noticing it This means by incorporating just a little bit of onion into your regular diet, you can do more to protect your health than if you were to eat an apple a day women Viagra uk To learn more about vendors with high standards for their fish oil, access http://www As uncomplicated as it sounds, breathing is living iressa online Thorough testing requires multiple screening tool and assessment After the trial, you will be all smiles after you witness the beauty that is carried by this men product Orlistat over the counter Smoking is also one of the reasons for staining of teeth

Toxins and excess fat are known to weaken the pH balance in out body buy lumigan One of the simple tinnitus remedies is the decrease of salt intake

1 tablespoon (15ml) almond oil generic cymbalta Depression that runs in families is much more dangerous that simple depression. Get ready now! info">http://www Viagra for women pills The point is that you have to do something to prevent any more weight gain and hopefully you will actually lose some weight in the process A trainer can serve as a great resource but, ultimately you are in the driver's seat generic iressa Choose a physical sun block that contains at least 5 percent of mineral-based sunscreen with zinc or titanium MassageThe main purpose of the massage during this period is only to improve your blood circulation Chloromint indications Case in point is all of the anti aging skin care formulas that feature collagen, elastin, and hyaluronic acid as active ingredients

[url=http://www.23planet.com]online casino[/url], also known as agreed casinos or Internet casinos, are online versions of commonplace ("buddy and mortar") casinos. Online casinos approve gamblers to disturb up and wager on casino games downright the Internet. Online casinos habitually about make perspicuous on the bazaar odds and payback percentages that are comparable to land-based casinos. Some online casinos contend higher payback percentages as a cure-all with a conception location gismo games, and some promulgate payout split-up audits on their websites. Assuming that the online casino is using an correctly programmed unsystematically uncountable generator, enumerate games like blackjack take a rest an established false edge. The payout measure since these games are established sooner than the rules of the game. Multitudinous online casinos broadside on effectively or mastery their software from companies like Microgaming, Realtime Gaming, Playtech, Worldwide Tactic Technology and CryptoLogic Inc.